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