Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deleting rows causes database storage to grow in MySQL

Tags:

mysql

innodb

I've just observed something very odd which I hope someone can explain to me. I have a MySQL 5.5.58 database with InnoDB tables on a Linux virtual server. One of the tables is called stats_archive and in ordinary usage is write-only: it's never read or deleted. Its content is kept purely for legal compliance purposes for a certain length of time, and a monthly cronjob ought to delete old entries. Unfortunately the cronjob was failing silently and as a result the table was allowed to grow excessively large. This morning I tried to remove the data:

master:~# du -sh /var/lib/mysql
6.3G /var/lib/mysql

master:~# mysql -u root -p

mysql> select count(*) from stats_archive;
+-----------+
|  count(*) |
+-----------+
|  26339050 |
+-----------+
1 row in set (39.40 sec)

mysql> delete from stats_archive where archive_date < '2018-01-01';
Query OK, 24628026 rows affected (7 min 17.61 sec)

master:~# du -sh /var/lib/mysql
7.4G /var/lib/mysql

As you can see, the storage used by MySQL has grown by a little over 1GB. There was no other appreciable database activity while I did this. The delete wasn't done in an uncommitted transaction, so the database shouldn't still be holding on to it in case I rollback.

The extra 1GB of space has (unsurprisingly) been used by /var/lib/mysql/ibdata1, and as I understand it this file never shrinks, so I'm stuck with it until I can do something major like deleting all my databases, restoring from backup and setting innodb_file_per_table=1 (which it currently isn't). I will do that in due course.

But what I really want to know is why this happened, and is the same going to happen each time I delete rows from the database?

Note: This is not a duplicate of this question. That question is about the storage not being released, which is well known with InnoDB and essentially the same as the question I linked to. My question is about the delete causing the storage usage to grow significantly.

like image 676
Richard Smith Avatar asked Jan 29 '23 15:01

Richard Smith


2 Answers

InnoDB table size can indeed grow even when you delete records from it. The root cause is the known fact (or bug) that InnoDB does not free up, nor reclaim space that was used by deleted records.

To support transactional features (rolling back, consistent reads, etc.), InnoDB employs multi-versioning, meaning that it may maintain multiple versions of the same record. The multiple versions are stored in the undo log sections of the InnoDB table spaces. The undo log section can grow indefinitely.

Since deleting data is also a modification, the previous version of the records is stored in the undo log section resulting in a significant growth in the file size.

like image 127
Shadow Avatar answered Feb 05 '23 17:02

Shadow


(Shadow gave a good explanation of why; I'll talk about what to do now.)

The best thing to do is to PARTITION the table by weeks or months (whatever makes sense and leads to, say, 20-60 partitions). Then DROP PARTITION instead of the much slower DELETE. And REORGANIZE PARTITION to get a new partition. More details here.

The better way, in the case you just had was to copy over the rows to keep. This is because you are keeping only 10% of the rows; the new table would be much smaller. See here for more discussion how to do the create-copy-rename.

Meanwhile, you can (with the cost of a few minutes of tying up the table), clean it up by doing OPTIMIZE TABLE. If you don't like tying it up, do the create-copy-rename, above.

like image 44
Rick James Avatar answered Feb 05 '23 17:02

Rick James