mysql最佳化—–ddl語句

author：skate
time：2012/07/02

mysql最佳化-----ddl語句 

在drop table維護mysql資料庫時，在drop操作期間，整個系統會被hang住，這個hang的時間的長短與Buffer Pool的大小相關。主要原因在於InnoDB在drop table時，會連續兩次遍曆buf pool LRU 鏈表，遍曆的過程加鎖，因此導致系統hang住。

 

第一遍用於釋放adaptive hash中的記錄：
0. 擷取當前buf pool的mutex，為遍曆buf pool LRU list的準備
1. 從LRU鏈表尾部開始遍曆
2. 對於每一個屬於Drop Table的page，判斷page中是否有項進入adaptive hash，若有，則收集當前page
3. 收集到1024個這樣的page後，釋放buf pool mutex，集中調用函數buf_LRU_drop_page_hash_batch，釋放page在adaptive hash中的項
4. 重新擷取buf pool mutex，繼續遍曆buf pool LRU 鏈表，直至鏈表頭
5. 最終，釋放buf pool mutex，退出

 

第二次遍曆buf pool LRU鏈表，釋放所有drop table對應的page
1. 擷取buf pool mutex
2. 遍曆buffer pool LRU鏈表
3. 若為dirty page，則將dirty page設定為clean page，並從flush list中移除
4. 將page從LRU list中移除，並且添加入free list
5. 最後，釋放buf pool mutex

 

測試：
環境：
os：redhat5.2
mysql：5.5.24-log

session1：
mysql> insert into tt2 select @a:=@a+1,name from tt2;
Query OK, 4194304 rows affected (1 min 11.52 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

開始drop tt2，同時在session2中insert一條記錄
mysql> drop table tt2;
Query OK, 0 rows affected (6.42 sec)

mysql>

session2：
正常insert一條記錄需要0.03s
mysql> insert into t2 values (11,'22');
Query OK, 1 row affected (0.03 sec)

當在執行drop tt2的時候，就需要4.17s
mysql> insert into t2 values (11,'22');
Query OK, 1 row affected (4.17 sec)

mysql>

 

總結：
1.第一遍遍曆LRU鏈表，會定期釋放buf pool mutex，因此對於系統hang的影響較小；而第二遍會一直持有，對系統hang的影響較大。
2.在維護drop操作時，一定要選擇空閑時間，因為他會阻塞所有ddl和dml操作。

 

 

---end----