Created: 2009-08-11
Last modified: 2009-08-13




MySQL Performance: XtraDB-6 & InnoDB plugin-4 @dbSTRESS






by Dimitri


SSC Team, 2009
Sun Microsystems Inc.





Ces informations sont données à titre indicatif et n'engagent pas Sun Microsystems.

Table of contents


Benchmark Information
Customer Name(s): SSC Team

NDA: no

Contact Information: dimitri (at) sun.com

Dates: Aug.2009

Keywords: MySQL 5.4, MySQL 5.Perf, XtraDB-6, XtraDB-3, InnoDB plugin-1.0.4, dbSTRESS, M5000

Hardware Configuration
Server(s):
     - M5000 8CPU SPARC64-VII (quad-core bi-thread) 2400Mhz, 64GB RAM, 2x FC-port 4Gbit

Storage:
     - ST6140 2x LUN (500GB RAID1 8HD), each LUN connected by its own fiber channel to the host

Software Configuration
System:
     - Solaris 10 u7
     - UFS

Application(s):
     - MySQL 5.4
     - MySQL 5.Perf (build45)
     - XtraDB-6
     - InnoDB plugin-1.0.4
     - dbSTRESS (injector)

Abstract
Overview: Several very positive events happened during last weeks:

I may only say here: let's test? ;-))

Result(s): see report..


Benchmark Details
My intention again is first to replay exactly the same tests as before and on the same M5000 (quad core) server which I've tested and presented results in May. The newly shipped XtraDB-6 and InnoDB plugin-4 are making the difference, as well the latest Perf.version tool. So I'll skip again all previously made explanations about db_STRESS tests and scenario and go directly to the action :-))

NOTE: I'm using here the redo log size = 1024MB as it shows much better better performance level (and "furious flushing" is near to be fixed :-)) and the same dirty page percentage = 15% as before (it doesn't change anything for the moment within a current code implementation on all current InnoDB variations).

NOTE: I was surprised not all engines used by default the same number of redo log files within a log group, so for all engines the innodb_log_files_in_group = 3

NOTE: for all of the tests the second thread was enabled on each CPU core. To put attention on this point I add a mention about number of core threads on used labels. For example 8cores become 8cores(2) (or cores=8(2) for ex.) if both threads were activated on each core. Otherwise 8cores remaining only 8 cores were active with only one thread.


XtraDB-6
Many changes were integrated by Percona team since this release (see announce details for more information). But my main interest here will be around a timer concurrency model - this model was introduced with MySQL 5.4 and now integrated within XtraDB-6 too. On the previous tests XtraDB demonstrated a high troughput on the read-only workload but only with innodb_thread_concurrency=0 setting. When changing it to innodb_thread_concurrency=16 for example, it gave a negative impact (while it brings more stability on a higher workload). But now having timer model things should change!.. As well a new implementation of the "buffer split mutex" should bring a better scalability!

Let's see..

InnoDB plugin-4
Very interesting changes came with this release, and I'm very curious to see their performance impact :-) Current plugin is claiming to bring back a grouped commit! As well adopt an I/O model used currently by XtraDB and MySQL 5.4, and having I/O capacity setting too. On the previous tests InnoDB plugin was negatively impacted by missing this feature..

Let's test?.. ;-)

MySQL 5.Perf (#45)
Perf version build #45 contains all most positively seen performance patches presented over a time (see Mikael's blog for more details), as well some performance numbers on AMD previously posted here..

Work continues, so stay tuned ;-))

Benchmark Results
Few comments :

Any comments are welcome! :-)


Workload STATs
Following are detailed STAT graphs observed during all tested workloads.

Used abbreviations :

Engine naming :