why mysql does not reclaim free space in ibd file?

Question

I am using mysql5, and I want to shrink some 'deleted' spaces in ibd file. I already search for 'optimize table' option, but I cannot use it now, because it is a very critical table. It uses 19G in Mysql, but 33G in OS. I just figure out 33G is getting an increase.

I heard that it reuses in 33G spaces, like the black area. But why it increases every day?

MySQL does not reuse these free spaces? I mean, about 24G is never reused?

Thanks for the read, I hope not my poor English makes you are confused when you read it.

Answer 1

The option you are looking for is innodb_use_trim=1, in combination with innodb_file_per_table=1, if the version of MySQL you are using is new enough to have it. This will release back empty pages by punching holes in the tablespace files.

Answer 2

The valid parts of a table are scattered throughout the .ibd. file. The Operating System does not have a way to reuse freed up pieces from the middle of a table.

So, InnoDB never shrinks a tablespace, only grows it.

Meanwhile, new INSERTs will fill in some of the freed space.

What led to this situation? Did you DELETE lots of rows? If you expect to do another big DELETE, see the following advice for next time: http://mysql.rjweb.org/doc.php/deletebig

Meanwhile, if the table was created with innodb_file_per_table = 1, then OPTIMIZE TABLE will copy the table over and release the old copy to the OS. But... You need extra disk space to do the task. And it will block some operations against the table. (The details depend on which version of MySQL/MariaDB you are using.)

Why do you need to recoup the space? If you are not running low on disk space, then does it really matter?

Low on space?

Show us SHOW CREATE TABLE. There may be some tips on making the table smaller. (This does not solve the problem, but delays future problems.) For example, if an INT (4 bytes) can be changed to SMALLINT (2 bytes), that could help shrink the table. Or an INDEX might be redundant; DROPping it would help.

The ALTER to change such things might do the OPTIMIZE at the same time. (In MySQL 5.0, virtually all ALTERs are performed by copying the table over.)

Another tip when disk space is terribly tight... (Assuming innodb_file_per_table has always been on): OPTIMIZE smaller tables; Some of them may shrink a little bit, thereby giving you some extra disk space.

Another tip:

1. Determine which tables are in their own tablespaces.
2. Find out how much free space is in ibdata1.
3. SET innodb_file_per_table=OFF;
4. ALTER TABLE t ENGINE=InnoDB; for some of the small tables is step 1. But stop before Data_free of ibdata1 drops too small.

That tip will free up the space for those table that could be moved without causing ibdata1 to grow.