How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I just switched my Wordpress databases to use the InnoDB engine.

When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work.

I manually ran OPTIMIZE TABLE and the suggested ALTER TABLE but none of them removed the overhead. I ran mysqltuner on my server which showed 14 fragmented tables - I have a total of 14 tables. Even after running optimize and alter tables, all 14 tables are still fragmented!

How can I properly OPTIMIZE and REPAIR InnoDB tables?

Ouch you have innodb_file_per_table off

I wrote articles on this before. You will have to convert the InnoDB infrastructure.

• Oct 29, 2010 : My Original Post in StackOverflow

• Apr 01, 2012 : Is innodb_file_per_table advisable?

• Mar 25, 2012 : Why does InnoDB store all databases in one file?

• Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB

• Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

Once you do this, anytime you do OPTIMIZE TABLE to an InnoDB, it will actually shrink.

If you do not perform the InnoDB infrastructure cleanup, the ibdata1 file will actually grow in size. To make matter worse, I think mysqltuner.pl is using a weird algorithm for determining InnoDB fragmentation. I tend to ignore it.

I check for fragmentation differently.

Back on April 19, 2012 I answered a question similar to this: How do you remove fragmentation from InnoDB tables?.

Here is what I said in that earlier post:


After running the above steps, how can you determine what tables need
to be defragmentated? It is possible to find out, but you will have
script it.


Here is an example: Suppose you have the table mydb.mytable. With
innodb_file_per_table enabled, you have the file
/var/lib/mysql/mydb/mytable.ibd


You will have to retrieve two numbers


FILESIZE FROM OS : You can ascertain the filesize from the OS like
this

ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'

FILESIZE FROM INFORMATION_SCHEMA : You can ascertain the filesize from
information_schema.tables like this:

SELECT (data_length+index_length) tblsize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

Just subtract INFORMATION_SCHEMA value from OS value and divide the
difference by the INFORMATION_SCHEMA value.

From there you would decide what percentage deems it necessary to defrag that table. In other words, if there is a big difference between the filesize of the .ibd file and the number from INFORMATION_SCHEMA.TABLES, go ahead with OPTIMIZE TABLE. Regardless of the filesize, please don't worry if the fragmentation is less than 1%.

StackExchange Database Administrators Q#23206, answer score: 4