Use another disk for "optimize table" temporary file

Question

I have a large table (70 Go) on a disk with only 9 Go free space, which I need to optimize ("optimize table ..").

I have changed the "tmpdir" parameter so that the temporary file is created on another disk with enough space.

However the temporary file "#sql..." is still created in the mysql data folder, where tables are located.

Is there a way to tell mysql to create the temporary file on another disk for "optimize table"?

If not, is the only solution to transfer the table onto another disk, run "optimize table" there, then replace the old table file?

Here is the row returned by "SHOW TABLE STATUS" for the table:

Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
InnoDB 10 Compact 218780456 176 38628507648 0 18179555328 17278435328 290029517 2016年02月25日 16:33:50 NULL NULL utf8_general_ci NULL

• tombom: Not sure, if this is an option for you, but taking a backup with mysqldump and restoring it again would optimize the table as well.
• Dave Rix: Unfortunately, I believe that intermediate files like these are always created in the same folder as the original table, so a backup and reload of that table as suggested by @tombom might be your best solution.
• Rick James: Why OPTIMIZE? It may not shrink the table, especially if it is in ibdata1.
• Rick James: As a first step, please provide SHOW TABLE STATUS.
• leyou: @tombom Alright, I will probably do it that way then.

Answer 1

If you have deleted most of the rows from a table, OPTIMIZE table needs only enough room to build a new, smaller, copy. After the copy is finished, it will rename, then drop the old table.

If the table had its own tablespace (built with innodb_file_per_table=ON), then the freed space will be returned to the OS. But... The 17GB free in the .ibd cannot be used for the table copy. The copy will be in a separate file. So it could fail if the resulting table is more than 8GB (data+index).

If =OFF, the OPTIMIZE will try to use free space in ibdata1 before increasing the size of that file.

There is 17GB free somewhere; I can't tell where.

Also, check TABLE STATUS for other tables. The Data_free for all tables in ibdata1 will be the same value. What is that value?

There are tricks to work around the cramped space, but I need answers to the questions posed.