Due to a malfunctioning plugin I had a database table that quickly grew up to a size of 50GB, using about 50% of my vm disk space. Here’s how I got rid of it.
When your database is tiny, but the diskspace used is huge
So there you have it. Your server is running fine, you have a steady number of visitors every day, but suddenly your monitoring shows that you are running out of disk space.
First thought: I got hacked! Did I?
Well, nope. And as the heading tells, it was just a malfunctioning plugin, that caused session data to be written to the database. Every second. Multiple times. Always the same payload.
Even more so: Why should someone store huge amounts of data in my database, right?
So for the moment I decided to free up some space, by deleting old entries. I figured that in my case I would not need sessions older than a day, so after running a quick
I found that about 70.000 rows had been deleted, but the table.idb file still had the size of 51GB.
rw-rw---- 1 mysql mysql 51G Aug 31 21:39 huge_table.ibd
(And bye the way: “quick” means the query only took 13min 5sec to finish.)
How innodb uses the tablespace
Turns out, InnoDB does not free space in .idb files but instead writes new rows into the free space. This happens mainly for performance reasons. This leaves us with the problem, that while your database might only have very few current entries, the table might still be of enormous size, since the file keeps it’s size and only adds news rows into the empty space.
Solution: Optimize your tables from time to time
If you really want to free disk space you will have to “optimize” the table.
MariaDB [myHugeDatabase]> OPTIMIZE TABLE huge_table;
This will tell MySQL (or in my case MariaDB) to reorganize table and indexes, allowing the operating system to reclaim disk space.
See the MySQL documentation for more on this.