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
database.

Maybe relevant side info: some tables contain
TEXT
and
MEDIUMTEXT
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

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.