MySQL 5.1.69
Modern web applications place a massive load on the database. I’ve been working extensively with Magento lately and the demand on the database is enormous. We could argue all day about which language is easier on the database in terms of access routines and read/write cycles. Ruby (Sinatra)?
Regardless of the application’s structure, we can help MySQL to cope better with a few simple tweaks. Here is one: we are going to enable MySQL’s query cache feature.
The name kinda says it all, but basically, whenever we submit a new query, MySQL will check if it has a cached version, in which case it will serve the cached copy and never hit the actual database. If there is no cached copy, MySQL runs our request against the data as normal and caches the result for future use. This method of query caching can offer us huge performance boosts.
So, let’s turn it on.
I’m using MySQL on the CLI. First, we check if query cache is available and if it is enabled. Log into MySQL and run the following:
1 | mysql> SHOW VARIABLES LIKE '%query_cache%'; |
1 2 3 4 5 6 7 8 9 10 11 | +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 6 rows in set (0.00 sec) |
So query cache is enabled, as in we can use it, but it’s not set – the line query_cache_size is set to 0. Let’s set it so that it starts working for us! There are two ways to do this. We can set it temporarily by issuing a command:
1 | mysql> SET GLOBAL query_cache_size = 64M; |
That will work, but the next time we restart MySQL (or the server), our setting will be scrubbed. So, alternatively we can make this setting permanent. My server is running CentOS 6, so I create a new my.cnf and populate it as follows:
File: /etc/my.cnf
1 2 3 | query_cache_size=64M query_cache_type=1 query_cache_limit=2M |
That’s it, all done. Restart your MySQL service and you are good to go.
1 2 3 | $ service mysqld restart OR $ /etc/init.d/mysqld restart |
If we run the SHOW VARIABLES query again, we’ll get the following:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 67108864 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 6 rows in set (0.00 sec) |
Query caching is enabled and in use. I have used 64 Megabytes as an example and because it is a good middle-of-the-road setting. We don’t want a cache that is too large and gets stale nor do we want it so small that if offers no benefit. If you have loads of RAM and loads of traffic, you might find 128, 256 or even 512 works better for you.
Also, bear in mind this setting is per-server. Multiple databases on the same server will share the allocated RAM across databases.
For more info on the query_cache_type and query_cache_limit, have a look here.
Fly like the wind, web appz!