What does information_schema.TABLES.DATA_FREE mean in MySQL?

Question

I am not entirely sure what information_schema.TABLES.DATA_FREE means in MySQL.

Can someone please help me understand it?

Thank you.

Answer 1

According to MySQL's information on MySQL 5.6:

The DATA_FREE column shows the free space in bytes for InnoDB tables

For earlier versions like 5.5:

The DATA_FREE column shows the free space in bytes for InnoDB tables.

For MySQL Cluster, DATA_FREE shows the space allocated on disk for, but not used by, a Disk Data table or fragment on disk. (In-memory data resource usage is reported by the DATA_LENGTH column.)

Answer 2

DATA_FREE- The number of allocated but unused bytes. It is the size of the database files compared to the data stored in the database files. PHPMyAdmin shows this information as 'Overhead in tables'.

However for InnoDB this is important- "InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace.". So with a typical InnoDB setup ('innondb_file_per_table' is not set) you will get the free space for all tables and not for a single table.

Refer: http://dev.mysql.com/doc/refman/5.5/en/tables-table.html and http://dev.mysql.com/doc/refman/5.5/en/show-table-status.html

Answer 3

With innodb_file_per_table=OFF all InnoDB tables are stored in the same tablespace. DATA_FREE (the number of allocated but unused bytes) is reported for that single tablespace.

Read http://dev.mysql.com/doc/refman/5.0/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. InnoDB always reserves some extents for cleanup and other internal purposes; these reserved extents are not included in the free space."

So, for InnoDB tables free space in tablespace is reported, and as all your tables share the same tablespace, the same value is the result.