Improving MySQL Query Cache

Important Note: From MySQL 5.6.8,query_cache_type is set to OFF by default. So if you haven’t explicitly turned it ON on old version, it may not work anymore!

Check current status of query_cache

mysql -e "show variables like 'query_cache_%'"

Will output something like:

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 2097152   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_strip_comments   | ON        |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

Query Cache Config

Add something like below following to your /etc/my/my.cnf

query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
query_cache_strip_comments =1

Please note that query_cache_strip_comments variable is available on Percona & MariaDB mysql variants as of now.

Meaning of variables

query_cache_type

Just turn it ON. From mysql 5.6.8 its OFF by default.

If this is OFF, you will see error “[!!] Query cache is disabled” when running mysqltuner.

Earlier mysql version used to setquery_cache_type = 1 butquery_cache_size = 0. If either ofquery_cache_type andquery_cache_size is set to 0, query_cache gets disabled.

query_cache_size

Default is 1MB. You can set it upto 4GB but very high values are not recommend for sites where tables are modified quite frequently.

ADVERTISEMENT

In WordPress scenario, if you have a multi-author blog, or some other custom post types, then query cache prunes might be frequent. Query cache is invalidated for entire table even if a small value is modified.

query_cache_limit

Default is 1MB. You can set it upto 4GB. Again very high values are not recommended.

Better optimize your codes to not fetch too much data in one query. If you need 10 rows, add pagination, WHERE conditions rather than fetching all rows from mysql and then using only 10 rows out of them!

InnoDB Buffer & Query Cache

Many references on Internet will tell you that query cache is useless if InnoDB is being used.

Well if you are using InnoDB only and have limited RAM, InnoDB buffer pool without a doubt should get first priority.

If you have some spare RAM, it is highly recommended to use query_cache for WordPress sites. Even for big WordPress sites, most likely percentage of SELECT queries will be much higher as compared to INSERT or UPDATE.

Best way is to monitor query cache efficiency using mysqltuner. Look for a line like:

[OK] Query cache efficiency: 71.5% (8K cached / 11K selects)

100% Query cache efficiency may not be possible for 100% read-only sites but try to stay above 50%.

4 responses to “Improving MySQL Query Cache”

  1. Hi,

    After adding query_cache_strip_comments =1 to my /etc/mysql/my.conf, I get:

    service mysql restart
    [ ok ] Stopping MySQL database server: mysqld.
    [FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

  2. After mariadb 5.5 migration from percona we have a problem with our new db engine:

    We have changed from percona mysql server 5.5 to mariadb but performace is not as good as we thought. This graph is from a day in mariadb. You can see the low performace in terms of cache_hits http://i.stack.imgur.com/gAApV.png

    We have others servers with percona mysql server 5.5. The graph below is from one of them: http://i.stack.imgur.com/5BnjT.png

    We dont know how there is only 46% of query cache efficiency. With percona mysql server was near 90%.

    mysqltuner:
    [–] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM +SPHINX
    [–] Data in InnoDB tables: 8M (Tables: 10)
    [–] Data in MyISAM tables: 872M (Tables: 146)
    [–] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 11

    ——– Performance Metrics ————————————————-
    [–] Up for: 1d 20h 47m 59s (40M q [248.063 qps], 2M conn, TX: 87B, RX: 3B)
    [–] Reads / Writes: 97% / 3%
    [–] Total buffers: 2.9G global + 1.0G per thread (200 max threads)
    [!!] Maximum possible memory usage: 204.3G (651% of installed RAM)
    [OK] Slow queries: 1% (529K/40M)
    [OK] Highest usage of available connections: 11% (23/200)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/92.7M
    [OK] Key buffer hit rate: 100.0% (61B cached / 44K reads)
    [OK] Query cache efficiency: 45.3% (28M cached / 62M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (111 temp sorts / 1M sorts)
    [!!] Temporary tables created on disk: 48% (1M on disk / 2M total)
    [OK] Thread cache hit rate: 99% (23 created / 2M connections)
    [OK] Table cache hit rate: 68% (370 open / 541 opened)
    [OK] Open file limit used: 2% (517/20K)
    [!!] Table locks acquired immediately: 93%
    [OK] InnoDB buffer pool / data size: 256.0M/8.4M
    [OK] InnoDB log waits: 0

    Any help will be welcome.

    Many thanks in advance