Tips to Achieving Fast Web Response under Peak Loads

In a web environment, apart from optimizing your queries and your application, what can be done to ensure proper functioning of your system during high load peaks?

This is a very common problem in web development. It happens often even if the queries are optimized relatively well, but the site just does not have enough computing reserve to tackle short term traffic burst problems. As the site starts responding slower, people start pressing reload, thus resulting in more requests, which in the worst case can knock down

Both MySQL and Apache have overload protection, available through their max_connection and MaxChildren settings, respectively. They are mostly used to avoid the system becoming unusable due to overloading. Other approaches are needed to ensure good response times from the system during traffic peaks.

There are several possible solutions, but there is no bullet that is best for all cases. Below is a list of some solutions:

1) Use an architecture with an application server. By using an application server you can easily limit concurrency for some particular operations, as well as block reloads or kill old queries from the same session. If you have time and skills, you can program the same logic even without application server.

2) Make sure MySQL will be the first component that stops accepting new queries. This allows you to print nice error messages if the database is overloaded, instead of locking your scripts with queries that take a very long time to run. Typically you would not want your home/entry page to be inaccessible due to a lot of complex search operations being done in other parts of your web system.

3) Use Multiple Database Servers. This is useful as an additional defense if other limitations fail for some reason. For example, use one database for areas of your site that are important but generate low database load (such as the front page, contact information, and so forth). Use another database for areas that generate high load. Another option is to use a replication solution to do load balancing.

4) Block reloads. Most development systems allow you to use "session" concept, where the remote user is identified in some way. This can be used to block consecutive reloads of the same page. This way you have at least an option to kill the previous query.

5) Kill old/long-running queries.If you're using automatic query generation, there is a good chance that some of them will take an unexpectedly long time compared to hand-optimized queries. For example, searching for keyword "Mb" in a computer store or the family name "Smith" in a people directory. If such queries that a long time to finish, the user may not wait for them. It is also possible that the Web server or scripts may time out. To deal with this, you can kill queries that take too long to finish an inform the user that the search should be refined. This is preferable to simply timing out. If you have timeout in the Web server set to 60 sec, kill queries that take longer than 45 sec, so you still have time to print the error message. Currently MySQL does not allow you a very nice way to do the timeouts, but there is an easy solution to this problem. Add a /* TIMEOUT=3D60 */ comment to SELECT queries. Then write a script that runs "SHOW PROCESSLIST" every few seconds and kills queries that contain the comment and that run longer than the maximum time allowed. Native MySQL support for this is on the way.

6) Limit concurrency for complex operations. As an additional defense against system lockout, this also helps improve system performance. Having too many concurrent queries typically only decreases response times for all users. It reduces overall system performance by making disk access more random, by making CPU and file caches less efficient, and so forth.The optimal number of concurrent queries varies for each system, so you need to do benchmarking to identify your system's limits.

But still the most important action to take is to look at the queries and schema and trying to optimize them.Compared to having even the nicest-sounding error message possible, it is much better if you do not run into such overload situations in the first place. MySQL Support customers have access to MySQL developers to get their schemas and queries optimized the proper way.