patternphpMinor
Mysql Fragmentation. How bad is too bad?
Viewed 0 times
fragmentationbadtoomysqlhow
Problem
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.
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.
Solution
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
The value of
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
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
Doing
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.
Context
StackExchange Database Administrators Q#48645, answer score: 2
Revisions (0)
No revisions yet.