MySQL 5.6 Compatible Percona Toolkit 2.2 Released
This post comes from Daniel Nichter at the MySQL Performance Blog.
A new Percona Toolkit series has been released: Percona Toolkit 2.2 for MySQL 5.6. Several months in the making and many new features, changes, and improvements make this a great new series. It replaces the 2.1 series for which we plan to do only one more bug fix release (as 2.1.10) then retire. 2.2 is largely backward-compatible with 2.1, but some tools were changed significantly (e.g. pt-query-digest, pt-upgrade, and pt-online-schema-change). Here are some highlights:
Official support for MySQL 5.6 and Percona XtraDB Cluster
We started beta support for MySQL 5.6 in 2.1.8 when 5.6 was still beta. Now that MySQL 5.6 is GA, so is our support for it. Check out the Percona Toolkit supported platforms and versions. When you upgrade to MySQL 5.6, be sure to upgrade to Percona Toolkit 2.2, too.
We also started beta support for Percona XtraDB Cluster in 2.1.8, but now that support is official in 2.2 because we have had many months to work with PXC and figure out which tools work with it and how. There’s still one noticeable omission: pt-table-sync. It’s still unclear if or how one would sync a cluster that, in theory, doesn’t become out-of-sync. As Percona XtraDB Cluster develops, Percona Toolkit will continue to evolve to support it.
pt-online-schema-change (pt-osc) is much more resilient
pt-online-schema-change 2.1 has been a great success, and people have been using it for evermore difficult and challenging tasks. Consequently, we needed to make it “try harder”, even though it already tried pretty hard to keep working despite recoverable errors and such. Whereas pt-osc 2.1 only retries certain operations, pt-osc 2.2 retries every critical operation, and its tries and wait time between tries for all operations are configurable. Also, we removed –lock-wait-timeout which set innodb_lock_wait_timeout because that now conflicts, or is at least confused with, lock_wait_timeout (introduced in MySQL 5.5) for metadata locks. Now –set-vars is used to set both of these (or any) system variables. For a quick intro to metadata locks and how they may affect you, see Ovais’s article.
In short: pt-online-schema-change 2.2 is far more resilient out of the box. It’s also aware of metadata locks now, whereas 2.1 was not really aware of them. And it’s highly configurable, so you can make the tool try very hard to keep working.
pt-upgrade is brand-new
pt-upgrade was written once long ago and not updated since. Now that we have four base versions of MySQL (5.0, 5.1, 5.5, and 5.6), plus at least four major forks (Percona Server, MariaDB, Percona XtraDB Cluster, and MariaDB Galera Cluster), upgrades are fashionable, so to speak. Problem is: “original” pt-upgrade was too noisy and too complex. pt-upgrade 2.2 is far simpler and far easier to use. It’s basically what you expect from such a tool. Moreover, it has a really helpful new feature: “reference results”, i.e. saved results from running queries on a server. Granted, this can take a lot of disk space, but it allows you to “run now, compare later.”
If you’re thinking about upgrading, give pt-upgrade a try. It also reads every type of log now (slow, general, binary, and tcpdump), so you shouldn’t have a problem finding queries to run and compare.
pt-query-digest is simpler
pt-query-digest 2.2 has fewer options now. Basically, we re-focused it on its primary objective: analyzing MySQL query logs. So the ability to parse memcached, Postgres, Apache, and other logs was removed. We also removed several options that probably nobody ever used, and changed/renamed other options to be more logical. The result is a simpler, more focused tool, i.e. less overwhelming.
Also, pt-query-digest 2.2 can save results in JSON format (–output=json). This feature is still in development while we determine the optimal JSON structure.
Version check is on by default
In 2.1.4, released September/October 2012, we introduced a feature called “version check” into most tools. It’s like a lot of software that automatically checks for updates, but it’s also more: it’s a free service from Percona that advises when certain programs (Percona Toolkit tools, MySQL, Perl, etc.) are either out of date or are known bad versions. For example, there are two versions of the DBD::mysql Perl module that have problems. And there are certain versions of MySQL that have critical bugs. Version check will warn you about these if your system is running them.
What’s new in 2.2 is that, whereas this feature (specifically, the option in tools: –version-check) was off by default, now it’s on by default. If the IO::Socket::SSL Perl module is installed (easily available through your package manager), it will use a secure (https) connection over the web, else it will use a standard (http) connection.
pt-stalk and pt-mysql-summary have built-in MySQL options
No more “pt-stalk — -h db1 -u me”. pt-stalk 2.2 and pt-mysql-summary 2.2 have all the standard MySQL options built-in, like other tools: –user, –host, –port, –password, –socket, –defaults-file. So now the command line is what you expect: pt-stalk -h dhb1 -u me.
pt-stalk –no-stalk is no longer magical
Originally, pt-stalk –no-stalk was meant to simulate pt-collect, i.e. collect once and exit. To do that, the tool magically set some options and clobbered others, resulting in no way to do repeated collections at intervals. Now –no-stalk means only that: don’t stalk, just collect, respecting –interval and –iterations as usual. So to collect once and exit: pt-stalk –no-stalk –iterations 1.
pt-fk-error-logger and pt-deadlock-logger are standardized
Similar to the pt-stalk –no-stalk changes, pt-fk-error-logger and pt-deadlock-logger received mini overhauls in 2.2 to make their run-related options (–run-time, –interval, –iterations) standard. If you hadn’t noticed, one tool would run forever by default, while the other would run once and exit. And each treated their run-related options a little differently. This magic is gone now: both tools run forever by default, so specify –iterations or –run-time to limit how long they run.
There are more changes in addition to those highlights. For example, three tools were removed, and there were several bug fixes. See https://launchpad.net/percona-toolkit/+milestone/2.2.1 for the full list. If you upgrade from 2.1 to 2.2, be sure to re-read tools’ documentation to see what has changed.
As the first release in a new series, 2.2 features are not yet finalized. In other words, we may change things like the pt-query-digest –output json format in future releases after receiving real-world feedback.
In summary, Percona Toolkit 2.2 for MySQL is an exciting release with many new and helpful features. Users are encouraged to begin upgrading, particularly given that, except for the forthcoming 2.1.10 release, no more work will be done on 2.1 (unless you’re a Percona customer with a support contract or other agreement).