Sample Header Ad - 728x90

Optimize table on large table online DDL Feature

0 votes
1 answer
733 views
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
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?
Asked by Adam Mulla (143 rep)
Feb 16, 2020, 09:51 AM
Last activity: Jun 26, 2023, 07:07 PM