Mysql Fragmentation. How bad is too bad? I ran a table status for my companies production site and its showing close to around '49085939712'  for just about every table in my database and we have around 400 tables. 

I am not a DB administrator and my mysql skills are not the best when it comes to optimizing. To me it looks pretty bad. but I need everyone else's oppinion.

My question is how bad is too bad. What is an on average threshold to know when its time to optimize a single table.
Fragmentation should have little to no effect on performance of InnoDB, it only affects the size of the data file.  So it's "too much fragmentation" if you are running out of disk space.

If you have innodb_file_per_table set to OFF, then all InnoDB tables are stored together in one tablespace file, by default this file is called ibdata1.  

The value of information_schema.tables.data_free shows the same value for all tables in this tablespace.

See http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html


  When you ask for available free space in the tablespace by issuing a SHOW TABLE STATUS statement, InnoDB reports the extents that are definitely free in the tablespace.


In other words, the value reported in data_free is not the amount of free space for any single table, it's the amount of space taken by totally free extents in the tablespace file.  An extent is a block of 1MB, which is 64 pages by default.

You may notice that 49085939712 is exactly 46812MB.  That's how many unused extents you have in your tablespace file.  

But you probably also have partially unused space within other extents.  This is not reported by data_free.  It's a little bit harder to get this information, one way is to use the InnoDB tablespace monitor to report it.

Doing OPTIMIZE TABLE won't recover free space, since you are apparently not using innodb_file_per_table.  InnoDB will reuse those extents for new data before expanding the size of your tablespace file further, but if you need to recover that ~45GB right away, you unfortunately have to do it the hard way:


Dump all InnoDB tables (or at least convert them to MyISAM temporarily)
Shut down mysqld
rm ibdata1
Restart mysqld
Restore all InnoDB tables.