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
Customer Name(s): SSC Team
Contact Information: dimitri (at) sun.com
Keywords: MySQL 5.4, MySQL 5.Perf, XtraDB-6, XtraDB-3, InnoDB plugin-1.0.4, dbSTRESS, M5000
- M5000 8CPU SPARC64-VII (quad-core bi-thread) 2400Mhz, 64GB RAM, 2x FC-port 4Gbit
- ST6140 2x LUN (500GB RAID1 8HD), each LUN connected by its own fiber channel to the host
- Solaris 10 u7
- MySQL 5.4
- MySQL 5.Perf (build45)
- InnoDB plugin-1.0.4
- dbSTRESS (injector)
Overview: Several very positive events happened during last weeks:
- Percona announced the new XtraDB-6 release! many performance improvement should be expected!
- Innobase/Oracle announced the new InnoDB plugin-4 release! with improved I/O capacity management and adaptive flushing!
- Sun/MySQL Performance Team started to finalize performance builds and the latest build (#45) now contains all positively shown improvements :-)
- As well my recent tests run much better on the latest Solaris 10 update7(!), so hope it'll bring more stability on the workload!
I may only say here: let's test? ;-))
Result(s): see report..
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.
Here is the list of MySQL versions I've prepared to test:
- MySQL 5.4.0
- MySQL 5.Perf (#45)
- MySQL 5.1 XtraDB-6
- MySQL 5.1 InnoDB plugin-1.0.4
All binaries are compiled with GCC4.3 and use GCC atomics.
Contents of my.conf for 5.4 and 5.Perf :
sort_buffer_size = 2097152
table_open_cache = 8000
innodb_log_group_home_dir=/LOG/log.54 #separated storage LUN
innodb_thread_concurrency=16 # or 0, or 32
# perf special
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 2000
Additionally for XtraDB :
innodb_adaptive_checkpoint= 1 # or 0
innodb_thread_concurrency_timer_based= 0 # or 1
Additionally for InnoDB-plugin4 :
innodb_adaptive_flushing = 1 ## (ON by default, but let's precise)
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!
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?.. ;-)
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 ;-))
Few comments :
- huge performance improvements observed for both XtraDB and InnoDB plugin!
- it'll be nice to see the timer concurrency model integrated within an InnoDB plugin too - it helps a lot to keep a growing workload!
- XtraDB is a true winner currently on the read-only workload!
- But the difference on the read-only load become very small between engines.. Time to improve more in depth?.. ;-)
- Performance version is outpassing others since there are writes present in workload
- Latest Solaris 10 update7 brings more stability and increase MySQL performance and scalability!
- current test was not long enough to reach the highest throughput for all engines, so more results will come soon! :-)
Any comments are welcome! :-)
Read-Only @8cores, innodb concurrency = 0
Read-Only @8cores, innodb concurrency = 16
Read-Only @16cores, innodb concurrency = 0
Read-Only @16cores, innodb concurrency = 16
Read-Only @16cores, innodb concurrency = 32
Read+Write @8cores, innodb concurrency = 0
Read+Write @8cores, innodb concurrency = 16
Read+Write @16cores, innodb concurrency = 0
Read+Write @16cores, innodb concurrency = 16
Read+Write @16cores, innodb concurrency = 32
10Reads/Write @8cores, innodb concurrency = 0
10Reads/Write @8cores, innodb concurrency = 16
10Reads/Write @16cores, innodb concurrency = 0
10Reads/Write @16cores, innodb concurrency = 16
10Reads/Write @16cores, innodb concurrency = 32
Full Results @Concurrency
InnoDB Thread Concurrency impact @db_STRESS Results
Number of CPU cores impact @db_STRESS Results
Following are detailed STAT graphs observed during all tested workloads.
Used abbreviations :
- RW=0 - Read-Only workload (no writes per read)
- RW=1 - Read+Write workload (one write per read)
- RW=10 - 10Reads/Write (one write per 10 reads)
- ccr=N - InnoDB thread concurrency set to N during the test
Engine naming :
- MySQL-5.4 -- MySQL 5.4.0, 64bit, GCC4.3 compiled
- MySQL-5.Perf-b45 -- MySQL Performance version build #45, 64bit, GCC4.3 compiled
- XtraDB-6 -- XtraDB-6 with timer concurrency model OFF, Adaptive Checkpoint is ON, 64bit, GCC4.3 compiled
- XtraDB-6-tc -- XtraDB-6 with timer concurrency model ON, Adaptive Checkpoint is ON, 64bit, GCC4.3 compiled
- InnoDB-plugin4 AF=on -- MySQL 5.1.37 compiled with InnoDB plugin-1.0.4, Adaptive Flashing is ON, 64bit, GCC4.3 compiled
- InnoDB-plugin4 AF=off -- MySQL 5.1.37 compiled with InnoDB plugin-1.0.4, Adaptive Flashing is OFF, 64bit, GCC4.3 compiled
MySQL 5.4 STATs
MySQL 5.Perf (#45) STATs
InnoDB plugin-4 STATs