Navigating the complexities of database upgrades can be daunting, but with pt-upgrade, the process becomes manageable.

Introducing pt-upgrade: Your Go-To Tool for MariaDB Troubleshooting in a Linux Environment

Navigating the complexities of database upgrades can be daunting, but with pt-upgrade, the process becomes manageable. This versatile tool verifies that query results are identical on different servers, ensuring a smooth transition between MySQL versions.

pt-upgrade

NAME

pt-upgrade - Verify that query results are identical on different servers.

SYNOPSIS

Usage

pt-upgrade[OPTIONS]LOGS|RESULTSDSN[DSN]

pt-upgrade executes queries in the given MySQL LOGS on each DSN, compares the results, and reports any significant differences. The tool can also save the results for later analyses. LOGS can be slow, general, binary, tcpdump, and “raw”.

Compare host2 to host1 using queries in slow.log:

pt-upgrade h=host1 h=host2 slow.log

Compare host2 to saved results from host1:

pt-upgrade h=host1 --save-results host1_results/ slow.log

pt-upgrade host1_results1/ h=host2

RISKS

Percona Toolkit is mature, proven in the real world, and well tested, but all database tools can pose a risk to the system and the database server. Before using this tool, please:

• Read the tool’s documentation

• Review the tool’s known “BUGS”

• Test the tool on a non-production server

• Backup your production server and verify the backups

DESCRIPTION

pt-upgrade helps determine if it is safe to upgrade (or downgrade) to a new version of MySQL. A safe and conservative upgrade plan has several steps, one of which is ensuring that queries will produce identical results on the new version of MySQL.

pt-upgrade executes queries from slow, general, binary, tcpdump, and “raw” logs on two servers, compares many aspects of each query’s execution and results, and reports any significant differences. The two servers are typically development servers, one running the current production version of MySQL and the other running the new version of MySQL.

USE CASES

pt-upgrade has two use cases. The first, canonical case is running “host to host”. A log file and two DSN are given on the command line, one for each MySQL server. See the first example in the “SYNOPSIS”. Queries are executed and compared on each server as the tool runs. Queries with differences are printed as the tool runs, or when it finishes (see “WHEN QUERIES ARE REPORTED”). Nothing is saved to disk, so this use case requires less hard disk space, but the queries must be executed on both servers if the tool is ran again, even if one of the servers hasn’t changed. If there are a lot of queries or executing them takes a long time, and one server doesn’t change, you may want to use the second use case.

The second use case is running “reference results to host”. Reference results are the complete results from a single MySQL server, saved to disk. In this case, you must first generate the reference results with --save-results, then run the tool a second time to compare another MySQL server to the results. See the second example in the “SYNOPSIS”. Results are typically generated for the current version of MySQL which doesn’t change. This use case can require a lot of disk space because the results (i.e. rows) for all queries must be saved, plus other data about the queries. If you plan to do many comparisons against a fixed version of MySQL, this use case is more efficient. Or if you don’t have access to both servers at the same time, this use case allows you to “execute now, compare later”.

IMPORTANT CONSIDERATIONS

CONSISTENCY

Consistent environments and consistent data are crucial for obtaining an accurate report. pt-upgrade should never be ran on a production server or any active server because there is no easy way to ensure a synchronous read for each query. If data is changing on either server while pt-upgrade is running, the report could contain more false-positives than legitimate differences.  pt-upgrade assumes that both MySQL servers are static, unchanging (except for any changes made by the tool if ran with ``–no-read-only``). A read-only workload shouldn’t affect the tool, except maybe query times, so read-only slaves could be used.

COMPARED TO

In a host to host comparison, results from the first host establish the norm to which results from the second host are compared. In a reference results to host comparison, the reference results are the norm to which the host is compared. Comparative phrases like “smaller than”, “better than”, etc. mean compared to the norm.

For example, if the query time for an event is 0.01 on the first host and 0.5 on the second host, that is a significant difference because 0.5 is worse than 0.1, and so the query will be reported.

READ-ONLY

By default, pt-upgrade only executes SELECT and SET statements. (This does not include ‘SELECT…INTO’ statements, which do not return rows but dump output to a file or variable.) If you’re using recreatable test or development servers and wish to compare write statements too (e.g. INSERT, UPDATE, DELETE), then specify --no-read-only. If using a binary log, you must specify --no-read-only because binary logs don’t contain SELECT statements. See --[no]read-only.

TRANSACTIONS

The tool does not create its own transactions, but any transactions in the LOG are executed as-is. Since logs are serial, transactions shouldn’t normally be an issue. If, however, you need to compare queries that are somehow transactionally related (in which case you probably also need to disable --[no]read-only), then pt-upgrade probably won’t do what you need because it’s not designed for this purpose.

pt-upgrade runs with autocommit=1 by default.

THROTTLING

pt-upgrade has no throttling options because the tool should only be ran on dedicated testing or development servers. Do not run :program:`pt-upgrade` on production servers! Consequently, the tool is CPU, memory, disk, and network intensive. It executes queries as fast as possible.