【MySQL】binlog缓存的问题和性能
之前在没有备库的情况下，遇到过more than 'max_binlog_cache_size' bytes of storage 的错误，今天在主备复制的时候⼜遇到了这个问题
Last_SQL_Errno: 1197
Last_SQL_Error: Worker 14 failed executing transaction '' at master log mysql-bin.050101, end_log_pos 2669492980; Could not execute Delete_rows_v1 event on table time_d_task_1889; Multi-statement transaction required m slave
root@(none) 10:11:38>show variables like '%binlog_%size%';
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| max_binlog_cache_size | 2147483648 |
| max_binlog_size | 524288000 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+
root@(none) 09:51:00>show status like 'binlog_%';
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Binlog_cache_disk_use | 86332 |
| Binlog_cache_use | 320562298 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 145 |
+----------------------------+-----------+
master
root@(none) 10:07:17>show variables like '%binlog_cache_size%';
+-----------------------+-------------+
| Variable_name | Value |
+-----------------------+-------------+
| binlog_cache_size | 32768 |
| max_binlog_cache_size | 10737418240 |
+-----------------------+-------------+
主备设置不⼀致导致主库写binlog成功，备库在恢复relog的时候同样记录binlog，但是主备cache⼤⼩设置不同，所以导致了上边的问题。
主备参数的⼀致性很重要
修复
root@(none) 10:08:49>stop slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
root@(none) 10:08:32>set global max_binlog_cache_size=10737418240;
Query OK, 0 rows affected (0.00 sec)
root@(none) 10:08:49>start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
max_binlog_cache_size
如果事务需要内存超过此字节数，服务器⽣成如下错误ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage;max_binlog_cache_size最低值
是4096，最⼤可能值为16EB（艾字节）。推荐的最⼤值为4GB ；因为⽬前的MySQL当⼆进制⽇志位置⼤于4GB时并不⽣效。
Note:
Prior to MySQL 5.6.7, 64-bit Windows platforms truncated the stored value for this variable to 4G, even when it was set to a greater value (Bug #13961678).
在MySQL 5.6 ，会话对于max_binlog_cache_size的感知依赖于binlog_cache_size系统变量；换句话说，改变其值只会影响后续新建会话。
max_binlog_size
如果写⼆进制⽇志导致当前⽇志⽂件⼤⼩超过了这个变量的值时，服务器旋转⼆进制⽇志（关闭当前的⽂件，并打开下⼀个）。的最⼩值为4096字节。最⼤值和默认值是1GB
。
事务是以块为单位写⼊⼆进制⽇志的，因此它不会被拆分在⼏个⼆进制⽇志。如果你有⼤的交易，你可能会看到⽐max_binlog_size较⼤的⼆进制⽇志⽂件。
如果max_relay_log_size为0，max_binlog_size的值会被应⽤到中继⽇志。
max_binlog_stmt_cache_size
如果⼀个事务中的⾮事务性的语句需要⼤于这个数量的更多的内存，服务器会产⽣报错。最低值是4096 ，在32位平台最⼤和默认值是4GB。在64位平台最⼤和默认值是
416EB（艾字节）。
Note
Prior to MySQL 5.6.7, 64-bit Windows platforms truncated the stored value for this variable to 4G, even when it was set to a greater value (Bug #13961678).
max_binlog_cache_size针对事务语句，max_binlog_stmt_cache_size针对⾮事务语句，当我们发现Binlog_cache_disk_use或者Binlog_stmt_cache_disk_use⽐较⼤时就需要
考虑增⼤cache的⼤⼩
事务性语句&⾮事务性语句
在事务性语句执⾏过程中，服务器将会进⾏额外的处理，在服务器执⾏时多个事务是并⾏执⾏的，为了把他们的记录在⼀起，需要引⼊事务缓存的概念。在事务完成被提交的时
候⼀同刷新到⼆进制⽇志。对于⾮事务性语句的处理。遵循以下3条规则：
1. 如果⾮事务性语句被标记为事务性，那么将被写⼊事务缓冲。
2. 如果没有标记为事务性语句，⽽且事务缓存中没有，那么直接写⼊⼆进制⽇志。
3. 如果没有标记为事务性语句，但是事务缓存中有，那么写⼊事务缓冲。
注意如果在⼀个事务中有⾮事务性语句，那么将会利⽤规则2，优先将该影响⾮事务表语句直接写⼊⼆进制⽇志。
mysql下载32位如果⼀切正常，当事务中执⾏事务语句（DML）的时候，所以这些语句形成的event都会被记录到缓存⾥⾯，等到commit事务以后，这个缓存的数据会⽴即被刷⼊磁盘上的binlog
⽂件⾥⾯，之后通过slave请求发送到slave，在slave重复执⾏。
顺便提⼀下，mysql的binlog⾥⾯的事务都是相互独⽴按顺序排列的。
现在开始假设意外的发⽣。
正常意外⼀：事务执⾏中rollback了。
如果在⼀个事务中，所有执⾏的语句都是在innodb表上执⾏（对于多个事务引擎如tokudb等并⽤尚未测试），直接执⾏rollback，会导致mysql直接清除缓存，不会写⼊任何
event记录到binlog。
正常意外⼆：事务执⾏中使⽤了⾮事务表（myisam），然后rollback了。
在⼀个事务中，即有事务表，也有⾮事务表，如果正常执⾏，则没有任何问题，当如果在⾮事务表上的修改执⾏了，复制设置为statement的时候，binlog会把所有执⾏的event全部记录下来写⼊⽇志，包括事务与⾮事务语句，然后在最后⾯加上rollback。如果复制格式设置为row，那么所有事务表相关的数据都会被清理掉，⽽⾮事务表上的数据会被标记为已提交事务写⼊binlog。
正常意外三：事务执⾏顺序如下：事务语句1－》⾮事务语句2－》save point －》事务语句3 －》 rollback to savepoint －》事务语句4 －》提交
对于上⾯的情况，statement情况下，所有执⾏的语句包括savepoint与rollback都会按顺序被记录到binlog。row情况下，⾮事务语句与事务语句是分别记录到binlog中，⾮事务执⾏event会全部被记录，事务执⾏event会连带savepoint与rollback都记录下来。
正常意外四：事务执⾏顺序如下：事务语句1-》savepoint－》⾮事务语句2-》事务语句3-》rollback to savepoint－》事务语句4 －》 commit
同上。
异常意外⼀：master正常提交了，但是在slave执⾏失败了。
master执⾏完成，提交之后，slave接收到relay然后执⾏，执⾏期间由于意外原因失败了。这个时候，statement格式下，mysql会重新回到事务begin处重新执⾏，如果再次失败的话就会报错。row模式下情况类似，区别在于，如果是包含⾮事务表执⾏的事务，⾮事务表上的event不会被重新执⾏。
参考
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论