Those who fear to use MySQL from SSH, you can read this easy guide. In this guide we will show how to easily, safely enable MySQL caching from my.cnf file. This is a huge performance trick for WordPress like PHP-MySQL App. If you use Percona MySQL wizard, by default, the generated settings for my.cnf will use query_cache_type = 0 that is query cache is disabled. MySQL query cache is a cache mechanism that stores the text of the query and the result of the query into memory.
Enable MySQL Caching Vs Enable Database Caching Via WordPress Plugin
Whatever WordPress Plugin you use for database caching that is different that the thing we are talking about. We are going to tweak my.cnf file which is usually located at /etc/mysql/my.cnf in case of Ubuntu, Debian etc distro. The WordPress plugins decreases count of queries to database by caching queries in temp files in cache directory which is usually wp-content/tmp/.
There are matters around this MySQL cache. Like in some conditions in this cache will not work like if multiple servers hitting to one query and matters which probably an ordinary user need not to know. Other thing is the limit in size. There is documentation on MySQL website around it :
---
1 | https://dev.mysql.com/doc/refman/5.5/en/ |
In short, too much big query cache size definitely will lead to performance degradation as there will be cache overhead and locking. Plus those insert, update, delete modifications to a table will flush the query cache. In general, even a MySQL query cache size of 150 MB is too big. One tenth of it practical, you can test and increase in increments to keep a balance from your practical frontend experience.
Enable MySQL Caching : Example
First take a database backup. Open your my.cnf file and find similar lines and add or modify like below :
1 2 3 4 | query_cache_type = 1 query_cache_limit = 256K query_cache_min_res_unit = 2k query_cache_size = 90M |
Save the file, restart MySQL :
1 | service mysql restart |
Check your frontend. First time things will suck slight more time. Another end of example which can guide you to find a perfect point :
1 2 3 4 | query_cache_type = 1 query_cache_size = 16M query_cache_min_res_unit = 2k query_cache_limit = 5M |
We were talking about MySQL from SSH because you can run query like :
1 | mysql> SHOW VARIABLES LIKE 'have_query_cache'; |
You’ll get output :
1 2 3 4 5 | +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ |
Check whether caching is working :
1 | mysql> show variables like 'query%'; |
Output :
1 2 3 4 5 6 7 8 9 10 11 | +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 8388608 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +------------------------------+---------+ |
Here, the important things in the list are :
- query_cache_size : size of the cache in bytes. If this value is 0 that will effectively disable caching
- query_cache_type : value must be ON or 1 for query caching to be enabled
- query_cache_limit : maximum size query (in bytes) to be cached.
We actually could run commands in this way :
1 2 3 | mysql>SET GLOBAL query_cache_size = 8388608; mysql>SET GLOBAL query_cache_limit = 1048576; mysql>SET GLOBAL query_cache_type = 1; |
8388608 is 1024 * 1024 * 8 = 8 MB.
Monitor performance by running :
1 2 3 | mysql> SHOW STATUS LIKE 'Qcache%'; # or mysql> SHOW STATUS LIKE 'Qc%'; |
and get this kind of output :
1 2 3 4 5 6 7 8 9 10 11 12 13 | +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Qcache_free_blocks | 65 | | Qcache_free_memory | 201440 | | Qcache_hits | 18868 | | Qcache_inserts | 2940 | | Qcache_lowmem_prunes | 665 | | Qcache_not_cached | 246 | | Qcache_queries_in_cache | 492 | | Qcache_total_blocks | 1430 | +-------------------------+--------+ 8 rows in set (0.00 sec) |
