365bet亚洲版登录-bet官网365入口

365bet亚洲版登录拥有超过百间客房,bet官网365入口的文化历经几十年的传承和积淀形成的核心内容获得业界广泛的认可,365bet亚洲版登录是目前信誉最高的娱乐场所,同国内外几百家网上内容供应商建立了合作关系。

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。

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的总次数也相应由19日增到了十六遍。

 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的值,多行事务手艺得逞实行。该参数值修改后,注意要与配置文件中的值大小一样。

本文由365bet亚洲版登录发布于数据库,转载请注明出处:1197多行事务须要更加大的max_binlog_cache_size管理与

您可能还会对下面的文章感兴趣: