Question :
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 .
/dev/mapper/vgdbtmp-lvdbtmp
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.
https://dev.mysql.com/doc/refman/5.6/en/optimize-table.html
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?
Answer :
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 ofALTER 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 forgh-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.