pt-online-schema-change

pt-online-schema-change
Name	pt-online-schema-change
Developer	Percona
Released	2009
Programming language	Perl
Operating system	Unix-like
License	GNU General Public License

pt-online-schema-change pt-online-schema-change is a command-line utility for performing nonblocking schema changes on MySQL and MariaDB tables, developed by Percona. It enables administrators and developers to alter table structures with minimal downtime by creating shadow tables and copying rows while preserving availability for applications such as Facebook, Twitter, GitHub, Shopify, and Airbnb. The tool is part of the Percona Toolkit and is often used alongside replication, backup, and monitoring systems from organizations like Oracle Corporation, Amazon Web Services, Google Cloud Platform, and Microsoft Azure.

Overview

pt-online-schema-change implements an online DDL approach that circumvents full-table locks characteristic of traditional ALTER TABLE operations in MySQL 5.0, MySQL 5.6, and MariaDB 10.0. It is designed to integrate with environments running InnoDB, MyISAM, or other storage engines supported by MySQL forks and distributions such as Oracle MySQL, MariaDB Corporation, Percona Server for MySQL, and cloud-managed services including Amazon RDS and Google Cloud SQL. Administrators from enterprises like LinkedIn, eBay, Booking.com, Spotify, and Netflix have adapted similar techniques to maintain service-level agreements (SLAs) during schema evolution.

Operation and Features

The tool performs schema changes by creating a new table, copying data, and using triggers to synchronize ongoing changes; this strategy is comparable to techniques used in Facebook’s online schema changes and in projects like gh-ost. Core features include per-row copying throttling, chunked data migration, and filesystem-friendly temporary table naming compatible with systems such as Linux, FreeBSD, and Solaris. pt-online-schema-change supports safeguards like --dry-run and interactive prompts familiar to users of Percona Toolkit utilities and can integrate with monitoring stacks including Prometheus, Grafana, and Zabbix. The utility is scriptable with shell pipelines and orchestration platforms like Ansible, Chef, Puppet, and Kubernetes.

Usage and Examples

Common usage patterns involve invoking the tool against live databases on hosts managed by SaltStack or Terraform and coordinating with change control systems such as JIRA and ServiceNow. Examples include adding indexes for workloads generated by Magento stores, adjusting VARCHAR columns for WordPress sites, and modifying schemas backing Postgres-to-MySQL migration workflows used by Dropbox. Administrators often run pt-online-schema-change with replication-aware options when using MySQL Replication, Group Replication, or Galera Cluster to avoid replication lag and inconsistencies. Integration examples show combining the tool with backup solutions like Percona XtraBackup and point-in-time recovery workflows in Amazon Aurora migrations.

Safety, Limitations, and Performance Considerations

pt-online-schema-change reduces downtime but introduces risks such as trigger overhead, replication lag, and increased disk I/O affecting systems like Redis caching layers or Elasticsearch indexing pipelines. It requires careful coordination to avoid conflicts with long-running transactions from applications like Drupal or data warehouses like Snowflake. Limitations include incompatibilities with certain operations (for example, changing primary keys or operations on tables with foreign key constraints as encountered in Oracle Database migrations) and edge cases in versions of MySQL where metadata locking semantics differ. Performance tuning typically involves adjusting --chunk-size, --sleep, and transaction behavior while monitoring metrics reported to New Relic, Datadog, or Splunk.

Compatibility and Requirements

pt-online-schema-change requires client access to the target MySQL server with privileges sufficient to create tables, triggers, and perform DML operations; it is compatible with distributions and forks such as Oracle MySQL, MariaDB, and Percona Server for MySQL. The host environment typically runs on Unix-like systems including Ubuntu, Debian, CentOS, and Red Hat Enterprise Linux, and uses Perl modules common to distributions maintained by organizations like Debian Project and Red Hat. The tool assumes InnoDB or compatible storage engines for safe online operations and may not support engines with differing transactional semantics without risk, as documented in operational playbooks used by Wikimedia Foundation and large-scale deployments at NASA.

History and Development

pt-online-schema-change was introduced by Percona as part of the Percona Toolkit to address operational challenges faced by teams at companies such as Flickr, StumbleUpon, and AOL. Its development responded to needs articulated in conference talks at Percona Live, MySQL Connect, and OSCON and has evolved in dialogue with contributors from Oracle Corporation engineers, independent consultants, and open-source communities including developers from GitLab and Red Hat. The approach draws lineage from earlier industry practices, including online schema change techniques pioneered at Facebook and tools like gh-ost and has been referenced in technical literature and blog posts by Jeremy Cole and other database practitioners.

Alternatives and Related Tools

Notable alternatives include gh-ost, Online Schema Change for MySQL (OSC), and native online DDL features introduced in MySQL 5.6 and enhanced in MySQL 5.7 and MySQL 8.0. Related tooling for migration and orchestration comprises Flyway, Liquibase, Alembic (software), and ecosystem components such as pt-archiver and pt-table-checksum from the Percona Toolkit. Operational ecosystems often combine these with CI/CD platforms like Jenkins, GitHub Actions, and GitLab CI/CD to automate safe schema evolution in production environments.

Category:Database administration tools