This tool has been very helpful in our environment for a variety of reasons. One common one is to rebuild tables with row_format=compressed to help control use of disk space on reporting tables. This works really well, except for the part where the old table is dropped. (This is with file_per_table, btw) A global lock is taken inside mysql while the operating system deletes the file for the old table.
Everything hangs while the OS takes its time if the file is very large. We had a hang in excess of three minutes after dropping an 80G file.
Slightly better than the default behavior would be to set the --no-drop-old-table option so that we could defer the drop until a time when the impact on the users would be minimized.
However, it would be best if there was a way to avoid a lengthy hang altogether. I ran a test where I simply zeroed out the old table’s ibd file before issuing a drop command, and it didn’t raise any errors. Can anyone think of a reason why you wouldn’t be OK doing this:
For large table FOO
$ pt-online-schema-change --execute --no-drop-old-table --alter “ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4” D=database, t=bigtable
when that is finished
$ cp /dev/null bigtable_old.ibd
mysql> drop table bigtable_old;
Nothing would be using bigtable_old in between the schema change job and the other steps.
This worked in testing of a 1G table file, but the production database tables where this would be useful are much larger.