Menu
My application is very database intensive so I've tried really hard to make sure the application and the MySQL database are working as efficiently as possible together.
Currently I'm tuning the MySQL query cache to get it in line with the characteristics of queries being run on the server.
query_cache_size is the maximum amount of data that may be stored in the cache and query_cache_limit is the maximum size of a single resultset in the cache.
My current MySQL query cache is configured as follows:
query_cache_size=128M query_cache_limit=1M tuning-primer.sh gives me the following tuning hints about the running system:
QUERY CACHE Query cache is enabled Current query_cache_size = 128 M Current query_cache_used = 127 M Current query_cache_limit = 1 M Current Query cache Memory fill ratio = 99.95 % Current query_cache_min_res_unit = 4 K However, 21278 queries have been removed from the query cache due to lack of memory Perhaps you should raise query_cache_size MySQL won't cache query results that are larger than query_cache_limit in size And mysqltuner.pl gives the following tuning hints:
[OK] Query cache efficiency: 31.3% (39K cached / 125K selects) [!!] Query cache prunes per day: 2300654 Variables to adjust: query_cache_size (> 128M) Both tuning scripts suggest that I should raise the query_cache_size. However, increasing the query_cache size over 128M may reduce performance according to mysqltuner.pl (see http://mysqltuner.pl/).
How would you tackle this problem? Would you increase the query_cache_size despite mysqltuner.pl's warning or try to adjust the querying logic in some way? Most of the data access is handled by Hibernate, but quite a lot of hand-coded SQL is used in the application as well.
548
To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache, as does setting query_cache_type=0 . By default, the query cache is disabled. This is achieved using a default size of 1M, with a default for query_cache_type of 0.
Open terminal and run the following command to open MySQL configuration file. We have enabled query cache by setting query_cache_type variable to 1, with individual query cache as 256Kb and total cache as 10Mb. You can change the values of query_cache_size and query_cache_limit as per your requirements.
Query to disable the cache From the MySQL command line, a application like phpMyAdmin, or from a script, run the following SQL command to disable the query cache: SET GLOBAL query_cache_size = 0; This will take effect immediately. You will also need to modify the MySQL configuration file to keep it disabled on reboot.
The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines. Rene confirmed this in his post yesterday, but it has also previously been mentioned by Stewart Smith, Domas Mituzas (update: and Kristian Koehntopp).
The warning issued by mysqltuner.py is actually relevant even if your cache has no risk of being swapped. It is well-explained in the following: http://blogs.oracle.com/dlutz/entry/mysql_query_cache_sizing
Basically MySQL spends more time grooming the cache the bigger the cache is and since the cache is very volatile under even moderate write loads (queries gets cleared often), putting it too large will have an adverse effect on your application performance. Tweak the query_cache_size and query_cache_limit for your application, try finding a breaking point where you have most hits per insert, a low number of lowmem_prunes and keep a close eye on your database servers load while doing so too.
191
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
