0

Using MySql and innodb with innodb_file_per_table flag ON.

  • We have a table with a size of ~100GB. We delete the data and insert fresh data (approx same size) every day (the delete is in chunks by a key, when this key task is fetch fresh data).

  • We see that there is no reuse, the table size is increasing with ~100GB every day.

  • As far as we know mysql should reuse deleted space (same table).

  • We do not want to reclaim the space, just to have the reuse on the same table working...

What could cause this behavior? We cannot optimize the table every single day.

(similar to Does mysql use deleted rows space in a table?)

Amichay Miara
  • 1
  • 1

1 Answers1

0

If you are deleting all the data of a table, it is best to create and populate another table, then use RENAME TABLE to swap it in place.

If you are deleting most of a table, it is best to create a new table, then insert the rows to keep into it. Then swap.

It may make sense to PARTITION the table, then delete one hour's worth each hour. More discussion:

(I would rather that you change your process before discussing "reuse" of space. Most of my suggestions are good at not bloating the disk, not locking the table too long, etc. So, come back when you have change the processing.)

Rick James
  • 80,479
  • 5
  • 52
  • 119