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

1197多语句事务供给更加大的max_binlog_cache_size报错

mysqlbinlog参数设置

  binlog_cache_size:为各样session
分配的内部存款和储蓄器,在业务进度中用来存款和储蓄二进制日志的缓存,提升记录bin-log的作用。未有怎么大业务,dml亦非很频仍的景况下得以设置小一些,即便事情大而且多,dml操作也多次,则能够方便的调大学一年级点。

1.mysql有无数种类变量可以设置,系统变量设置区别,会导致系统运营状态的例外。因而mysql提供两组命令,分别查看系统安装和平运动转境况。

1、系统装置:

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW VARIABLES shows the values of MySQL system variables.
2、运市价况:
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW STATUS provides server status information.

备考:SHOW XXX
恐怕会显得比非常多剧情,类似Linux下内容太多了,往往须求grep来过滤,那么mysql也设想到了那一点,使用LIKE字句能够过滤。

在装置完MySQL之后,料定是内需对MySQL的各样参数选项进行一些优化调节的。就算MySQL系统的紧缩性很强,不仅可以在有很富厚的硬件能源景况下飞快的运作,也足以在极少财富情形下很好的周转,但好歹,尽大概丰裕的硬件能源对MySQL的性质提高总是有扶持的。在此一节我们入眼深入分析一下MySQL的日志(首假设Binlog)对系统质量的影响,并依赖日志的连带天性得出相应的优化思路。

日记爆发的属性影响

出于日记的笔录带来的直白品质损耗便是数据库系统中可是昂贵的IO能源。

在前边介绍MySQL物理架构的章节中,大家早已通晓到了MySQL的日记富含错误日志(ErrorLog),更新日志(UpdateLog),二进制日志(Binlog),查询日志(QueryLog),慢查询日志(SlowQueryLog)等。当然,更新日志是老版本的MySQL才有的,最近早已被二进制日志代替。

在默许意况下,系统仅仅张开错误日志,关闭了其余具备日志,以达成尽也许收缩IO损耗进步系统性情的目标。不过在日常不怎么主要一点的实际上采取场景中,都起码供给开采二进制日志,因为那是MySQL相当多囤积引擎进行增量备份的底子,也是MySQL完结复制的基本规范。有的时候候为了进一步的质量优化,定位推行极慢的SQL语句,相当多系统也会打开慢查询日志来记录施行时间超越一定数值(由大家机关安装)的SQL语句。

貌似景况下,在生育种类中很稀有系统会展开查询日志。因为查询日志张开之后会将MySQL中举办的每一条Query都记录到日志中,会该系统带来极大的IO肩负,而带来的实留意义却并非不行大。平时独有在支付测验景况中,为了永远某个作用具体使用了哪些SQL语句的时候,才会在长期段内张开该日记来做相应的分析。所以,在MySQL系统中,会对品质发生潜濡默化的MySQL日志(不包蕴各存储引擎本身的日志)紧要便是Binlog了。

max_binlog_cache_size设置的参照他事他说加以考察标准

2.Binlog 相关参数及优化计策。

binlog_cache_size

Binlog_cache_disk_use

Binlog_cache_use

max_binlog_cache_size

max_binlog_size

sync_binlog

“binlog_cache_size”:在工作进度中容纳二进制日志SQL语句的缓存大小。二进制日志缓存是服务器扶助工作存款和储蓄引擎并且服务器启用了二进制日志(—log-bin选项)的前提下为各个客商端分配的内部存储器,注意,是每种Client都足以分配设置大小的binlogcache空间。若是读者对象的系统中时常会产出多语句事务的华,能够尝尝扩张该值的高低,以获取更某个品质。当然,我们得以经过MySQL的以下多个状态变量来判定当前的binlog_cache_size的状况:Binlog_cache_use和Binlog_cache_disk_use。

Binlog_cache_disk_use:表示因为我们binlog_cache_size设计的内部存储器不足导致缓存二进制日志用到了一时文件的次数

Binlog_cache_use :表示 用binlog_cache_size缓存的次数

当对应的Binlog_cache_disk_use 值不小的时候 大家得以考虑十分的调高
binlog_cache_size 对应的值

show global status like ‘bin%’;

上述语句大家能够获得当前 数据库binlog_cache_size的施用状态

mysql> show status like ‘binlog_%’;
+———————–+———–+
| Variable_name | Value |
+———————–+———–+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 120402264 |
欧洲杯竞猜平台 ,+———————–+———–+

“max_binlog_cache_size”:和”binlog_cache_size”相对应,可是所表示的是binlog可以使用的最大cache内部存款和储蓄器大小。当大家推行多语句事务的时候,max_binlog_cache_size假如非常不足大的话,系统或然会报出“Multi-statementtransactionrequiredmorethan’max_binlog_cache_size’bytesofstorage”的错误。

“max_binlog_size”:Binlog日志最大值,常常的话设置为512M也许1G,但不能够超过1G。该大小并不可能特别严峻调整Binlog大小,特别是当达到Binlog比较接近尾巴部分而又遇上一个非常大专业的时候,系统为了保障工作的完整性,不恐怕做切换日志的动作,只可以将该职业的有所SQL都记录步入当前天记,直到该业务截至。那一点和Oracle的Redo日志有一些不平等,因为Oracle的Redo日志所记录的是数据文件的大意地点的变动,况且在那之中还要记录了Redo和Undo相关的音讯,所以同叁个作业是或不是在二个日志中对Oracle来讲并不重大。而MySQL在Binlog中所记录的是数据库逻辑变化消息,MySQL称之为Event,实际上即使带来数据库变化的DML之类的Query语句。

“sync_binlog”:这一个参数是对此MySQL系统来讲是根本的,他不唯有影响到Binlog对MySQL所带来的性质损耗,何况还影响到MySQL中多少的完整性。对于“sync_binlog”参数的各样设置的表明如下:

sync_binlog=0,当事情提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的音讯到磁盘,而让Filesystem自行决定哪天来做一道,或许cache满了今后才联合到磁盘。

sync_binlog=n,当每进行n次事务提交之后,MySQL将开展二回fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统暗中认可的安装是sync_binlog=0,也正是不做别的强制性的磁盘刷新指令,那时候的品质是最佳的,不过风险也是最大的。因为借使系统Crash,在binlog_cache中的全数binlog新闻都会被遗失。而当设置为“1”的时候,是最安全然而质量损耗最大的装置。因为当设置为1的时候,就算系统Crash,也最多遗失binlog_cache中未到位的叁个事情,对实在多少未有任何实质性影响。从未来经验和相关测验来看,对于高并发事务的种类来讲,“sync_binlog”设置为0和设置为1的类别写入品质差异也许高达5倍乃至更加的多。

1.mysql有很多系统变量能够安装,系统变量设置分歧,会导致系统运长势况的不比。因而mysql提供两组命令,分别查看系统…

 
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。

1 [root@172-16-3-190 shells]# bash +x load_data_into.sh 
2                 文件的总数为:1 
3                 文件名为:/tmp/load/HAOHUAN.txt 
4 当前正在处理的文件是:/tmp/load/HAOHUAN.txt
5 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)
6 Warning: Using a password on the command line interface can be insecure.
7 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扩展到了十六遍。

 1 mysql> show global variables like '%binlog_cache%';
 2 +-----------------------+-----------+
 3 | Variable_name | Value |
 4 +-----------------------+-----------+
 5 | binlog_cache_size | 16777216 |
 6 | max_binlog_cache_size | 268435456 |
 7 +-----------------------+-----------+
 8 2 rows in set (0.00 sec)
 9 
10 mysql> show global status like '%binlog_cache%';
11 +-----------------------+-------+
12 | Variable_name | Value |
13 +-----------------------+-------+
14 | Binlog_cache_disk_use | 1 |
15 | Binlog_cache_use | 15 |
16 +-----------------------+-------+
17 2 rows in set (0.00 sec)
18 
19 mysql> set @@global.max_binlog_cache_size=300000000;
20 Query OK, 0 rows affected, 1 warning (0.00 sec)
21 
22 [root@172-16-3-190 shells]# bash +x load_data_into.sh          
23                 文件的总数为:1 
24                 文件名为:/tmp/load/HAOHUAN.txt 
25 当前正在处理的文件是:/tmp/load/HAOHUAN.txt
26 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)
27 Warning: Using a password on the command line interface can be insecure.
28 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
29 
30 mysql> show global status like '%binlog_cache%';         
31 +-----------------------+-------+
32 | Variable_name | Value |
33 +-----------------------+-------+
34 | Binlog_cache_disk_use | 2 |
35 | Binlog_cache_use | 16 |
36 +-----------------------+-------+
37 2 rows in set (0.00 sec)

万不得已直接扩展max_binlog_cache_size的值到500M时难题才消除(后经test实际给到400M也足以load成功),然则slave上的值未有当即更动,由此SQL同步线程报错,stop同步线程,同master同样的更改后,同步才算平常

 1 mysql> set @@global.max_binlog_cache_size=500000000;
 2 Query OK, 0 rows affected, 1 warning (0.00 sec)
 3 
 4 mysql> show slave status \G;
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 172.16.3.190
 8                   Master_User: repl
 9                   Master_Port: 3309
10                 Connect_Retry: 30
11               Master_Log_File: binlog.000018
12           Read_Master_Log_Pos: 120
13                Relay_Log_File: relay_bin.000006
14                 Relay_Log_Pos: 6973
15         Relay_Master_Log_File: binlog.000017
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: Yes
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 1197
25                    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
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 11408
28               Relay_Log_Space: 333526981
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: 208
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 1197
43                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
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1903309
46                   Master_UUID: 1b589d80-f450-11e7-9150-525400f4ecb2
47              Master_Info_File: /opt/app/mysql_3309/logs/master.info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: Reading event from the relay log
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 180803 17:39:08
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: 
59                 Auto_Position: 0
60 1 row in set (0.00 sec)
61 
62 mysql> stop slave;
63 Query OK, 0 rows affected (1 min 10.64 sec)

【故障总计】

  max_binlog_cache_size参数时动态参数,该值的装置能够参照他事他说加以考察binlog_cache_use的轻重来对号入座增加。load导入或然delete数据的高低必供给抢先max_binlog_cache_size的值,多行事务才干成功试行。该参数值修改后,注意要与配置文件中的值大小同等。

相关文章