- Run the DDL on master and slaves concurrently. Assuming you can take the master and slaves down at the same time, don't use MySQL replication for long running DDL statements. If you do, then the operation takes twice as long as the DDL statement as the slaves don't start the DDL statement until the master finishes it. Make sure to run set sql_log_bin=0 on the master prior to running the DDL statement.
- Use a large value for innodb_log_file_size. You shouldn't always use the largest value possible, but it can make DDL much faster for operations that are IO bound because there is less checkpoint IO. For a test I ran today, the alter table operation was twice as fast with 3 1.3GB log files than with 3 128MB log files.
Saturday, July 12, 2008
If you are not running MySQL 5.1 with the InnoDB plugin, then DDL on large tables takes a long time because it usually requires making a copy of the table and rebuilding all indexes. There are two easy ways to make this go faster: