Search

Top 60 Oracle Blogs

Recent comments

MySQL 5.6 vs 5.7

Whitelist preview of Performance Insights has just started on RDS MySQL and it gave me a chance to visually compare load profiles of MySQL 5.6 and 5.7.

I first tried to use sysbench and ran into some curious anomolies.

The test I ran was

sysbench \
           --test=oltp \
           --oltp-table-size=10000000 \
           --oltp-test-mode=complex \
           --num-threads=10 \
           --max-time=0 \
           --max-requests=0 \
           --mysql-host=$host \
           --mysql-user=$user \
           --mysql-password=$password \
           --mysql-db=sysbench run

Load chart on MySQL 5.6 and 5.7

 

MySQL 5.6

I first ran on MySQL 5.6 and in Performance Insights the load looked like:

sysbench_56

 

Which looks like a small load. The DB instance and load driving machine are both in the same AWS region (N.Virginia) i.e. not much latency. If there is a lot of latency between the machine driving load and the database then it can often be hard to drive a high load. In this case latency was low so it should be easy to drive a high load.

MySQL 5.7

I then ran the same load on 5.7 and it looked like:

sysbench_57

which is radially different. Where as on 5.6 the host was mainly idle since load was well below the # of vCPUs on 5.7 the load is above the # of vCPUs indicating a bottleneck.

SQL on 5.6 vs 5.7

 

MySQL 5.6

Let’s look at the SQL showing up in Performance Insights. For MySQL 5.6 the list was quite limited and there was no “commit”.

sysbench_56_sql

MySQL 5.7

On 5.7 most of the load was on commit and there were quite a few other SQL showing up:

sysbench_57_sql

Waits on 5.6 vs 5.7

Looking at the waits on 5.6 and 5.7 they are quite different as well.

MySQL 5.6

MySQL 5.6 spends most of it’s time on CPU and none on write to stable storage.

sysbench_56_waits

MySQL 5.7

on MySQL 5.7 most of the time is spent waiting on writes to stable storage and little, relatively , on CPU

 

sysbench_57_waits

Analysis

At first I started to wonder if there was something fundamentally different between 5.6 and 5.7.

The main difference I was seeing was on COMMIT and writes to stable storage so rather than trying to go debug sysbench , I decided to run a basic workload that should help me see if there was a basic big difference between 5.6 and 5.7.

Update workload on MySQL 5.6 and 5.7

I ran a workload of 4 sessions, each updating a single row table 10,000 times. In one case autocommit was on and the other it was off. With autocommit on, I’d expect a lot of waits on writes to stable storage and with it off I’d expect much less.

It turns out the load looks almost exactly the same on 5.6 as 5.7. On the left of each chart below there is a little spike a few seconds wide. That little spike is 40,000 updates by 4 users in a few seconds with hardly any wait as autocommit is off. Then the load jobs sleeps for 10 seconds, then the large load in both is the 40,000 updates with autocommit on and we wait on writes to stable storage as expected.

MySQL 5.6

mysql_66_update

MySQL 5.7

mysql_57_update

Conclusion

Looks like something is seriously wrong with sysbench on MySQL 5.6. I’ll have to dig into this.

In the mean time it looks like MySQL 5.6 and 5.7 do respond quite similarly. If anything MySQL 5.7 was a bit faster. Here are the actual timings for 40,000 updates by 4 concurrent users:

5.6
autocommit=0
real   0m5.285s
autocommit=1
real   3m32.095s
 
5.7
autocommit=0
real   0m5.186s
autocommit=1
real   2m57.236s