Skip to content

# Thoughts and ideas for Online Schema Change

Development MySQL Development Indexing INFORMATION_SCHEMA InnoDB Open Source openark kit Opinions python Schema scripts Triggers October 7, 2010October 7, 2010

Here’s a few thoughts on current status and further possibilities for Facebook’s Online Schema Change (OSC) tool. I’ve had these thoughts for months now, pondering over improving oak-online-alter-table but haven’t got around to implement them nor even write them down. Better late than never.

The tool has some limitations. Some cannot be lifted, some could. Quoting from the announcement and looking at the code, I add a few comments. I conclude with a general opinion on the tool’s abilities.

#### “The original table must have PK. Otherwise an error is returned.”

This restriction could be lifted: it’s enough that the table has a UNIQUE KEY. My original oak-online-alter-table handled that particular case. As far as I see from their code, the Facebook code would work just as well with any unique key.

However, this restriction is of no real interest. As we’re mostly interested in InnoDB tables, and since any InnoDB table should have a PRIMARY KEY, we shouldn’t care too much.

#### “No foreign keys should exist. Otherwise an error is returned.”

Tricky stuff. With oak-online-alter-table, changes to the original table were immediately reflected in the ghost table. With InnoDB tables, that meant same transaction. And although I never got to update the text and code, there shouldn’t be a reason for not using child-side foreign keys (the child-side is the table on which the FK constraint is defined).

The Facebook patch works differently: it captures changes and writes them to a delta table, to be later (asynchronously) analyzed and make for a replay of actions on the ghost table.

So in the Facebook code, some cases will lead to undesired behavior. Consider two tables, country and city, with city holding a RESTRICT/NO ACTION foreign key on country‘s id. Now consider the scenario:

1. Rows from city are DELETEd, where the country Id is Spain’s.
   - city‘s ghost table is still unaffected, Spain’s cities are still there.
   - A change is written to the delta table to mark these rows for deletion.
2. A DELETE is issued on country‘s Spain record.
   - The DELETE should work, from the user’s perspective
   - But it will fail: city’s ghost table has not received the changes yet. There’s still matching rows. The NO ACTION constraint will fail the DELETE statement.

Now, this does not lead to corruption, just to seemingly unreasonable behavior on the database part. This behavior is probably undesired. NO ACTION constraint won’t do.

However, with CASCADE or SET NULL options, there is less of an issue: operations on the parent table (e.g. country) cannot fail. We must make sure operations on the ghost table make it consistent with the original table (e.g. city).

Consider the following scenario:

1. A new country is created, called “Sleepyland”. An INSERT is made to country.
   - Both city and city‘s ghost are immediately aware of it.
2. A new town is created and INSERTed to city. The town is called “Naphaven”.
   - The change takes time to propagate to city‘s ghost table.
3. Meanwhile, we realized we made a mistake. We’ve been had. There’s no such city nor country.
   1. We DELETE “Naphaven” from city.
   2. We DELETE “Sleepyland” from country.
   - Note that city‘s ghost table still hasn’t caught up with the changes.
4. Eventually, the INSERT statement for “Naphaven” reaches city‘s ghost table.
   - What should happen now? The INSERT cannot succeed.
   - Will this fail the entire process?

Looking at the PHP code, I see that changes written on the delta table are blindly replayed on the ghost table.

Since the process is asynchronous, this should not be the case. We can solve the above if we use INSERT IGNORE instead of INSERT. The statement will fail without failing anything else. The row cannot exist, and that’s because the original row does not exist anymore.

Unlike a replication corruption, this does not lead to accumulation mistakes. The replay is static, somewhat like in binary log format. Changes are just written, regardless of existing data.

I have given this considerable thought, and I can’t say I’ve covered all the possible scenario. However I believe that with proper use of INSERT IGNORE and REPLACE INTO (two statements I heavily relied on with oak-online-alter-table), correctness can be achieved.

There’s the small pain of re-generating the foreign key definition on the “ghost” table (CREATE TABLE LIKE … does not copy FK definitions). And since foreign key names are unique, a new name must be picked up. Not pretty, but perfectly doable.

#### “No AFTER_{INSERT/UPDATE/DELETE} triggers must exist.”

It would be nicer if MySQL had an ALTER TRIGGER statement. There isn’t such statement. If there were such an atomic statement, then we would be able to rewrite the trigger, so as to add our own code to the end of the trigger’s code. Yuck. Would be even nicer if we were allowed to have multiple triggers of same event.

So, we are left with DROP and CREATE triggers. Alas, this makes for a short period where the trigger does not exist. Bad. The easy solution would be to LOCK WRITE the table, but apparently you can’t DROP the trigger (*) when the table is locked. Sigh.

(*) Happened to me, apparently to Facebook too; With latest 5.1 (5.1.51) version this actually works. With 5.0 it didn’t use to; this needs more checking.

#### Use of INFORMATION_SCHEMA

As with oak-online-alter-table, the OSC checks for triggers, indexes, column by searching on the INFORMATION_SCHEMA tables. This makes for nice SQL for getting the exact listing and types of PRIMARY KEY columns, whether or not AFTER triggers exist, and so on.

I’ve always considered this to be the weak part of openark-kit, that it relies on INFORMATION_SCHEMA so much. It’s easier, it’s cleaner, it’s even more correct to work that way — but it just puts too much locks. I think Baron Schwartz (and now Daniel Nichter) did amazing work on analyzing table schemata by parsing the SHOW CREATE TABLE and other SHOW commands regex-wise with Maatkit. It’s a crazy work! Had I written openark-kit in Perl, I would have just import their code. But I’m too lazy busy to do the conversion from Perl to Python, and rewrite that code, what with all the debugging.

OSC is written in PHP. Again, much conversion work. I think performance-wise this is an important step to make.

#### A word for the critics

Finally, a word for the critics. I’ve read some Facebook/MySQL bashing comments and wish to relate.

In his interview to The Register, Mark Callaghan gave the example that “Open Schema Change lets the company update indexes without user downtime, according to Callaghan”.

PostgreSQL was mentioned for being able to add index with only read locks taken, or being able to do the work with no locks using CREATE INDEX CONCURRENTLY. I wish MySQL had that feature! Yes, MySQL has a lot to improve upon, and the latest PostgreSQL 9.0 brings valuable new features. (Did I make it clear I have no intention of bashing PostgreSQL? If not, please re-read this paragraph until convinced).

Bashing related to the notion of MySQL being so poor that Facebook used an even poorer mechanism to work out the ALTER TABLE.

Well, allow me to add a few words: the CREATE INDEX is by far not the only thing you can achieve with OSC (although it may be Facebook’s major concern). You should be able to:

- Add columns
- Drop columns
- Convert character sets
- Modify column types
- Add partitioning
- Reorganize partitioning
- Compress the table
- Otherwise changing table format
- Heck, you could even modify the storage engine! (To other transactional engine)

These are giant steps. How easy would it be to write these down into the database? It only takes a few weeks time to work out a working solution with reasonable limitations, just using the resources the MySQL server provides you with. The MySQL@Facebook team should be given credit for that.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
8
Leave a Reply

8 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
5 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Justin Swanhart

They certainly deserve a lot of credit, and so do you.

Thanks for you contributions to the open source community surrounding MySQL.

Guest
Jonas Oreland

Maybe you already no, but if you’re using ndb with MySQL (aka MySQL Cluster) then we have create/drop index, add column, reorganize partition and optimize table online. And by online I mean without table copying and both reads/writes are allowed in parallel.

Guest
willem

“Well, allow me to add a few words: the CREATE INDEX is by far not the only thing you can achieve with OSC (although it may be Facebook’s major concern). You should be able to: * Add columns * Drop columns * Convert character sets * Modify column types * Add partitioning * Reorganize partitioning * Compress the table * Otherwise changing table format * Heck, you could even modify the storage engine! (To other transactional engine) These are giant steps.” I agree- in 1985 that would have been a big step indeed. Wonderful how well described the crappiness of… Read more »

Author
shlomi

@willem,
To support your point, can you please provide with counter examples or facts?

Guest
Rudi

I agree, in an age of online application it should be possible to do database schema updates without having to bring your applications offline. This is a wonderful tool by facebook that could be taken further and save many headaches for administrator and developers.

You would almost expect this to be a function built into mysql workbench! Load a list of SQL DB Schema change statements and run them.

I have looked but do not seem to be able to find any projects related to the live upgrade of mysql schemas.
Is live updates not a big problem for most HA applications?

Guest
Rudi

Maakit has just released a new command line tool for mysql live online schema changes as well. Have a look. Seems to also support foreign keys 😉

http://code.google.com/p/maatkit/issues/detail?id=1268

http://code.google.com/p/maatkit/
http://www.maatkit.org/

Author
shlomi

@Rudi,

Yes, I was aware they were developing the tool: see discussion on first links you’ve attached.
I wasn’t aware it was out, though. I suspect this will become the preferred tool for this job.

Author
shlomi

Hmmm, I don’t see that it has been released yet.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.