MySql Online DDL操作記錄詳解
一、環境
為支持用戶賬號刪除功能,需要在 user 表上加一個字段 deleted。
數據庫:Mysql5.6
被 操作表 user:數量級為100w,外鍵200多個
操作:alter table user add deleted boolean NOT NULL default false comment '用戶註銷標識' , algorithm=inplace, lock=none;
二、執行過程分析
在Mysql5.6之後,mysql支持 Online DDL 操作。
Online DDL Support for Column Operations
Operation | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata |
---|---|---|---|---|
Adding a column | Yes | Yes | Yes* | No |
Dropping a column | Yes | Yes | Yes | No |
Renaming a column | Yes | No | Yes* | Yes |
Reordering columns | Yes | Yes | Yes | No |
Setting a column default value | Yes | No | Yes | Yes |
Changing the column data type | No | Yes | No | No |
Dropping the column default value | Yes | No | Yes | Yes |
Changing the auto-increment value | Yes | No | Yes | No* |
Making a column NULL | Yes | Yes* | Yes | No |
Making a column NOT NULL | Yes* | Yes* | Yes | No |
Modifying the definition of an ENUM or SET column | Yes | No | Yes | Yes |
如圖所示,所執行的添加列操作整個過程為:
- 初始化:為創建臨時表的表結構,獲取MDL的排他鎖
- 執行:根據參數 algorithm=inplace, lock=none ,MDL鎖降級為共享鎖進行數據拷貝
- 提交:由於涉及到增量備份和臨時表的重命名,MDL鎖需要升級為排他鎖
分析後認為,整個過程隻有在初始化和提交的極短過程內需要用到MDL排他鎖(影響線上),故而就直接在線上進行操作嘗試。
三、遇到的問題
在初始化和提交階段需要用到MDL的排他鎖,而如果DDL操作一直沒獲取MDL鎖(默認獲取MDL鎖超時時間為一年),那麼就會造成 Waiting for table metadata lock 狀態,也會阻塞後面所有對 user 表的操作(包括select)。後面會看到連接占滿,服務502:
在遇到這樣的問題後,為不影響線上,於是後面進行瞭工具的嘗試。
四、工具嘗試
工具涉及到 pt-online-schema-change、gh-ost和阿裡雲無鎖DDL。三個工具大同小異,均為使用臨時表,將原表數據拷貝到臨時表,最後將臨時表重命名替換掉原表。區別是在增量同步方面,一個用的觸發器、一個用的binlog日志。
但是在處理外鍵方面,pt-online-schema-change用的刪除、重建外鍵,gh-ost和阿裡雲無鎖DDL則是不支持主表外鍵的變更。eg:阿裡雲無鎖DDL的失敗嘗試
故使用工具進行 DDL 操作也不適合。
五、Online DDL 嘗試
在本地測試30w數據新增列隻需440ms後,嘗試選擇瞭 online ddl 的操作:
設置 session 級別獲取 MDL 鎖等待時間時間,避免長時間阻塞其他線程
$ set lock_wait_timeout=10; # 在10s內獲取不到MDL鎖,直接退出 alter 操作 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
kill 掉對應的線程以及事務
$ select group_concat(stat separator ' ') from (select concat('kill query ',id,';') as stat from information_schema.processlist where command != 'Sleep' and Time > 5 order by Time desc) as stats; +-------------------------------------+ | group_concat(stat separator ' ') | +-------------------------------------+ | kill query 42510; kill query 42514; | +-------------------------------------+ 1 row in set (0.00 sec) $ select group_concat(stat separator ' ') from (select concat('kill ',trx_mysql_thread_id,';') as stat from information_schema.innodb_trx order by trx_started desc) as stats; +-------------------------------------------------------------------------+ | group_concat(stat separator ' ') | +-------------------------------------------------------------------------+ | kill 42436; kill 42435; kill 42521; kill 42511; kill 42510; kill 42483; | +-------------------------------------------------------------------------+ 1 row in set (0.01 sec)
執行 DDL 操作:
$ alter table user add deleted boolean NOT NULL default false comment '用戶註銷標識' , algorithm=inplace, lock=none;
結果:
以上是在停服後操作的結果,其中 2 操作在停服後,沒有需要 kill 的 ID。
參考文獻
InnoDB and Online DDL
以上就是MySql Online DDL操作記錄詳解的詳細內容,更多關於MySql Online DDL操作的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- Mysql Online DDL的使用詳解
- 為什麼MySQL 刪除表數據 磁盤空間還一直被占用
- MySQL 大表添加一列的實現
- MySQL kill不掉線程的原因
- Mysql查詢所有表和字段信息的方法