MySQL query cache to the rescue

Dynamic database driven websites are great. You just add your content and the website software grabs that content from the database, applies all the necessary formatting and displays it nicely and consistently to the user.

Until you wind up with thousands of “articles” and a very popular website, as one user complained in a recent forum topic. Googling for “+php +memory_limit” produces over 400,000 results, which shows just how widespread this problem really is.

As one of the users correctly pointed out, this problem is not so much caused by the website software and it’s inability to deal with the high volume of traffic but rather by a poorly configured database.

For instance, our experience shows that tweaking the MySQL query cache can produce performance improvements as high as 400%, so it’s worth spending some time changing these settings from their “default” values:

• query_cache_size
• query_cache_limit
• query_cache_type
• query_cache_min_res_unit

The first two settings are crucial, especially in a shared environment. Anything under 32MB for "query_cache_size" and under 2MB for "query_cache_limit" is probably inadequate for most web applications powered by that ubiquitous combination of Apache, PHP and MySQL.

Posted in Blogs, Database, SQL by Ivan