(原文:http://www.mysqlperformanceblog.com/2009/06/16/slow-drop-table/)
众所周知,EXT3并不是效率最高的文件系统,例如文件删除就是其隐痛并且会造成大量的随机I/O。而且,有时会严重影响到MySQL的性能。
当我们运行DROP TABLE命令时,会依序发生以下步骤:
1. 给表加写锁,以使它不能被其他线程访问。
2. 存储引擎(MyISAM,InnoDB...)删除数据文件
3. 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的MySQL表要花费几秒钟,锁持续被占用,以至于全部的查询都被挂起。
+-----+------+-----------+----+---------+------+----------------+-----------------------------------------------
| Id | User | Host | db | Command | Time | State | Info
+-----+------+-----------+----+---------+------+----------------+-----------------------------------------------
| 1 | root | localhost |test| Query | 7 | NULL | drop table large_table
| 329 | root | localhost |test| Query | 7 | Opening tables | select sql_no_cache * from other_table limit 1
+-----+------+-----------+----+---------+------+----------------+-----------------------------------------------
我曾尝试过其他方法,试图让MySQL删除小一些的文件以降低影响,例如:
* 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 或 OPTIMIZE TABLE在删除旧表的时候都要使用LOCK_open锁:
| 3 | root | localhost | test | Query | 7 | rename result table | ALTER TABLE large_table ENGINE=MyISAM
| 679 | root | localhost | test | Query | 6 | Opening tables | select * from other_table limit 1
唯一替换的方法是改变文件系统,例如将文件系统改为删除文件更有效的XFS。
EXT3
mysql> drop table large_table;
Query OK, 0 rows affected (7.44 sec)
XFS
mysql> drop table large_table;
Query OK, 0 rows affected (0.29 sec)
更好的解决方法是在MySQL内部模拟删除表,其实将文件保留并在不使用LOCK_open锁的情况下物理删除。然而,实际情况并没这么简单,因为删除的指令是由存储引擎执行的,而并非是MySQL。
以上并非一般情况,但有些时候会引起问题。(例如删除旧的不用的表)