Thursday, 28 October, 2010
MySQL Performance: 5.5 and InnoDB btr_search_latch
This post continues the story started during the previous testing with MySQL 5.5.6 where I've observed a hot contention on the btr_search_latch and was curious to get a more detailed view on this performance issue.. Well, the story is starting here, but you never know what will be at the end.. ;-))
So, I've started with the same initial test workload I've before:
- dbSTRESS injector
- READ transaction: SELECT1 + SELECT2 queries
- WRITE transaction: UPDATE only queries
- think time between transactions: 0 sec.
- Concurrent user sessions: 1, 2, 4, 8, .. 256 users (growing workload)
- TPS and Query response times are collected live during all workloads..
What is interesting on these graphs:
- more the load become IO-bound, more the contention on the "btr_search_latch" become hot
- curiously the main performance impact is coming from reading!
- even Read+Write workload is more impacted by reading rather writing.
Seems there were several problems at once.. So, to understand one thing a time I'll try to split the issues and will keep the focus only on the Read-Only workload for the moment..
Specially that it was a great post published by Mark Leith when between other interesting things he also mentioned that the use of the "btr_search_latch" may be completely disabled by setting innodb_adaptive_hash_index off.. - that was a good point and interesting option to try!! :-))
So, let's get a look now on the MySQL 5.5.6 performance on the Read-Only workload once the
innodb_adaptive_hash_index = 0
|MySQL 5.5.6 Read-Only workload + innodb_adaptive_hash_index= 1 / 0|
- so far, the load is growing from 1 to 256 users step by step (1, 2, 4, 8, 16, 32,.. 256)
- on the left side the TPS level obtained while innodb_adaptive_hash_index = 1
- on the right side: innodb_adaptive_hash_index = 0
- as you can see, since 16 users TPS become lower in the second case, and since 32 users it's as twice as lower comparing to the performance with innodb_adaptive_hash_index = 1 ...
Let's get a more detailed view now.. [ Continue reading... ]
The full report is here: http://dimitrik.free.fr/db_STRESS_MySQL_55_and_btr_search_latch_InnoDB_Oct2010.html
Any comments are welcome! ;-)