Increased query_cache_size, Queries slowed drastically on increased traffic

Question

I increased our servers query_cache_size to 2gb, hoping for performance benefits. After sometime the query performance would drastically decrease. Once I dug in deeper, I found the queries in State "Waiting for query cache lock ". Lowering the query cache to 512M brought the performance back to normal. Why would this happen, and why would lowering it increase performance ?

Answer 1

That happens because all requests to the QC are serialized. The larger QC is the more time it takes to find and invalidate entries.

Answer 2

According to the MySQL Documentation on Query Cache States

This state occurs while a session is waiting to take the query cache lock. This can happen for any statement that needs to perform some query cache operation, such as an INSERT or DELETE that invalidates the query cache, a SELECT that looks for a cached entry, RESET QUERY CACHE, and so forth.

This is definitely a huge problem with InnoDB tables because InnoDB mechanisms make inroads on the query cache. I wrote about this phenomenon before

• Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?
• Sep 26, 2013 : query cache hit value is not changing in my database

There are some options you have in this

OPTION #1

You could disable the query cache completely

SET GLOBAL query_cache_size = 0;

If all queries against InnoDB tables are satisfactory thereafter, then add this to my.cnf

[mysqld]
query_cache_size = 0;

OPTION #2

If you really want a bigger query cache, perhaps you should place limits on how big entries should be

• You could raise query_cache_min_res_unit. It's default is 4K. If you set this higher, this will prevent small results from being in the query cache. This will reduce the overall number of entries in the query cache.
• You may want to change query_cache_limit. It's default is 1M.

Changing those options can allow you to dictate the minimum number of query cache entries.

For example, if you set the following

[mysqld]
query_cache_size = 2G
query_cache_min_res_unit = 64M
query_cache_limit = 128M

This will limit the number of query cache entries

• Minimum of 16 (2G / 128M)
• Maximum of 32 (2G / 64M)

If you really know your data workload and throughput, you could experiment with the limits.

Answer 3

Because of the way the query cache works, setting it to a large value will have detrimental effects. Generally, acceptable values range from 20 to 100 Mb. You should start with a low value, and then increase it little by little to maximize query cache hits.

From the MySQL manual:

Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be.

Other useful reading: http://haydenjames.io/mysql-query-cache-size-performance/