Magicians' Secrets: How pt-online-schema-change Performs Zero-Downtime Schema Changes

Tools

pt-online-schema-change, part of Percona Toolkit, is a free tool that performs online, non-blocking schema changes in MySQL. Since most ALTER actions lock the table, they are not suitable for production environments where the table cannot be locked for long (or short) periods of time. Therefore, the only solution for altering a table without affecting normal access to it is an online schema change tool, such as pt-online-schema-change.

pt-online-schema-change is, however, rather "magical"--that is to say, complex and subtle--because it must overcome several limitations inherent in MySQL. This session will explore and explain the various "tricks" employed by pt-online-schema-chnage. Although the tool is designed to work safely and automatically in most environments, anyone using or thinking of using the tool should know what it does "behind the scenes" to ensure that it works well in their particular environment.

Since using pt-online-schema-change is relatively easy (tell it which table to alter and how), this session will focus on advanced technical details of the tool rather than how to run it. A broad understanding of MySQL topics--such as replication, triggers, ALTER statements, foreign keys, etc.--will be presumed. The information presented will give the user an expert-level knowledge of the tool.

Speakers

Daniel Nichter
Lead User Tools Developer, Percona
Speaker Biography: 
Daniel Nichter is the lead developer and database tools specialist at Percona. He has been developing database tools for MySQL since 2005. At the 2010 MySQL Conference & Expose, Daniel was named the Community Contributor of the Year for his work on Maatkit.