MYSQL教程:緩慢的drop table 操作

大家都知道,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的表，因為這個操作阻塞了很長時間。。