November 28, 2014

Percona Toolkit 2.1 with New Online Schema Change Tool

I’m proud to announce the GA release of version 2.1 of Percona Toolkit. Percona Toolkit is the essential suite of administrative tools for MySQL.

With this release we introduce a new version of pt-online-schema-change, a tool that enables you to ALTER large tables with no blocking or downtime. As you know, MySQL locks tables for most ALTER operations, but pt-online-schema-change performs the ALTER without any locking. Client applications can continue reading and writing the table with no interruption.

With this new version of the tool, one of the most painful things anyone experiences with MySQL is significantly alleviated. If you’ve ever delayed a project’s schedule because the release involved an ALTER, which had to be scheduled in the dead of the night on Sunday, and required overtime and time off, you know what I mean. A schema migration is an instant blocker in the critical path of your project plan. No more!

The pt-online-schema-change tool is not new to the toolkit. We developed it some time ago for a client and quietly included it, without knowing how much it would be used. We found that people were using it eagerly even without advertisement. However, it had many limitations and missing features. The new 2.1 version of the tool removes those limitations and makes the tool fully featured and much easier to use. The key is that it is replication-aware and inspects replicas, throttles its operation to avoid causing replication delay, watches for load on the master, and so forth. In this respect it is quite similar to pt-table-checksum, and it has all of the safeguards you might be familiar with from pt-table-checksum.

I will give a free webinar about the new pt-online-schema-change tool and how it enables zero-downtime schema changes on May 2nd.

In addition to pt-online-schema-change, we have completely redesigned and rebuilt the pt-summary and pt-mysql-summary tools. The changes are not so obvious to the user, but the benefit is that in the future we will be able to add features to these tools rapidly, for example, to support new types of RAID controllers.

Percona Toolkit is a hot topic at the upcoming MySQL Conference and there will be many talks about various parts of the toolkit:

The new Percona Toolkit release is available for download today. It is also in our YUM and DEB repositories. And you can read the documentation, discuss it on the mailing list, get the source code from Launchpad, and file bug reports on Launchpad.

PS: in addition to Percona Toolkit’s release today, there is also a version 1.0 GA release of the Percona Monitoring Plugins project. It features a variety of bug fixes.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

Comments

  1. Mr. Left says:

    Is there any performance report about the tool?
    My team deal with a big table, and always need to alter it by adding a column for business need.
    And i also want to know if it’s usefull for MySQL5.5 GA or it’s just for Percona?

  2. It works on all versions of MySQL 5.0 and newer.

  3. Justin says:

    Quick question… does the pt-online-schema-change tool work at tables using the MyISAM storage engine? Would it work to convert a table from MyISAM to InnoDB?

  4. Yes, it should work with all storage engines, because everything it does is performed at the server layer, not the storage engine layer. And I have done just what you have asked, with success.

  5. I would like to point out a limitation that is not currently documented: the tool will get confused if you rename a column, and won’t do the right thing.

  6. Pranab Sharma says:

    This would be a great tool. We have some large tables and sometime we have to alter these table for business requirement which is very painful. Even yesterday I got requirement from my bosses that if we could do online alter without affecting user transactions. Waiting for your webinar. Is there any recording of your tutorial?

  7. No, people pay a lot of money for the tutorials. We don’t give them away to others for free.

  8. Webamster Eddie says:

    Hello,

    Guillame at DotDeb.org led me to percona and I have a few questions:

    1. Will the Percona Toolkit version just released work for regular community MySQL 5.5.23 or does it only work (or work better) with a Percona type version of MySQL – ie one of Percona’s “improved” versions of MySQL (that is what Percona does, isn’t it?)?

    2. Secondly, I read here that you are the lead author of High Performance MySQL. Is that the same title as the O’Reilly “High Performance MySQL” by Jeremy Zawodny & Derek Balling (which I own, but is outdated as it is a 2004 version) ? If so, is there any discount for owners of a previous version, to “upgrade”?

    3. Thirdly, thanks for your help and information. I just made the MySQL, apache2 and php updates available to Debian Squeeze avalable on DotDeb.org, and I find my qps dropping by 2/3 – with no changes to what is happening on the server. Any ideas on what is happening here? It seems as if MySQL 5.5 has been getting much slower (in terms of qps) with the last
    two or three minor updates since the end of last year; shortly after Oracle took it over. Can you comment on why this is happenign, and if it is happening across the board to everyone? Have some updates made to INNOdb tables really slowed down mySQL as measured by qps, in the last 4-6 months?

    Best regards,

    Eddie

  9. 1. Percona Toolkit works for all flavors of MySQL.

    2. I am the lead author of the second and third editions of that book. I have no involvement in selling it. You should ask O’Reilly for a discount. As the author I feel entitled to earn at least $1000 per copy sold, but O’Reilly may not agree with me on that!

    3. It sounds like you are Doing Something Wrong, but that is not a topic for this comment thread. You could try our forum or consulting/support services!

  10. Just wanted to swing by and leave a thank you for the team here at Percona. Thanks to the online schema tool we were able to add 6 new columns to our most active table on our production system with over 20 million records. Not only did the alter complete flawlessly, server load impact was nearly zero while the process was running. You saved us some serious headaches!

    Keep up the great development work.

Speak Your Mind

*