So I have an alter table (adding 4 columns) to do on a fairly large table (74 million rows). The problem is this table has firstly to remain online and writable during the whole operation and secondly is in a master->slave circle of 5 database servers. (These servers are all running the latest stable MariaDB 5.5). The table in question is InnoDB with no FK constraints and no triggers.
I’ve been playing with pt-online-schema-chage (part of the Percona toolkit) and it seems to do exactly what I want to do and isn’t too slow that it makes the job unfeasible.
My main fear is replication lag. The tool is able to check replication lag and throttle itself if needs be but that would only apply to the next slave right? If it was a slave 2 or 3 levels of replication away, it wouldn’t see that would it?
I guess I’m only going to be sure after testing, testing and more testing and then only really when it completes. Is there a better strategy? Is it possible to throttle pt-online-schema-change so that I could drag it out to happening over many days instead of many hours?
Hi t0mmyt;
By default the pt-online-schema-change tool (2.1+) actually attempts to check slave lag on all connected replicas, but the question is always if it will actually be able to do so in your environment or not, so you’ll have to test that out:
[url]pt-online-schema-change — Percona Toolkit Documentation
As for throttling it, you can use the max-load and critical-load options, which prevents the database from getting backed up (i.e. you could have it pause if there are 50 threads running at once).
[url]pt-online-schema-change — Percona Toolkit Documentation
[url]pt-online-schema-change — Percona Toolkit Documentation
-Scott