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?)
-
1We delete all the data and insert fresh data (approx same size) every day. How do you delete rows? Use TRUNCATE.Akina– Akina2024年07月11日 16:30:49 +00:00Commented Jul 11, 2024 at 16:30
-
@Akina We use DELETE because we delete and insert for each account when each account data is ready, so we cannot just truncate...Amichay Miara– Amichay Miara2024年07月14日 06:23:49 +00:00Commented Jul 14, 2024 at 6:23
-
?? In the question you tell that We delete all the data. Now you tell that you delete the data partially (may be finally all, but in progress by chunks). Please edit the question if its facts are not correct.Akina– Akina2024年07月14日 09:08:58 +00:00Commented Jul 14, 2024 at 9:08
1 Answer 1
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.)