MySQL: 一个参数引起的宕机血案
一个参数引起的MySQL从库宕机血案
Part1:max_binlog_cache_size
max_binlog_cache_size 表示的是binlog 能够使用的最大cache 内存大小,为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存。
当我们执行多语句事务的时候,所有session的使用的内存超过max_binlog_cache_size的值时,就会报错:
Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage
登录MySQL,查询 max_binlog_cache_size 值:
MariaDB [mimvp]> show variables like '%cache%'; +--------------------------------+----------------------+ | Variable_name | Value | +--------------------------------+----------------------+ | aria_pagecache_age_threshold | 300 | | aria_pagecache_buffer_size | 134217728 | | aria_pagecache_division_limit | 100 | | aria_pagecache_file_hash_size | 512 | | binlog_cache_size | 32768 | | binlog_stmt_cache_size | 32768 | | have_query_cache | YES | | host_cache_size | 705 | | innodb_disable_sort_file_cache | OFF | | innodb_ft_cache_size | 8000000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_total_cache_size | 640000000 | | join_cache_level | 2 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | key_cache_file_hash_size | 512 | | key_cache_segments | 0 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_stmt_cache_size | 18446744073709547520 | | metadata_locks_cache_size | 1024 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_strip_comments | OFF | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | stored_program_cache | 256 | | table_definition_cache | 400 | | table_open_cache | 2000 | | thread_cache_size | 0 | +--------------------------------+----------------------+ 31 rows in set (0.09 sec)
Part2:为什么它能引起宕机
Warning:警告1
max_binlog_cache_size 在主从设置不一致的情况下,主库参数值大于从库参数值,在主库进行大事务操作时,主库顺利进行,从库因max_binlog_cache_size值低于该事物所需,从库会抛出“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”错误号为1197。
Warning:警告2
max_binlog_cache_size在主从参数设置一样的情况下,主库执行大事务操作,如主库提示需提高该参数以顺利执行SQL,但DBA只调整了主库的max_binlog_cache_size而忘记调整从库的max_binlog_cache_size,则同样从库会爆出1197错误,导致主从不同步。
Part3:该设置值为多少
具体值设置为多少,不能纸上谈兵,还需要看公司的具体业务以及硬件内存大小
这里除了不设置该值,使用默认值(默认值很大, max_binlog_cache_size = 18446744073709547520)以外,个人推荐值为4G,基本已经足够应付大部分场合,但无论是否指定该值,在对大表进行操作时,都需注意上述的警告内容,避免该值设置不合理引起从库无法执行报1197的问题。具体命令如下:
set global max_binlog_cache_size =4294967296;
总结
max_binlog_cache_size 值为动态参数,可以随时利用上述命令进行调整,
所以别忘记将该参数加入到my.cnf,以防止重启数据库后失效。
版权所有: 本文系米扑博客原创、转载、摘录,或修订后发表,最后更新于 2017-07-30 01:50:06
侵权处理: 本个人博客,不盈利,若侵犯了您的作品权,请联系博主删除,莫恶意,索钱财,感谢!