EmergencyEMERGENCY? Get 24/7 Help Now!

State of Percona Server 5.6, MySQL 5.6 and MySQL 5.7 RC

 | October 26, 2015 |  Posted In: Benchmarks, MySQL, Percona Server for MySQL

PREVIOUS POST
NEXT POST

Benchmarking PS for MySQL This week Oracle will release MySQL 5.7 GA, so it’s a perfect time to do a quick review of the current state of Percona Server 5.6.26, MySQL 5.6.26 and MySQL-5.7.8 RC. We used two boxes from our benchmark lab for this:
– Box 1: 16 Cores+HT (32 virt cores)/fast PCIe ssd card/RAM: 192GB
– Box 2: 24 Cores+HT(48 virt cores)/fast PCIe ssd card/RAM: 128GB

Dataset: sysbench/uniform, 32 tables with 12M rows each, ~95GB
Tests: sysbench – point select, oltp read only, oltp read/write
Test sequence: start server, warmup, series of the tests (each lasts 5 minutes) from 1 to 4096 threads

Tests were run for two setups:
– CPU bound (in memory) – innodb_buffer_pool_size=100GB
– IO bound – innodb_buffer_pool_size=25GB

5657.cpubound.ct.sm01.uniform

5657.iobound.ct.sm01.uniform

Observations:

* CPU bound
– It’s clear that MySQL 5.7 RC, in both read-only scenarios (adhoc and transaction), outperforms MySQL 5.6/Percona Server 5.6 and scales very well up to 4k threads, especially on Box 2 with 48 cores. It shows great improvements over 5.6 in the read only scalability area. In the read-write scenario there are still some problems with the 5.7 RC. It shows a stable result on the 16 core box, but notably degrades for high threads on Box 2 with 48 cores. Percona Server 5.6 is OK up to 1024/2048 threads for both types of boxes, and then tps drops as well. MySQL 5.6 in this test scales up to 512 threads only and then tps dramatically decreases.

In general, in the CPU-bound scenario, 5.7 RC on Box 1 with 16 cores showed a bit worse results than 5.6. It looks like it is limited by something, and this may require additional analysis. We will recheck that after GA.

* IO bound
– Again, 5.7 RC shines in read-only scenarios. For Box 1 with 32 cores, Percona Server 5.6 competes with 5.7 RC, but on Box 2 with 48 cores the difference is quite notable with higher threads. Read/write workload in the IO-bound scenario is the most problematic case for 5.7 – it shows an almost similar pattern to MySQL 5.6 on Box 1 and is slightly better on Box 2. We have checked that case with Performance Schema for all 3 servers on each box and according to that, (see charts below) the most notable waits for 5.7 are caused by a doublewrite mutex. MySQL 5.6 is affected by contention of the buffer pool mutex and for Percona Server 5.6 log_sys mutex is the hottest one.

blog.ct.pse.synch.ps56.m57.m56

blog.sm01.pse.synch.ps56.m57.m56

Charts with mutex info above are for the OLTP_RW test for the runs with 64 and 1024 threads for Percona Server 5.6.26/MySQL 5.7.8/MySQL 5.6.26
mysql server settings

PREVIOUS POST
NEXT POST
Alexey Stroganov

Alexey Stroganov is a Performance Engineer at Percona, where he works on improvements and features that makes Percona Server even more flexible, faster and scalable. Before joining Percona he worked on the performance testings/analysis of MySQL server and it components at MySQL AB/Sun/Oracle for more than ten years. During this time he was focused on performance evaluations, benchmarks, analysis, profiling, various optimizations and tunings.

6 Comments

  • Alexey,
    I see you’re looking at 5.7.8 which is release candidate here. Were were any significant changes in GA release which would make us to believe it would have significantly different performance ?

  • Alexey, can you explain the difference between your results and the 1.6 million QPS results published by Oracle (for in-memory point select)? I don’t see how better hardware could account for their benchmark running 5x faster than yours.

  • Alexey, when you re-run the test with MySQL 5.7.9 GA, can you also throw MariaDB 10.1.8 GA into the mix? Would be nice to see how it compares to their own benchmark: https://blog.mariadb.org/maria-10-1-mysql-5-7-commodity-hardware/

    Btw. instead of going for a Percona Server 5.7.x-yy.z based on MySQL 5.7.x you might consider going for a Percona Server 10.1.x-yy.z based on MariaDB 10.1.x. Get the best of both worlds and make Oracle’s distribution irrelevant 😉

  • Alexey, one point on the 1.6M QPS is Dimitri uses sysbench 0.4, possibly customized – could be something to look into as well? Just curious.

Leave a Reply