I showed in an earlier post how to drop a whole database in a very safe way (no replication lag at all) and that technique is usable to drop a single table too, but cleaning up a table can take hours if not days to finish, so this is not the most comfortable way to do that. We also don’t want to have even a small spike of replication lag, so we need to find an another solution.

How to remove database in a safe way When you have to drop a large database, you’ll encounter some problems, mainly replication… Read more What happens when you issue a DROP TABLE command? The table has to be removed from the table dictionary – which is a fast, atomic operation – and has to be removed from file system too. If you use older version than 5.5.10 you have to calculate with a huge amount of time if your buffer pool is big, because the server will scan through the pages there, checking if anything is in memory from that table; between 5.5.10 and 5.5.30 you can use the ‘innodb_lazy_drop_table’ variable to adjust this behaviour – when you set it to “1” this check will happen in background -, but after 5.5.30 you don’t have to deal with this.

Okay, so you have two atomic operations, but last time you dropped a huge table it took seconds, why? It takes a constant amount of time to unlink a single block, but files beyond the size of a single block consists of multiple blocks linked together, and the larger the file the larger the quantity of blocks that are linked.

To solve this, we have to know about one more thing, called hard links. On UNIX every file has an inode where the file begins, and if you create a hard link, then it will look like you have a new file, but that will be the same as the old one. (Technically all the files are hard links to inodes.)

[root@db-dev.xyz /var/lib/mysql/kinja]# ls -i event.ibd
17433044 event.ibd
[root@db-dev.xyz /var/lib/mysql/kinja]# ln event.ibd event.ibd_link
[root@db-dev.xyz /var/lib/mysql/kinja]# stat event.ibd*
File: `event.ibd'
Size: 13023313920 Blocks: 25436168 IO Block: 4096 regular file
Device: 801h/2049d Inode: 17433044 Links: 2
Access: (0640/-rw-r-----) Uid: ( 496/ mysql) Gid: ( 497/ mysql)
Access: 2015-01-13 03:13:52.193601140 -0500
Modify: 2015-01-13 01:04:55.000000000 -0500
Change: 2015-01-13 05:51:32.762582905 -0500
File: `event.ibd_link'
Size: 13023313920 Blocks: 25436168 IO Block: 4096 regular file
Device: 801h/2049d Inode: 17433044 Links: 2
Access: (0640/-rw-r-----) Uid: ( 496/ mysql) Gid: ( 497/ mysql)
Access: 2015-01-13 03:13:52.193601140 -0500
Modify: 2015-01-13 01:04:55.000000000 -0500
Change: 2015-01-13 05:51:32.762582905 -0500

To remove a file on UNIX(-alikes) we can use the unlink() (man 2 unlink) C function, which decreases the link count by 1 on a hard link (file), and removes the inode when link count equals to 0. To check this see how MySQL handles file deletion!

The following source code sniplet is taken from MySQL-5.6.22 source code. (/storage/innobase/os/os0file.cc)

/***********************************************************************//**
1807 Deletes a file if it exists. The file has to be closed before calling this.
1808 @return TRUE if success */
1809 UNIV_INTERN
1810 bool
1811 os_file_delete_if_exists_func(
1812 /*==========================*/
1813 const char* name) /*!< in: file path as a null-terminated
1814 string */
1815 {
1816 #ifdef __WIN__
1817 bool ret;
1818 ulint count = 0;
1819 loop:
[... SOME UNIMPORTANT WINDOWS STUFF :) ...]
1853 #else
1854 int ret;
1855
1856 ret = unlink(name);
1857
1858 if (ret != 0 && errno != ENOENT) {
1859 os_file_handle_error_no_exit(name, "delete", FALSE);
1860
1861 return(false);
1862 }
1863
1864 return(true);
1865 #endif /* __WIN__ */
1866}

You can see that as it was expected, the file will be removed with the unlink() function! So far, so good. What we can expect, when we create a hard link to a database table file? Nothing at all. That will be not shown up in the table dictionary, and the link count increase will be handled by the file system.

So the hacky way to remove table fast is the following: create a hard link to that table on file system, and drop the table inside mysql. The unlink part will run fast, and removes the original file, however the link still exists, but MySQL will not know about that file, which you can remove manually. YAY!