Last week we were busy to align XtraDB performance with 5.4, now we have some results. Currently it is available as “hacks” to XtraDB (available on Lauchpad lp:~percona-dev/percona-xtradb/hacks-porting-tune if you are interested). Basically we took improvements from 5.4 and backported ones performance related to XtraDB.
Here are results for tpcc-like workload, 100W (~10GB) ( raw results and parameters are available here
http://spreadsheets.google.com/ccc?key=rxUEhM2dqbX0uAfq9j6WQ_w ). Box Dell PowerEdge R900 (Does Dell have referral program ? ), with RAID10 (8 disks) on ext3, 32GB of RAM.
As you see there almost no difference and you may say what’s the reason in XtraDB ? The most interesting reason is XtraDB based on InnoDB-plugin and contains its nice features like FAST INDEX CREATION and dynamic pages. And XtraDB has some parameters like “adaptive_checkpoint” and control of “read_ahead”. And if in the same benchmark you disable read-ahead (innodb_read_ahead=none), you can see improvement about 15%
Actually control of read_ahead is very simple patch and can easily be included in 5.4 or InnoDB-plugin.
For curiosity I run the same benchmarks on ext3 vs xfs on SSD card, the results are:
Interesting facts:
– xfs is 25% faster ext3
– the gaps on SSD is more visible than on RAID10
– in the same time gaps can be smoother with disabled read_ahead, however disabling it does not show such improvement like on RAID10
I should mention I have strange results on xtradb running it on xfs on RAID10, I have results about 2times slower than for ext3. I am not sure yet – is it xtradb or xfs problem, and why it appears only on RAID10, but not SSD.
About the Author
Vadim TkachenkoVadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.
What did you backport from 5.4?
Mark,
Here is comment from Yasufumi:
– “pause” (rep; nop) instruction in spin loop should increase performance of
mutex/rw_lock contention. It may affect 5~10% in throughput.
– log_buffer_flush_maybe_sync() will decrease the number of log sync
at innodb_flush_log_at_trx_commit=[0|2]. It may affect to performance much.
– The number of request slots for the each IO thread has been increased. (x4)
It might be short for some high-end environments.
– relax condition to flush log and contract ibuf at inner loop of srv_master_thread()
– if (n_pend_ios < 3 && (n_ios – n_ios_old < PCT_IO(5))) {
+ if (n_pend_ios < PCT_IO(3) && (n_ios – n_ios_old < PCT_IO(5))) {
Should read_ahead always be disabled then to improve performance?
What are the pros and cons?
Andy,
I do not think so, it depends on IO subsystem. For single HDD read_ahead may be useful.
What are your thoughts on innodb_random_read_ahead?
Also given this is an old post, how does linear with a small vs large value compare to random in newer versions?