- 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
DDL twice as fast
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:
Subscribe to:
Post Comments (Atom)
Posts
I agree that the InnoDB plugin is quite fantastic, but I am a bit concerned that there is not a version out yet for 5.1.25 (at least that I was able to find on innodb.com). I am, as a result, a little hesitant to start really using this, particularly in production (of course I am not brave enough to use nor recommend 5.1 in production anyway until it is GA).
ReplyDeleteIf 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.
ReplyDeleteHow does the plugin change this? I haven't seen much information on what the plugin does differently from the compiled-in version.
Also, I hate to be obvious, but you probably want to point out that this performance gain is solely for InnoDB tables.
Prior to 5.1, most DDL operations to a table (add/drop column, add/drop index) are implemented by copying the table and rebuilding all indexes. MySQL docs are rather quiet about this. The handler interface in 5.1 provides methods that allow this to not be done. Adding an index with the InnoDB 5.1 pluging creates the index without copying the table. Not only is this fast, but the index has less fragmentation because it is created with the output from a sort (InnoDB 5.1 includes merge sort).
ReplyDelete