Table of contents

• Benchmark Information
• Hardware Configuration
• Software Configuration
• Abstract
• Benchmark details
• MySQL versions
• MySQL Configuration file


• XtraDB-5
• First observations
• Adaptive Checkpoint
• Updated XtraDB-5


• Google v3 patched MySQL
• Compiling
• First observations


• Results Notes
• Performance of MySQL 5.1 and 5.0 versions
• Performance improvement: XtraDB-5 vs XtraDB-3
• Performance level @Read-Only Workload
• Performance level @Read+Write Workload
• Performance gain by adding more CPU cores...


• Full Benchmark Results
• All Wrokload Results
• MySQL 5.0 & 5.1
• XtraDB Performance
• InnoDB Thread Concurrency
• CPU Cores & Performance


• SUMMARY
• Appendix: Workload STATs

Customer Name(s): SSC Team

NDA: no

Contact Information: dimitri (at) sun.com

Dates: May.2009

Keywords: MySQL 5.4, XtraDB-5, XtraDB-3, InnoDB plugin-1.0.3, dbSTRESS, M5000

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

System:
     - Solaris 10 u6
     - UFS

Application(s):
     - MySQL 5.4
     - XtraDB-5
     - XtraDB-3
     - InnoDB plugin-1.0.3
     - Google v3 patched MySQL
     - MySQL 5.1.34
     - MySQL 5.0.77
     - dbSTRESS (injector)

Overview: Recently Percona announced the new release - XtraDB-5, and Google published v3 patch for MySQL 5.0.
How to resist and do not test them? ;-))

Result(s): see summary :-)

My intention is to replay exactly the same tests as in my previous report but on the newest M5000 (quad core) server and and included the newly shipped XtraDB-5 as well Google v3 patched MySQL version. So I'll skip all previously made explanations about db_STRESS tests and scenario and go directly to the action :-))

NOTE: I keep the same redo log size = 128MB and the same dirty page percentage = 15% as before. I'll go more in depth about these choices in my next tests. But for the moment let's keep them unchanged.

NOTE: most of tests were run with a second thread 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.

MySQL versions

Initially I've prepared to test the following list of MySQL versions (all that freely available for download :-))

• MySQL 5.0.77
• MySQL 5.1.34
• MySQL 5.1 InnoDB plugin-1.0.3
• MySQL 5.1 XtraDB-5
• MySQL 5.1 XtraDB-3
• MySQL 5.4
• MySQL 5.0 Google v3 patched

All binaries are compiled with GCC4.3 that time to reduce the number of test cases.

I've tested 5.0.77 and 5.1.34 just to see if there is still any performance degradation in the default 5.1 comparing to 5.0 (as it was observed in the previous testing at the end of the last year)..

And of course XtraDB-3 is present to see better new improvements of XtraDB-5. As well I did not see any result on Solaris/SPARC with a Google patched MySQL until now :-)

All other versions are retested to have a compatible result sets on this new M5000 server (CPU is faster and has 4 cores, and each core has 2 threads (see http://www.sun.com/servers/midrange/m5000/ for more details)


MySQL Configuration file

I continue to use still the same my.conf base:

[mysqld]
 max_connections=2000
 key_buffer_size=200M
 low_priority_updates=1
 sort_buffer_size = 2097152
 table_open_cache = 8000

#files
 innodb_file_per_table
 innodb_open_files = 4000
 innodb_log_group_home_dir=/LOG/5.1.34  #redo log is on separated LUN
 innodb_log_file_size=128M

# buffers
 innodb_buffer_pool_size=12000M
 innodb_additional_mem_pool_size=20M
 innodb_log_buffer_size=8M

# tune
 innodb_checksums=0
 innodb_doublewrite=0
 innodb_support_xa=0
 innodb_thread_concurrency = 16  # 0 /16 /32
 innodb_flush_log_at_trx_commit=2
 innodb_flush_method= O_DIRECT
 innodb_max_dirty_pages_pct=15

Additionally for 5.4 :

# perf special
 innodb_read_io_threads = 16
 innodb_write_io_threads = 16
 innodb_io_capacity = 2000

And for ExtraDB :

# perf special
 innodb_adaptive_checkpoint = 1
 innodb_read_io_threads = 16
 innodb_write_io_threads = 16
 innodb_io_capacity = 2000

This release is mainly fixing previously discovered instability issues of XtraDB-4 by moving to the Google rw_lock code, as well some other improvements (see details on XtraDB-5 announce ).

First observations

First big surprise was to see an important TPS jump down once running XtraDB-5 with innodb thread concurrency = 16. Instead of keeping a near the top level reaching with zero concurrency as usually and then continue with a slowly decreasing TPS, it's stopping to grow on 12.000 TPS, and then decreasing slowly..

Look at the following picture:


XtraDB-5 concurrency: 0 vs 16

As you may see, with concurrency = 16 the top ~16.000 TPS level is not reached and stopped on 12.000 TPS. Difference in near 4.000 TPS is quite huge! 25% loss is too much. So, to avoid any unclear interpretation, I've retested it again with a second thread on CPU core disabled - the gap is still the same... (since SPARC64-VII chip mostly all workloads are running faster with a second thread enabled, but in case I found a one bad "exception" I wanted to be sure about). Where is the problem?.. To be sure I did not dream and confirm what I saw before I retested the same case with XtraDB-3.

XtraDB-3 concurrency: 0 vs 16

As you see I did not dream :-) On XtraDB-3 max TPS on concurrency = 16 is not far of max on zero concurrency, and gain on stability is not bringing any significant performance degradation. So, I may only suppose it's related to some new code which is only active on zero concurrency, or other.. Probably folks from Percona may bring more light here.




Adaptive Checkpoint

As I already said in my previous report , Adaptive Checkpoint is an absolutely great feature! But reading this Vadim's post I also realized there is may be a kind of performance degradation if my test is not running log enough...

Until now I saw only a better performance by enabling an Adaptive Checkpint, but this post bring me a doubt :-) And as expected to do only honest comparison, I've restarted a whole test scenario to see if there in no any gain when this option is disabled...


XtraDB-5 Adaptive Checkpoint: ON vs OFF

As you may see performance is better while Adaptive Checkpoint is enabled! All other cases with disabled Adaptive Checkpoint you may see from the final results labeled as "XtraDB-5-AdpOFF"




Updated XtraDB-5

I did not finish my tests yet while Percona is slightly updated XtraDB-5 code. It was on time for me to test this release too. I've called it "XtraDB-5.1" to differentiate its results from original XtraDB-5 :-)

I was very curious to see how performs "the main source" of all MySQL improvements - any InnoDB engine variation includes today at least one Google patch! :-)

But the story is not as simple as I expected :-) First of all I did not find any README or HOWTO explaining how to apply Google patch and compile it :-) I know, it's stupid, but did not find :-)) So, if somebody other did not find too - there will be at least mine! :-))

Compiling

Before to compile anything you need to apply the patch first. Google patch is coming as a single gzipped file. Several times trying to apply it on Solaris I remind Google is mostly Linux oriented. And finally it worked on my Linux laptop :-))

Applying the v3 patch

• so, you need a Linux box (with patch, automake and autoconf tools installed)
• download MySQL 5.0.37 sources from mysql.com archives

   $ tar xzf mysql-5.0.37-src.tgz
   $ cd /path/to/mysql-5.0.37-src
   $ gunzip < /path/to/patch.gz | patch -p1
   $ autoconf
   $ automake
   $ tar czf ../mysql-5.0.37-src-patched.tgz .
  

Copy now mysql-5.0.37-src-patched.tgz file into a Solaris box and let's continue :-)

On Solaris box you'll need a GCC 4.3.2 installed or higher - Google code is using GCC atomic builtins, so to compile it correctly you need a recent GCC to be installed on your box.

Then:

 $ /usr/sfw/bin/gtar xzf /path/to/mysql-5.0.37-src-patched.tgz
 $ cd mysql-5.0.37-src-patched
 
 $ CC=/usr/local/gcc4/bin/gcc  
 $ CXX=/usr/local/gcc4/bin/g++ 
 $ CFLAGS="-m64 -mcpu=v9 -O3" 
 $ CXXFLAGS="-m64 -mcpu=v9 -O3"
 $ LDFLAGS="-lmtmalloc" 
 $ export CC CXX CFLAGS CXXFLAGS LDFLAGS


 $ configure --with-plugins="myisam,innobase" --with-fast-mutexes
 $ make
 ...

I've configured with fast mutexes option because it enables a great feature added with Google patch - you may monitor mutex locking activity via "show global mutex status" command!



Compiling a mysqld binary

However a real problems coming once you have typed make :-))

• NAN and INFINITY defines are missing on Solaris, need to add GCC builtins into sql/field.cc :

  extern "C" {
  #define _ISOC99_SOURCE
  #include 
  #define NAN    (__builtin_nanf (""))
  #define INFINITY       (__builtin_inff())
  }
  

• isinf() function is undefined, so add into sql/item_strfunc.cc :

  extern "C" { 
  extern int isinf(); 
  }
  

• asprintf() function is missing on Solaris, so add into sql/sql_parse.cc :

    detail= malloc ( 64 * 1024 );  //expecting 64KB is enough :-)
    ...
    //count= asprintf( &detail, 
    count= sprintf( detail,             //replacing asprintf() by sprintf()
    ...
    free( detail );  //free allocated memory at the end!
  

• mallinfo() is missing on mtmalloc, just comment it in sql/sql_show.cc

Hope I did not forget anything :-))

After that the make process will still not arrive till the end (there is still some missmatch with 64bit options and it'll try to compile with 32bit libraries, etc.) - but in "sql" directory you may already find a newly compiled mysqld binary!



Getting working the patched MySQL instance

To get a fully worked patched installation just download a binary 5.0.37 distribution, replace its mysqld binary by the newly compiled and you're ready! :-))


First observations

Once started the first test I've got at once an impression something is still going wrong with this code on Solaris:

• performance was lower than expected
• having non-zero innodb thread concurrency did not bring workload stability
• other signs of abnormal functionality...

I've contacted Mark Callaghan, investigation continues...

So I don't publish any results for Google patched version as it doesn't make sense for the moment.

Before to present all final results here, I'd like to put an accent on some of them.

Just a reminder for graphs:

• X-axis representing a number of concurrent users (sessions)
• Y-axis representing a reached TPS level (transactions per second)

Performance of MySQL 5.1 and 5.0 versions

Performance of the current "default" 5.1.34 and 5.0.77 are really low comparing to others. But as I said before I wanted to check if there is still any performance gap between these "default" versions.

As you may see on the following graphs there is no much difference between 5.0 and 5.1 now. And if somebody is still hesitating to upgrade from 5.0 the reason should not be about performance. Specially now when there are several options may used according your needs: MySQL 5.4, XtraDB or InnoDB plugin all easily outperforming 5.1 today.

NOTE: due a so low performance level of "default" 5.0 and 5.1 I tested them only on 8 cores (on 16 cores it'll be a simple wasting of time)...


Read-Only, concurrency=0
Read-Only, concurrency=16
Read+Write, concurrency=0
Read+Write, concurrency=16




Performance improvement: XtraDB-5 vs XtraDB-3

Several important points here:

• No one freeze observed during any test now! (as it was with XtraDB-4 and time to time with XtraDB-3 before)
• XtraDB-5 outperforms XtraDB-3, my congratulations to Percona team! :-)
• Strange Read-Only MAX performance drop on XtraDB-5 once switching concurrency from 0 to 16 (less important on XtraDB-3) - BTW, there is exactly the same issue with InnoDB plugin too, and probably it's coming from plugin code as XtraDB-5 is based on the latest plugin code..
• Disabling Adaptive Checkpoint decreasing average performance (even if the Max value may be better)
• Don't know what was wrong with XtraDB-5, but in several cases it outpassed XtraDB-5.1 :-))

Following graphs are reflecting discussed points. All other results are present on the final graphs.


Read-Only @8cores, concurrency=0
Read-Only @8cores, concurrency=16
Read+Write @8cores, concurrency=0
Read+Write @8cores, concurrency=16




Performance level @Read-Only Workload

What is interesting here:

• XtraDB-5 is the winner @8cores with innodb thread concurrency disabled (=0), even better than InnoDB plugin!
• MySQL 5.4 is the winner @8cores with enabled innodb thread concurrency (=16)!
• But @16cores MySQL 5.4 is outpassing all others regardless innodb thread concurrency setting!
• Analyzing a performance drop observed on XtraDB while moving from disabled to enabled innodb thread concurrency - it may me think the best result here will give a merge of XtraDB improvements + MySQL 5.4 new concurrency model!

Following graphs are representing performance levels during Read-Only workloads.


Read-Only @8cores, concurrency=0
Read-Only @8cores, concurrency=16
Read-Only @16cores, concurrency=0
Read-Only @16cores, concurrency=16




Performance level @Read+Write Workload

Observations:

• MySQL 5.4 is a true winner here!
• The impact of having several I/O writers is well seen here comparing XtraDB and InnoDB!
• A merge of MySQL 5.4 improvements + XtraDB Adaptive Checkpoint feature should give even better results!!!




Read+Write @8cores, concurrency=0
Read+Write @8cores, concurrency=16
Read+Write @16cores, concurrency=0
Read+Write @16cores, concurrency=16
Read+Write @16cores, concurrency=16 including 5.Perf-b5

I've included this graph just to show you the current research in MySQL performance improvement is going the right way. There are a lot of things to do yet, but things will become only better now :-)




Performance gain by adding more CPU cores...

As you probably saw from previous graphs, the gain in performance by moving from 8 to 16 CPU cores is quite low - 25% on Read-Only and near nothing on Read+Write workload.

• It's too early to say MySQL is scaling well up to 16 cores..
• There is still a lot of work ahead!
• As I said before, the improvement is huge anyway, as there is no more performance degradation on 16 cores either!
• 32 cores is too much for the moment :-))

Following graphs are reflecting a Read-Only workload with disabled thread concurrency (so all engines are going on their full speed). All other workload cases you may find on the final results page.


CPU Cores impact: MySQL 5.4
CPU Cores impact: XtraDB-5
CPU Cores impact: InnoDB plugin

Few notes :

• All final results are grouped in several subject of interest
• Within each subject the same results are also filtered per workload whenever possible
• As usually you'll find AVG TPS levels from one hand and MAX TPS level form another - it's important to know MAX values to evaluate a performance stability! For example, you may see on XtraDB Max TPS is better while Adaptive Checkpoint is off, but AVG is worse! etc.. As well, if the gap between MAX and AVG is huge - there is something definitively to do with a code!

All Wrokload Results

db_STRESS Final Results

• ALL results
• Read-Only results
• Read+Write results
• 10 Reads per Write results

MySQL 5.0 & 5.1

db_STRESS MySQL 5.0 & 5.1 Results

• ALL results
• Read-Only results
• Read+Write results

XtraDB Performance

XtraDB improvements @db_STRESS Results

• ALL results
• Read-Only results
• Read+Write results

InnoDB Thread Concurrency

InnoDB Thread Concurrency impact @db_STRESS Results

• ALL results
• Read-Only results
• Read+Write results
• 10 Reads per Write results

CPU Cores & Performance

Number of CPU cores impact @db_STRESS Results

• ALL results
• Read-Only results
• Read+Write results
• 10 Reads per Write results

So well:

• Great performance improvement came with new XtraDB-5!
• MySQL 5.4 is still the most performant engine on my tests
• Need to investigate more far with Google v3 patched version..
• Current goal in MySQL code improvement should be to gain at least 75% in performance comparing to results between 16 and 8 CPU cores! :-)

Here you may find all main graphs reflecting system and InnoDB activity during most of tested workloads...

Used Abbreviations:

• RW=0 -- Read Only Workload
• RW=1 -- Read+Write Workload
• RW=10 -- 10 Read transactions per single Write
• ccr=N -- InnoDB thread concurrency set to =N
• cores=C(T) -- tested on C cores, and each core has T threads activated
• XtraDB-5-AdpOFF -- XtraDB-5 tested with disabled Adaptive Checkpoint option

• dbSTRESS RW=0 MySQL-5.4.0-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=1 MySQL-5.4.0-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=10 MySQL-5.4.0-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=0 MySQL-5.4.0-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=1 MySQL-5.4.0-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=10 MySQL-5.4.0-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=0 XtraDB-5 cores=8(2) ccr=0
• dbSTRESS RW=1 XtraDB-5 cores=8(2) ccr=0
• dbSTRESS RW=10 XtraDB-5 cores=8(2) ccr=0
• dbSTRESS RW=0 XtraDB-5 cores=8(2) ccr=16
• dbSTRESS RW=1 XtraDB-5 cores=8(2) ccr=16
• dbSTRESS RW=10 XtraDB-5 cores=8(2) ccr=16
• dbSTRESS RW=0 MySQL-5.1.34-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=1 MySQL-5.1.34-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=10 MySQL-5.1.34-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=0 MySQL-5.1.34-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=1 MySQL-5.1.34-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=10 MySQL-5.1.34-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=0 InnoDB-plugin3-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=1 InnoDB-plugin3-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=10 InnoDB-plugin3-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=0 InnoDB-plugin3-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=1 InnoDB-plugin3-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=10 InnoDB-plugin3-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=0 MySQL-5.0.77-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=1 MySQL-5.0.77-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=10 MySQL-5.0.77-gcc43 cores=8(2) ccr=0
• dbSTRESS RW=0 MySQL-5.0.77-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=1 MySQL-5.0.77-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=10 MySQL-5.0.77-gcc43 cores=8(2) ccr=16
• dbSTRESS RW=0 MySQL-5.4.0-gcc43 cores=16(2) ccr=0
• dbSTRESS RW=1 MySQL-5.4.0-gcc43 cores=16(2) ccr=0
• dbSTRESS RW=10 MySQL-5.4.0-gcc43 cores=16(2) ccr=0
• dbSTRESS RW=0 MySQL-5.4.0-gcc43 cores=16(2) ccr=16
• dbSTRESS RW=1 MySQL-5.4.0-gcc43 cores=16(2) ccr=16
• dbSTRESS RW=10 MySQL-5.4.0-gcc43 cores=16(2) ccr=16
• dbSTRESS RW=0 MySQL-5.4.0-gcc43 cores=16(2) ccr=32
• dbSTRESS RW=1 MySQL-5.4.0-gcc43 cores=16(2) ccr=32
• dbSTRESS RW=10 MySQL-5.4.0-gcc43 cores=16(2) ccr=32
• dbSTRESS RW=0 XtraDB-5 cores=16(2) ccr=0
• dbSTRESS RW=1 XtraDB-5 cores=16(2) ccr=0
• dbSTRESS RW=10 XtraDB-5 cores=16(2) ccr=0
• dbSTRESS RW=0 XtraDB-5 cores=16(2) ccr=16
• dbSTRESS RW=1 XtraDB-5 cores=16(2) ccr=16
• dbSTRESS RW=10 XtraDB-5 cores=16(2) ccr=16
• dbSTRESS RW=0 XtraDB-5 cores=16(2) ccr=32
• dbSTRESS RW=1 XtraDB-5 cores=16(2) ccr=32
• dbSTRESS RW=10 XtraDB-5 cores=16(2) ccr=32
• dbSTRESS RW=0 InnoDB-plugin3-gcc43 cores=16(2) ccr=0
• dbSTRESS RW=1 InnoDB-plugin3-gcc43 cores=16(2) ccr=0
• dbSTRESS RW=10 InnoDB-plugin3-gcc43 cores=16(2) ccr=0
• dbSTRESS RW=0 InnoDB-plugin3-gcc43 cores=16(2) ccr=16
• dbSTRESS RW=1 InnoDB-plugin3-gcc43 cores=16(2) ccr=16
• dbSTRESS RW=10 InnoDB-plugin3-gcc43 cores=16(2) ccr=16
• dbSTRESS RW=0 InnoDB-plugin3-gcc43 cores=16(2) ccr=32
• dbSTRESS RW=1 InnoDB-plugin3-gcc43 cores=16(2) ccr=32
• dbSTRESS RW=10 InnoDB-plugin3-gcc43 cores=16(2) ccr=32
• dbSTRESS RW=0 XtraDB-5-AdpOFF cores=8(2) ccr=0
• dbSTRESS RW=1 XtraDB-5-AdpOFF cores=8(2) ccr=0
• dbSTRESS RW=10 XtraDB-5-AdpOFF cores=8(2) ccr=0
• dbSTRESS RW=0 XtraDB-5-AdpOFF cores=8(2) ccr=16
• dbSTRESS RW=1 XtraDB-5-AdpOFF cores=8(2) ccr=16
• dbSTRESS RW=10 XtraDB-5-AdpOFF cores=8(2) ccr=16
• dbSTRESS RW=0 XtraDB-5-AdpOFF cores=16(2) ccr=0
• dbSTRESS RW=1 XtraDB-5-AdpOFF cores=16(2) ccr=0
• dbSTRESS RW=10 XtraDB-5-AdpOFF cores=16(2) ccr=0
• dbSTRESS RW=0 XtraDB-5-AdpOFF cores=16(2) ccr=16
• dbSTRESS RW=1 XtraDB-5-AdpOFF cores=16(2) ccr=16
• dbSTRESS RW=10 XtraDB-5-AdpOFF cores=16(2) ccr=16
• dbSTRESS RW=0 XtraDB-5-AdpOFF cores=16(2) ccr=32
• dbSTRESS RW=1 XtraDB-5-AdpOFF cores=16(2) ccr=32
• dbSTRESS RW=10 XtraDB-5-AdpOFF cores=16(2) ccr=32
• dbSTRESS RW=0 XtraDB-3 cores=8(2) ccr=0
• dbSTRESS RW=1 XtraDB-3 cores=8(2) ccr=0
• dbSTRESS RW=10 XtraDB-3 cores=8(2) ccr=0
• dbSTRESS RW=0 XtraDB-3 cores=8(2) ccr=16
• dbSTRESS RW=1 XtraDB-3 cores=8(2) ccr=16
• dbSTRESS RW=10 XtraDB-3 cores=8(2) ccr=16
• dbSTRESS RW=0 XtraDB-3 cores=16(2) ccr=0
• dbSTRESS RW=1 XtraDB-3 cores=16(2) ccr=0
• dbSTRESS RW=10 XtraDB-3 cores=16(2) ccr=0
• dbSTRESS RW=0 XtraDB-3 cores=16(2) ccr=16
• dbSTRESS RW=1 XtraDB-3 cores=16(2) ccr=16
• dbSTRESS RW=10 XtraDB-3 cores=16(2) ccr=16
• dbSTRESS RW=0 XtraDB-3 cores=16(2) ccr=32
• dbSTRESS RW=1 XtraDB-3 cores=16(2) ccr=32
• dbSTRESS RW=10 XtraDB-3 cores=16(2) ccr=32