4

How should the decision to enable / disable the MySQL query cache be made, on a server that uses just InnoDB tables. Say for example, if the cache is enabled, then how should the output of:

SHOW STATUS LIKE 'Qcache%';

be interpreted to make the decision? Or what other queries/profiling can be performed to get more information, and then how should those be interpreted? If it's to be enabled, how should its size be determined? I'm on Amazon RDS, using MySQL 5.6. I will end up being around ~250 separate databases totalling ~200gb in space.

Michal Charemza
  • 181
  • 1
  • 2
  • 12
  • Query cache is good in low concurrent environments with prevailing repeating SELECTs. If enabled keep its size 32-64 MB or stalls become inevitable. – akuzminsky Apr 17 '14 at 13:32
  • Another criteria to consider is whether the database fits into the buffer pool. If it does, it makes no sense to enable the query cache. – akuzminsky Apr 17 '14 at 13:34

3 Answers3

5

SHOW GLOBAL STATUS;

Then compute these (whether using MyISAM or InnoDB):

  • Qcache_lowmem_prunes / Uptime -- How often the QC is being pruned -- More than 15/sec says there is a lot of overhead in having the QC on.
  • Qcache_not_cached / Uptime -- Cache attempts failed (per second). >40 is probably bad.
  • Qcache_not_cached / (Qcache_hits + Com_select + Qcache_not_cached) -- Percent of SELECTs that are not cached in the QC -- >30% means the QC is not very useful.
  • Qcache_hits / Qcache_inserts -- Hit to insert ratio -- > 10 is desirable
Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
Rick James
  • 78,038
  • 5
  • 47
  • 113
4

I had discussed this in earlier posts

InnoDB internals has a very hands-on approach to the query cache since it micromanages query cache entry invalidation. In the MySQL 4.x days, the query cache was disabled by default for the sake of InnoDB. In MySQL 5.x, it can be a toss-up. The comments from @akuzminsky show local issues center around InnoDB.

Given the fact you are using Amazon RDS, you find the following challenges

  • Changing some InnoDB settings can be cumbersome
  • Some InnoDB settings cannot be changed
  • Tuning aspects of InnoDB has limits due to RDS

I would take away any guess work and just leave the query cache disabled. If having a bigger buffer pool size (as suggested in @akuzminsky's second comment) you will have to migrate to a larger Server Model ($$$ Cha-Ching $$$). Here are the Buffer Pool sizes for the Server Models

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)
Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • My total database size is > 200gb, so will never have enough memory to have have a buffer pool bigger than it (at least within the available budget). Does that make a difference? (I've edited the question to specify this). – Michal Charemza Apr 17 '14 at 17:23
2

It really depends on the read-write split on your data in general. If its read heavy, then you save CPU load in RDS by smartly using query_cache. However, I would never recommend turning it to default to ON, but rather use DEMAND (number 2 in RDS parameter) that way you can use SQL_CACHE in your select statements for particularly cumbersome queries to avoid potentially going to disk (which is very slow in RDS generally) while still not having query_cache and the expiring of data take over your DB in an out-of-control fashion.

Using option 2 should allow you to keep your query_cache_size small and avoid evictions while still providing enough room for your worst query execution results to be cached. In a read-heavy situation, this has yielded good speed-ups to our slowest queries with a 500GB RDS database while not burdening the system at the same time.

HTH

Ross
  • 251
  • 2
  • 8