MySQL Performance Benchmarking: MySQL 5.7 vs MySQL 8.0

MySQL 8.0 brought enormous changes and modifications that were pushed by the Oracle MySQL Team. Physical files have been changed. For instance, *.frm, *.TRG, *.TRN, and *.par no longer exist. Tons of new features have been added such as CTE (Common Table Expressions), Window Functions, Invisible Indexes, regexp (or Regular Expression)–the latter has been changed and now provides full Unicode support and is multibyte safe. Data dictionary has also changed. It’s now incorporated with a transactional data dictionary that stores information about database objects. Unlike previous versions, dictionary data was stored in metadata files and non-transactional tables. Security has been improved with the new addition of caching_sha2_password which is now the default authentication replacing mysql_native_password and offers more flexibility but tightened security which must use either a secure connection or an unencrypted connection that supports password exchange using an RSA key pair.

With all of these cool features, enhancements, improvements that MySQL 8.0 offers, our team was interested to determine how well the current version MySQL 8.0 performs especially given that our support for MySQL 8.0.x versions in ClusterControl is on its way (so stay tuned on this). This blog post won’t be discussing the features of MySQL 8.0, but intends to benchmark its performance against MySQL 5.7 and see how it has improved then.

Server Setup and Environment

For this benchmark, I intend to use a minimal setup for production using the following AWS EC2 environment:

Instance-type: t2.xlarge instance
Storage: gp2 (SSD storage with minimum of 100 and maximum of 16000 IOPS)
vCPUS: 4
Memory: 16GiB
MySQL 5.7 version: MySQL Community Server (GPL) 5.7.24
MySQL 8.0 version: MySQL Community Server – GPL 8.0.14

There are few notable variables that I have set for this benchmark as well, which are:

• innodb_max_dirty_pages_pct = 90 ## This is the default value in MySQL 8.0. See here for details.
• innodb_max_dirty_pages_pct_lwm=10 ## This is the default value in MySQL 8.0
• innodb_flush_neighbors=0
• innodb_buffer_pool_instances=8
• innodb_buffer_pool_size=8GiB

The rest of the variables being set here for both versions (MySQL 5.7 and MySQL 8.0) are tuned up already by ClusterControl for its my.cnf template.

Also, the user I used here does not conform to the new authentication of MySQL 8.0 which uses caching_sha2_password. Instead, both server versions uses mysql_native_password plus innodb_dedicated_server variable is OFF (default), which is a new feature of MySQL 8.0.

To make life easier, I setup MySQL 5.7 Community version node with ClusterControl from a separate host then removed the node in a cluster and shutdown the ClusterControl host to make MySQL 5.7 node dormant (no monitoring traffic). Technically, both nodes MySQL 5.7 and MySQL 8.0 are dormant and no active connections are going through the nodes, so it’s essentially a pure benchmarking test.

Commands and Scripts Used

For this task, sysbench is used for testing and load simulation for the two environments. Here are the following commands or script being used on this test:

sb-prepare.sh

#!/bin/bash

host=$1
#host192.168.10.110
port=3306
user='sysbench'
password='MysqP@55w0rd'
table_size=500000
rate=20
ps_mode='disable'
sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --threads=1 --max-requests=0 --time=3600 --mysql-host=$host --mysql-user=$user --mysql-password=$password --mysql-port=$port --tables=10 --report-interval=1 --skip-trx=on --table-size=$table_size --rate=$rate --db-ps-mode=$ps_mode prepare

sb-run.sh

#!/usr/bin/env bash

host=$1
port=3306
user="sysbench"
password="MysqP@55w0rd"
table_size=100000
tables=10
rate=20
ps_mode='disable'
threads=1
events=0
time=5
trx=100
path=$PWD

counter=1

echo "thread,cpu" > ${host}-cpu.csv

for i in 16 32 64 128 256 512 1024 2048; 
do 

    threads=$i

    mysql -h $host -e "SHOW GLOBAL STATUS" >> $host-global-status.log
    tmpfile=$path/${host}-tmp${threads}
    touch $tmpfile
    /bin/bash cpu-checker.sh $tmpfile $host $threads &

    /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --events=$events --threads=$threads --time=$time --mysql-host=$host --mysql-user=$user --mysql-password=$password --mysql-port=$port --report-interval=1 --skip-trx=on --tables=$tables --table-size=$table_size --rate=$rate --delete_inserts=$trx --order_ranges=$trx --range_selects=on --range-size=$trx --simple_ranges=$trx --db-ps-mode=$ps_mode --mysql-ignore-errors=all run | tee -a $host-sysbench.log

    echo "${i},"`cat ${tmpfile} | sort -nr | head -1` >> ${host}-cpu.csv
    unlink ${tmpfile}

    mysql -h $host -e "SHOW GLOBAL STATUS" >> $host-global-status.log
done

python $path/innodb-ops-parser.py $host

mysql -h $host -e "SHOW GLOBAL VARIABLES" >> $host-global-vars.log

So the script simply prepares the sbtest schema and populates tables and records. Then it performs read/write load tests using /usr/share/sysbench/oltp_read_write.lua script. The script dumps global status and MySQL variables, collects CPU utilization, and parses InnoDB row operations handled by script innodb-ops-parser.py. The scripts then generates *.csv files based on the dumped logs that were collected during the benchmark, then I used an Excel spreadsheet here to generate the graph from *.csv files. Please check the code here in this github repository.

Now, let’s proceed with the graph results!

InnoDB Row Operations

Basically here, I only extracted the InnoDB row operations which does the selects (reads), deletes, inserts, and updates. When number of threads goes up, MySQL 8.0 significantly outperforms MySQL 5.7! Both versions do not have any specific config changes, but only the notable variables I have set. So both versions are pretty much using default values.

Interestingly, with regards to the claims of the MySQL Server Team about the performance of reads and writes in the new version, the graphs point to a significant performance improvement, especially in a high-load server. Imagine the difference between MySQL 5.7 versus MySQL 8.0 for all its InnoDB row operations, there’s a high difference especially when number of threads goes up. MySQL 8.0 reveals that it can perform efficiently regardless of its workload.

Transactions Processed

As shown in the graph above, MySQL 8.0 performance shows again a huge difference in the time it takes to process transactions. The lower, the better it performs which means it’s faster to process transactions. The transactions processed (the second graph) also reveals that both numbers of transactions do not differ from each other. Meaning, both versions executes almost the same number of transactions but differ in how fast it can finish. Although I could say, MySQL 5.7 still can handle a lot at lower load, but the realistic load especially in production could be expected to be higher – especially the busiest period.

The graph above still shows the transactions it was able to process but separates the read from writes. However, there’s actually outliers in the graphs which I didn’t include as they’re tiny tidbits of the result which would skew the graph.

MySQL 8.0 reveals a great improvements especially for doing reads. It displays its efficiency in writes especially for servers with a high workload. Some great added support that impacts MySQL performance for reads in version 8.0 is the ability to create an index in descending order (or forward index scans). Previous versions had only ascending or backward index scan, and MySQL had to do filesort if it needed a descending order (if filesort is needed, you might consider checking the value of max_length_for_sort_data). Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others. See here for more details.

CPU Resources

During this benchmarking, I decided to take some hardware resources, most notably, the CPU utilization.

Let me explain first how I take the CPU resource here during benchmarking. sysbench does not include collective statistics for hardware resources utilized or used during the process when you are benchmarking a database. Because of that, what I did is to create a flag by creating a file, connect to the target host through SSH, and then harvest data from Linux command “top” and parse it while sleeping for a second before collecting again. After that, take the most outstanding increase of CPU usage for the mysqld process and then remove the flag file. You can review the code there I have in github.

So let’s discuss again about the graph result, it seems to reveal that MySQL 8.0 consumes a lot of CPU. More than MySQL 5.7. However, it might have to deal with new variables added in MySQL 8.0. For example, these variables might impact your MySQL 8.0 server:

• innodb_log_spin_cpu_abs_lwm = 80
• innodb_log_spin_cpu_pct_hwm = 50
• innodb_log_wait_for_flush_spin_hwm = 400
• innodb_parallel_read_threads = 4

The variables with its values are left by its default values for this benchmark. The first three variables handles CPU for redo logging, which in MySQL 8.0 has been an improvement due to re-designing how InnoDB writes to the REDO log. The variable innodb_log_spin_cpu_pct_hwm has CPU affinity, which means it would ignore other CPU cores if mysqld is pinned only to 4 cores, for instance. For parallel read threads, in MySQL 8.0, it adds a new variable for which you can tune how many threads to used.

However, I did not dig further into the subject. There can be ways that performance can be improved by taking advantage of the features that MySQL 8.0 has to offer.

Conclusion

There are tons of improvements that are present in MySQL 8.0. The benchmark results reveals that there has been an impressive improvement, not only on managing read workloads, but also on a high read/write workload comparing to MySQL 5.7.

Going over to the new features that MySQL 8.0, it looks to be that it has taken advantage of the most up-to-date technologies not only on software (like great improvement for Memcached, Remote Management for better DevOps work, etc.) but also in hardware. Taken for example, the replacement of latin1 with UTF8MB4 as the default character encoding. This would mean that it would require more disk space since UTF8 needs 2-bytes on the non-US-ASCII characters. Although this benchmark did not take advantage of using the new authentication method with caching_sha2_password, it won’t affect performance whether it uses encryption. Once it’s authenticated, it is stored in cache which means authentication is only done once. So if you are using one user for your client, it won’t be a problem and is more secure than the previous versions.

Since MySQL leverages the most up-to-date hardware and software, it changes its default variables. You can read here for more details.

Overall, MySQL 8.0 has dominated MySQL 5.7 efficiently.