MySQL show table status always returns Data_free 17825792

Question

When I execute SHOW TABLE STATUS databaseName;

I get Data_free info with 17825792 value in all tables :

| Name             | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options     | Comment                                    |

| ubqACL           | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  17825792 |              1 | 2011-08-23 17:45:48 | NULL                | NULL       | utf8_general_ci   |     NULL |                    | Access Control List per a ubqDocs          |
| ubqAssociacions  | InnoDB |      10 | Compact    | 1216 |            148 |      180224 |               0 |       262144 |  17825792 |           1246 | 2011-08-23 17:45:48 | NULL                | NULL       | utf8_general_ci   |     NULL |                    | Vincles entre documents                    |

the database engine is InnoDB.

I want to retrieve this value for calculate fragmentation and trigger optimization actions.

score 8 · Accepted Answer · edited May 23 '17 at 12:41

The reason Data_free always comes back with the same number on every table is simple:

You are not using innodb_file_per_table
All your InnoDB data in sitting inside this one big file called /var/lib/mysql/ibdata1.

As long as these two conditions exist, you will never eliminate fragmentation. Any attempt to run OPTIMIZE TABLE against an InnoDB table will make the data and indexes for the table contiguous but gets appeneded to ibdata1, making ibdata1 that much bigger.

In light of this, you must know what is inside ibdata1. There are four(4) types of information inside:

Table Data Pages
Table Index Pages
Table MetaData
MVCC Data

You must do four(4) major things:

Dump all databases
Delete ibdata1
Rreconfigure InnoDB using innodb_file_per_table
Reload dump

This will keep all data and indexes out of ibdata1 and store them in separate .ibd files. From there, you can run OPTIMIZE TABLE against the InnoDB table configured as innodb_file_per_table. Thus, .ibd files can be individually defragmented.

This will make ibdata1 as small as possible, never again to grow wildly out-of-control. All worries of InnoDB defragmentation is just an OPTIMIZE TABLE away.

-- It should be noted that OPTIMIZE TABLE on an INNODB table operates a bit differently than using other storage engines. Percona has a good article on improving the speed of OPTIMIZE TABLE and when you should/shouldn't consider performing said action here.

"For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index."

More info on 'OPTIMIZE TABLE'

MySQL show table status always returns Data_free 17825792

1 Answers1