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.
Best Answer
The reason Data_free always comes back with the same number on every table is simple:
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:
You must do four(4) major things:
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.
More info on 'OPTIMIZE TABLE'