MariaDB InnoDB does not reuse blob storage and file keeps getting bigger. Why?

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 innodb_file_per_table=ON.

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.

Method 1

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply