可以看到部分配置信息,mysql binlog日志优化及思
分类:巴黎人-数据库

1197多语句事务供给越来越大的max_binlog_cache_size报错

mysqlbinlog参数设置

原文:

mysql binlog日志优化及思路

mysql的内部存储器分配,是调优的重要,所以必得搞领悟内部存款和储蓄器是怎么分配的

  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了。

共事告诉说有个cdb mysql实例方今异常慢,写入速度巨慢,何况是间歇性的一些时候每隔7到8分钟就卡一会,不经常每隔12分钟就卡一会,问他们是还是不是有定期职分在拉数据?他们说未有。 

 

mysql> show global variables like '%buffer%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| bulk_insert_buffer_size | 4194304    |
| innodb_buffer_pool_size | 2013265920 |
| innodb_change_buffering | inserts    |
| innodb_log_buffer_size  | 8388608    |
| join_buffer_size        | 1048576    |
| key_buffer_size         | 16777216   |
| myisam_sort_buffer_size | 262144     |
| net_buffer_length       | 16384      |
| preload_buffer_size     | 32768      |
| read_buffer_size        | 1048576    |
| read_rnd_buffer_size    | 1048576    |
| sort_buffer_size        | 1048576    |
| sql_buffer_result       | OFF        |
+-------------------------+------------+
13 rows in set (0.01 sec)

mysql> show global variables like '%cache%'; 
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 1048576              |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 0                    |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 256                  |
| table_open_cache             | 100                  |
| thread_cache_size            | 100                  |
+------------------------------+----------------------+
14 rows in set (0.00 sec)

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提供两组命令,分别查看系统...

这是或不是有多数一点也不快的sql把io财富消耗光了呢,去看慢查询记录,结果开采一条select都并未有,反而是有无数insert语句,见鬼啦,那咋回事呢?

在数据库安装达成,对于binlog日志参数设置,有部分参数的调节,来满意工作要求或使品质最大化。Mysql日志首要对io质量发出潜移暗化,本次重大关怀binlog 日志。

能够观察某些安排新闻

  Binlog_cache_disk_use表示因为大家binlog_cache_size设计的内部存款和储蓄器不足导致缓存二进制日志用到了偶尔文件的次数;Binlog_cache_use 表示用binlog_cache_size缓存的次数,当对应的Binlog_cache_disk_use 值十分大的时候 大家得以怀念格外的调高 binlog_cache_size 对应的值

慢查询有众多记录,如下所示,insert on duplicate key update,粗粗一看,断定是on duplicate key update的问题,如下:

 

内部存款和储蓄器的结缘

【故障情景】

# User@Host: hsh_ext[hsh_ext] @  [devtest.yikan.com]  Id: 37459
# Query_time: 1.170256  Lock_time: 0.000118 Rows_sent: 0  Rows_examined: 0
SET timestamp=1504065495;
/*id:57539043*/insert into hy_deive(record_time, platform, device_id,
    install_id, device_token, push_enabled,
    `uid`, model, app_version, is_login, device_type, created_at,
    updated_at)
    values

      (
      1504065494, 'android', '863049030002995',
      '417e03c9-b879-4741-86b6-beb8c1f42497', 'Anj6kMy77g-2sKlb7idPuxAQ58eXdE_JILDvT-xITBfb', 0,
      4234883169, 'OPPO', '3.36.2', 1, 'umeng',
      1504065494, 1504065494
      )
     , 
      (
      1504065494, 'android', '863049030002995',
      '417e03c9-b879-4741-86b6-beb8c1f42497', 'F5nrlikA1gCLSrLZ7Xby1ASn+fXqSJZ3xATxvkJtXzU=', 0,
      4234883169, 'OPPO', '3.36.2', 1, 'xiaomi',
      1504065494, 1504065494
      )
     , 
      (
      1504065494, 'android', '863049030002995',
      '417e03c9-b879-4741-86b6-beb8c1f42497', '0863049030002995200000184200CN01', 0,
      4234883169, 'OPPO', '3.36.2', 1, 'huawei',
      1504065494, 1504065494
      )

    on duplicate key update
    record_time = IF(record_time > values(record_time), record_time, values(record_time)),
    platform = IF(record_time > values(record_time), platform, values(platform)),
    install_id = IF(record_time > values(record_time), install_id, values(install_id)),
    device_token = IF(record_time > values(record_time), device_token, values(device_token)),
    push_enabled = IF(record_time > values(record_time), push_enabled, values(push_enabled)),
    model = IF(record_time > values(record_time), model, values(model)),
    app_version = IF(record_time > values(record_time), app_version, values(app_version)),
    is_login = IF(record_time > values(record_time), is_login, values(is_login)),
    updated_at = IF(record_time > values(record_time), updated_at, values(updated_at));

查一下二进制日志相关的参数  

1、线程分享内部存款和储蓄器  2、线程独享内存

  通过脚本以load的不二秘籍术辅导入数据时,出现多行事务需求的max_binlog_cache_size空间不足。该数据文件HAOHUAN.txt只包罗以逗号分隔的500万行左右的数量,每行四列,文件大小为270M。

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

 

used_Mem =
+ key_buffer_size
+ query_cache_size
+ innodb_buffer_pool_size
+ innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections *(
    + read_buffer_size
    + read_rnd_buffer_size
    + sort_buffer_size
    + join_buffer_size
    + binlog_cache_size
    + thread_stack
    + tmp_table_size
    + bulk_insert_buffer_size
)
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

可是实际上,图谋2条无用的insert into … values… on duplicate key update …..,异常快就实施完了,不到0.01s,这为何今年,还会有那么多的慢查询记录呢?

   mysql> show variables like '%binlog%';

线程独享内部存款和储蓄器

【故障排查】

去查看了cdb的监察记录,select、udpate、insert未有吗间隙性的尖刀出现,尽管有起伏有上升空间,不过都比较平稳,未有尖刀,我们看上面包车型的士图L 
图片 1

+--------------------------------+----------------------+

1、read_buffer_size:逐一读取数据缓冲区使用内部存款和储蓄器**

  查看max_binlog_cache_size的高低,开掘数据文件的大小确实较max_binlog_cache_size的值要小,借使max_binlog_cache_size的深浅不足以寄放事务的binlog,那么会有时选取磁盘不经常文件来贮存binlog,通过查看Binlog_cache_disk_可以看到部分配置信息,mysql binlog日志优化及思路。use发掘使用偶然文件寄放的次数为1。由此增大max_binlog_cache_size的值到300M,再次实行脚本开掘依然报一样的荒唐。且使用有时文件的次数为2,使用不时文件的寄存binlog的总次数也呼应由十二日增到了18回。

图片 2

 

那有个别内部存款和储蓄器首要用来当须要各类读取数据的时候,如无发使用索引的气象下的全表扫描,全索引围观等。在这种时候,MySQL 根据数据的蕴藏顺序依次读取数据块,每趟读取的数量快首先会暂存在read_buffer_size中,当 buffer 空间被写满只怕全体数目读取甘休后,再将buffer中的数据再次回到给上层调用者,以进步功能。

 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)

想开既然是insert语句,那么就去看binlog日志吧,看下全体的binlog日志,看看那么些卡的时间点,到底都试行了些什么操作呢? 

| Variable_name                  | Value                |

2、read_rnd_buffer_size随便读取数据缓冲区使用内部存款和储蓄器

不得已直接扩充max_binlog_cache_size的值到500M时难题才消除(后经test实际给到400M也得以load成功),不过slave上的值未有立时改换,由此SQL同步线程报错,stop同步线程,同master同样的改变后,同步才算平常

结果一看binlog列表,发现binlog每隔8分钟就能够flush下,而这些flush的时光和慢查询的年华正好吻合。 

 

和顺序读取相对应,当 MySQL 进行非顺序读取(随机读取)数据块的时候,会动用那一个缓冲区暂存读取的多少。如基于目录消息读取表数据,根据相排版序后的结果集与表展开Join等等。总的来讲,即是当数据块的读取要求满意一定的依次的情状下,MySQL 就须求发出随机读取,进而使用到 read_rnd_buffer_size 参数所设置的内存缓冲区。

 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)

binlog日志生成时间: 
图片 3

+--------------------------------+----------------------+

3、sort_buffer_size:排序使用内部存款和储蓄器**

本文由巴黎人手机版发布于巴黎人-数据库,转载请注明出处:可以看到部分配置信息,mysql binlog日志优化及思

上一篇:没有了 下一篇:tinyint 数据类型能存储从0到255,bit数据类型是整
猜你喜欢
热门排行
精彩图文