Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is the fragmentation causing problems? How big are the tables we are talking about? –  ypercube Aug 27 '12 at 15:22
    
Please run SHOW VARIABLES LIKE 'innodb_file_per_table'; and tell me, is it enabled or disabled ??? –  RolandoMySQLDBA Aug 27 '12 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) –  EApubs Aug 27 '12 at 15:33
    
@RolandoMySQLDBA innodb_file_per_table | OFF –  EApubs Aug 27 '12 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). –  ypercube Aug 27 '12 at 15:36

1 Answer 1

up vote 2 down vote accepted

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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