Vitess introduces a new way to run schema migrations: non-blocking, asynchronous, scheduled online DDL. With online DDL Vitess simplifies the schema migration process by taking ownership of the operational overhead, and providing the user a simple, familiar interface: the standard ALTER TABLE statement. Let’s first give some background and explain why schema migrations are such an issue in the databases world, and then dive into implementation details The relational model and the operational overhead # The relational model is one of the longest surviving models in the software world, introduced decades ago and widely used until today.
10 Older Entries »
Recently I was dealing with an unexpected issue raised by our Support customer, in which data became inconsistent after a schema change was applied.
After some investigation, it turned out that affected tables had a special word in the comments of some columns, which triggered an already known (and fixed) issue with the TableParser.pm library of Percona Toolkit. The problem is that the customer was using an outdated Toolkit version, where pt-online-schema-change was using that buggy parser.
This bug applies only to Percona Toolkit versions up to 3.0.10, so if you have already 3.0.11 or newer installed, you can skip the rest of this post as these are no longer affected.
I am writing this post to warn every user of pt-online-schema-change who has not upgraded the toolkit, as …[Read more]
Foreign key related issues are very common when dealing with DDL changes in MySQL using Percona toolkit. In this blog post, I will explain how the tool (pt-online-schema-change) handles foreign key constraints when executing a DDL change.
First of all, I would like to explain why foreign keys have to be handled at all before writing more about the “How”. Foreign key constraints are aware of table rename operations. In other words, if the parent table is renamed, the child table automatically knows it and changes the foreign key constraint accordingly. Please have a look at the below example, and you can see the table name is automatically updated in the child table after the rename operation on the parent table:
mysql> show create table prd_details \G *************************** 1. row *************************** …[Read more]
Before we proceed, here is useful blog post written by Peter on Hijacking Innodb Foreign Keys.
However, if you are trying to get rid of an unused Foreign Key (FK) constraint and related columns from versions older than mysql 5.6 or tables which cannot be executed with
ALTER TABLE ... ALGORITHM=INPLACE
because of limitations mentioned here (specifically tables with 5.5 TIMESTAMP formats), you can use pt-online-schema-change to drop such foreign keys.
For DROP FOREIGN KEY
with PT-OSC requires specifying
rather than the real
constraint_name …[Read more]
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 foreign key (FK) constraint and related columns from versions older than MySQL 5.6, or tables that cannot be executed with
ALTER TABLE ... ALGORITHM=INPLACE
because of limitations mentioned here (specifically, …[Read more]
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.
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 is easy to add disk space. But what if you are not that lucky, and your datadir is running on a single disk? Not so funny!
That is the exact situation we recently faced with a customer, for both the master and slave server. When trying to figure out a solution we saw that:
- There was enough space on a different partition within the same server.
- The tables have their own tablespace (innodb_file_per_table = on)
- The MySQL version was …
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 details. First, a high-level explanation of how the tool works.
This is an example from the documentation:
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor
The tool runs an ALTER on the table “actor” from the database “sakila.” The alter adds a column named “c1” of type …[Read more]
This article discusses how to salvage a
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
RENAME TABLE and
commands. The normal process to recover from a crashed
pt-online-schema-change is to drop the triggers on
your original table and drop the new table created by the script.
Then you would restart
this case, this wasn’t possible.
A customer recently needed to add a primary key column to a very busy table (with around 200 million rows). The table only had a …[Read more]
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 in some cases, we may want to apply an
first on slaves, taking them out of traffic pool one by one and bringing them back after the
is done. In the end, we can promote one of the already altered slaves to be new master, so that the downtime/maintenance time is greatly minimized. The ex-master can be altered later, without affecting production. Of course, this method works best when the schema change is …[Read more]
10 Older Entries »