5

I have a pretty large Mysql table, over 25GB. When extracting an overview with Table Inspector in MySQL Workbench, I noticed the field Data Free Because the textfields are not selectable, I'm posting a picture of it. Does it mean some sort of reserved space? Or is it actually free space due to a size limit? Or something else?

enter image description here

4

What does "Data_free" mean? Not much. Seriously, there are several areas in a table where there is "free space"; Data_free refers to only one of them:

  • An InnoDB block is exactly 16KB; there is almost always some wasted space in a block.
  • A block in a BTree is normally between 50% and 100% full.
  • Groups of blocks ("extents") are allocated in chunks of 8MB(?). Your 7MiB is part of one such extent. You will very often see 4, 5, 6, or 7, in spite of the main allocation unit being 16KB.
  • If you are talking about the main tablespace (ibdata1), Data_free refers to how much space is "free" in it; this is usually a large number.

If you were to OPTIMIZE TABLE, the table might, or might not, shrink from the current 25.6 GiB; it could even increase! The 7.0 MiB would probably stay 7, or might change to 4 or 5 or 6.

"Does it mean some sort of reserved space?" -- Partially a fluke of the way it allocates space. But mostly it is a useless number. If it is bigger than 7 MiB, then there is likely to be some space that could be given back to the OS with OPTIMIZE -- _assuming it is file_per_table. But don't use OPTIMIZE unless you have both file_per_table and Data_free is a significant fraction of the table size. (This may occur after a big DELETE.)

"Or is it actually free space due to a size limit?" -- Tables under some threshold are allocated 16KB at a time; over that threshhold, they are allocated 8MB at a time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.