Database Administrators Asked by Adam Mulla on December 14, 2020
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?
OPTIMIZE TABLE
is almost useless for InnoDB. Why are you running it?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.ALTERs
; maybe it would help here.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.
Answered by Rick James on December 14, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP