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
Last activity: Jun 26, 2023, 07:07 PM