回收InnoDB表空間_MySQL

回收InnoDB表空間

以下論述均假定innodb_file_per_table開啟

先用常規optimize回收：

[plain]

mysql> select count(*) from t; 
+----------+ 
| count(*) | 
+----------+ 
| 1200096 | 
+----------+ 
[mysql@even employees]$ ls -alh t.ibd 
-rw-rw---- 1 mysql dba 72M 10-08 17:39 t.ibd 
mysql> delete from t limit 800000; 
mysql> select count(*) from t; 
+----------+ 
| count(*) | 
+----------+ 
| 400096 | 
+----------+ 
[mysql@even employees]$ ls -alh t.ibd 
-rw-rw---- 1 mysql dba 72M 10-08 17:41 t.ibd 
mysql> optimize table t; 
+-------------+----------+----------+-------------------------------------------------------------------+ 
| Table | Op | Msg_type | Msg_text | 
+-------------+----------+----------+-------------------------------------------------------------------+ 
| employees.t | optimize | note | Table does not support optimize, doing recreate + analyze instead | 
| employees.t | optimize | status | OK | 
+-------------+----------+----------+-------------------------------------------------------------------+ 
[mysql@even employees]$ ls -alh t.ibd 
-rw-rw---- 1 mysql dba 29M 10-08 17:43 t.ibd

來看下這種方法的缺陷

[plain]

mysql> show processlist;

+----+------+-----------+-----------+---------+------+-------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+-----------+---------+------+-------------------+------------------+

| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |

| 6 | root | localhost | employees | Query | 64 | copy to tmp table | optimize table t |

+----+------+-----------+-----------+---------+------+-------------------+------------------+

mysqladmin debug結果如下

[plain]

Thread database.table_name Locked/Waiting Lock_type

6 employees.t Locked - read Read lock without concurrent inserts

用optimize缺點顯而易見：讀鎖，特別當你有大表時性能恐怕會深受影響

這里推薦使用percona公司：pt-online-schema-change，避免加鎖

[plain]

$ pt-online-schema-change -uroot -poracle --alter "ENGINE=InnoDB" D=employees,t=t --execute

執行pt工具時有可能發生的類似錯誤：

[plain]

Cannot chunk the original table There is no good index and the table is oversized

這是因為被作用的表需要含有主鍵或者唯一索引，這或許也能成為這款工具的小bug吧

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com