Monday, 11 April, 2011
MySQL Performance: 5.6 Notes, part 1 - Discovery...
I was lucky to discover MySQL 5.6 features ahead and here are my notes about performance improvements and pending issues observed on my benchmark workloads.. The story finally was very passionate and that's why very long for a one blog post :-)) so I've decided to split it into several parts and will publish all of them over a day..
Here is the first part, Discovery of MySQL 5.6.2 as it..
- first of all a split of the "kernel_mutex" was introduced! - it was one of the most hot contentions observed in MySQL 5.5 and limited InnoDB performance in so many cases..
- purge processing became multi threaded! - so we may expect to not see purge lagging anymore or reduce it considerably on heavy writes..
- page cleaning and flushing activity was removed from the Master Thread and assigned to the Page Cleaner (dedicated thread)
- METRICS table was introduced to make monitoring easier ;-))
- Performance Schema was improved and extended with a lot of new stuff..
sort_buffer_size = 2097152
table_open_cache = 8000
### innodb_flush_method= O_DIRECT
### innodb_change_buffering= none
# perf special
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 4000
### innodb_adaptive_hash_index = 0
# transaction isolation
### transaction-isolation = REPEATABLE-READ
### transaction-isolation = READ-COMMITTED
### transaction-isolation = READ-UNCOMMITTED
# Monitoring via METRICS table since MySQL 5.6
innodb_monitor_enable = '%'#-------------------------------------------------------
- first of all, there is a title on each graph ;-)
- then the legend on the right side explaining the meaning of curves..
- and then, if nothing else specified, I've tested a step by step growing workload, and on each step there were x2 times more concurrent user sessions than on the previous one..
- high contention on the btr_search_latch mutex since 16 sessions is gone!
- contention on btr_search_latch mutex is starting now since 32 concurrent sessions as before..
- since 64 sessions the trx_sys mutex contention is added too..
- since 256 sessions the trx_sys mutex contention is dominating..
disabling A.H.I is completely removing btr_search_latch mutex
contention, but it's not improving performance:
even before any other contention is coming in game, the TPS level
is already lower when AHI=OFF
(for ex.: on 8 sessions TPS is 7.500 with AHI=OFF, while it's 10.000 with AHI=ON)
then we hit a contention on the buffer pool index..
NOTE: buffer pool mutex contention may be very random - all depends how the data arrived into the buffer pool.. - if one of the BP instances is more accessed than others, we're getting back a situation similar to a single BP instance..
then once again the trx_sys mutex is taking a dominating position..
- even before any other contention is coming in game, the TPS level is already lower when AHI=OFF
- why do we still have a contention on the btr_search_mutex even once all accessed pages are already loaded to the buffer pool ???
any idea about reducing the trx_sys mutex contention ??..
- on 24 cores the ~max TPS level is kept up to 512 concurrent sessions
- on 32 cores it's up to 256 sessions only..
- on 48 cores performance is already decreasing since 64 sessions..
To be continued.. (see part #2)..
blog comments powered by DisqusNote: if you don't see any "comment" dialog above, try to access this page with another web browser, or google for known issues on your browser and DISQUS..