Mysql – How to remove file handle from MySql after Dropping 1TB Mysql Table to free disk space

debian-wheezydisk-space-utilizationfilesystemsMySQL

On our productive System we have recently dropped a 1TB Table.
After the drop was complete the Table disappeared in mysql but the file was still present in the /lib/mysql/dbname/ folder. (We use the one file per table setting) I deleted the files associated with the table.

So I checked with 

lsof  | grep crawl_link | grep deleted

and found that there are still open handles by the mysql process

mysqld    38115            mysql   11uW     REG                8,3 1016938364928  182524780 /var/lib/mysql/seobility/_crawl_links_new.ibd (deleted)
mysqld    38115  2110      mysql   11uW     REG                8,3 1016938364928  182524780 /var/lib/mysql/seobility/_crawl_links_new.ibd (deleted)
mysqld    38115  4530      mysql   11uW     REG                8,3 1016938364928  182524780 /var/lib/mysql/seobility/_crawl_links_new.ibd (deleted)
mysqld    38115  8192      mysql   11uW     REG                8,3 1016938364928  182524780 /var/lib/mysql/seobility/_crawl_links_new.ibd (deleted)
(Total 120 Lines)

I know the issue would be resolved by restarting MySql but our Database is running at 2TB+ and I would really like to avoid downtime.

Is there a way to drop the file handle and free the disk space occupied?

Best Answer

The obvious solution is to kill (or restart) the process which is holding the file descriptor (process ID 38115 as shown in lsof output).

If you don't want to do that, you can truncate the file (even if it is deleted) to free up disk space. You can try the following:

$ :> /proc/38115/fd/11

Where 38115 is the process ID and 11 is the file descriptor. Don't forget to execute as root when required.

Related Topic