Last modified: 2009-06-08
MySQL Performance: MySQL 5.4 & others @AMD 32cores 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 builds, XtraDB-5, InnoDB plugin-1.0.3, dbSTRESS, X4600-M2
- X4600 8CPU AMD Opteron 8384 (quad-core) 2700Mhz, 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 update6
- MySQL 5.4
- MySQL 5.Perf builds (4,5,11)
- InnoDB plugin-1.0.3
- dbSTRESS (injector)
Overview: 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...
- test MySQL/InnoDB on AMD box with db_STRESS
- evaluate a performance gap with some of new builds
- evaluate an impact of InnoDB thread concurrency
Result(s): see report :-))
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.
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.1
- MySQL 5.1 compiled with InnoDB plugin-1.0.3
All MySQL versions are :
- compiled with GCC 4.3.3
- compiled with flags: -O3
- 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 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 full results are presented at the end, but some of them I'd like to present individually.
Read-Only Workload @16cores AMD
Concurrency = 0
Concurrency = 16
Concurrency = 32
Read+Write Workload @16cores AMD
Concurrency = 0
Concurrency = 16
Concurrency = 32
InnoDB thread concurrency impact @16cores AMD
Read-Only: MySQL 5.4
Read-Only: InnoDB plugin-1.0.3
Read-Only: MySQL 5.Perf build4
Read+Write: MySQL 5.4
Read+Write: InnoDB plugin-1.0.3
Read+Write: MySQL 5.Perf build4
MySQL db_STRESS @AMD Final Results
Results comaring InnoDB thread concurrency
InnoDB Thread Concurrency impact @db_STRESS AMD Results
Results comaring CPU cores impact
Number of AMD CPU cores impact @db_STRESS Results
Workload STATs @x4600