【MySQL】ERROR 1799 (HY000) innodb_online_alter_log_max_size引數過小的一則case

神諭丶發表於2017-06-19
MySqlError
【問題描述】：
版本：MySQL 5.6.36 （以上的版本都可能出現）
行為：對某些訪問頻繁的大表做Online DDL
現象：
ERROR 1799 (HY000): Creating index 'GEN_CLUST_INDEX' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

比如：

  1. mysql> ALTER TABLE a ENGINE=innodb;
  2. ERROR 1799 (HY000): Creating index 'GEN_CLUST_INDEX' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

  4. mysql> ALTER TABLE a DROP idx_name;
  5. ERROR 1799 (HY000): Creating index 'idx' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.


只要是DDL相關的，就可能遇到這樣的問題，比如在optimize table時：
  1. mysql> OPTIMIZE TABLE a;
  2. +--------+----------+----------+-------------------------------------------------------------------+
  3. | Table  | Op       | Msg_type | Msg_text                                                          |
  4. +--------+----------+----------+-------------------------------------------------------------------+
  5. | test.a | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
  6. | test.a | optimize | error    | Creating index 'FTS_DOC_ID_INDEX' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again. |
  7. | test.a | optimize | status   | OK                                                                |
  8. +--------+----------+----------+-------------------------------------------------------------------+
  9. 2 rows in set (18.13 sec)
雖然optimize對innodb表沒什麼用，但是仍然會丟擲該錯誤


那麼檢視一下手冊，可以得知：
innodb_online_alter_log_max_size控制在用於在Online DDL操作時的一個臨時的日誌檔案的上限值大小。
該臨時的日誌檔案儲存了在DDL時間內，dml操作的記錄。這個臨時的日誌檔案依照innodb_sort_buffer_size的值做擴充套件。
如果該日誌超過了innodb_online_alter_log_max_size的最大上限，DDL操作則會丟擲失敗，並且回滾所有未提交的DML操作。
反過來說，該值如果設定更高，則可以允許在做Online DDL時，有更多的DML操作發生。
但因此帶來的問題就是，在DDL做完之後，需要更多時間來鎖表和應用這些日誌。


另外對於某些DDL操作，比如
ADD INDEX/COLUMN，則可以通過調整innodb_sort_buffer_size的大小來加快操作速度。
但是實際上分配的記憶體為3倍的innodb_sort_buffer_size值。

innodb_online_alter_log_max_size和innodb_sort_buffer_size均為5.6 Online DDL的新引數。



【解決方案】：

知道這個引數控制的是什麼東西，就好解決了。
臨時調大該值，此處改成了256MB：
  1. mysql> SET GLOBAL innodb_online_alter_log_max_size=256*1024*1024;
  2. Query OK, 0 rows affected (0.03 sec)

該值預設為128MB，還是建議在做完DDL之後再將其改為預設值，也就是134217728。



作者微信公眾號（持續更新）


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-2140971/，如需轉載，請註明出處，否則將追究法律責任。

相關文章