7

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

1 Answer 1

7

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.

4
  • Is there a way to find out the number of extents (group of blocks) that are in a chunk? I am asking because i need to delete records from a table with 100 million rows. The goal is to be able to fast delete records older than 4 months.
    – surfmuggle
    Jun 30, 2021 at 9:04
  • @surfmuggle - 8M/16K = 512. The link points to a Question that you deleted. Partitioning is optimal for deleting "old" data; walking through (via the PK) in chunks of 1000 rows may be second best. You have seen my deletion discussion
    – Rick James
    Jun 30, 2021 at 15:51
  • Thanks for pointing to your blog post. My trouble is that i do not know what needs to be done before any actions are taken. My question is kind of broad and therefore i assume it is not a good fit for SO. I posted it here dba.stackexchange.com/questions/295009 but am still unsure if it is good fit because of the broad nature of the question.
    – surfmuggle
    Jun 30, 2021 at 17:22
  • Each "Partition" in a "table" is effectively a "table". That is, DROP PARTITION is quite efficient at freeing the disk space. The discussion of "extents" is mostly irrelevant.
    – Rick James
    Jun 30, 2021 at 18:12

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.