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?

  • Is the fragmentation causing problems? How big are the tables we are talking about? Commented Aug 27, 2012 at 15:22
  • Please run SHOW VARIABLES LIKE 'innodb_file_per_table'; and tell me, is it enabled or disabled ??? Commented Aug 27, 2012 at 15:31
  • @ypercube No I can't see any problems in the site... The sql dump of the database is 32MB (A small database)
    – THpubs
    Commented Aug 27, 2012 at 15:33
  • @RolandoMySQLDBA innodb_file_per_table | OFF
    – THpubs
    Commented Aug 27, 2012 at 15:35
  • With such a tiny database, I don't think you should worry at all. Rolando should comment really, on when and if you should run OPTIMIZE and REPAIR on InnoDB tables (or provide links to his previous answers). Commented Aug 27, 2012 at 15:36

1 Answer 1


Ouch you have innodb_file_per_table off

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

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%.

  • Do I have to drop even the performance_schema and the mysql databases?
    – THpubs
    Commented Aug 27, 2012 at 15:58
  • You do not have to drop performance_schema and the mysql databases. In fact, please do not touch the mysql schema. Commented Aug 27, 2012 at 16:00
  • Perfect thanks a lot... Im going to clean it now :)
    – THpubs
    Commented Aug 27, 2012 at 16:00
  • Performed the cleanup and it did a big change :) But still, mysqltuner show 5 more fragmented tables. How to check this?
    – THpubs
    Commented Aug 27, 2012 at 16:33
  • Updated my answer... Commented Aug 27, 2012 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.