Setting the query_cache_size in MySQL
So what size do you set the mysql query_cache_size variable to?
The MySQL query cache is an in memory cache that stores the complete result sets of frequent SELECT queries. Instead of issuing the query again to the database engine, MySQL retrieves the result set from the cache and returns that to the client. The query cache is global, in that, all MySQL clients use the same query cache. The query caching functionality is very useful to websites that have a relatively static database and the pages are just a rendering of the data in the database. But query caching can also be useful, to some extent, to websites that have a high number of frequent SELECT statements from tables that change often.
Increasing your query_cache_size will definitely have an impact on performance of a few high frequency queries.
The decision on whether to increase the size of the variable query_cache_size can be easily made using the following mysql command.
First login to mysql using the mysql command line client.
Run the following command:
The Qcache_free_memory shows that currently there is about 58 MB of free memory from the available 256 MB.
However, note that there is another variable called Qcache_lowmem_prunes which indicates how many times MySQL had to prune the query cache (remove some data) to make space for the outputs of other queries. This clearly indicates that increasing your query_cache_size will have a positive impact on performance. Try to increase the size of the cache till you get a very low value of Qcache_lowmem_prunes.
To increase the query_cache_size you will have to adjust your my.cnf/ini.
Add/adjust the following
Then issue this command (setting the cache via cli you will have to use values in multiples of 1024).
512MB = 536870912 bytes.
There shouldn't be a need to restart MySQL.
The MySQL query cache is an in memory cache that stores the complete result sets of frequent SELECT queries. Instead of issuing the query again to the database engine, MySQL retrieves the result set from the cache and returns that to the client. The query cache is global, in that, all MySQL clients use the same query cache. The query caching functionality is very useful to websites that have a relatively static database and the pages are just a rendering of the data in the database. But query caching can also be useful, to some extent, to websites that have a high number of frequent SELECT statements from tables that change often.
Increasing your query_cache_size will definitely have an impact on performance of a few high frequency queries.
The decision on whether to increase the size of the variable query_cache_size can be easily made using the following mysql command.
First login to mysql using the mysql command line client.
Run the following command:
mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 13525 | | Qcache_free_memory | 58761352 | | Qcache_hits | 70886035 | | Qcache_inserts | 2178358 | | Qcache_lowmem_prunes | 126387 | | Qcache_not_cached | 276082 | | Qcache_queries_in_cache | 60985 | | Qcache_total_blocks | 136687 | +-------------------------+----------+ 8 rows in set |
The Qcache_free_memory shows that currently there is about 58 MB of free memory from the available 256 MB.
However, note that there is another variable called Qcache_lowmem_prunes which indicates how many times MySQL had to prune the query cache (remove some data) to make space for the outputs of other queries. This clearly indicates that increasing your query_cache_size will have a positive impact on performance. Try to increase the size of the cache till you get a very low value of Qcache_lowmem_prunes.
To increase the query_cache_size you will have to adjust your my.cnf/ini.
Add/adjust the following
query_cache_size = 536870912 or query_cache_size = 512MB |
Then issue this command (setting the cache via cli you will have to use values in multiples of 1024).
512MB = 536870912 bytes.
SET GLOBAL query_cache_size = 536870912; |
There shouldn't be a need to restart MySQL.
Please Register.
If you wish to add comments.
Cheers
Adam