MySQL Forums
Forum List  »  General

Space occupied by deleted rows not getting re-used
Posted by: William Wilson
Date: February 15, 2019 11:20AM

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

Options: ReplyQuote


Subject
Written By
Posted
Space occupied by deleted rows not getting re-used
February 15, 2019 11:20AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.