HostsVault BlogHostsVault Blog

MySQL has different caching methods. Most of those are dependent on the storage engine that is used. For example the key buffer caches the indexes for MyISAM tables while the caching of data is left to the OS. InnoDB has the buffer pool for both data and indexes and so on. The query cache however, is independent of the storage engine that is used. Unlike most caches it does not store records or pages of data but complete result sets and the queries that caused those results to be returned. This is a very disputable concept since the way that it works is that if any of the tables used in a result set is modified, the whole cached result set is thrown out of the cache.

If your data is kinda static (not many change)  the query cache can give you an enormous performance boost. It even bypasses the query optimizer so that if the query is complex even more cpu time is saved. Knowing this you can optimize your application by changing complex queries into smaller queries that only use that data that never changes.

Of course there are some tricks to using the query cache. The first one is the size of the query cache. The default is 16MB which isn’t enough for shared-hosting  production servers. However, keep in mind that any memory assigned to the query cache is removed from another cache so it’s very important to strike a good balance. Of course the balance varies from one application to another. The second parameter is the maximum allowed result set size. It really doesn’t do any good to allow 16MB result sets into the cache because it would take only one poorly written query to flush out the entire cache.

So is query cache a good or bad thing? Well, in short, if your cache gets flushed out all the time and only adds to the overhead it’s usually better to assign the memory to storage engine dependent cache. If your data are constantly updating and inserts/updates most of your tables it will invalidate the results in the query cache pretty quickly and assigning memory to it is a waste of resources.

You can use MySQL Tuner for some quick information about the efficiency of the query cache.

Here is a sample config for my.cnf for a busy server so it can handle many requests , this setup is suitable for 4 GB RAM server running MySQL 5 :

[mysqld]
skip-name-resolve
thread_concurrency=4
max_connections=500
max_user_connections=8
key_buffer=512M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=2M
sort_buffer_size=2M
table_cache=1024
thread_cache_size=64
interactive_timeout=20
wait_timeout=15
connect_timeout=8
max_allowed_packet=16M
max_connect_errors=10
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
flush
flush_time=3600
long_query_time = 10
safe-show-database
collation_server=utf8_unicode_ci
character_set_server=utf8

[mysqld_safe]
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout