Why Online Schema Change?<\/h2>\n
Online schema change tools come as workarounds to an old problem: schema migrations in MySQL were blocking, uninterruptible, aggressive in resources, replication unfriendly. Running a straight Yes. InnoDB supports Online DDL<\/a>, where for many Yes. But unfortunately extremely limited. Mostly just for adding a new column. See here<\/a> or again here<\/a>. Instant DDLs showed great promise when introduced<\/a> (contributed to MySQL by Tencent Games DBA Team) three years ago, and the hope was that MySQL would support many more types of True. But you don’t need to to be Google, or Facebook, or GitHub etc. scale to feel the pain of schema changes. Any non trivially sized table takes time to In the relational model tables have relationships. A column in one table indicates a column in another table, so that a row in one table has a relationship one or more rows in another table. That’s the “foreign key”. A foreign key constraint<\/em> is the enforcement of that relationship. A foreign key constraint is a database construct which watches over rows in different tables and ensures the relationship does not break. For example, it may prevent me from deleting a row that is in a relationship, to prevent the related row(s) from becoming orphaned.<\/p>\n No, this is a technical discussion (we’re getting there, I promise). But, for context:<\/p>\n I’ve been working on and around schema migration for many years now, and my current work on Vitess introduces some outrageous<\/em> new super powers for schema migrations, which I can’t wait to present (and if you can’t wait, either, feel free to browse the public PRs, it’s free and open source).<\/p>\n Every once in a while, this<\/a> pops up, on twitter, on Hacker News, on internal discussions. And the question gets asked: why can’t we support foreign keys?<\/p>\n And so this post explains why, technically, there’s an inherent problem in supporting foreign keys in Online Schema Changes. This is not about opinions for or against foreign keys.<\/p>\n Yes, no. Not quite, and I’ll elaborate as we dive into the details. And, to clarify, OK, let’s dive in.<\/p>\n Consider the following extremely simplified model. Don’t judge me on the oversimplification, we just want to address the foreign keys issue here.<\/p>\n Say we want to What have ended up with? Take a look:<\/p>\n The table To drop the foreign key constraint from Alas, more turtles. Dropping a MySQL table is production is a cause for outage. Here’s a lengthy discussion<\/a> form the ALTER TABLE<\/code> in production means locking your table, generating high load on the primary, causing massive replication lag on replicas once the migration moves down the replication stream.<\/p>\nIsn’t there some Online DDL?<\/h2>\n
ALTER<\/code> types, your table remains unblocked throughout the migration. That’s an important improvement, but unfortunately not enough. Some migration types do not permit concurrent DDL (notably changing column data type, e.g. from INT<\/code> to BIGINT<\/code>). Migration is still aggressive and generates high load on your server. Replicas still run the migration sequentially. If your migration takes 5 hours to run concurrently on the primary, expect a 5 hour replication lag on your replica, i.e. complete loss of your fresh read capacity.<\/p>\nIsn’t there some Instant DDL?<\/h2>\n
ALTER TABLE<\/code> in INSTANT<\/code> DDL. At this time this has not happened yet, and we do with what we have.<\/p>\nNot everyone is Google or Facebook scale, right?<\/h2>\n
ALTER<\/code>, which results with lock\/downtime. If your tables are limited to hundreds or mere thousands of small rows, you can get away with it. When your table grows, and a mere dozens of MB of data is enough, ALTER<\/code> becomes non-trivial at best case, and outright a cause of outage in a\u00a0common<\/em> scenario, in my experience.<\/p>\nLet’s discuss foreign key constraints<\/h2>\n
Is this a biased post? We hear you don’t like foreign keys<\/h2>\n
Wait! pt-online-schema-change does<\/em>\u00a0support foreign keys! There’s command line flags for that!<\/h2>\n
pt-online-schema-change<\/code> attempts to make the best of the situation. Back when developing gh-ost<\/code>, we saw that as a non-feasible solution. pt-online-schema-change<\/code> does a good job at explaining the restrictions and limitations of its foreign key support, and we will cover these and beyond, here.<\/p>\nWe begin, let’s first present a model<\/h2>\n
\nCREATE TABLE country (\nid INT NOT NULL,\nname VARCHAR(255) NOT NULL,\nPRIMARY KEY (id)\n);\n\nCREATE TABLE person (\nid INT NOT NULL,\ncountry_id INT NOT NULL,\nname VARCHAR(255) NOT NULL,\nPRIMARY KEY(id),\nKEY country_idx (country_id),\nCONSTRAINT person_country_fk FOREIGN KEY (country_id) REFERENCES country(id) ON DELETE NO ACTION\n);\n\nCREATE TABLE company (\nid INT NOT NULL,\ncountry_id INT NOT NULL,\nname VARCHAR(255) NOT NULL,\nPRIMARY KEY(id),\nKEY country_idx (country_id),\nCONSTRAINT company_country_fk FOREIGN KEY (country_id) REFERENCES country(id) ON DELETE NO ACTION\n);\n<\/pre>\n
Some analysis, rules and facts<\/h2>\n
\n
\n
country<\/code> is a parent<\/em> table in both relationship<\/li>\nperson<\/code> is a child<\/em>\u00a0table in relationship with country<\/code><\/li>\ncompany<\/code> is a child<\/em>\u00a0table in relationship with country<\/code><\/li>\n<\/ul>\n<\/li>\ncountry<\/code> is a small table (maybe a couple hundred rows), and that both person<\/code> and company<\/code> are large tables (just, large enough to be a problem)<\/li>\n
\n> MySQL\u2019s plan is to add foreign keys for all storage engines. The plan is on print for quite a few years now.
\nThis didn’t happen, external foreign keys do not exist.<\/li>\nALTER TABLE<\/code> statement. This is where the turtles begin to pile up.<\/li>\nRENAME<\/code> a parent table, for example, than children’s foreign keys follow the table under its new name. This is where our pillar of turtles becomes higher.<\/li>\nNO ACTION<\/code> (aka RESTRICT<\/code>), but it doesn’t really matter to our discussion.<\/li>\nSET FOREIGN_KEY_CHECKS=0<\/code><\/li>\nSET GLOBAL FOREIGN_KEY_CHECKS=0<\/code>, but this does not affect existing sessions, only ones created after your statement.<\/li>\ngh-ost<\/code>, fb-osc<\/code>, pt-online-schema-change<\/code> ,LHM<\/code>, and Vitess’s VReplication<\/code>, work by creating a “shadow” table, which I like to call the\u00a0ghost<\/em> table.\n\n
RENAME<\/code> the original table away, e.g. to _mytable_old<\/code>, and RENAME<\/code> the ghost table in its place, at which time it assumes production traffic.<\/li>\n![\"\"](\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2021\/03\/osc-flow.png.png\")
<\/a><\/p>\nChanging a child table<\/h2>\n
ALTER TABLE person MODIFY name VARCHAR(1024) NOT NULL CHARSET utf8mb4<\/code>. Or add a column. Or an index. Whichever. Let’s see what happens.<\/p>\nperson<\/code> has a foreign key. We therefore create the ghost table with similar foreign key, a child table that references the parent country<\/code> table. Funnily, even though InnoDB’s foreign keys live inside a table scope, their names are globally unique. So we create the ghost table as follows:<\/p>\n\nCREATE TABLE _person_ghost (\nid INT NOT NULL,\ncountry_id INT NOT NULL,\nname VARCHAR(255) NOT NULL,\nPRIMARY KEY(id),\nKEY country_idx (country_id),\nCONSTRAINT person_country_fk2 FOREIGN KEY (country_id) REFERENCES country(id) ON DELETE NO ACTION\n);\n\n<\/pre>\n
\n
pt-online-schema-change<\/code> is based on synchronous, same-transaction, data copy via triggers. At any point in time, if we populate _person_ghost<\/code> with a row, that row also exists in the original person<\/code> table during that same transaction. This means the data we insert to _person_ghost<\/code> is foreign key safe.<\/li>\ngh-ost<\/code>, fb-osc<\/code>, Vitess<\/code> use an asynchronous approach where they tail either the binary logs or a changelog table. It is possible that as we INSERT<\/code> data to _person_ghost<\/code>, that data no longer exists in person<\/code>. It is possible that there’s no matching entry in country<\/code>! We can overcome that by disabling foreign key checks on our session\/connection that populates the ghost table. We run SET FOREIGN_KEY_CHECKS=0<\/code> as make the server (and our users!) a promise, that even while populating the table there may be inconsistencies, we’ll figure it all out at time of cut-over.<\/li>\n_person_ghost<\/code> in place of person<\/code>.<\/li>\n<\/ul>\nERROR!<\/h3>\n
![\"\"](\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2021\/03\/fk-tables.png\")
<\/a><\/p>\nperson_OLD<\/code> still exists, and maintains a foreign key constraint on country<\/code>. Now, suppose we want to delete country<\/code> number 99<\/code>. We delete or update all rows in person<\/code> which point to country 99<\/code>. Good. We proceed to DELETE FROM country WHERE id=99<\/code>. We can’t. That’s because person_OLD<\/code> still has rows where country_id=99<\/code>.<\/p>\nWell, why don’t you just drop that old constraint?<\/h3>\n
person_old<\/code> is to ALTER TABLE person_old DROP FOREIGN KEY person_country_fk<\/code>. What’s that? An ALTER TABLE<\/code>? Wasn’t that the thing we wanted to avoid in the first place? There was a reason we ran an online schema change! So that’s an absolute no go.<\/p>\nWell, why don’t you just drop the old table?<\/h3>\n
pt-online-schema-change<\/code> offers --alter-foreign-keys-method drop_swap<\/code>: to get rid of the foreign key we can drop the old table. The logic it offers is:<\/p>\n\n
DROP<\/code> the original table (e.g. person<\/code>)<\/li>\nRENAME<\/code> the ghost table in its place<\/li>\n<\/ol>\nProblem: DROP<\/h3>\n
gh-ost<\/code> repo. Digging my notes shows this post<\/a> from 2010. This is an ancient problem where dropping a table places locks on buffer pool and on adaptive hash index, and there’s been multiple attempts to work around it. See Vitess’s table lifecycle<\/a> for more.<\/p>\n
![\"\"](\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2021\/03\/parent-side-swap-1.png\")
<\/a><\/p>\n