1197多行事務要求更大的max_binlog_cache_size處理與優化
1197多語句事務要求更大的max_binlog_cache_size報錯
binlog_cache_size:為每個session 分配的記憶體，在事務過程中用來儲存二進位制日誌的快取,提高記錄bin-log的效率。沒有什麼大事務，dml也不是很頻繁的情況下可以設定小一點，如果事務大而且多，dml操作也頻繁，則可以適當的調大一點。
max_binlog_cache_size設定的參考標準
Binlog_cache_disk_use表示因為我們binlog_cache_size設計的記憶體不足導致快取二進位制日誌用到了臨時檔案的次數；Binlog_cache_use 表示用binlog_cache_size快取的次數，當對應的Binlog_cache_disk_use 值比較大的時候 我們可以考慮適當的調高 binlog_cache_size 對應的值
【故障情景】
通過指令碼以load的方式匯入資料時，出現多行事務需要的max_binlog_cache_size空間不足。該資料檔案HAOHUAN.txt只包含以逗號分隔的500萬行左右的資料，每行四列，檔案大小為270M。
[root@172-16-3-190 shells]# bash +x load_data_into.sh
檔案的總數為:1
檔名為:/tmp/load/HAOHUAN.txt
當前正在處理的檔案是:/tmp/load/HAOHUAN.txt
load data infile '/tmp/load/HAOHUAN.txt' into table practice.temp_baofoo_unbind fields terminated by ',' lines terminated by '\n' (merchant_no,bank_code,bank_card,protocol_no)
Warning: Using a password on the command line interface can be insecure.
ERROR 1197 (HY000) at line 1: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
【故障排查】
檢視max_binlog_cache_size的大小，發現數據檔案的大小確實較max_binlog_cache_size的值要小，如果max_binlog_cache_size的大小不足以存放事務的binlog，那麼會臨時使用磁碟臨時檔案來存放binlog，通過檢視Binlog_cache_disk_use發現使用臨時檔案存放的次數為1。因此增大max_binlog_cache_size的值到300M，再次執行指令碼發現還是報相同的錯誤。且使用臨時檔案的次數為2，使用臨時檔案的存放binlog的總次數也相應由15增加到了16次。
mysql> show global variables like '%binlog_cache%';
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| binlog_cache_size | 16777216 |
| max_binlog_cache_size | 268435456 |
+-----------------------+-----------+
2 rows in set (0.00 sec)
mysql> show global status like '%binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 1 |
| Binlog_cache_use | 15 |
+-----------------------+-------+
2 rows in set (0.00 sec)
mysql> set @@global.max_binlog_cache_size=300000000;
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@172-16-3-190 shells]# bash +x load_data_into.sh
檔案的總數為:1
檔名為:/tmp/load/HAOHUAN.txt
當前正在處理的檔案是:/tmp/load/HAOHUAN.txt
load data infile '/tmp/load/HAOHUAN.txt' into table practice.temp_baofoo_unbind fields terminated by ',' lines terminated by '\n' (merchant_no,bank_code,bank_card,protocol_no)
Warning: Using a password on the command line interface can be insecure.
ERROR 1197 (HY000) at line 1: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
mysql> show global status like '%binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 2 |
| Binlog_cache_use | 16 |
+-----------------------+-------+
2 rows in set (0.00 sec)
無奈直接增加max_binlog_cache_size的值到500M時問題才解決（後經test實際給到400M也可以load成功），但是slave上的值沒有及時改動，因而SQL同步執行緒報錯，stop同步執行緒，同master一樣的更改後，同步才算正常
mysql> set @@global.max_binlog_cache_size=500000000;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.3.190
Master_User: repl
Master_Port: 3309
Connect_Retry: 30
Master_Log_File: binlog.000018
Read_Master_Log_Pos: 120
Relay_Log_File: relay_bin.000006
Relay_Log_Pos: 6973
Relay_Master_Log_File: binlog.000017
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1197
Last_Error: Could not execute Write_rows event on table practice.temp_baofoo_unbind; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; Writing one row to the row-based binary log failed, Error_code: 1534; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log binlog.000017, end_log_pos 268602107
Skip_Counter: 0
Exec_Master_Log_Pos: 11408
Relay_Log_Space: 333526981
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 208
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1197
Last_SQL_Error: Could not execute Write_rows event on table practice.temp_baofoo_unbind; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; Writing one row to the row-based binary log failed, Error_code: 1534; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log binlog.000017, end_log_pos 268602107
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1903309
Master_UUID: 1b589d80-f450-11e7-9150-525400f4ecb2
Master_Info_File: /opt/app/mysql_3309/logs/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180803 17:39:08
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (1 min 10.64 sec)
【故障總結】
max_binlog_cache_size引數時動態引數，該值的設定可以參考binlog_cache_use的大小來相應增加。load匯入或者delete資料的大小必須要大於max_binlog_cache_size的值，多行事務才能成功執行。該引數值修改後，注意要與配置檔案中的值大小一致。
Linux公社的RSS地址 ：https://www.linuxidc.com/rssFeed.aspx
本文永久更新連結地址：https://www.linuxidc.com/Linux/2018-09/154397.htm