MySQL Bugs: #47529: query cache performance is bad on multi-core servers

Bug #47529	query cache performance is bad on multi-core servers
Submitted:	22 Sep 2009 22:15	Modified:	9 Dec 2010 1:01
Reporter:	Mark Callaghan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S5 (Performance)
Version:	5.1.38, 5.1.39	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	cache, core, multi, performance, query, SMP

Description:
Query cache performance was bad on 5.0.44, good on 5.0.84 and bad again on 5.0.84 using multi-core servers with concurrent clients. All of the details are at: http://www.facebook.com/note.php?note_id=143487095932

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

How to repeat:
Run sysbench readonly with the query cache enabled/disabled:

# common parameters
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

# parameters for query cache enabled 
query_cache_type=1
query_cache_size=100000000

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

Suggested fix:
fix the query cache mutex code, again

Possibly related bugs:
http://bugs.mysql.com/bug.php?id=43758
http://bugs.mysql.com/bug.php?id=47277

"good on 5.0.84 and bad again on 5.0.84" ?

davi: 5.1.38. This is with the new lock code, right? Since throughput seems to be consistently better with no QC it means very little though. The old code allowed for SE execution if the QC didn't respond in time, hence the better throughput could be because QC simply wasn't blocking queries. I suppose the OLTP test consist of a mix of updates and invalidations, which obviously isn't the perfect context for using the QC.

Redesign is proposed but out of the question. Finding bottlenecks has been tried but proved difficult to execute on and still doesn't address the scale out issues. Maybe if we got more input from some of the success stories we could find some light on how to better handle the misconceptions on QCs abilities/disabilities, or in case that there are no success stories, make a sensible decision on its future.

I doubt that neither bug 43758 nor 47277 is related to the bad results on the sysbench OLTP test. The design simply doesn't play well in a multi-core environment. There are probably some things to do to fix the slow hash invalidation and good results has come from disabling the linear search in the memory allocation code.

From my test for 8 concurrent sessions. Qcache_not_cached is much larger for 5.0.84

SHOW STATUS like "Qc%"

from 5.0.84
Qcache_free_blocks      16340
Qcache_free_memory      55504824
Qcache_hits     803829
Qcache_inserts  929132
Qcache_lowmem_prunes    887534
Qcache_not_cached       5670349
Qcache_queries_in_cache 41598
Qcache_total_blocks     99537

And from 5.1.38
Qcache_free_blocks      16289
Qcache_free_memory      55692536
Qcache_hits     920295
Qcache_inserts  1069627
Qcache_lowmem_prunes    1028158
Qcache_not_cached       112
Qcache_queries_in_cache 41469
Qcache_total_blocks     99228

Mark, see bug #39253 that might help with what you're seeing. It's where some recent work has been done and addresses some cases where performance wasn't improved by the fix for bug #21074 (5.0.52, 5.1.23). Looks as though Kristoffer might be doing a bit more on 21074 as well.

If you have any more tests, particularly small ones, now is a good time to supply them since I've just suggested that Kristoffer gather a selection for ongoing use to help catch possible regressions or concurrency-related crashes in the future.

Re Qcache_not_cached, some past work arranged for the query cache to be skipped when it was busy removing entries, so it didn't just freeze the server, as it used to.

Can we build a hash table that scales on multi-core and then use that for both the HEAP engine and the query cache? I realize that might not be feasible for 5.1.

I think that's one of the ways being considered. Kristofer would have to say more tomorrow. You're almost certainly right about 5.1 and I can't see much chance of it this year. With the focus on concurrency it might happen next year or later since this is a clear scalability limiting factor.

Thank you for the report.

Verified with following results:

I assume throughput is transactions per second.

version	1		2		4		8		16		32
5.1.39	436.66	538.66	708.75	672.95	615.13	503.40
5.0.85	464.31	894.26	867.16 	656.47	611.45	514.19

$./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 5.1.39 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW STATUS like "Qc%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 15821    |
| Qcache_free_memory      | 54237456 |
| Qcache_hits             | 4195887  |
| Qcache_inserts          | 4562988  |
| Qcache_lowmem_prunes    | 4520331  |
| Qcache_not_cached       | 73       |
| Qcache_queries_in_cache | 42657    |
| Qcache_total_blocks     | 101136   |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> \q

$./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 70
Server version: 5.0.85 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW STATUS like "Qc%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 16822    | 
| Qcache_free_memory      | 55382584 | 
| Qcache_hits             | 3685036  | 
| Qcache_inserts          | 4053803  | 
| Qcache_lowmem_prunes    | 4012030  | 
| Qcache_not_cached       | 2361299  | 
| Qcache_queries_in_cache | 41773    | 
| Qcache_total_blocks     | 100369   | 
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> \q
Bye

This is 2 CPU, 4 cores box

As described in bug#39253, the current hash table has a high penalty for deleting entries. If the load uses much invalidation then this penalty will be significant for all load.
If there are a lot of memory prunes then the query cache will eventually become severely fragmented and the cost for finding available memory blocks will be significant.

The memory allocator is not multi-threaded, and all memory allocation will thus always be single threaded.

| Qcache_hits             | 3685036  | 
| Qcache_inserts          | 4053803  | 

If you see hits less than inserts it's an excellent clue that you should try turning off the query cache because it's not likely to be helpful for your workload. The work saved by one hit is unlikely to be less than the work taken for one insert. It can be for slow queries but in practice that's not the usual case.

If hits are four or five times inserts it's likely to be helpful. In all cases it's worth testing to find out.

If you do turn off the query cache be aware that you might shift load to another contention point like the InnoDB adaptive hash index and could decrease throughput if you make that more overloaded because the query cache used to offload much work from it.

| Qcache_free_blocks      | 16822    | 

If you see this over 10,000 it's a hint that you might test to see whether running FLUSH QUERY CACHE regularly improves performance by reducing the work in allocating blocks. Look for a change in CPU load shortly after running it. 

If Qcache_free_blocks reaches 100,000 you're almost certainly suffering significant CPU overhead for allocating and freeing blocks and should do something about it. 100k is the sort of level that used to sometimes cause server freezes of several to tens of seconds when a seldom updated table was changed. Improvements as a result of bug fixes have eliminated that symptom, now the query cache is just temporarily ignored instead.

If you know that a SELECT is going to be very fast even without the query cache then from 5.0.79 and 5.1.33 you can consider using SELECT SQL_NO_CACHE to have the SELECT ignore the query cache and not take the query cache mutex at all, improving performance for queries that do benefit. SELECT /* SQL_NO_CACHE */ does not have this effect. The mutex is still taken for versions before 5.0.79 and 5.1.33.

James Day, MySQL Senior Support Engineer, Sun Microsystems

Doesn't SELECT SQL_NO_CACHE prevent from inserting the results in the cache rather than tell MySQL not to use it?
We are having deadlocks on MySQL 5.0.81 on FreeBSD 7.0 on 16 cores, it seems that all the resources that use a shared Mutex can cause big problems up to deadlock MySQL (context switches from 10k to 200k-300k). I built 10 different key caches for must used tables to remove the contention on the key cache but it is still happening, so the spotlight is on query cache now which, when disabled totally, causes the CPU load go high from 1 to 8(medium traffic website).
My question is: Is it any OS distribution better than another in managing this contentions?

Closed as a duplicate of Bug#37844. It's essentially the same problem, the current query cache architecture does not scale.