Optimize table on large table online DDL Feature

Posted on

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://mysqlserverteam.com/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables/

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 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.

Leave a Reply

Your email address will not be published. Required fields are marked *