MySQL: diminishing hard drive space after lots of imports

Linc

I do a lot of forum migrations, which means importing MySQL database dumps frequently. So, lots of databases cycle thru my laptop. As a result, I've had a diminishing amount of hard drive space, resulting in frequent "low hard drive space" warnings. After purging unused databases, I still had an extremely suspicious amount of space missing.

I used Disk Inventory X to take a peek at where things were hiding and I found 2 big problems:

First, a whole bunch of mysql-bin files of varying sizes. After some research, I found these are log files which can be removed by simply running this MySQL command:

purge binary logs before NOW();

That freed over 200GB of my 500GB drive. .

Second, I had a gigantic ibdata1 file that was now up to 40GB. It turns out this is sort of a bug in MySQL where all InnoDB tables are using the same giant index file and can never reclaim the space once it's used. The solution is to set this in your my.cnf:

innodb_file_per_table = 1

So that it makes a separate ibdata file for every InnoDB table, which is then deleted along with the database. But, to reclaim that space, you now must dump & delete EVERY database, delete the file & restart MySQL, then re-create each database and re-import it. I finally did this a couple weeks ago.

So basically half my hard drive is mine again, and I'm cruising along with over 300GB of free space instead of hovering under 50GB day-to-day.

Tushon

Nice find. We recently went through a similar thing where I recovered ... 700GB? across 10 servers because IIS 7+ logs every request by default (so for our most active sites, we had 40GB+ of log files that just said "get /quote...."

ardichoke

:P You should have pinged me, I could have saved you the googling and told you all that.

Also, some clarification, mysql-bin files are binary logs.... not logs in the traditional sense. Basically, MySQL writes out what it is going to commit to the database before it actually does it. Then, if MySQL were to crash for some reason while writing the changes to the database, it can replay the binary log to get to a consistent state. It can also ship those logs to a slave server in order to have a standby copy that is kept in the same state as the master (this is a gross oversimplification, but essentially correct explanation of binary logs, also known as the write ahead log in postgresql).

InnoDB using one gigantic file for all tables and databases isn't a bug, it's a really really stupid feature. Seriously, I would love to find the person that thought "hey, lets lump all the tables into a single file so we don't have to open more than one file" and punch them in the neck. It's horrible design for little to no benefit. I hate it. Also, the fact that it can't reclaim space from deleted data is just plain terrible. Bit me in the ass on all my puppet servers.

ardichoke

Oh, as a sidenote, if you want to keep your binlogs from eating your whole disk again you might want to set the following:

max_binlog_size = 100M (sets the max size for each binlog file)
expire_logs_days = 7 (expire binary logs after a certain number of days, may not be applicable to all MySQL versions though...)

Linc

@ardichoke said:
InnoDB using one gigantic file for all tables and databases isn't a bug, it's a really really stupid feature.

The bug is specifically that the space can never be reclaimed.

ardichoke

@Linc said:
The bug is specifically that the space can never be reclaimed.

I know, but I wanted an excuse to throw out the "it's not a bug, it's a feature" trope.