Category: Technical Page 1 of 14

Mysql 5.7 is reaching end of life in October, so it is becoming important to upgrade. I am using Percona Mysql and the upgrade process with Apt and the Percona repositories is simple. Mysql automatically upgrades files and tables to suit the new version too. There are config file changes required, and quite a lot of defaults changed in Mysql 8, but that is not the focus of this post.

Reading up on the performance differences from Mysql 5.7 to Mysql 8, the story is mixed. In my case, Mysql 8 is definitely slower out of the box for queries that are not well indexed. We are talking often about 50% slower on larger selects. With well indexed queries, the difference is negligible. I kept looking for some config setting that would bring back old Mysql 5.7 performance, but I found no way to make Mysql 8 perform as well out of the box. The solution I found was to add more indexes. These indexes had not been required in 5.7 for good performance. In Mysql 8, they proved vital.

Considering the difference in performance between versions, and additional indexes I had put in place, I wanted to test the performance before upgrading my production setup to Mysql 8. A good way to test performance would have been to mirror incoming requests between real production, and a cloned production server, and watch the comparative performance. This is arguable the best option, but requires quite a bit of infrastructure work and HTTPS offloaded from the production server to something that is mirroring the requests and ignoring responses from the clone. AWS supports traffic mirroring, but I decided it wasn’t the best option in my situation as it would have required significant infrastructure and production changes for my setup.

The alternative that worked in my case, was to record all database queries for a period of medium-high site load, and then replay these on clone servers, to test the relative performance between database server versions and index additions. Tools exist to do this, but they are a bit dated.

If you’re interested in using approach, first record all queries to file at a time of significant load, using the Slow Log on your production server. Simultaneously, take a snapshot of the server that you can use for creating clone servers at this point in time.

To test relative performance on clones, I used Percona Playback. This is a very handy tool but old and unmaintained – still, it was the best option I found. To make Playback work, you need to run it under CentOS 7, which is easily achieved using Docker. I tried updating the code to run on modern Ubuntu but it was too big a job, libraries it depended on had changed too much.

To install, set up a data directory and grab and build my Dockerfile (I updated it a little from @lichnost’s version):

mkdir -p playback-docker/data
cd playback-docker
curl https://github.com/jcrisp/percona-query-playback/blob/master/Dockerfile > Dockerfile
docker build -t percona-playback .

Transfer your query log (eg, MyServerName-slow.log) to the clone. I’d also recommend taking a snapshot at this point, and creating new clones for performance testing from this new snapshot, since it now has Percona Playback installed and the query log file available.

To replay the queries in full on a cloned database server:

docker run --mount type=bind,source=$PWD/data,target=/app/data  --mount type=bind,source=/var/run/mysqld/mysqld.sock,target=/var/lib/mysql/mysql.sock percona-playback --mysql-max-retries 1 --mysql-host localhost --mysql-port 3306 --mysql-username <USERNAME> --mysql-password <PASSWORD> --mysql-schema <DATABASE_NAME> --query-log-file /app/data/MyServerName-slow.log

Note that this is “destructive”. All updates/inserts/deletes will be applied, so the run is only fully valid once. Re-running it will lead to errors like duplicate ID inserts or deletes of records that don’t exist any more.

For a non-destructive performance test, you can filter out just the selects using another tool, pt-query-digest, which is happily still maintained and can be installed as part of the percona-toolkit package. To make a select only log file:

cat MyServerName-slow.log |  pt-query-digest --filter '$event->{arg} =~ m/^select/i' --output slowlog > selects.log

For performance comparisons, I ran the non-destructive select-only version first to warm up the database before running the full destructive version as the real test.

This approach gave me confidence that, with additional indexes, Mysql 8 would be faster than Mysql 5.7 on a real production load for my app.

If you want to collect all queries running on your database for a period of time, for analysis or replay, the simplest way to do this is using the Slow Query Log.

To start collecting:

SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=0;
SET GLOBAL log_slow_admin_statements=1;
SET GLOBAL log_queries_not_using_indexes=1;
SET GLOBAL slow_query_log_always_write_time=0;
set global slow_query_log_use_global_control="all";

To stop collecting:

SET GLOBAL slow_query_log=0;
SET GLOBAL long_query_time=10;
SET GLOBAL log_slow_admin_statements=0;
SET GLOBAL log_queries_not_using_indexes=0;
SET GLOBAL slow_query_log_always_write_time=10;

The statements will be logged to your mysql data directory, into a file named after the hostname with -slow.log on the end. For example:

/var/lib/mysql/MyServerName-slow.log

The performance degradation from logging all queries was not significant in my case, with my site under medium-high load.