« November 2010 | Main | September 2010 »

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..

To don't repeat again, there are just 2 links to the last graphs describing the Read-Only (here ) and Read+Write (here ).

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
was added to the my.conf file:

MySQL 5.5.6 Read-Only workload + innodb_adaptive_hash_index= 1 / 0

Observations:

  • 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 ...

Why?...

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! ;-)

Posted by Dimitri at 20:08 - Comments...
Categories: MySQL, Tools/ dbSTRESS