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

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

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.

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