Tuesday, July 17, 2012

Drop/Truncate tables quickly in MySQL

Today I learned a nifty trick from the PalominoDB MySQL geeks to improve the delete or truncate time in MySQL. In both delete and truncate the table data file (assuming the innodb_file_per_table is on), is removed (syscall unlink()) from Unix. Apart from that, Innodb keeps a mutex on the buffer pool while the file is being removed from the filesystem. This is a big bottleneck!

So what's the trick? Forcing the database not to remove the file from the filesystem and we do that manually behind the scenes! How? Simple - by using hardlinks. If you need a refresh lesson on hardlinks, consider the diagram below:


On the left hand side is a file with no hardlinks. When we call unlink() either through unix 'rm' or through MySQL 'delete' or 'truncate', the file name is unlinked from the data and the data subsequently removed from the filesystem since no nodes are pointing to it. On the right handside when we create a hard link prior to the unlink, the data is still being pointed at by this hard link and therefore the data is not removed from the system. The data is only removed after we delete the hardlink as well.

So in summary, if you don't want to understand the above unix theory, from now onwards you can delete/drop/truncate tables as follows:

1. Create hardlink. Ex. ln table_1.ibd delete_me_later
2. Delete table. Ex.  delete table_1
3. Remove hardlink. Ex. rm delete_me_later

Post a Comment