Why are all MySQL InnoDB tables fragmented?

Question

For some reason, all InnoDB tables in my MySQL server are being listed as fragmented when I run mysqltuner. I only installed the server a few hours ago (on OSX Lion), and it has a bunch of fresh data imported from batch files in it.

I tried converting all the tables in one database to MYISAM, and sure enough the number of fragmented tables went down. Strangely though, as soon as I converted those tables back to InnoDB the fragmented table count shot back up again. This is contrary to my research so far which suggests that running ALTER TABLE table_name ENGINE=INNODB; should fix the fragmentation.

After a bit of Googling I ran:

SELECT table_schema, table_name, data_free/1024/1024 AS data_free_MB 
FROM information_schema.tables
WHERE engine LIKE 'InnoDB' AND data_free > 0

Which supposedly lists all fragmented tables (it does indeed return the same number of results as mysqltuner outputs for the fragmented table count). Every single entry has exactly the same number in the data_free_MB column (currently 7.00000000).

Is this actually a real problem or something mysqltuner is doing wrong? If it is a problem, how do I fix it?

EDIT

I'm becoming more and more suspicious that I'm an idiot and that the 7MB fragmentation is for the whole file, not for each table. Can anyone confirm if that would be the case?

Answer 1

As per my comments above, not all output from sqltuner indicates errors. Unless the script very clearly states that it's a problem, usually on the next line, followed by suggestions for remediation, then it's an informational item only.

Answer 2

When you enabled innodb_file_per_table, all you did was setup a protocol to make any new InnoDB tables get created in an external .ibd file. All InnoDB tables that you created prior to this are still imbedded in ibdata1.

With innodb_file_per_table disabled, any time you run

ALTER TABLE table_name ENGINE=INNODB;

all it does is append the table's data and index pages to ibdata1. This will make the table exist in contiguous pages and remove fragmentation, The downside is that ibdata1 grows quickly.

RECOMMENDATION

You will need to export all data, remove ibdata1, ib_logfile0, ib_logfile1, and reload.

I wrote up how and why to do this

• Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?
• Jul 05, 2012 : Move ibdata1, set innodb_data_file_path

UPDATE 2012-08-15 12:05 EDT

You may want to look into the mysqltuner.pl script itself. IMHO I think it is using an old formula for measuring fragmentation. Make sure you have the latest version of mysqltuner.

As for measuring fragmentation of InnoDB tables stored externally, I wrote a post about that back on April 11, 2012 (See the Update on the bottom for April 19, 2012)