The latest from the MySQL community

Percona Toolkit’s pt-online-schema-change (pt-osc) has long been the preferred solution for performing online schema changes with minimal downtime. Its chunk-based copy algorithm is designed to adapt dynamically to the workload, making it suitable for very large tables in production environments.

However, under specific conditions, one of its optimization mechanisms can become counterproductive.

During a customer engagement involving Percona XtraDB Cluster (PXC), we investigated a case where running pt-online-schema-change with a selective –where clause caused severe Galera Flow Control and general overhead, effectively stalling the cluster for an extended period. The issue was not caused by the schema change itself, but by the interaction between:

• adaptive chunk auto-resizing
• highly selective copying of the newest rows
• clustered replication in Galera
• InnoDB …

In this blog post, I will run a gh-ost benchmark against the performance of pt-online-schema-change.

When gh-ost came out, I was very excited. As MySQL ROW replication became commonplace, you could use it to track changes instead of triggers. This practice is cleaner and safer compared to Percona Toolkit’s pt-online-schema-change. Since gh-ost doesn’t need triggers, I assumed it would generate lower overhead and work faster. I frequently called it “pt-online-schema-change on steroids” in my talks. …