Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from superuser.com Aug 15 '12 at 11:56

This question came from our site for computer enthusiasts and power users.

    
Do you really think 7 free MB is a problem? –  David Schwartz Aug 15 '12 at 9:39
    
@DavidSchwartz Not a clue, that's why I asked ;) There are 2314 tables, each with 7MB free, and I don't know what that means. I'm not sure why mysqltuner would show me that figure if it wasn't a potential cause for concern. I was hoping someone here might be able to to tell me how concerned to be given the numbers, and what I can do to mitigate the problem since the 'standard' methods don't work –  Clive Aug 15 '12 at 9:47
    
Migrating this question as per the user's request in a flag. –  Daniel Beck Aug 15 '12 at 11:56
    
Most of the details mysqltuner shows is for information only. Not everything is a problem. If it is a problem it will clearly say so. Was this indicated as a problem? –  John Gardeniers Aug 15 '12 at 12:41
    
@JohnGardeniers I believe so, the message is: [!!] Total fragmented tables: 2314, which I'm pretty sure indicates a problem (with the double red exclamation marks) –  Clive Aug 15 '12 at 12:48

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer

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

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)

share|improve this answer
1  
Ahhh ok, things make a lot more sense now, thanks. I ended up exporting the data, wiping MySQL completely, then reinstalling (but adding innodb_file_per_table to the conf file before starting the server and re-importing). Prior to that I was getting all sorts of InnoDB errors (the really bad kinds...ones that meant I had to run with innodb_force_recovery at level 6 just to get the data out!), and all sorts of 'the log file date is in the future!' errors. Those seem to have stopped now but I've still got a fair few fragmented tables. I'll just keep my eye on it, thanks again for the input –  Clive Aug 15 '12 at 15:49
    
awesome! this is very much informative. Thanks @RolandoMySQLDBA ! –  Sudhi Mar 6 '13 at 11:01

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.