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` (
`Name` varchar(255) DEFAULT NULL,
`feature` varchar(255) DEFAULT NULL,
UNIQUE KEY `Name` (`Name`,`feature`),
KEY `feature` (`feature`)
1 row in set (0.00 sec)

Also, here are our innodb-related configurations:

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

Written By
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.