What to Test Before Upgrading to MySQL 5.7

As we saw in a previous post, there are some important changes between MySQL 5.6 and 5.7. Since the behaviour of some existing features been altered, some detailed testing of the upgrade is in order. In this blog post, we will look at some of the important things to keep in mind when preparing and performing these tests.

How to design a test environment?

Your test environment has to be as similar to your production environment as possible. We are talking here about using the same hardware, using the same dataset and running the same query mix. If you have a complex replication topology, try to replicate it in test as well. Of course, sometimes it’s not possible to use real data or real queries because of  security concerns. But keep in mind that the results get less and less reliable as you have more differences between test and production.

Collecting queries for regression tests

To run real-world queries, you need to collect them first. There are many ways to do that – you can enable the slow query log and log all queries there (long_query_time=0), you can use tcpdump and capture the traffic somewhere between MySQL and the application. It’s very important to keep in mind that you need to collect all types of queries. If your query mix is stable all the time, probably 20 – 30minutes of traffic will suffice. If you have some variation in it, though, things will be different. Maybe your application runs different queries at different times of the day. Maybe there’s some ETL process that gets kicked off at a certain scheduled time. Maybe you run some reporting. So on and so forth – the bottomline is – you have to collect all types of queries in order to make tests as realistic as possible.

Performance regression tests

Some simple tests can be executed by a DBA – tools like Percona Playback or pt-upgrade can help here. Especially pt-upgrade is useful as it can collect performance data about queries executed on different MySQL hosts and prepare nice reports about them. When running pt-upgrade, you need to make sure your test nodes are at the exact same state – ideally, cold, no data in cache or buffers. You need to keep in mind that query performance can be affected by network latency, therefore it’s better to execute pt-upgrade locally.

Tests on the application side

Simple tests like pt-upgrade won’t replace detailed acceptance tests executed from the application. This step is a must for any upgrade runbook. The more data you can collect while running tests on the application, the better. Exact process will differ between applications but the idea is simple – check everything you can and make sure all aspects of your application work correctly with a new version.

The above is a brief overview of the testing process.  You can find more details in our ebook “Upgrading to MySQL 5.7”.