All we need is an easy explanation of the problem, so here it is.
I have two tables in my MariaDB (10.4.12) which store images (JPEG). They are
mediumblobs, typically around 1MB each (though it varies). When a new image first arrives, it gets placed in the
TempImages table where it stays for… less than a minute. Usually 10-20 seconds. Then it gets moved to
FinalImages table where it is kept until the end of time.
The images come in fairly rarely – one every few minutes or so – so the
TempImages table is actually empty most of the time. As it currently stands, the absolutely very worst it could have is maybe 20 images at the same time, and that’s already pushing it.
However for some reason this table continues to grow. Yesterday it had reached over 30GB in size, so we dropped and recreated it because disk space was getting scarce. But today it’s already reached 150MB.
The engine is InnoDB (there are transactions involved when inserting/deleting in the table) and
Why doesn’t InnoDB reuse the empty space in the table but just keeps increasing file size?
Here’s the full create script, maybe there are some extra important details there:
CREATE TABLE `TempImages` ( `id` int(11) NOT NULL, `image` mediumblob NOT NULL, `small_image` mediumblob DEFAULT NULL, `mime_type` varchar(255) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `FK_DATA` FOREIGN KEY (`id`) REFERENCES `TempData` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
(Note: this is not the same as not releasing disk space. I expect the disk space to remain allocated to the table, but I also expect the new rows to reuse the space that was left over by deleted rows, as explained in the linked question. For some reason this isn’t happening.)
(Preemptive note: Fragmentation also shouldn’t be an issue since the table is literally empty most of the time)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This was answered on https://www.reddit.com/r/mariadb/, but for the record here as well, the solution is to update to a more recent version of MariaDB, as this was fixed in https://jira.mariadb.org/browse/MDEV-23072
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂