Mysql – Does InnoDB ever release unused disk space

deletedisk-spaceinnodbMySQL

A large MySQL association table with 500M rows recently ballooned into 1B rows due to a bug (now fixed). Disk usage went from ~130GB to 240GB.

Unfortunately there is not room on this server for any of the standard remedies (OPTIMIZE or ALTER TABLE table ENGINE=InnoDB, dump & rebuild, build into temporary table & rename & drop old table, etc.). I only have 50GB remaining.

Here's the question:

While I've successfully removed over 100M rows so far (1B down to 850M), disk usage has actually grown by 10GB (to 250GB). Yes, new records are also being added every second, but I'm deleting far faster than they are being added.

Will InnoDB ever release this space?
Why does the table disk usage
grow, while I'm shrinking the row count?

Any other advice to reclaim unused disk space when the regular solutions don't fit?

Best Answer

InnoDB tablespace size for individual tables (.ibd) can be reduced with OPTIMIZE and ALTER TABLE ... ENGINE=InnoDB;, but the system tablespace file ibdata1 can be never be shrunk.

The source of ibdata1's growth in in the undo logs.

I have written posts about this since July 2013. I first learned about this phenomenon from Percona's post Reasons for run-away main Innodb Tablespace.

Suggestion #1

If you mysqldump the data into another server running mysql in a newly installed instance, you could setup replication to it, then failover and trash the old master.

Suggestion #2

If you do not have another server, you'll have stop the application, mysqldump the data, drop all DBs, shutdown mysql, delete ibdata1, start mysql (which recreates ibdata1, reload the mysqldump). See my very old post Howto: Clean a mysql InnoDB storage engine?

Related Solutions

Mysql – Space not reclaimed after dropping MyISAM table

If you dropped a 4GB MyISAM table and still have 100% usage of the disk, then something else is clogging up the disk. This is especially true if /var/lib/mysql is on the same mount and /root.

Here is what you can do to start investigating: Go to the OS and do this:

cd /var/lib/mysql
df -h .

If the root partition is 100% despite dropping 4GB,

your datadir is not var/lib/mysql
another table consumed up the space quickly
something else clogged the root partition

Who are the usual suspects ?

Binary Logs : You cannot run PURGE BINARY LOGS with a 100% full disk. You will have to locate the folder with the binary logs, delete the oldest ones, and mysqld will get moving.
Error Log : Just a text file. Suppose your error log is /var/log/mysqld.log. You run echo -n > /var/log/mysqld.log and the space from that file is reclaimed. I don't use rm since mysqld has an open file handle to the error log.
Slow Log : Locate the Slow Log and do the same thing as you did with the Error Log
Temp Tables : These are usually created in /tmp. If you see .MYD file in /tmp with old timestamps, you can manually delete them.

Whatever it is, find the offending files and delete/truncate them because mysqld will not crash on Error 28 (Out of Disk Space). The mysqld will pause and wait patiently for diskspace to be available (See my post "Site Offline" MySQL server failing to start and stop).

Innodb – Does InnoDB Log Files take RAM space or Disk Space

InnoDB log files are stored on disk and so consume disk space - http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html. However the innodb_buffer_pool_size does impact memory usage.

I find this a good reference for memory sizing - http://www.mysqlcalculator.com/