Optimization causes tmpdir to fill up with 'deleted' file handle

Question

Using Percona 5.6.34-79.1. Storage Engine is InnoDB. One of the table to be optimized is 300Gb in size. tmpdir is a separate partition, and datadir is separate.

datadir has 500Gb of space, so optimize should work (taking its time). tmpdir has less space than table size.

Its a standby slave, so no query of any kind is hitting this db instance, so tmpdir partition is un-used. Before I run any query, if I do lsof | grep -i deleted, I don't see any thing related to mysql.

Once I fire the optimize command, the tmpdir starts to fill up, and doing lsof | grep -i deleted shows large number of deleted but yet held files related to mysql.

Also on the datadir, the new file by the name #sql-ib4586-1246909784.ibd forms but remains in few kilobyte in size.

Normally tmpdir should not fill up and the new file (#sql-ib4586-1246909784.ibd) should increase in size taking up datadir space. Why is that not happening?

Answer 1

Short answer: Can't be done.

Long answer: There is a glimmer of hope...

Assuming that the optimized table will still be more than 200GB, you cannot successfully run OPTIMIZE TABLE in the small space you allotted. Are there other things you can delete? Can you OPTIMIZE the smaller tables first - that might free enough room. I assume you have innodb_file_per_table=ON? How big is ibdata1?

Please provide SHOW TABLE STATUS LIKE 'tablename'.

Please explain why you want to run OPTIMIZE. It is rarely worth doing for InnoDB tables.

Answer 2

Your other option is to use pt-online-schema-change or similar to rebuild the table. This will create an actual copy of the table on the data directory so you do not need to fill your tmpdir - it is also non-blocking.

pt-online-schema-change --alter='ENGINE=InnoDB' h=localhost,D=db,t=table