mysql数据库故障处理学习-max_binlog_cache_size设置不当引发的问题

问题概述

再一次执行update语句的过程中发现执行失败并且报错：

ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

大事务导致出现问题。

问题排查定位：

从字面意思来看第一反应这个错误是数据库问题并且与binlog cache有关系，数据库应该不会有问题，因为一直运行稳定，所以先从程序入手。

首先看程序执行的update语句设计多少行数据，把所涉及到update条件的语句进行查询后发现涉及数据量非常大，然后询问dba后得知目前max_binlog_cache_size 设置的256M，对于目前这个业务所使用的数据这个值设置的已经不小了，但是由于数据量大导致bin log未能写入成功导致回滚。

问题分析：

    max_binlog_cache_size 该参数标识binlog能够使用的最大cache内存大小，当mysql执行事物操作的时候所对应的物理日志会记录在redo log中，undo log、bin log记录逻辑日志，bin log又是数据恢复与mysql主从同步的关键日志是非常重要的日志存在。一个事物所影响的行数据最终会记录在bin log中，而事物执行过程中会将dml语句生成的event写入binlog cache，如果binlog cache空间不满足则会将binlog cache的数据写入binlog 临时文件，同时清理binlog cache，如果bin log 临时文件的大小大于了设置的max_binlog_cache_size则会直接报错。

set global max_binlog_cache_size=10*1024*1024*1024

具体步骤可以理解为：

1.事物开启

2.执行DML语句，并且分配binlog cache。

3.DML语句执行的过程中生成的event不断写入binlog cache。

4.如果binlog cache满了则将binlog cache数据写入bin log临时文件并且清理binlog cache，此处binlog cache临时文件的大小如果大于设置的max_binlog_cache_size则报错。

5.事物提交，binlog cache和binlog 临时文件的数据写入binlog file中，同时释放binlog cache和binlog 临时文件。

事后分析：

首先对于生产业务中一次更新大量数据最好进行拆分，避免大事物的产生。

对于一定要一次性更改大量数据最好选在凌晨访问量比较少时，并且评估执行数据量是否会成功，避免mysql binlog写入失败。

那这个时候redo log是否写入成功？又是如何执行的回滚操作？

redo log其实已经写入成功了，只不过对应的状态是未提交状态，因为mysql的持久性就是由redolog来保证，当事物开始的时候所对应的操作都会记录在redo log中并且状态是prepare，当binlog 写入成功后redo log的状态才会变成commit。

那redo log写入磁盘是否会影响效率？

redo log 虽然记录在磁盘，但实际上会先写到redolog buffer中，redolog buffer会写入到redolog file中，由于是顺序写入所以效率非常高。

如果事物执行失败或者抛出异常则会通过undo log来回滚数据。

redo log的刷盘时机？

redo log刷盘时机也是可以指定的通过innodb_flush_log_at_trx_commit设定。

1.实时写，实时刷，每次提交事物都将redo log 写入buffer并且刷盘。就是commit的时候刷盘，这个是最保险的，因为如果没有提交即便是崩溃了也没关系，毕竟数据没提交，但是性能比较差。

2.实时写，延时刷，每次提交事物将redo log写入buffer但不会马上刷盘。每次提交事物都将redo log写入os buffer，不会立马刷盘，但是间隔1秒后刷。

0.延时写，事物提交不会立刻将redo log写入buffer，而是1秒后写入buffer并且立刻刷盘。