Be nice to the query cache

Be nice to the query cache: "The query cache has had an interesting history with MySQL. I don't have much experience with it. I might want to use it in the future so I enabled it during a run of sysbench readonly on an 8-core server. The results are fascinating. Performance with the query cache enabled is bad for MySQL 5.0.44, good for MySQL 5.0.84 and then bad again for MySQL 5.1.38. By bad I mean that throughput does not increase with concurrency as it does when the query cache is disabled.

This lists the throughput in the number of transactions per second reported by sysbench readonly using an 8-core server. It was run for 1, 2, 4, 8, 16 and 32 concurrent sessions.

Throughput with query cache enabled:
           1     2     4     8    16    32
5.0.44    663   746   872   834   730   694
5.0.84    639  1033  1819  3216  2636  2466
5.1.38    662   725   818   782   731   699

Throughput with query cache disabled:
           1     2     4     8    16    32
5.0.44     856 1429  2807  4431  4327  4199
5.0.84     790 1466  2781  4393  4255  4172
5.1.38     747 1387  2698  4385  4293  4152

Configuration for the test with the query cache enabled:

innodb_buffer_pool_size=2000M
innodb_log_file_size=100M
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8
max_connections=500
innodb_max_dirty_pages_pct=80
innodb_flush_log_at_trx_commit=2
query_cache_type=1
query_cache_size=100000000

Configuration for the test with the query cache disabled:

innodb_buffer_pool_size=2000M
innodb_log_file_size=100M
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8
max_connections=500
innodb_max_dirty_pages_pct=80
innodb_flush_log_at_trx_commit=2

The sysbench command line:

for nt in 1 2 4 8 16 32; do
../sysbench --test=oltp --oltp-table-size=2000000 --max-time=180 --max-requests=0 \
--mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes \
--oltp-read-only --num-threads=$nt run
done

There appears to be much more mutex contention in the query cache for 5.1.38 (CPU time is lower, idle time is higher). This lists typical vmstat output when sysbench is run with 8 concurrent sessions and the query cache is enabled:

# output for MySQL 5.0.84
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
6  0  17732 32097788 1417064 29284560    0    0     0    53 1026 213912 71 12 18  0  0

# output for MySQL 5.1.38
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
5  0  17732 32120104 1417076 29285028    0    0     0   117 1020 102607 23  6 71  0  0

PlanetMySQL Voting:
Vote UP /
Vote DOWN"