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

share|improve this question

migrated from stackoverflow.com Aug 24 '13 at 1:33

This question came from our site for professional and enthusiast programmers.

    
What does that number mean? I think you left out some information –  bengoesboom Aug 23 '13 at 17:17

1 Answer 1

up vote 2 down vote accepted

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:

  1. Dump all InnoDB tables (or at least convert them to MyISAM temporarily)
  2. Shut down mysqld
  3. rm ibdata1
  4. Restart mysqld
  5. Restore all InnoDB tables.
share|improve this answer

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.