Tag - pt-online-schema-change

Dropping the Foreign Key Constraint Using pt-online-schema-change

pt-online-schema-change

In this blog post, we’ll look at how to get rid of the unused Foreign Key (FK) constraint and/or related columns/keys with the help of pt-online-schema-change and the power of its plugins.
Before we proceed, here is a useful blog post written by Peter Zaitsev on Hijacking Innodb Foreign Keys.
If you are trying to get rid of an unused […]

Read more

Percona Toolkit 2.2.20 is now available

Percona Toolkit

Percona announces the availability of Percona Toolkit 2.2.20. Released December 9, 2016, Percona Toolkit is a collection of advanced command-line tools that perform a variety of MySQL server and system tasks that DBAs find too difficult or complex for to perform manually. Percona Toolkit, like all Percona software, is free and open source.
This release is the current […]

Read more

Changing the Tablespace Directory with pt-online-schema-change

MariaDB ColumnStore

In this blog, we’ll discuss changing the tablespace directory using pt-online-schema-change.
One of the most annoying situations in the life of a DBA is realizing that the disk where the datadir resides is running out of space. If you’re lucky enough to run over an LVM volume or a RAID (depending on the level, though), it […]

Read more

Amazon RDS and pt-online-schema-change

Amazon RDS and pt-online-schema-change

In this blog post, I discuss some of the insights needed when using Amazon RDS and pt-online-schema-change together.
The pt-online-schema-change tool runs DDL queries (ALTER) online so that the table is not locked for reads and writes. It is a commonly used tool by community users and customers. Using it on Amazon RDS requires knowing about some specific […]

Read more

Rescuing a crashed pt-online-schema-change with pt-archiver

crashed pt-online-schema-change

This article discusses how to salvage a crashed pt-online-schema-change by leveraging pt-archiver and executing queries to ensure that the data gets accurately migrated. I will show you how to continue the data copy process, and how to safely close out the pt-online-schema-change via manual operations such as RENAME TABLE and DROP TRIGGER commands. The normal process […]

Read more

pt-online-schema-change (if misused) can’t save the day

GTID replication

In this blog post we’ll discuss pt-online-schema-change, and how to correctly use it.
Always use pt-osc?
Altering large tables can be still a problematic DBA task, even now after we’ve improved Online DDL features in MySQL 5.6 and 5.7. Some ALTER types are still not online, or sometimes just too expensive to execute on busy production master.
So […]

Read more

Testing MySQL partitioning with pt-online-schema-change

There are cases where we need to implement MySQL partitioning, or at least test if it is an option to solve an issue. However, how are we able to test it when the table on our production environment has hundreds of millions of rows, several composite indexes and/or the size on disk is hundreds of gigabytes?
Testing […]

Read more

Schema changes in MySQL for OpenStack Trove users

People using OpenStack Trove instances can hit a common issue in the MySQL world: how to perform schema change operations while minimizing the impact on the database server? Let’s explore the options that can allow online schema changes.
Summary
With MySQL 5.5, pt-online-schema-change from Percona Toolkit is your best option for large tables while regular ALTER […]

Read more

Avoiding MySQL ALTER table downtime

MySQL table alterations can interrupt production traffic causing bad customer experience or in worst cases, loss of revenue. Not all DBAs, developers, syadmins know MySQL well enough to avoid this pitfall. DBAs usually encounter these kinds of production interruptions when working with upgrade scripts that touch both application and database or if an inexperienced […]

Read more

Syncing MySQL slave table with pt-online-schema-change

I recently encountered a situation in which after running Percona Toolkit’s pt-table-checksum on a customer system, 95% of the table on the MySQL master was different on the MySQL slave. Although this table was not a critical part of the infrastructure, from time to time, writes to the table from the master would break […]

Read more