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

栏目: 数据库 · 发布时间: 6年前

内容简介:1197多语句事务要求更大的max_binlog_cache_size报错binlog_cache_size:为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。没有什么大事务,dml也不是很频繁的情况下可以设置小一点,如果事务大而且多,dml操作也频繁,则可以适当的调大一点。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


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

技术赋权

技术赋权

郑永年 / 邱道隆 / 东方出版社 / 2014-4-1 / CNY 45.00

在本书中,作者对中国互联网的历史做了一次突破性的研究,细致又全面地观察了中国互联网对于国家和社会的影响,发现互联网给中国的社会—政治变革带来了新的动力。政府权力和社会力量在以互联网为媒介的公共领域中转换。 从大量的数据梳理和事实分析中,作者得出了四重的研究结论。首先,互联网给政府和社会都增加了权力。互联网在促进政治自由化中扮演了重要的角色,使政府更加开放、透明和负责任。第二,互联网产生了大量......一起来看看 《技术赋权》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换