Recently someone asked my if it's possible to find the total free
space within InnoDB. I thought this would be very easy as the
INFORMATION_SCHEMA.TABLES table has a DATA_FREE column. So we
could just use SELECT SUM(DATA_FREE) FROM
INFORMATION_SCHEMA.TABLES couldn't we?
So what does the DATA_FREE column tell us? It tells us the free data within InnoDB for that particular table. A table can share a tablespace with multiple other tables.
The tablespace which is used by a table depends on whether the innodb_file_per_table was enabled during table creation and/or at the last time the table was rebuild (e.g. by OPTIMIZE TABLE).
If innodb_file_per_table was always disabled then this query probably reports the correct free space:
SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' LIMIT 1;
This is because all tables will share 1 tablespace. …