How To Test Your Upgrades – pt-upgrade

Upgrades are usually one of the biggest part of any database infrastructure maintenance. Even with enough planning something else can go bad after sending your production application to the version you’ve upgraded to. Let’s look at how one Percona Toolkit tool, pt-upgrade can help you identify what to expect and test your upgrades better which is one important step when upgrading your servers.


This tool lets you test your SELECT queries against multiple MySQL servers and reports on how each type of query performs from every server you let it test on. It can report differences with results like row counts, checksums, column count, query execution time including execution errors and warnings.

So how do you exactly test your queries on servers of multiple versions.

  1. First, ideally, each server must have identical set of data. One approach and most recommended is by having a slave upgraded to your target version and let it replicate until such time you are ready to test.  Another is populating spare servers from backup and testing with them. With the former, you can easily verify that data is consistent across the master and the slave with pt-table-checksum.
  2. Capture your production queries, we recommend either slow query log (use long_query_time = 0) or tcpdump (other types supported by pt-query-digest’s –type option is also possible, see below).
  3. Test away! Some example scenarios below.

Using slow query log:

Using sources supported by pt-query-digest –type:

Few essential notes when using pt-upgrade:

  1. Make sure to cover all your query types from your log (slow log or tcpdump). If you have 5GB of log collected within 2hrs, it does not make sense to test all 5GB if your runtime will not cover all fingerprints. Passing the queries to pt-query-digest and using a sample of 100 like above should be enough sample per fingerprint and reduces the runtime (–runt-time) of the tests.
  2. Using a separate database and table with –temp-database and –temp-table is recommended, this ensures separation of the test table from your production databases and tables.
  3. Use a separate account for testing – one that has read (i.e. SELECT) to all objects within the database server and has all privileges to the specified –temp-database from #1. pt-upgrade is a read-only tool, however I can only emphasize you should try to use an account with only the necessary privileges.
  4. It is also important to note to run pt-upgrade itself on a 3rd server if the servers you are testing against are on two different machines. This helps account for the network latency and provide more accurate results.
  5. Do not test against production servers, 1) because it can incur extra load and 2) the existing production load can affect your results. Testing on EC2 (+EBS) is no exception, although you can easily spin up instances for testing, the unpredictable nature of the cloud can also affect your results.
  6. Lastly, although if you are upgrading your hardware alongside your MySQL version, you should also consider running these tests on similar hardware and OS configuration. Sometimes, a slight difference in these factors can skew your results greatly.

Here is a simple report output from pt-upgrade. As you can see, there are no errors or warnings and only differences with query times.

Looking at the sample above, there are microsecond differences between the query times. The most important is the average (avg) which tells in general how each fingerprint performs on the test servers. About 10% variation is tolerable and can be accounted for other MySQL overhead like network latency.

pt-upgrade provides valuable insight on how your production queries will behave on your target version, but it is only a part of the process. There is still no alternative to doing your research for incompatibilities, changes and bug fixes or new bugs introduced that can potentially break your application.

Share this post

Comments (8)

  • Will

    This is a great process. I’ve used this to go from MySQL 5.0 to 5.5 in less than 3 months (including an intermediate upgrade to 5.1)

    January 6, 2012 at 1:42 am
  • sam

    I have got full query log including insert, delete and update as well. Should I parse to pt-query-digest first and print in the sloq query log format or does pt-upgrade will work as efficient with ‘–convert-to-select ‘? if I have to parse it to pt-quuery-digest first and then test it with pt-upgrade?

    # pt-query-digest query.log –no-report –print > log-query

    Should I assume the output would be slow query format?

    Second question is doest these test table and test databases are human created or they are being created as a part of this query process?

    what are your thoughtst for the following query?

    #pt-upgrade /var/lib/mysql/query.log h=localhost,u=root,P=3306 h=,u=perc,p=perc,P=3306 –convert-to-select –compare results,warnings –compare-results-method rows –limit 100% –temp-database pt_upgrade –clear-warnings-table pt_upgrade.temp –continue-on-error –base-dir /tmp/ptupgrade/ > /tmp/ptupgrade/test1 2>/tmp/ptupgrade/test1.error

    July 23, 2012 at 8:34 am
  • sam

    One more thing my log is not from one database and it belongs to different 3-4 databases.

    July 23, 2012 at 8:52 am
  • Jervin Real


    pt-upgrade is a read-only tool and it will replay only SELECT statements. pt-query-digest will not convert to SELECT automatically with –no-report –print so you should go straight with pt-query-digest. You only need to pass through pt-query-digest of the input file format is other than slow query log and yes the output is is slow query log format.

    The temp tables will be created automatically – the database should pre-exist though. From your command, I suggest you leave the default comparisons, not just results and warnings.

    July 24, 2012 at 4:50 am
  • sam


    Thanks for replying. I thought its ok to use pt-upgrade –convert-to-select with query.log (without passing this log to pt-query-digest) and it will covert all of the DML queries to select and then run it on both machines. I could see some entries on both servers query logs like INSERT and UPDATE, so I assumed as its definition (–convert-to-select) its working fine but I might be wrong then.

    So correct me if I am wrong, I will be doing the following steps

    1. # pt-query-digest query.log > /var/lib/mysql/log-query-for-pt-upgrade
    2. # pt-upgrade /var/lib/mysql/log-query-for-pt-upgrade h=localhost,u=root,P=3306 h=,u=perc,p=perc,P=3306 –convert-to-select –limit 100% –temp-database pt_upgrade –clear-warnings-table pt_upgrade.temp –continue-on-error –base-dir /tmp/ptupgrade/ > /tmp/ptupgrade/test1 2>/tmp/ptupgrade/test1.error

    Here I have omitted –compare results,warnings –compare-results-method rows,

    Should I use –convert-to-select in the above pt-upgrade query?

    I have same set of data in mysql-4.1.x and mysql-5.5.x (tested with pt-table-checksum).

    My aim is to get a list of errors, queries where I have those warning or errors so we can rewrite the sql queries if required?

    Thanks for your time.

    July 24, 2012 at 6:38 am
  • sam

    Any recommendations?

    July 26, 2012 at 8:58 am
  • Jervin Real


    Again – if your log file is already slow log format, you can skip #1 (pt-query-digest). #2 should be fine 🙂

    July 26, 2012 at 9:06 pm
  • Pembicara Internet Marketing

    only need to pass through pt-query-digest of the input file format is other than slow query log and yes the output is is slow query log format. About 10% variation is tolerable and can be accounted for other MySQL overhead like network latency.

    September 8, 2016 at 11:44 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.