Created: 2009-11-25
Last modified: 2009-12-14




MySQL Performance: Breaking limits with XtraDB!






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

Contact Information:
     - Dimitri (at) sun.com

Dates: Nov - Dec.2009

Keywords: MySQL 5.4, XtraDB-8, InnoDB plugin-1.0.4, show mutex status, rollback segments, partitions, purge thread


Hardware Configuration
Server(s):
     - X4450 4CPU quad-core Intel, 16GB RAM

Storage:
     - ST6140, x2 LUNs RAID1 (/DATA and /LOG)


Software Configuration
System:
     - Solaris 10 update7
     - UFS

Application(s):
     - MySQL 5.4
     - MySQL 5-perf-b5
     - InnoDB plugin-1.0.4
     - XtraDB-8

Abstract
Overview: This testing was inspired by Yasufumi's post about tuning for heavy writing workloads - mainly because initially I did not observed the same things. And also I was very curious to analyze currently present hot lock contentions in InnoDB with an improved feature coming with plugin-1.0.4 - "SHOW MUTEX STATUS" presenting now lock wait stats in compact and easy for monitoring form!

Goal(s): For this testing I don't have my usual M5000 SPARC server, but less powerful x4450 server with Intel CPUs (16 cores only). So I cannot expect to reach the highest possible absolute performance with MySQL, but rather highest possible performance for the testing workload on this machine :-)

Result(s):
     - SHOW MUTEX STATUS feature is absolutely great for analyzing!
     - XtraDB rocks! :-)


Benchmark
All details about dbSTRESS benchmark you may find here: http://dimitrik.free.fr/db_STRESS.html

During this testing I'm concentrating mainly on the Read+Write workload (Read-Only is already working pretty good and on the current server is limited by H/W capacity :-))

dbSTRESS scenario :

H/W configuration :



Probe Test
First of all I've executed a probe test on this platform - I did not have any idea what kind of performance I may expect from a such small server (well, everything is relative :-)). Then I was interested not as much on the absolute TPS numbers as on the ratio between different engines.. Similar results were previously posted on my blog and compared MySQL 5.4 with XtraDB-8 and InnoDB-plugin-1.0.4 - but discussing with Yasufumi I realized I did not test the right XtraDB version: they day it was announced the published sources were not up-to-date... So now, I'm sure to test a true XtraDB-8 code! :-)

So far, the probe test is executed using a "similar" my.conf for all engines (for XtraDB and plugin-1.0.4 it's just simply the same)

Engines used in probe test :

I've made 2 tests :

The first test is labeled as "RW-Long,tx2" (tx2 is used as abbreviation from innodb_flush_log_at_trx_commit=2). This test running in partially-safe transaction conditions (as InnoDB is not flushing data on every commit, but just write them to the filesystem and flush (fsync) them once per second). As this test is not fully redolog-I/O-bound it leaves some room for other parts of the InnoDB code to "express" their bottlenecks :-) BTW, until your OS is safe enough and used H/W is stable and uses a power protection - it'll still be very safe! :-) This test is also running with innodb_thread_concurrency=16 to reduce internal concurrency contention.

The second test is labeled as "RW-Long,tx1" and running in fully-safe transaction conditions (InnoDB is flushing (fsync) redolog on every commit). This test is fully redolog-I/O-bound and cache on the controller of the storage array helps a lot here! But on the same times writes are still remaining serialized and even processing very fast still limiting a lot overall database performance. This test is running with innodb_thread_concurrency=0 as due I/O-bound we're yet far to see any internal concurrency contention.

During each test I've also monitored MUTEX waits (whenever such information was available).


Breaking Limits with XtraDB
All further testing I'm continuing with XtraDB.

Why?

So, until all these features are not shipped "officially" - it's very easy to validate their impact on your workload - and more cases will be tested, better MySQL/InnoDB will be! Don't wait! :-)

Let see now more closely on lock waits observed on XtraDB during the probe test...


Seeking for stability...
You may see from the TPS graphs of all presented tests with XtraDB there is yet missing TPS stability - periodical performance drops are happening due hard purge work and having a separated purge thread feature need to be implemented to resolve it.. Curiously, adoption of this solution on the InnoDB-plugin-1.0.4 code giving a lower TPS throughput comparing to the default code (while on MySQL 5.4 performance remained the same + it gave the expected TPS stability)...

Percona implemented this feature and XtraDB has now an option: innodb_use_purge_thread=1/0 - let's see the impact now on the same workload.


SUMMARY
Main points :

In several days it'll be an anniversary of XtraDB - 1 year! :-) Many things changed during this time and many improvements were initiated by XtraDB... And it's really great to celebrate a such event being once again the most advanced and most performance-ahead InnoDB engine implementation available for today! Kudos Percona! :-)

ANNEX
In following chapters you may find detailed STATs about all presented tests.

Few words about used abbreviations :

Hope I did not missed anything else :-)