云顶集团官网手机版-云顶集团网站

热门关键词: 云顶集团官网手机版,云顶集团网站
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卡塔 尔(英语:State of Qatar),慢查询日志(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称之为伊芙nt,实际上就算带给数据库变化的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的总次数也对应由八日增至了十七遍。

 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的值,多行事务本领学有所成推行。该参数值改过后,注意要与配置文件中的值大小同等。

本文由云顶集团官网手机版发布于数据库,转载请注明出处:1197多行事务必要更加大的max_binlog_cache_size管理与

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文