

Ask HN: Dirty tricks for faster mysql alter table ? - jacquesm

Mysql is a great program, countless websites wouldn't exist if it weren't for this really neat open source package.<p>One thing I can't get around though is that every time I have to do an 'alter table' on something that has been in production for a while the whole thing stops dead cold as long as it takes it to do its job. And that can be quite long if the table is large enough.<p>There are a few dirty tricks out there to speed things up a bit, the most frequently used one builds an empty 'shell' table with the right structure but without the indexes and then uses the repair facility to recreate them. It's a little faster, but even that is too long in all but the simplest cases.<p>Is there a better way to do this ?<p>I'm about to 'do it again' on a production table with 50M rows in it and I really am not looking forward to taking things down while doing this.
======
danudey
Here's a trick to consider if you've got a truly large table to migrate, but
with today's ActiveRecord-style abstractions it's harder to implement than if
you were doing it yourself (like back in the PHP days).

1\. Create a new table with the desired structure. 2\. Modify your application
to check the new table for data first, then the old table if the new table
doesn't have the data. 3\. Create a script that migrates data X rows at a time
(in a transaction), populating the new table with the old data. 4\. Once the
new table is in place, replace the old table with new, and modify the
application to check only that table.

Again, the setup required here can be developer-intensive and somewhat
hackish, but if you have a truly colossal data store that'll take hours or
days to ALTER TABLE, it's one way to work around it.

~~~
jacquesm
That's a good approach, it will be a bit more work but at least I don't have
to contend with a lot of downtime like that.

Thank you!

~~~
danudey
Glad I could help. Hope it works out for you!

------
ScottWhigham
This isn't a MySql problem; it's simply how databases work. When you change a
table's structure by adding/removing/changing a column, _any_ database (MySql,
Oracle, DB2, etc) has to change _every single row_. To change all rows, that
requires an "exclusive lock" on the table.

An ALTER TABLE statement that adds/removes/changes-data-types of a column
requires what is called a "schema lock" - meaning that you must acquire
exclusive access to the entire table so that you can fundamentally change each
and every row. You can't add a new column to only "some" of the rows, right?
You have to modify the entire table. In order to adhere to relational database
standards, this must be an "atomic" operation: all or nothing. To do so, MySql
locks the entire table.

There are no shortcuts that I know of. Sorry!

