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.
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.
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).
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.
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!
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.
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...