Space occupied by deleted rows not getting re-used
The subject line describes my suspicion, but let me give a rundown of what I'm seeing. We're running MySQL 8.0.11.
We have a very small table of only ~10,000 rows that we update frequently. Every minute, inside of a transaction, we run a DELETE FROM on the entire table, then we INSERT about 10,000 new rows (using a single multi-row INSERT statement), then COMMIT.
Following this method, our .ibd files on the filesystem just keep growing. Over the course of about 24 hours this 10,000-row table grew to 1.4 GB. The performance of the INSERT in particular degrades from taking a few seconds to taking 60-90 seconds.
My suspicion is that the INSERT is not actually re-using previously deleted rows, which forces the table to claim more and more blocks over time and eventually hit seriously slow performance.
Is this expected? Is there another angle that I may be missing?
We can switch to a different method of updating the table (create new table, insert rows, swap with a rename, drop old table). But I'd like to understand if our current approach has fundamental problems with it.
The table schema is dead simple:
root@mysql [statusdb]> show create table features\G
*************************** 1. row ***************************
Table: features
Create Table: CREATE TABLE `features` (
`features_tstamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Name` varchar(255) DEFAULT NULL,
`feature` varchar(255) DEFAULT NULL,
UNIQUE KEY `Name` (`Name`,`feature`),
KEY `feature` (`feature`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Also, here are our innodb-related configurations:
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 192G # System has 256 GB
innodb_log_buffer_size = 256M
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 900