I am running optimize table on MySQL table size 340GB in size and storage engine is InnoDB.

mysql> show variables like '%tmp%';
| Variable_name              | Value            |
| default_tmp_storage_engine | InnoDB           |
| innodb_tmpdir              |                  |
| max_tmp_tables             | 32               |
| slave_load_tmpdir          | /dbtmp/mysql_tmp |
| tmp_table_size             | 33554432         |
| tmpdir                     | /dbtmp/mysql_tmp |

tmpdir directory is filling up space .

                      296G  231G   51G  83% /dbtmp

What will be the alternate solution to optimize table without creating copy of table.

MySQL 5.6 version

mysql> select version();
| version()  |
| 5.6.46-log |
1 row in set (0.01 sec)

I have read below MySQL link.



Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL. Consequently, concurrent DML (INSERT, UPDATE, DELETE) is not permitted on a table while OPTIMIZE TABLE is running, and secondary indexes are not created as efficiently.

But here no one is firing DDL/DML/select on table currently.

It should run online without creating copy of table right?

1 Answer 1

  • OPTIMIZE TABLE is almost useless for InnoDB. Why are you running it?
  • Some people like to break up the disk into partitions. But that leads to overflowing one partition when there is spare room in another. You could move tmpdir to a bigger partition?
  • OPTIMIZE TABLE table must copy the table over. Ditto for flavors of ALTER TABLE. Hence, you must have enough spare disp space, preferrable in the same FS, as the table.
  • MySQL 8.0 has some "inline" or "in place" ALTERs; maybe it would help here.
  • Percona's pt-online-schema-change avoids blocking writes during schema changes. Ditto for gh-ost. These probably need the extas disk space, so may not be relevant.

Moral of this story... Don't let the disk get so full that you can't make a full copy of the biggest table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.