DimitriK's (dim) Weblog

MySQL Performance: MySQL 5.4 and other InnoDB engines on 32cores AMD server & dbSTRESS

Currently several probe InnoDB code improvements were done by our MySQL Team. I was happy to test them with db_STRESS workloads but on Solaris/SPARC server (M5000). Then discussing with Mikael  I was surprised he saw much less improvement from the latest probe builds on his Linux/AMD64 box... And it was unclear why the performance improvement gap is more important on SPARC: due SPARC itself? due Solaris? due a test case?.. To bring more lights and understand better what's going differently on an AMD box I've preferred to avoid to change too many things on the same time :-) So, once one of the latest 32cores AMD server (X4600-M2) was available, I was curious to test it under Solaris10 and connected to the same storage box as M5000 before. And here are my results...

My intention is to replay exactly the same tests as previously on M5000 but on the newest X4600 (8CPU AMD quad core) server. All MySQL configuration files are exactly the same as before too. Few comments anyway:

Redo log size: I keep the same redo log size = 128MB and the same dirty page percentage = 15% as before even if performance is better with a bigger log size  and dirty page percentage will be never considered as I showed before  on a such stressful workload. I'll privilege a performance stability during current testing rather seeking for the higher possible numbers..

MySQL versions:

• MySQL 5.4 (public beta)
• MySQL 5.Perf builds 4, 5 and 11 (details about these builds you may find on Mikael's blog  or ask Mikael directly :-)
• XtraDB 5.
• MySQL 5.1 compiled with InnoDB plugin-1.0.3

All MySQL versions are :

• compiled with GCC 4.3.
• compiled with flags: -O
• linked with: -lmtmalloc

Benchmark summary:

• I was really impressed by performance level of a single MySQL session on the AMD box - it's near twice faster comparing to M5000! However once wokload is growing, this potential performance gap is decreasing and throughput become less or more comparable to the SPARC server.
  
  
• Negative performance gap between setting innodb thread concurrency = 0 and 16 is even more important for InnoDB plugin and XtraDB on AMD 16cores: from 20,000 Read-Only TPS they are dropping to 12,500(!) - loosing 37% in performance! - Seems to me time based concurrency introduced with MySQL 5.4 should be absolutely integrated into InnoDB plugin

• Surprisingly results on 32 cores are slightly better on AMD comparing to the old SPARC (I did not run 32cores test on M5000 due limited server availability time) As well performance obtained with InnoDB thread concurrency = 32 seems to be much more close to the concurrency = 16 on the new perf builds - good sign for scalability improvement! :-)
  
  
• On the same time perf builds 4 and 5 are outpassing 5.4 as it was observed on SPARC: ~15% on Read-Only and more than 20% on Read+Write workload!
  
  
• And I was pleasantly surprised by Read-Only performance of XtraDB and InnoDB plugin tested with zero InnoDB concurrency - on AMD server they outpassed MySQL 5.4 version! - and only perf build4 was able to compete with them on this box!
  
  
• Another surprise was to see a higher Read+Write TPS throughput on XtraDB-5 comparing to MySQL 5.4 - quite interesting what is making a difference here comparing to SPARC :-)
  
  
• Finally buil4 and build5 are the most performant on my tests - probably it's a time to merge them into 5.4? ;-)

As usually all details and full results are presented in the final report, but some of them I'd like to present individually.

Read-Only Workload @16cores AMD

Read+Write Workload @16cores AMD

Full benchmark report is here: http://dimitrik.free.fr/db_STRESS_MySQL_540_and_others_at_AMD_32cores_Jun2009.html

Any comments are welcome! :-)