We have a large MySQL5.7 production table. We try to decrease the Data_free value.
We normally just run the Optimize command on various database tables to shrink it to 7MG size. However, with one table we could not achieve that (We are using innodb_file_per_table).
Is there a way to force the system to reduce the value to normal size?
Are you using ENGINE=InnoDB? Is the table in question Partitioned?
MySQL's InnoDB preallocates "extents" in anticipation of new rows being inserted. This usually (not always) shows up as Data_free being exactly 4MB, 5MB, 6MB, or 7MB. You can't get rid of it.
Have you noticed that OPTIMIZE TABLE rarely shrinks any table by much? Have you noticed that the operation blocks other activities on the table? In general, that command is useless for InnoDB tables.
Data_free is the only visible metric of "fragmentation" and other "free" space. Don't fret over it.
A PARTITIONed "table" is composed of multiple "tables", one per partition. Each sub-table may have some Data_free. Hence the whole partitioned table can have a much larger Data_free. Again, this Data_free cannot be shrunk (except in rare cases).
