阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

1197多行事务要求更大的max_binlog_cache_size处理与优化

244次阅读
没有评论

共计 5305 个字符,预计需要花费 14 分钟才能阅读完成。

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 的值,多行事务才能成功执行。该参数值修改后,注意要与配置文件中的值大小一致。

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计5305字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中