找到MySQL服务器发生SWAP罪魁祸首

0、导读

MySQL数据库服务器发生SWAP相信很多人都遇到过,如何找出元凶,又如何应对呢?

1、写在前面

在昨晚的知数堂公开课中,其实用的就是本次的案例。本次公开课的PPT、视频已上传到百度云盘,链接: https://pan.baidu.com/s/1eR53Qd8,欢迎转存及转发(扫描下方二维码识别)

知数堂培训全新MySQL DBA课程第九期和Python运维开发班第三期正在招生中,扫描下方二维码加入QQ群 529671799 (加群暗号:知数堂) 获得最新信息。

关于知数堂

http://zhishuedu.com

“知数堂培训”是由资深MySQL专家叶金荣、吴炳锡联合推出专业优质在线培训课程,主要有MySQL DBA实战优化和Python运维开发两个课程,是业内最有良心、最有品质的培训课程。

2、问题交代

我的朋友小芳(这次不是小明了,hoho),最近遇到了一个郁闷的问题:明明OS还有大量的空闲内存,可是却发生了SWAP,百思不得其解,她就来找我搬救兵了(嗯,我可不是🐒派来的)。

先看下SWAP是干嘛的,了解下它的背景知识。

在Linux下,SWAP的作用类似Windows系统下的“虚拟内存”。当物理内存不足时,拿出部分硬盘空间当SWAP分区(虚拟成内存)使用,从而解决内存容量不足的情况。

SWAP意思是交换,顾名思义,当某进程向OS请求内存发现不足时,OS会把内存中暂时不用的数据交换出去,放在SWAP分区中,这个过程称为SWAP OUT当某进程又需要这些数据且OS发现还有空闲物理内存时,又会把SWAP分区中的数据交换回物理内存中,这个过程称为SWAP IN。在vmstat的输出结果中,分别表现为 si\so 两列,如下图.1

swap1图1

看到这里我们就知道了,发生SWAP的最直接可能的原因是进程向OS申请内存时,发现物理内存不足,当没有SWAP可用的话,这时可能会一直等待,也可能会触发OOM-killer机制,OS把消耗内存最多的那个进程kill掉以释放内存,这个选择取决于内核参数 vm.swappiness。该参数可选范围从 0 – 100,设为 0 就是希望最大限度使用物理内存,尽量不使用swap,设为 100 则是希望积极使用swap。在运行数据库进程的服务器上,我们通常强烈建议这个值小于等于10,最好是设置为 0。原因很简单,对数据库这种需要集中CPU资源、大内存、高I/O的程序而言,如果用SWAP分区代替内存,那数据库服务性能将是不可接受的,还不如直接被OOM kill(数据库进程通常占用最多内存,最容易被OOM kill)来的痛快(早死晚死都是死,还不如痛快的死,反正很快就能重生,嗯)。

先介绍完这么多信息,大家肯定已经不耐烦了,我们就来看看现场并进行排查吧。

3、现场排查

首先,看下系统整体的状况,如下图.2所示
swap3图2

从上图能看出来什么呢,有几个关键信息:

  • 系统负载不算高,最近的平均load是6.8;
  • CPU负载也不算高,有大量的空闲,idle为 98.4%;
  • 内存主要分配给mysqld进程,占用了80.2%;
  • 尽管物理内存有256G,空闲的也将近39G,但确实发生swap了,并且把SWAP都耗尽了。

得到第一个排查结果:物理内存还有不少空闲,但却把swap都耗尽了。作为一个有经验的DBA,遇到这种情况第一反应是什么呢?嗯,先不点破,继续往下看。

再执行 free -gt 查看内存、SWAP消耗情况,如下图.3所示swap4-jpg
图3

看出来了吧,尤其是参加过 知数堂培训MySQL DBA优化班课程 的同学应该都知道,我们在课上多次强调:遇到这种情况,第一条件反射很直接就是:发生内存泄露(memory leak)了。

一般来说,如果发现内存统计结果中,cached 和 used 相差特别大的话,基本可确定系统发生内存泄露。相应的处理手法有:

  • 治标的办法:择机重启进程,彻底释放内存归还给OS;
  • 治本的办法:找到代码中导致泄露的代码,修复之(我们这次面对的是mysql代码,还是去官方提交bug吧,哈哈);
  • 治本的办法:升级程序版本,通常新版本会解决旧版本存在的问题,推荐此方案。

再看下MySQL中内存相关选项怎么配置的:
swap8-jpg
图4

除了 innodb-buffer-pool 分配的稍微多一些外,其他的还算正常。看了下,MySQL的版本是 5.6.19,看来是有必要升级到5.6系列的最新版本。

到这里,我们得到第二个排查结果:mysqld进程发生内存泄露,建议择机重启进程,并尽快安排升级到最新版本

然而,仅仅是因为mysqld进程内存泄露导致的SWAP吗,貌似不全然?还记得上面我们有个地方还没点破的不:物理内存还有不少空闲,但把swap都耗尽了。同样滴,这种案例在我们知数堂的MySQL DBA培训课程里也被多次谈及,绝大多数情况是因为没有关闭NUMA引起的。在运行数据库进程的服务器上,强烈建议关闭NUMA,在之前的分享 比较全面的MySQL优化参考(上篇) 中也有提及。我们接着来看下NUMA的状况:

swap5-jpg
图5

swap6-jpg
图6

从上面图.5、图.6可见,NUMA问题导致其中一个CPU可分配的内存远小于另一个(1.8G vs 38G),那么这个CPU上如果要申请大内存时,显然不够了,所以发生SWAP。关于NUMA的相关背景知识我这里不赘述。

因此,我们得到第三个排查结果:由于服务器硬件、系统设置不当,没有关闭NUMA,导致发生SWAP。建议方案有:

  • 在BIOS设置层面关闭NUMA,缺点是需要重启OS;
  • 或修改GRUB配置文件,缺点也是要重启OS;
  • 升级MySQL版本到5.6.27及以后,新增了一个选项 innodb_numa_interleave,只需要重启mysqld实例,无需重启OS,推荐此方案。

说到这里,这个问题已经基本分析清楚了,相关的解决建议也给了,根据自己的情况去评估选择哪个方案即可。

4、写在最后

类似的案例发生也不是一次两次了,我肯定以后还会继续存在,看完案例的的同学也没办法立刻把所有服务器上的NUMA策略全部修整过来,或者可能冲动一下想修复,但过几天就又给忘光了。

老叶曾多次在各种场合不厌其烦地强调一些基础的MySQL运维、开发规范,也正是因为看到了其实还有不少此类问题的存在。这些基础的规范都没执行到位的话,早晚是有一天要去填坑的,不管是填自己的挖下的坑,还是前人留下的坑,哈哈。

最后,作为一篇有诚意的案例总结,需要有诚意的广告才能搭配。

老叶茶馆镇店之宝,扫码识别或访问 http://yejinrong.com 直达

FAQ系列 | 是什么导致MySQL数据库服务器磁盘I/O高?

0、导读

有个MySQL服务器的磁盘I/O总有过高报警,怎么回事?

1、问题

我的朋友小明,TA有个MySQL服务器最近总是报告磁盘I/O非常高,想着我这有免费的不用白不用的企业技术服务(TA自己这么想的),就找我帮忙给把把脉。

作为一个经验丰富(踩坑不断)的DBA,出现这种问题,一般来说,磁盘I/O很高无非是下面几个原因引起:

  1. 磁盘子系统设备性能差,或采用ext2/ext3之类文件系统,或采用cfq之类的io scheduler,所以IOPS提上不去;
  2. SQL效率不高,比如没有索引,或者一次性读取大量数据,所以需要更多的I/O;
  3. 可用内存太小,内存中能缓存/缓冲的数据不多,所以需要更多的I/O。

方法论已有,接下来就是动手开始排查了。

2、排查

先看磁盘I/O设备,是由十几块SSD组成的RAID 10阵列,按理说I/O性能应该不至于太差,看iops和%util的数据也确实如此。

innodb-anli10

再来看下文件系统、io scheduler的因素,发现采用xfs文件系统,而且io scheduler用的是noop,看来也不是这个原因。而且看了下iostat的数据,发现iops也不算低,说明I/O能力还是可以的。

innodb-anli2

再来看看当前的processlist,以及slow query log,也没发现当前有特别明显的slow query,所以也不是这个原因了。

innodb-anli8

现在只剩下内存不足这个因素了,看了下服务器物理内存是64G,用系统命令 free 看了下,发现大部分都在cached,而free的也不多。观察InnoDB相关的配置以及status,看能不能找到端倪。

首先,看下 innodb-buffer-pool-size 分配了多少:

innodb-anli9-jpg

嗯,分配了18G,好像不是太多啊~

再看一下 innodb status:

innodb-anli4-jpg

重点关注下几个wait值,再看下show engine innodb结果:

innodb-anli5-jpg

关注下unpurge列表大小,看起来还是比较大的(有111万)。

更为诡异的是,在已经停掉SLAVE IO & SQL线程后,发现redo log还在一直增长…

第一次看

innodb-anli6-jpg

停掉SLAVE线程后过阵子再看

innodb-anli7-jpg

看到这里,有经验的DBA应该基本上能想明白了,主要是因为 innodb buffer pool 太小,导致了下面几个后果:

  1. dirty page 和 data page 之间相互“排挤抢占”,所以会出现 Innodb_buffer_pool_wait_free 事件;
  2. redo log 也没办法及时刷新到磁盘中,所以在SLAVE线程停掉后,能看到LSN还在持续增长;
  3. 同时我们也看到unpurge的列表也积攒到很大(111万),这导致了ibdata1文件涨到了146G之大,不过这个可能也是因为有某些事务长时间未提交。

还有,不知道大家注意到没,Innodb_row_lock_current_waits 的值竟然是 18446744073709551615(想想bigint多大),显然不可能啊。事实上,这种情况已经碰到过几次了,明明当前没有行锁,这个 status 值却不小,查了一下官方bug库,竟然只报告了一例,bug id是#71520。

3、解决

既然知道原因,问题解决起来也就快了,我们主要做了下面几个调整:

  • 调大innodb-buffer-pool-size,原则上不超过物理内存的70%,所以设置为40G;
  • 调大innodb-purge-thread,原来是1,调整成4;
  • 调大innodb_io_capacity和innodb_io_capacity_max,值分别为2万和2.5万;

调整完后,重启实例(5.7版本前调整innodb-buffer-pool-size 和 innodb-purge-thread 需要重启才生效)。再经观察,发现IOPS下降的很快,不再告警,同时 Innodb_buffer_pool_wait_free 也一直为 0,unpurge列表降到了数千级别,搞定,收工,继续搬砖卖茶~

老叶茶馆镇店之宝,访问 http://yejinrong.com 直达

【重磅】MySQL 8.0 pre release看点

导读

MySQL 8.0即将发布,一起来看看都有什么料。

昨天就收到了官方发给ACEs的邮件,不过今天白天一直在忙着给新房子做保洁,没来得及认真查看邮件内容,只是简单扫了一眼,整体感觉还是有很多惊喜,不过也大体上在意料之中。

重大变更

  1. 彻底将数据字典表引擎从MyISAM改为InnoDB。显然地,这是要更进一步放弃MyISAM引擎的节奏,还在负隅顽抗的亲们,要认清形势了,哈哈。事实上,我当初的预期是在5.7就该这么做的。
  2. 增加了ROLE特性。这是继续向ORACLE看齐啊,我个人没啥特别感觉。
  3. 修改默认字符集为utf8mb4。这个,见仁见智吧,各有喜好,不管怎样,也算是紧跟着时代发展的决定了。
  4. 新增INVISIBLE(不可见)索引属性。可将一个索引设置为不可见,那么查询优化器不再识别该索引。这么做的用意是,方便DBA判断某个索引过一阵子是否可以删除,不再担心误杀。
  5. 位运算功能增强。以前只能针对INT类型做位运算,现在增加支持二进制类型的运算,比如 [VAR]BINARY/[TINY|MEDIUM|LONG]BLOB 等多种类型。另外,也支持对 IPV6 和 UUID 类型的位运算。
  6. 在线修改全局选项(GLOBAL VARIABLES)时,增加 PERSIST 关键字后,可以将该调整持久化,即便实例重启。这就可以避免了在线修改某些选项后,忘了同时修改 my.cnf,造成了实例重启后,这个修改又被恢复原样的问题。这个功能显然也是在学习ORACLE的控制文件做法呀。
  7. 增强P_S(performance_schema)功能,主要有几点:
    1. 可以在P_S中直接查看错误日志;
    2. 在P_S中增加了类似自适应哈希索引的索引机制,便于快速检索P_S中的数据;
    3. 在线修改全局选项(GLOBAL VARIABLES)时,增加 PERSIST 关键字后,可以将该调整持久化,即便实例重启。这就可以避免了在线修改某些选项后,忘了同时修改 my.cnf,造成了实例重启后,这个修改又被恢复原样的问题。这个功能显然也是在学习ORACLE的控制文件做法呀。
  8. 重构SQL解析器。简单来说,原先的解析器有严重的问题,维护性、扩展性都不好,在8.0里进行了重构,以后会做的更牛逼。
  9. 查询优化器Hints功能增强。在5.6及更早版本中,子查询中的派生表总是要进行物化,效率很低。从5.7开始,优化器会根据情况进行判断,自行决定在哪些情形下需要物化,哪些直接合并进外部查询中,通常来说,后一种做法效率会搞很多。在8.0中,可以在SQL中增加 /*+ merge */ 关键字来决定哪些情形下将派生表合并到外部查询中以提升查询效率。
  10. 查询优化器功能增强。它会判断InnoDB Buffer中数据、索引缓冲比例的情况,决定每个SQL的访问模式,尽量避免发生物理读。
  11. 增加直方图功能。不知道直方图什么意思的话,可以了解下MariaDB分支中的QUERY RESPONSE TIME这个插件的功能,和这个基本相似。又是学习ORACLE的做法呀,汗(⊙﹏⊙)b
  12. GIS功能增强。增加了经度、纬度、平面地图等功能。
  13. 提高数据扫描查询效率。例如下面这样的SQL大概能有5-20%的效率提升:

    SELECT * FROM t;

    或是

    SELECT * FROM t WHERE pk BETWEEN 1000 AND 10000;

  14. 增强InnoDB引擎中部分读取或更新BLOB数据类型的效率。
  15. InnoDB Memcached功能增强,增加一次取多个值(mget),以及范围搜索。
  16. 修复了InnoDB重启后,自增值丢失的bug,这个bug历史非常悠久(bug id是199,可想而知,嗯…)。
  17. 会对那些损坏的数据页加上标记,数据库实例在进行recovery时,就会忽略这些page了。
  18. 临时表增强。首先,将压缩类型临时表映射成未压缩格式。其次,将临时表的元数据存储在内存中。
  19. InnoDB性能大幅提升,主要做了几个事情:
    1. 可以把想通table ID组内的undo数据批量purge;
    2. 废除buffer pool mutex。将原来一个mutex拆分成多个,提高并发;
    3. 拆分LOCK_thd_list 和 LOCK_thd_remove 这两个mutex,大约可提高线程链接效率5%。
  20. MySQL复制功能方面的改进或提升不多,这个让我非常诧异,我也就没写出来了。

写到凌晨一点半,实在有点累(开头说过,今天做了一天保洁)。其他更多新特性,我大概过了一眼,好像不是太有诱惑性,这次就先不列出来了。

毕竟,现在还只是pre release,在后续的计划中,肯定还会发生很大变化。比如这次就完全没提到group replication,也压根没提到之前广大中国MySQL用户提交给官方的新功能需求,汗一个(⊙﹏⊙)b

再小小吐槽下,安装包真的是是越做越大了,建议官方发布的时候,再提供一个strip过后的二进制包吧,下载起来更快一些~~~

好了,先到这里,下次再扯。

【深度总结】服务器硬件管理

0、导读

17173系统部系统工程师余祥军倾心总结的多年服务器硬件管理经验。

1、内容简介

余祥军是17173系统部系统工程师,入职几年进步飞快,更是特别擅长总结分享。余祥军根据自己多年和服务器(尤其是DELL机型)打交道的经验,整理成本文,不吝拿出来和大家分享。

本次分享的PPT资料已经上传到百度云盘,链接: https://pan.baidu.com/s/1pLITS3h ,欢迎转存及转发。

【友情提示】知数堂培训全新MySQL DBA课程第八期已火爆开课,全新Python运维开发班第二期8.27开课,请加入QQ群:529671799 获得最新信息。

【干货】分享总结:MySQL数据一致性

0、导读

沃趣科技数据库工程师罗小波为大家全面分析如何保证MySQL的数据一致性。

1、活动总结

罗小波老师从MySQL的崩溃数据恢复安全性、MySQL复制原理及异步&semi sync复制原理、MySQL主从服务器如何保证数据一致性等多方面分析如何保证MySQL的数据一致性。

分享内容满满的干货,非常给力!

值得一提的是,罗小波是知数堂MySQL DBA实战优化班第5期学员,学习非常努力,在结业考试中名列前三甲。后来有幸加入沃趣科技,在职业道路上获得了飞跃。祝福小波!

本次分享的音视频、PPT相关资料已经上传到百度云盘,链接: https://pan.baidu.com/s/1miuDuDM ,欢迎转存及转发。

【友情提示】知数堂培训全新MySQL DBA课程第八期已火爆开课,全新Python运维开发班第二期8.27开课,请加入QQ群:529671799 获得最新信息。

最后再次感谢大家对知数堂培训的支持和关注!

闲扯DBA品质对日常生活的影响

导读

本来也想凑热闹说下宝宝的话题,想想还是算了。还是闲扯下DBA品质对日常生活的影响吧。

还有,今天(8.15)是日本无条件投降纪念日,勿忘国耻,奋发图强才是正道,别整天吵吵抵制日货,没毛用。

囧事

上周末带小孩回了趟老家,结果往车里放完东西后,很帅的顺手把车门一关,然后再很顺手的摸了一下钥匙,完蛋,给锁车里了。

按理说,钥匙在车里的情况下,不应该还能锁车才对,可是科帕奇居然还是给锁了,显然不合理(虽然可能由于其他什么原因这么设计的),问了几位其他车型的朋友,都没有这种情况。

幸运的是,我每次出远门,都习惯带两把钥匙(DBA重要品质之一:备份意识,告诉我应该这么做,O(∩_∩)O哈哈~),后来让家里人把备用钥匙从40公里之外的地方送过来,终于解救了。

之后,又发生了一件囧事,因为钥匙的问题耽误了很长时间,急着从老家赶到县城,结果钱包和驾照落在老家了,汗啊😓。这说明了DBA的重要品质之二:标准化/checklist,我没做到位才导致这个问题发生。

DBA重要品质有哪些

我觉得,想作为一个优秀的DBA,应该至少具备以下几个重要的品质:

  1. 备份意识。可以不够精通优化,可以不够深入核心,但一定要重视备份,没有备份的话,万一误操作数据被删,就一切无可挽回。备份意识不仅仅是数据备份,可能还有人员备份(备岗),需要从制度上强化备份意识。

    比如我会把家里钥匙放一把在附近朋友家,再放一把在公司,遇到出门忘带钥匙时就不怕了。

  2. 抗压/稳健。遇事不慌张,万一不慎“删库”了咋整,可别真的“跑路”啊。只有强大的抗压力,稳健的心态,在遇到大事时才能及时找到合适的应对方案。

    做男人压力很大啊,凡事都得自己扛,别趴下。。。

  3. 标准化/checklist。把一些重要操作参照流程,提前准备好各个环节要做的事情。如果有自动化运维平台,则可以把这个流程整合进去,由平台来帮我们完成标准化检查,避免漏掉个别环节,造成不可估量的损失。

    出门之前检查下手机、钱包、钥匙是不是都带了(现在我家小朋友出门前都会提醒我老婆要带这三样东西)

其他诸如思维敏捷、勤奋努力,以及要对数据库各种XXX原理深入理解的废话我就不多说啦。好了,闲扯结束,各位看官别嫌我啰嗦,抱歉抱歉 O(∩_∩)O~

MySQL?PostgreSQL?又开撕了…

168

到底我该选哪个

PostgreSQL宣称是:The world’s most advanced open source database

MySQL则宣称是:The world’s most popular open source database

而我的观点是

选择最合适的,而不是“最好”的!

那么,什么才是最合适的呢,我想有几点:

  1. 哪个数据库的使用量够大,从业者够多。人多力量大,有问题也能更早、更容易发现,更容易解决。最最最重要的,招人更快(小道消息:知数堂的MySQL DBA课程,已为行业培养了众多优秀DBA人才,可以到我们这里直招哟,发送“开班”了解详情)。
  2. 在项目初始时,某关键技术负责人的喜好基本直接决定了用哪个数据。
  3. 大部分开发工程师更熟悉哪种数据库就选哪个。

某些具体技术细节上的优劣,并不能直接决定最终的选择!

 

毛爷爷教导过我们,要实事求是。凡事要根据实际情况而定,不能因为我对MySQL最熟悉,所以要在所有业务场景下都选择MySQL(比如大数据分析场景),那显然也是很愚蠢的做法,会害了整个项目。

某公司高调宣传从某种数据库转变成另一种数据库时,作为从业者,我们更应该冷静看待、分析,到底因为哪些因素才促使他们作出新的选择,我们的业务中是否也有这些痛点。切!忌!盲!目!跟!风!

毕竟,虽然是以某公司的名义发布的文章,其实很可能仅仅只是公司内部的一个项目而已。就像早期国内还没什么人敢用Percona版本时,我就坚定的先用起来了,结果业内曾经有段时间在风传sohu公司是国内最大的Percona用户。实际上,我当时只是在sohu集团旗下的畅游公司里的某个项目先启用了Percona分支版本,呵呵!

知数堂培训在线免费分享《DBA神技之SQL Review》

2016.7.28

知数堂培训推出免费在线分享《DBA神技之SQL Review》

1、分享主题

《DBA神技之SQL Review》

2、嘉宾介绍

吴炳锡,知数堂培训联合创始人,前新媒传信首席DBA、MySQL中国用户组(ACMUG)主席,吴炳锡老师具有多年MySQL及系统架构设计及培训教学经验,擅长MySQL大规模运维管理优化、高可用方案、多IDC架构设计,以及企业应用数据库设计等经验。

3、主题介绍

在DBA工作中,有个重要内容是做SQL Review。

面对成千上万的SQL时,如何判断每个SQL的质量高低,怎么从中快速找到那些“害群之马”,有什么神技吗。

此外,线上Schema和SQL怎么进行优化,也特别考验DBA的综合能力。

让我们一起来看看,DBA进行SQL Review时都有那些神技可用。

Agenda

  1. DBA要做哪些SQL Review工作
  2. Schema Review的注意事项
  3. SQL Review的注意事项
  4. 线上Schema分析、优化技巧
  5. 线上SQL分析、优化技巧

其他

分享时间:2016.7.28(周四) 晚上20:30 – 21:30

分享方式:YY语音直播,在QQ&微信群发送PPT等图文内容

YY频道:53695719(需提前安装YY客户端,支持windows/ios/andriod多平台)

知数堂分享QQ主群:529671799(加群暗号:知数堂)

扫描下面二维码加入QQ群

QQ群1:529671799

zst-qq-2

关于知数堂

“知数堂培训”是由资深MySQL专家叶金荣、吴炳锡联合推出专业优质在线培训课程,当前主要有MySQL DBA实战优化和Python运维开发两个课程,是业内最有良心、最有品质的培训课程。

目前MySQL DBA实战优化班第八期以及Python运维开发班第二期均在火热招生中。学员已有400多人,超过40%的优秀学员进入腾讯、淘宝、京东、乐视、去哪儿、滴滴、猎豹、58、微博、金山云、聚美、苏宁、恩墨、沃趣、爱可生、37玩、宝存、人人贷、美的、新东方、平安金融等众多知名公司,在获得更好的职业发展机遇同时薪资也得到了大幅提升。有兴趣的同学请发送 “开班” 或 “招生” 关键字即可获得详细信息。

MySQL DBA实战优化班课程从第八期起全新升级,除了将MySQL教学版本升级到5.7外,还加入Percona、MariaDB的使用实践经验,以及更多实战案例,课程内容精彩纷呈不容错过。

更多关于知数堂培训招生请点击下面链接:

知数堂MySQL DBA在线培训第八期招生中

FAQ系列 | table id问题导致主从复制失败

0、导读

主从复制环境中,IO、SQL线程都很正常,也没设置过滤规则,但数据就是无法复制到slave上,什么原因?

1、问题描述

事实上,这个案例发生已经有一阵子了,一直拖到现在我才整理。

发现一个主从环境中,slave上的io_thread、sql_thread状态均正常,relay log也正常接收来自master的event,但slave上却无法正常应用这些event,个别表数据没有复制过来。而且slave上的binlog也没有记录这些表上的操作。

2、原因分析

接到现场后,第一反应是是先检查是否设置了ignore/do规则,发现并不是这个原因引起的。

我自己手动测试创建了个新的测试表,写了几条数据,发现在slave上这个表能被创建,但写入的测试数据仍旧无法复制过来。这说明,slave上的复制并不是完全失效的,只是有特殊情形下才会失效。

结合上面的问题,想到了可能是因为binlog format以及事务隔离级别等原因导致失效的,于是做了下面的尝试。

//首先修改事务隔离级别为RR(此前是RC),尽可能保证主从数据一致性

root@imysql [mydb]> set session transaction isolation level repeatable read;

//测试写入2条数据

root@imysql [mydb]> insert into z select 5,5;

root@imysql [mydb]> insert into z select 6,6;

经过观察,这2条数据不可以复制到slave上。

//修改binlog format为statement(此前是row),再写入2条数据

root@imysql [mydb]> set session binlog_format=’statement’;

root@imysql [mydb]> insert into z select 7,7;

root@imysql [mydb]> insert into z select 8,8;

经过观察,这2条数据则可以复制到slave上。

现在至少表面上看起来,是由于binlog format+事务隔离级别综合因素引起的,所以我们来对比下不同binlog format下的binlog有什么区别吧。

tableid1
这些日志中,前两条是row模式下的日志,后两条则是statement模式下的。我们注意到红框中内容是:table_id: 24874588093,正是由于这个原因导致了slave无法正常复制数据。

正常情况下,row模式下的binlog event应该是这样的:

tableid2
在上面的日志中,我们看到的是:table_id: 108,这种情况下就可以正常复制了。

现在问题很明确了,就是由于binlog中table id异常导致无法复制。那么,到底什么原因导致table id出现异常呢。

3、案例建议

搜索了一些资料,发现也有别人遇到同样的问题。我就不多啰嗦了,大家可以看下方参考文章详细了解下。简言之,发生这中问题的原因,主要是因为table cache不够了,导致要频繁打开、关闭table,导致table id急剧增长,因而导致主从数据复制失败。

解决办法有几个:

  1. 加大 table_cache_size,或者 table_open_cache 值,以及 table_definition_cache 选项。一般设置不低于总table数量的1.5倍,更严谨的话,要看 Open_tables 和 Opened_tables 这两个status值。Open_tables 表示当前正被打开的table数量,而 Opened_tables 表示历史上反复打开table的总次数。如果 Opened_tables 值特别高,表明 table cache 很可能不够用所致。
  2. 择机重启主库实例,让table id的值再次从0开始计数。
  3. 临时解决方案:把binlog format改成statement,并且把事务隔离级别改成RR,尽量避免数据不一致的风险。

本文参考:

1. 杨奇龙《【MySQL】再说MySQL中的 table_id 》,http://blog.itpub.net/22664653/viewspace-1158547/

2. yuyue2014《MySQL table_id原理及风险分析》,http://www.cnblogs.com/yuyue2014/p/3721172.html

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 直达