Over the weekend the Real World web sites went down following a torrent of visitors with the imminent release of Peter Gabriel’s Scratch My Back, which played havoc on our MySQL databases and downed our websites. Our connections maxed out and started to queue, causing any pages requiring a DB connection to refuse to load. After spending the weekend altering MySQL’s settings the average number of simultaneous connections on our MySQL database dropped from 100 to about 2 thanks to the vastly increased speed at which queries are now running. Here’s the top 3 things that were changed to speed things up (keep in mind, our web server currently runs both MySQL and Apache with 8GB RAM):
query_cache_size
The MySQL query cache will cache the result sets of your queries, serving them instead of executing the same queries again. According to MySQL, “Searches for a single row in a single-row table are 238% faster with the query cache than without it.” For a query to return a cached result it must match the cached query exactly, in terms of case and whitespace, and it must not contain subqueries (read this article about Rewriting Subqueries as Joins). This is disabled by default in MySQL, so be sure to enable it first. I set it to 64MB.
query-cache-type = 1 query_cache_size = 64M
key_buffer_size / innodb_buffer_pool_size
The key buffer size is the amount of memory allocated to store table indexes, which can considerably speed up a query as searching for which rows to retrieve may require no disk access. innodb_buffer_pool_size is the innoDB implementation of this setting. On dedicated MySQL servers it is recommended to set this to 75% of the amount of RAM installed. I set key_buffer_size to 256MB as we have few MyISAM tables, and innodb_buffer_pool_size to 1GB so Apache and other services were not adversely affected.
key_buffer_size = 256M innodb_buffer_pool_size = 1G
table_cache
The table cache is the number of tables that will be stored in memory at once, each table being stored in the cache as MySQL accesses it. I set it to 256.
table_cache = 256
helpful apps
MySQL Report will return information on how your server is currently performing and a list of some of the more useful variables. Get MySQL Report here, and get a guide to understand the report here.
MySQL Tuner will analyse your server and provide recommendations as to how different MySQL settings could be altered and any problems that could result from the current configuration. Get MySQL Tuner here.
