How to improve slow web-based system?

1. Try to optimize sql query(s) inside the system. If the query(s) is(are) quite complex, use stored procedure. Stored procedure allow the query processing done at the DB server side rather than at the web server.
2. Upgrade the RAM and hard disk in the DB server. And if you have enough budget upgrade both components at the web server too. Used high speed RAM and hard disk (15K RPM instead of 7.2K RPM). For DB server it is better if you use RAID 1+0 (or commonly known as RAID 10) for high read and write to the disk cluster. For RAM, read the server documentation to optimize the RAM usage and speed.
3. MySQL, MSSQL, Sybase, Oracle and all the latest DBMS allow technique call in-memory database. This technique will load database's tables with most usage into the memory rather than 'sit' in the hard disk. But please bear in mind, in MySQL, as soon as you updated the table content, all the data will need to be loaded back into the memory. You need to test this technique inside your production database since the optimization is not based on 'one size fits all'. FYI, the last time I do this technique, I took around 14 days to make it 'right'. You need to do this each time the server has new database.
4. Do archiving and data cleaning. Most of the time, DBA and sistem developer are too lazy to archive the data or do data cleaning. Admit it, most people don't like to do this thing. It is the most boring and repetitive task ever. But you need to do it. 12 million recordset is much faster to process than 100 million recordset. And please think, do you need all those 100 million recordset? If your organization is NSA, maybe you need. But for most of us, the latest data will be sufficient enough.
5. If you have budget (which I don't) you can take advantage of clustering/load balancing in IIS and Apache. I just read about this last month. Never had chances to try it yet.
6. Apache optimization. Search on Google on how to do this. Most default Apache installation is not optimize towards your need. As in step 3, you need to do this and test in your production system.
7. Free the server memory. Admit it. Current operating system will use most of the server resources. Once in a while, log into the system and free those resources from stubborn software.
8. Optimize those code. 60%-70% of slowness inside a web-based system is because of coding. Try look at those code before put the blame on server and network infrastructure. And get help from server administrators. They know all the errors generated by the system. Although they don't do coding, don't underestimate their coding knowledge. Maybe some of them still remember how to troubleshoot the coding. Be friendly with them. You never know when you will need their help.

There are still other tips and tricks do optimize your web-based system. If your organization is rich enough, hire a consultan to look at your site and give recommendation on how to improve it.

After I post this inside my FB account one of my friend add this :
 1.  The easiest, faster and cheaper way is to optimize the table index. If you want to tune sql query, it will need changes on application. If you tune the index, no need to change the application just need to know what index that need to be optimized.
2. One more thing, adding RAM is useless if you not doing web/DB tuning to utilize the added resources. After adding RAM, maybe you can increase cache (there are a lot of cache types inside DB/web, just Google it to see what you need and to calculate the optimum value)

No comments: