Is our MySQL poor performance related to excessive daily query cache prunes?

We are experiencing a high number of query cache prunes per day, currently 80851746. MySQL really struggles even under a reasonable load of say 50+ concurrent connections.

The host is a Physical Server with SSDs configured as RAID 5, 24 cores and 128GB RAM. It is a dedicated MySQL 5.6 server and is accessed by various PHP clients.

The instance consists of 3 databases with a total size of 1TB, all tables are InnoDB and compressed.

I've included as much information as possible below, any advice would be much appreciated. I can provide more information if needed. Thanks.

OS Information

# lsb_release -a
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.5 LTS
Release:    14.04
Codename:   trusty

# uname -a
Linux xxxxxxxxxx 3.13.0-32-generic #57-Ubuntu SMP Tue Jul 15 03:51:08 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

Memory Information

# free -m
             total       used       free     shared    buffers     cached
Mem:        128915     128335        580          0        396      78764
-/+ buffers/cache:      49174      79740
Swap:        30517        192      30325

CPU Information

```
# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 45
model name : Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
stepping : 7
microcode : 0x704
cpu MHz : 1200.000
cache size : 15360 KB
physical id : 0
siblings : 12
core id : 0
cpu cores : 6
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 s
s ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eag
erfpu pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic popcnt tsc_deadli
ne_timer aes xsave avx lahf_lm ida arat epb xsaveopt pln pts dtherm tpr_shadow vnmi flexpriori

I would yes for one reason: InnoDB and the Query Cache are not good neighbors. Why ?

On Jun 07, 2014, I answered the question Why query_cache_type is disabled by default start from MySQL 5.6?. In that post, I poetically described how InnoDB man-handles the Query Cache. I originally got that information from Pages 209-215 of High Performance MySQL (2nd Edition).

I have recommended people disable their query cache before:

• Sep 25, 2013 : invalidating query cache entries(key)

• Sep 26, 2013 : query cache hit value is not changing in my database

• Dec 23, 2013 : MySQL with high CPU and memory usage

RECOMMENDATIONS

Disable your query cache by setting query_cache_size and query_cache_type to 0. If things improve, all, well and good. If it does not improve, you must do three(3) things

• Set query_cache_type back to 1

• query_cache_size

• You have it set right now at 32M (33554432)

• Please increase it

• query_cache_limit

• You do not have it set. The default is 1M.

• With a query_cache_size of 32M, you can only hold, at the very least, 32 results that are 1M each. If you have smaller results sets going into the query cache, this might crowd the query cache and would cause lots of prunes because there is no elbow room for new incoming results sets.

HEADS UP !!!

If you are using MySQL 5.7.19 or prior, please read the MySQL Documentation on Query Cache Configuration for more authoritative advice.

For all those using MySQL 5.7.20 and beyond, the query cache is deprecated and will go away.

StackExchange Database Administrators Q#183322, answer score: 3