Ben Ben - 1 year ago 45
SQL Question

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

I have troubles interpreting the following stats from a

MySql 5.5

Maybe relevant side info: some tables contain
columns, and about
of them have null values. I did
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
    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'


total | free | data | index_data

19.5469 20.0000 18.1875 1.3594

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


Answer Source

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 does not include space left over by partially-filled pages, it only reports space left by totally empty pages. You'll notice that data_free is always a multiple of 16KB, which is the uniform page size in an InnoDB tablespace. Actually according to the documentation, the figure should be multiples of 1MB.