Hacker News Reader

Comment by dangrossman | Hacker News Reader

pt-online-schema-change, part of the Percona Toolkit, is my go-to tool for making production changes in MySQL.

Improvely and W3Counter both have tons of multi-gigabyte tables. A regular ALTER TABLE statement could take hours to run, and would lock the table the entire time, essentially taking down the service.

Percona's tool makes a copy of the table, runs the ALTER TABLE statements on the copy, then sets up triggers on the original to temporarily mirror data-altering queries to the copy. It then intelligently batches the data migration from the old table to the new one to avoid overloading the server, while printing progress and ETA on the console. When the copying's done, the old table is dropped and the modified one renamed in a single transaction.

It takes just one command and has always worked flawlessly.

http://www.percona.com/doc/percona-toolkit/2.2/pt-online-sch...

View on HN

For the many people discussing how this has been effective for them, does MySQL 5.6's online DDL [1] not solve this well enough for you? I know pt-online-schema-change does some extra stuff to e.g. prevent overloading the server, but so far regular migrations in 5.6 have handled this fine for us.

[1] http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-o...

For ActiveRecord (or other Ruby-based) migrations with MySQL, I've had good luck with Large Hadron Migrator[1]. It's worked flawlessly for some of our own tables with several hundred million rows (and no downtime).

[1] https://github.com/soundcloud/lhm

This looks interesting. Do you know how it actually works? The readme simply says, "The basic idea is to perform the migration online while the system is live, without locking the table. In contrast to OAK and the facebook tool, we only use a copy table and triggers." What's the technique used to achieve that?

From what I understand, it creates a new table with the same structure as the existing table, runs your migrations on it, adds triggers to bring live changes in from the original table while copying (in batches) the records in the old table. Once everything is copied and updated with triggers, it swaps out the old table for the new one in an atomic operation.

From what I understand/think, this is roughly:

Create a new table, add triggers from old table to new table (with new column(s)), copy data, once everything is copied, switch the tables

Just as anecdote, I've had more success with the Percona tool than LHM. They do roughly the same thing though.

I mentioned that, and yes, it is known to work really well. Shopify is using it, so does GitHub as far as I know.

Coincidentally, I was dreading having to add an index on a multigigabyte table this weekend, because I didn't know whether or not it would be done in time for Monday morning business. You just helped put my mind at ease, so I can have a stress-free weekend. Thanks for the tip!

Just be sure to test in a non production database first, I have seen PTOSC crash certain MySQL versions.

I assume you're using mysql, but for those using postgres, there is CREATE INDEX CONCURRENTLY.

Thanks for sharing, didn't know about that one.

Indeed. I used it successfully with large tables (100GB+ of data) under heavy transactional load. It takes time and requires extra disk space since it creates a second copy of the table thus you need to plan accordingly. It also works great with Galera as long as you are not touching primary key.

I'll chime in with a "pt-online-schema-change rocks". Wonderful tool.

Reply on news.ycombinator.com