Monday, 08 June, 2009
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 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
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
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! :-)