2

After running mysqltuner, I received this output:

Total fragmented tables: 284

To defragment I tried these:

1. ALTER TABLE tbl_name ENGINE=INNODB
2. Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

Both of the methods are described in MySQL documentation.

But none of the methods helped. Mysqltuner still says that tables are fragmented.

Note: innodb_file_per_table is OFF in my configurations.

How can I defragment my InnoDB tables?

torayeff
  • 9,296
  • 19
  • 69
  • 103
  • Does running `OPTIMIZE TABLE` on any of your tables help? – tadman Oct 22 '14 at 18:12
  • It returns "Table does not support optimize, doing recreate + analyze instead" – torayeff Oct 22 '14 at 18:15
  • I think Mysqltuner lies to you. `ALTER TABLE tbl_name ENGINE=INNODB` rebuilds the table, it can not be fragmented. You can use Jeremy's tool to explore InnoDB tablespace and check fragmentatopn level http://blog.jcole.us/2013/01/03/a-quick-introduction-to-innodb-ruby/ – akuzminsky Oct 22 '14 at 18:34
  • I use the queries here https://www.deciusac.com/linux-2/how-to-optimize-mysql-tables-and-defragment-to-recover-space/ to see if it is still fragmented after optimizing it, and still I have the tables as fragmented – Musa Haidari Oct 21 '20 at 14:00

3 Answers3

1

ALTER TABLE tbl_name ENGINE=INNODB

Is the only way to defragment a innodb table.

ALGORITHM=INPLACE can help us to do it online.

jmrenouard
  • 104
  • 3
1

As of MySQL 5.5.11, you can also use ALTER TABLE tbl_name FORCE to perform a “null” alter operation that rebuilds the table. Previously the FORCE option was recognized but ignored.

https://dev.mysql.com/doc/refman/5.5/en/alter-table.html

veganaiZe
  • 539
  • 5
  • 13
0

I had the same issue and the solution that worked for me was doing analyze after alter:

alter table xyz engine=InnoDB;
analyze table xyz;
Musa Haidari
  • 2,109
  • 5
  • 30
  • 53