案例 - optimize table 的一些坑

線上IM消息的數(shù)據(jù)庫，磁盤空間使用率已到達96%

1. 沒申請到擴容的新機器，無法做數(shù)據(jù)庫遷移

2. 保留的是全量聊天記錄，一條都不許刪

在這種場景下，為了減少空間容量，只能對表做碎片整理來釋放空間, optimize table

當我們使用mysql進行delete數(shù)據(jù)，delete完以后，發(fā)現(xiàn)空間文件ibd并沒有減少，這是因為碎片空間的存在，舉個例子，一共公司有10號員工，10個座位，被開除了7個員工，但這些座位還是保留的，碎片整理就像，讓剩下的3個員工都靠邊坐，然后把剩下的7個作為給砸掉，這樣就能釋放出空間了

OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. 

好處除了減少表數(shù)據(jù)與表索引的物理空間，還能降低訪問表時的IO，這個比較理解，整理之前，取數(shù)據(jù)需要跨越很多碎片空間，這時需要時間的，整理后，想要的數(shù)據(jù)都放在一起了，直接拿就拿到了，效率提高

拿一張大表做碎片整理，整理之前是96G

[root@localhost myshard]# du -ch tbl_immsg_bigo_96.ibd |grep total
3.5G    total

當執(zhí)行命令時

optimise table tbl_immsg_bigo_96;

整理完后，剩下2.9G

myshard> optimize no_write_to_binlog table tbl_immsg_bigo_96;
+---------------------------+----------+----------+-------------------------------------------------------------------+
| Table                     | Op       | Msg_type | Msg_text                                                          |
+---------------------------+----------+----------+-------------------------------------------------------------------+
| myshard.tbl_immsg_bigo_96 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| myshard.tbl_immsg_bigo_96 | optimize | status   | OK                                                                |
+---------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3 min 21.66 sec) 

[root@localhost myshard]# du -ch tbl_immsg_bigo_96.ibd |grep total
2.9G    total

整理期間會有很多慢查詢的告警，在告一個waiting for table metadata lock的狀態(tài)

ID: 121                                                  

USER: db_myshard_rw                                            

HOST: 127.0.0.1:56326                                         

DB: myshard                                               

COMMAND: Execute                                                

TIME: 1214                                                  

STATE: Waiting for table metadata lock                                

INFO: insert into myshard.tbl_immsg_bigo_0 (touid,fromuid,fromseqid,appid

這是因為optimize table的本質(zhì)，是alter table

mysql 5.5 的改表過程如下

1.創(chuàng)建一張新的臨時表 tmp 

2.把舊表鎖住，禁止插入刪除，只允許讀寫 （這就是為什么上面的insert語句都停留在waiting for table metadata lock）

3.把數(shù)據(jù)不斷的從舊表，拷貝到新的臨時表，(這就是上面報copy to tmp table)

4.等表拷貝完后，進行瞬間的rename操作

5.舊表刪除掉

所以optimize最大的問題是鎖表，鎖表會導致insert，delete，update語句堵住，上面等待了1214秒，還在繼續(xù)，所以第一個結(jié)論：在使用optimize table的時候，確保不要有任何dml語句，確保業(yè)務(wù)切走，否則可能會出事故

為什么要鎖表呢？

alter過程里，數(shù)據(jù)不停從舊表拷貝到新表，如果這個時候舊表被delete了數(shù)據(jù)了，那舊表與新表的數(shù)據(jù)就不一致了，到最后rename 新表 to 舊表表名 時候，數(shù)據(jù)量就多了

如果在拷貝數(shù)據(jù)的過程中，對舊表數(shù)據(jù)的delete，同時對新表也做delete,那數(shù)據(jù)就一致了，對于update和insert也一樣，這個功能可以通過 insert觸發(fā)器，delete觸發(fā)器，update觸發(fā)器實現(xiàn)

pt-online-schema-change就利用3個觸發(fā)器完成在線改表，也能完成在線碎片整理，命令使用 

--alter="ENGINE=InnoDB"

相當于optimize table的效果

具體命令如下，最好放在腳本里面實現(xiàn)，因為一次不止整理一個表，可以把整個數(shù)據(jù)庫的表都碎片整理

 pt-online-schema-change  
  -h地址
  -P端口號
  -u用戶名
  -p密碼   
  --database=數(shù)據(jù)庫
  t=表名字
  --charset=utf8 
  --max-lag=300 
  --check-interval=5 
  --alter="ENGINE=InnoDB" 
  --max-load="Threads_running:400" 
  --critical-load="Threads_running:400" 
  --nocheck-replication-filters 
  --alter-foreign-keys-method=auto  
  --execute

使用pt-online-schema-change可以跳過鎖表的坑

為了保持兩張表的數(shù)據(jù)一致性，拷貝的那部分數(shù)據(jù)需要上鎖，使用共享鎖share_mode來鎖行，可以通過show full processlist看到一次大概對10萬行，每次拷貝1秒不到

 INSERT LOW_PRIORITY IGNORE INTO `myshard`.`_tbl_immsg_bigo_128_new` (`sid`, `tm_timestamp`, `tm_lasttime`, `gid`, `group_name`, `default_flag`, `group_attr`, `group_owner`, `group_extension`, `is_del`, `app_id`, `mic_seat`, `invite_perm`, `invite_media_perm`, `pub_id_search`, `apply_verify`, `public_id`, `introduc`, `family_id`, `__version`, `__deleted`) SELECT `sid`, `tm_timestamp`, `tm_lasttime`, `gid`, `group_name`, `default_flag`, `group_attr`, `group_owner`, `group_extension`, `is_del`, `app_id`, `mic_seat`, `invite_perm`, `invite_media_perm`, `pub_id_search`, `apply_verify`, `public_id`, `introduc`, `family_id`, `__version`, `__deleted` FROM `myshard`.`tbl_immsg_bigo_128` FORCE INDEX(`PRIMARY`) WHERE ((`sid` >= '2112908055')) AND ((`sid` <= '2112916949')) LOCK IN SHARE MODE /*pt-online-schema-change 119079 copy nibble*/

本來使用碎片整理是因為磁盤使用率96%，但碎片整理時發(fā)現(xiàn)磁盤使用率變成99%，差點就爆了

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        58G  2.7G   53G   5% /
tmpfs         24G     0   24G   0% /dev/shm
/dev/sda1       485M   32M  428M   7% /boot
/dev/sda5       1.6T  452G  1.1T  31% /data
/dev/sdb1       1.3T  1.2T   25G  99% /data1

這是因為在把舊表拷貝到臨時表的時，會把表數(shù)據(jù)復(fù)制一份數(shù)據(jù)，10G的表，可能復(fù)制出來是7G，這個過程磁盤會快速消耗，不小心就會把磁盤撐滿造成數(shù)據(jù)丟失了

為了避免這個坑，應(yīng)該把整個數(shù)據(jù)庫的表，按照體積從小到大排序,并且把索引文件，表結(jié)構(gòu)去掉，為了方便顯示出體積，這里加了一個l參數(shù)，實際上是不加的，只獲取表名字，然后重定向一個文件里，碎片整理就按照這個順序

ls -lSr --ignore="*.frm"
-rw-rw---- 1 mysql mysql   4096 Jul 25 12:33 tables_priv.MYI
-rw-rw---- 1 mysql mysql   4096 Jul 25 12:33 procs_priv.MYI
-rw-rw---- 1 mysql mysql   4096 Jul 25 12:33 columns_priv.MYI
-rw-rw---- 1 mysql mysql   5120 Jul 25 12:33 proxies_priv.MYI
-rw-rw---- 1 mysql mysql   5120 Jul 25 12:43 db.MYI
-rw-rw---- 1 mysql mysql   8928 Jul 25 12:33 help_relation.MYD
-rw-rw---- 1 mysql mysql  16384 Jul 25 12:33 help_keyword.MYI
-rw-rw---- 1 mysql mysql  18432 Jul 25 12:33 help_relation.MYI
-rw-rw---- 1 mysql mysql  20480 Jul 25 12:33 help_topic.MYI
-rw-rw---- 1 mysql mysql  22078 Jul 25 12:33 help_category.MYD
-rw-rw---- 1 mysql mysql  89241 Jul 25 12:33 help_keyword.MYD
-rw-rw---- 1 mysql mysql 419392 Jul 25 12:33 help_topic.MYD

可以寫一個腳本，統(tǒng)計每個表整理的時間，整理前后的體積比較，效果如下

正在對表tbl_immsg_bigo_128進行碎片整理...第9張，還剩93張
+----------------------------+----------+----------+-------------------------------------------------------------------+
| Table                      | Op       | Msg_type | Msg_text                                                          |
+----------------------------+----------+----------+-------------------------------------------------------------------+
| myshard.tbl_immsg_bigo_128 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| myshard.tbl_immsg_bigo_128 | optimize | status   | OK                                                                |
+----------------------------+----------+----------+-------------------------------------------------------------------+
表:tbl_immsg_bigo_128, 整理前:3373M, 整理后:2729M, 節(jié)省空間:-644M,耗時:143秒
----------------------------------------------------------------------------------------------
正在對表tbl_immsg_bigo_132進行碎片整理...第10張，還剩92張
+----------------------------+----------+----------+-------------------------------------------------------------------+
| Table                      | Op       | Msg_type | Msg_text                                                          |
+----------------------------+----------+----------+-------------------------------------------------------------------+
| myshard.tbl_immsg_bigo_132 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| myshard.tbl_immsg_bigo_132 | optimize | status   | OK                                                                |
+----------------------------+----------+----------+-------------------------------------------------------------------+
表:tbl_immsg_bigo_132, 整理前:3541M, 整理后:2889M, 節(jié)省空間:-652M,耗時:153秒

全部表整理完以后，96%的空間，碎片整理完后變成85%，騰出130G的空間