optimize table

optimize table

> optimize table endpoint \G;*************************** 1. row *************************** Table: falcon_global.endpoint Op: optimizeMsg_type: noteMsg_text: Table does not support optimize, doing recreate + analyze instead*************************** 2. row *************************** Table: falcon_global.endpoint Op: optimizeMsg_type: statusMsg_text: OK2 rows in set (6.37 sec)ERROR: No query specifiedThu Aug 27 10:48:08 2020> alter table endpoint engine='InnoDB'; Query OK, 0 rows affected (6.26 sec)Records: 0 Duplicates: 0 Warnings: 0Thu Aug 27 10:49:21 2020

optimize table  apple;执行后，他会重建一个相同的表，ibd文件大小差不多，原本apple.ibd文件就有2.1T了，该系统总共只有2.6T，显然装不下临时文件了

[work@xxxxx apple]$ ll -htotal 2.1T-rw-r----- 1 work work 67 Aug 28 2019 db.opt-rw-r----- 1 work work 8.8K Sep 1 2019 apple.frm-rw-r----- 1 work work 2.1T Jun 4 09:55 apple.ibd-rw-r----- 1 work work 8.8K Jun 4 09:54 #sql-2cf54_e65805.frm-rw-r----- 1 work work 1.3G Jun 4 09:55 #sql-ib46-1552102934.ibd

同样的，alter table endpoint engine='InnoDB';  也会新建一个临时表，还是无法解决因为大表的存在而存储空间有限的问题

这里mysql给的提示是

Note>> Table does not support optimize, doing recreate + analyze instead Status>> OK也就是说 optimize table 对于innodb来说，无法作为

a single operation，实际的操作是：

ALTER TABLE test.foo ENGINE=InnoDB;ANALYZE TABLE test.foo;

MySQL5.7已经推荐对于InnoDB的table使用 alter table table_name engine=innodb;语句的方式来进行表碎片优化。

使用optimize table table.name;出现Table does not support optimize, doing recreate + analyze instead#########Everytime you do optimize MySQL, by using mysqlcheck -A -o or using ./mysql_optimize from here.You may see the outputTable does not support optimize, doing recreate + analyze instead.It is because the table that you are using is InnoDB.You can optimize the InnoDB tables by using this.#########1，ALTER TABLE table_name ENGINE='InnoDB';This will create a copy of the original table, and drop the original table, and replace to the original place.Although this is safe, but I suggest you do backup and test first before doing this.原来如此，大致意思是说innodb的数据库不支持optimize，可以用ALTER TABLE table.name ENGINE='InnoDB';该方法会对旧表以复制的方式新建一个新表，然后删除旧表。虽然这个过程是安全的，但是在进行操作时还是先进行备份为好还有一种方式：You can make OPTIMIZE TABLE work on other storage engines by starting mysqld with the --skip-new or --safe-mode option. In this case, OPTIMIZE TABLE is just mapped toALTER TABLE.2，上面是说要求我们在启动的时候指定--skip-new或者--safe-mode选项来支持optimize功能

良策：

01： 将binlog文件格式从rows改为mixed

#########################################

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。