MYSQL教程:缓慢的drop table 操作

核心提示：大家都知道,Ext3并不是最有效的文件系统，例如，MYSQL教程:缓慢的drop table 操作，删除文件会非常缓慢(那真是一个痛苦的过程，不是吗老兄?)，我最近刚跟客户做了个spoke,他们几乎无法删掉一个400G的表，因为这个操作阻塞了很长时间，造成大量的随机I / O，然而事实上,有时候它比你想象的更能影响My

大家都知道,Ext3并不是最有效的文件系统，例如，删除文件会非常缓慢(那真是一个痛苦的过程，不是吗老兄?)，造成大量的随机I / O。然而事实上,有时候它比你想象的更能影响MySQL的性能。那么，什么时候会发生，又为什么会发生呢?

当您运行DROP TABLE时,会有好几件事情需要去做:对表进行write lock,这样它不会被其他线程使用;存储引擎删除数据文件;当然,最后MySQL会删除表定义文件（.frm文件）。这还不是所有的事，还有另外一件事需要去做：

代码:

1. VOID(pthread_mutex_lock(&LOCK_open));
2. error= mysql_rm_table_part2(thd, tables, if_exists, drop_temporary, 0, 0);
3. pthread_mutex_unlock(&LOCK_open);

这整段删除表操作的代码都被LOCK_open互斥信号量所包围。这个互斥信号量在MySQL中不少地方都用到过,但主要是表在开启或关闭的时候。这意味着，当LOCK_open锁定时，没有查询语句可以执行，因为他们阻止任何访问。

这就解释了在ext3文件系统上删除10GB的文件何时成为了痛苦的等待的开始。删除10GB的文件将持续一段时间，如果这是一个MySQL表,这段时间mutex里将会一直存在,而这个互斥会拖延所有查询。

纯文字 代码：

1. +—–+——+———–+——+———+——+ —————-+——————————— —————+
2. | Id | User | Host | db | Command | Time | State | Info |
3. +—–+——+———–+——+———+——+ —————-+——————————— —————+
4. | 1 | root | localhost | test | Query | 7 | NULL | drop table large_table |
5. | 329 | root | localhost | test | Query | 7 | Opening tables | select sql_no_cache * from other_table limit 1 |
6. +—–+——+———–+——+———+——+ —————-+——————————— —————+

我尝试了一些替代的办法，让MySQL来在DROP TABLE时删除小文件，以尽量减少影响，如：

• TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table;
• TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table;

不幸的是，原来所有管理指令：ALTER TABLE <ALTER ALTER TABLE或OPTIMIZE TABLE实际都一样，或是在旧的表文件删除时使用了其他的LOCK_open互斥信号锁。

纯文字 代码：

1. | 3 | root | localhost | test | Query | 7 | rename result table | ALTER TABLE large_table ENGINE=MyISAM |
2. | 679 | root | localhost | test | Query | 6 | Opening tables | select * from other_table limit 1 |

唯一的选择似乎就只能是改变文件系统了。例如,换成处理文件删除更有效的XFS文件系统。

EXT3

纯文字 代码：

1. mysql> drop table large_table;
2. Query OK, 0 rows affected (7.44 sec)

的xfs

纯文字 代码：

1. mysql> drop table large_table;
2. Query OK, 0 rows affected (0.29 sec)

一个MySQL的内部操作可能更好一点:可以通过先重命名对应的数据文件，再在没有互斥信号锁的情况下删除物理文件。但是事实上可能没有那么简单,因为实际的删除操作是由存储引擎来完成的，因此不是MySQL的代码能控制的。

这肯定不是一个共同的情况，但是有时候(虽然我们极其不愿)确实可能会让任何人头痛（例如,删除一个老的不再使用的表） 。

这里绍明同学说应该翻译成:这不是一个常见的情况，但是它可能会在最不期望出现的时候成为一个问题（例如　删除没有使用的旧表）．

---

后附:

一个评论回复说:

从一个完全不同的领域的解决办法。

mythtv （电视记录系统）开发者们在ext3系统上删除大型文件时，通过“缓慢删除”功能取得更好的效果。一般来说，电视录音大概1 – 12GB每小时，所以一部电影可以20 + GB大小的文件。如果没有该功能，删除文件操作将锁定ext3整个系统约20-30秒。

“慢删除”只是在后台执行将文件中的块清空的操作。

另一个评论说:

我应该指出，这并不只适用于MyISAM表，对于InnoDB表，在innodb_file_per_table模式下也一样。
我最近刚跟客户做了个spoke,他们几乎无法删掉一个400G的表，因为这个操作阻塞了很长时间。。