Why is MySQL's data_free larger than data and indexes combined?

Question

I have troubles interpreting the following stats from a MySql 5.5database.

Maybe relevant side info: some tables contain TEXT and MEDIUMTEXT columns, and about 50% of them have null values. I did optimize the tables that use up most of the space.

1. Question: How I understood data_free from the Mysql documentation: Space allocated determined by the DDL but currently unused - so basically null values in rows. Is that interpretation correct and if not how is it exactly defined?

2. Question: I do not understand how the data_free can be bigger that the index and data length combined

 

select (sum(data_length) + sum(INDEX_LENGTH))/ 1024 / 1024 as total, 
sum(data_free)/ 1024 / 1024  as free ,
sum(data_length)/ 1024 / 1024 as data, 
sum(INDEX_LENGTH/ 1024 / 1024) as index_data 
from information_schema.tables t
where t.TABLE_SCHEMA = 'foo'

result:

total | free | data | index_data

19.5469   20.0000   18.1875 1.3594

So i have 20MB data_free and 19.5MB data+index. How can that be?

Thanks

Answer 1

I assume you are using InnoDB, because it's the default storage engine in MySQL 5.5.

InnoDB tablespaces grow as you insert data, but the files do not shrink when you delete data. So for example if you insert 1 million rows, and then delete them, the file will have a lot of space that is physically allocated, but no longer used. InnoDB will re-use that space if it can before growing the tablespace file again.

Also, even if you don't delete, there can be some "wasted" space because when tablespace files are increased in size, they are expanded by a big chunk of pages, determined by the config option innodb_autoextend_increment in megabytes. Until those pages are filled by data, they are free space.

Data_free reported by InnoDB is the amount of space "wasted" in empty pages in the central tablespace file. It has nothing to do with NULL values, it has to do with data pages that don't have rows in them.

Further, in MySQL 5.5, the default is for all tables to share one central tablespace called ibdata. The data_Free for all tables in this tablespace will report the same figure, which is the amount of space in free pages in the whole tablespace, not just for one table.

You can also allocate a separate tablespace per table (innodb_file_per_table=1), and for tables in separate tablespaces, you will see a different value per table for data_free.

Data_free only reports space left by totally empty extents (an extent is a block of pages equal to 1MB). You'll notice that data_free is always a multiple of 1MB. Smaller blocks of free pages are not counted in data_free, nor are partially-filled pages. So the "wasted" space is probably a lot greater, but we have no way of knowing it.