之前给客户部署sqlproxy来管理两个mysql节点间的复制关系。发现主从延迟特别的大,具体查看之后发现是slave上的sql thread延迟特别大。
经过一番折腾后发现了原因,这里记录一下。
用户那里设置的binlog记录模式为mix模式,而导致延迟特别大的语句是一个小表(几万行)上执行的
delete from... limit 0,10000
在mysql5.5版本中(不确定5.6是不是这样)如果在master执行的修改语句中带有了limit,那么mysql会认为这条语句是不安全的(即在从节点上执行的结果与主的不一致,主要是limit之后的数据集可能不一样),这种时候mysql会使用行级复制来复制这条语句的执行结果到slave上。
在客户现场那边就是被删除的那1万行会被记录到binlog中并同步到slave上。
但这个小表没有主键,这种情况下在slave上mysql会进行全表扫描去删除这1万行,这个表的数据量大约7万,
那么在slave上就需要扫描 7万*1万 = 7亿行。。。
表现出来就是这个binlog event在slave上执行了有20多秒。 而这个语句时每分钟一次的,所以延迟越来越大。。
解决,首先尝试给小表上索引,是会快不少,但也需要大概5秒,还是太慢。。。
最后只能是让应用层去修改sql语句避免使用limit。
参考 mysqlbug 53375
这个问题有对应的mysql work log 5597
据说这个问题的patch已经push到trunk的代码上了,估计5.6中就能解决了。
关于5.5版本中其他主从复制不安全的语句请查看:
http://dev.mysql.com/doc/refman/5.5/en/replication-rbr-safe-unsafe.html
经过一番折腾后发现了原因,这里记录一下。
用户那里设置的binlog记录模式为mix模式,而导致延迟特别大的语句是一个小表(几万行)上执行的
delete from... limit 0,10000
在mysql5.5版本中(不确定5.6是不是这样)如果在master执行的修改语句中带有了limit,那么mysql会认为这条语句是不安全的(即在从节点上执行的结果与主的不一致,主要是limit之后的数据集可能不一样),这种时候mysql会使用行级复制来复制这条语句的执行结果到slave上。
在客户现场那边就是被删除的那1万行会被记录到binlog中并同步到slave上。
但这个小表没有主键,这种情况下在slave上mysql会进行全表扫描去删除这1万行,这个表的数据量大约7万,
那么在slave上就需要扫描 7万*1万 = 7亿行。。。
表现出来就是这个binlog event在slave上执行了有20多秒。 而这个语句时每分钟一次的,所以延迟越来越大。。
解决,首先尝试给小表上索引,是会快不少,但也需要大概5秒,还是太慢。。。
最后只能是让应用层去修改sql语句避免使用limit。
参考 mysqlbug
这个问题有对应的mysql work log 5597
据说这个问题的patch已经push到trunk的代码上了,估计5.6中就能解决了。
关于5.5版本中其他主从复制不安全的语句请查看:
http://dev.mysql.com/doc/refman/5.5/en/replication-rbr-safe-unsafe.html