MySQL row格式的两个问题

作者简介:

刘伟 云和恩墨开源解决方案事业部首席架构师

多年一线互联网企业DBA经历,对MySQL、NoSQL,PostgreSQL等各类开源数据库均有涉猎,负责开发管理过数千实例规模数据库项目,并带领团队开发了MySQL数据库的监控、备份等自动化组件,对超大规模数据库运维平台的开发及管理有丰富经验。

在MySQL的一般场景中,通常我们推荐将复制格式设置为ROW格式,这样所有变更的数据都会被记录到binlog,可以对数据达到最好的保护,万一发生DML误操作,可以直接从binlog恢复数据。

但row格式的设置,会带来许多问题,运维也会相对复杂些。接下来我们通过两个真实的案例说明直接使用row格式复制出现的问题。

1

无索引表导致的延迟问题

原因简述

row格式的binlog event,在执行的时候,对于每一条数据,是通过类似翻译为对应SQL语句的方式逐条执行的。对于UPDATE及DELETE语句,如果表上面没有索引,或者已有索引区分度太低的话,执行速度会非常缓慢。

案例分析

这是我们在为客户排查数据库主从延迟时发现的问题:客户的核心数据库对外提供在线访问,并且应用系统的展示界面是直接读取数据库从库,数据库主从延迟会直接导致页面展示错误,造成经济损失。

在检查到主从同步延迟当时我们使用show slave status命令,(而实际上用心跳表方式更准确),由于业务系统非常重要,多个从库之间延迟严重,为了尽快解决问题,当时使用xtrabackup工具在线备份主库,并替换掉线上从库,暂时解决问题。

但延迟问题本身并没有得到解决。在随后的系统运行中,延迟仍然会产生,于是进行深入检查。

因为客户的binlog_format设置为row,复制进程也只是延迟,show slave status的relay_master_log_file,exec_master_log_pos两个变量一直没有变化,暂时判断为有操作大量数据的DML语句存在。

为了验证,我们解析了对应的binlog日志以及event。但实际结果,对应的event是一个delete event,涉及的数据只有三千多条,这个数量本身的操作并不应该是导致延迟。

然后我们直接查看对应的表结构,以及表的数据量。

mysql create table x ( i int, b varchar(10), v varchar(10), x_type varchar(10), index idx_type (x_type) );

表的数据量也只有几十万条。但是经询问开发人员得知,这个表每小时会插入几千条数据,之后再删除掉某一type的数据,这是表上新添加的规则,结合DBA的说法,这个增删数据的逻辑的导致问题的根源。

解决方法

解决办法有以下几种

1. 创建主键索引。如果目前表内数据本身支持创建唯一或者主键索引,可以直接建立索引解决问题。 2. 创建所有列的联合索引。如果表确实没有办法创建主键或者唯一索引,则尝试对所有列合并添加一个联合索引。 3. 创建部分列的联合索引。由于MySQL索引长度限制,如果确实没有办法创建所有列的索引,找出区分度比较大的几列创建索引。 4. 为表建立自增主键。以上都不能实现的话,为表创建自增id列并设置为主键。这种情况有可能会对应用造成影响,需要预先测试。

实际操作步骤

1. 停止同步。这种情况除了会导致sql延迟,也会导致sql线程持有非常多的行锁,stop slave语句的执行会比较慢需要等待,也可以直接kill掉线程,然后change master切换同步位点到问题event前的位置。 2. 为表加索引或者主键。 3. 启动同步。 4. 观察追上同步。

延伸讨论

MySQL中有一个参数,slave_rows_search_algorithms 可以控制row格式下,mysql执行event时候,搜索对应行的方式。

很多ORM框架由于对MySQL兼容不足,没有针对性的主键索引建立,在row格式下,会出现延迟。但在statement格式复制的情况下,未必会出现类似的问题。

常规建议:如果要使用row格式复制,需要保证自己数据库里面所有的innodb都必须有主键或者唯一键,这样才能避免由于没有合适索引导致的从库延迟问题。

另外,如果开发实用select以及insert语句的时候,都是明确指定列的方式使用的话,可以直接使用解决办法4最简单。这也是很多SQL规范推荐SQL语句需要明确指定列的原因之一。

2

从库alter语句导致同步中断

原因简述

MySQL row格式复制下,主从库之间同一个表如果列的类型不匹配,MySQL会尝试转码,如果转码失败(类型不兼容),则复制中断。

案例分析

问题来源为,客户主从复制中断,SQL线程报错信息为:

Last_SQL_Errno: 1677 Last_SQL_Error: Column 1 of table 'test.t' cannot be converted from type 'varchar' to type 'int'

这个错误明确说MySQL转码失败,询问DBA后,得知之前为了给表添加一列(开发要求列顺序必须为指定顺序):

mysql alter table a add column cl int

DBA试图采用的方式为:

1. 先为从库修改。 2. 在主库设置session的sql_log_off为0,修改从库。

然而执行完第一步后,就发现同步出现错误。直接原因确定为alter语句。经我们询问得知,出于安全考虑,数据库为最近才被修改为row格式,这次给表添加列是DBA在row环境下,初次执行这种类型的DDL语句。

新增的列由于被添加到列顺序中间,导致主库上是字符的列的位置,对应到从库上成了一个数字类型的列,MySQL无法转码,只能报错。

解决方法

  • 最方便的办法,也是最后采用的方法,就是从主库直接xtrabackup备份,之后恢复作为从库提供服务。
  • 另外一个办法是,在从库上把对应表新增的列去掉,重新启动同步。之后找合适的办法进行DDL的执行。

延伸讨论

由于row格式的event里面,只会按照主库的列顺序保存数据,并不会保存列名称,在从库的执行也是按照列顺序对应的,因此,row格式下修改列顺序并不是一个安全行为。

DDL的执行,在MySQL 5.7的时候,基本上都做到了在线改表。但主库在线修改完成之后,从库上的执行也会导致从库延迟,虽然不会阻塞从库的SELECT语句,但延迟本身就很难接受,因此实际情况中,如果无法接受延迟,还是需要考虑pt-osc或者gh-osc工具。

3

总结

MySQL的row格式复制对数据安全的保护,以及主从数据一致的保证是非常重要的,一般来说都建议设置成row格式。在MySQL 5.7之后已经作为复制的默认格式,但带来的运维方式方面的变更,以及对运维手段人员的要求也增高不少,如果需要设置,还是需要多加注意的。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-12-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏沃趣科技

ASM 翻译系列第十九弹:ASM Internal ASM Continuing Operations Directory

原作者:Bane Radulovic 译者: 魏兴华 审核: 魏兴华 ASM Continuing Operations Directory ASM...

3935
来自专栏刁寿钧的专栏

10 分钟梳理关系数据库基础知识(六) : 连接的算法与代价

每天10分钟,用去食堂吃饭的时间解决一个知识点。本文复习下做等值JOIN时不同的连接方式与代价,通过粗略的估算给大家一个直观的认识。

1860
来自专栏杨建荣的学习笔记

MySQL中批量初始化数据的对比测试(r12笔记第71天)

一直以来对于MySQL的存储过程性能还是颇有微词的,说实话够慢的。有时候想做一些对比测试,存储过程初始化几万条数据都得好一会儿,这功夫Oracle类似的测试...

3187
来自专栏SAP最佳业务实践

SAP最佳业务实践:无变式配置按订单生产(148)-7产成品的最终组装

1、MD04将计划订单转换为生产订单 创建不含外部处理工序的生产订单。 生成组件产成品 MTO (F226) 的计划订单。 后勤 - 物料管理 - 库存管理 -...

2757
来自专栏杨建荣的学习笔记

Mycat分库分表的简单实践

MySQL的使用场景中,读写分离只是方案中的一部分,想要扩展,势必会用到分库分表,可喜的是Mycat里已经做到了,今天花时间测试了一下,感觉还不错。 关于...

3616
来自专栏沃趣科技

sysbench的lua小改动导致的性能差异

最近在配合某同事做一项性能压测,发现相同数据量、相同数据库参数、相同sysbench压力、相同数据库版本和sysbench版本、相同服务器硬件环境下,我和同事的...

953
来自专栏微信终端开发团队的专栏

微信 iOS SQLite 源码优化实践

本文将分享在 SQLite 源码上进行的多线程并发、I/O性能优化等,并介绍优化相关的 SQLite 原理。

3030
来自专栏杨建荣的学习笔记

通过外部表改进一个繁琐的大查询 (r8笔记第32天)

今天处理了一个比较有意思的案例,说是有意思,因为涉及多个部门,但是哪个部门似乎都不愿意接。最后还是用了一些巧力,化干戈为玉帛。 问题的背景是这样的,业务部门需要...

3289
来自专栏xingoo, 一个梦想做发明家的程序员

循序渐进,了解Hive是什么!

一直想抽个时间整理下最近的所学,断断续续接触hive也有半个多月了,大体上了解了很多Hive相关的知识。那么,一般对陌生事物的认知都会经历下面几个阶段: ...

2035
来自专栏法海无边

Spring-data-jpa 让数据访问更简单、更优雅

JPA不属于ORM框架,只是一套持久化API使用规范,能够更加灵活方便的管理数据库操作。从一定意义上来讲,吸取了Hibernate和Mybatis各自的优缺点,...

1346

扫码关注云+社区