干货分享《如何系统的学好Python》

知数堂公开课《如何系统的学好Python》,详情请见『老叶茶馆』微信公众号图文分享

本次分享的视频地址请见百度云盘,链接: https://pan.baidu.com/s/1c21PxtA,密码: 7wxo

MySQL工具推荐 | 基于MySQL binlog的flashback工具

1、前言

相信您应该遇到过因为误操作破坏数据库的问题,比如忘了带WHERE条件的UPDATE、DELETE操作,然后就需要进行传统方式的全量 & 增量恢复。现在,给您介绍一下MySQL中的flashback玩法,也可以做到像Oracle的flashback那样。

目前MySQL的 flashback(又称 闪回)一般是利用binlog完成的,能快速完成恢复且无需停机维护。

第一个实现该功能的是阿里云的 彭立勋,他在MySQL 5.5版本上就已实现,并将其开源及提交给MariaDB,为社区提供了非常优秀的参考模型。

2、闪回原理

本节我们先来介绍一下MySQL binlog flashback的基本工作原理。

MySQL的binlog以event的形式,记录了MySQL中所有的变更情况,利用binlog我们就能够重现所记录的所有操作。

MySQL引入binlog主要有两个用途/目的:一是为了主从复制;二是用于备份恢复后需要重新应用部分binlog,从而达到全备+增备的效果。

MySQL的binlog共有三种可选格式(binlog_format),其各有优缺点:

  • statement,基于SQL语句的模式,一般来说生成的binlog尺寸较小,但是某些不确定性SQL语句或函数在复制过程可能导致数据不一致甚至出错;
  • row,基于数据行的模式,记录的是数据行的完整变化。相对更安全,推荐使用(但通常生成的binlog会比其他两种模式大很多);
  • mixed,混合模式,可以根据情况自动选用statement抑或row模式;这个模式下也可能造成主从数据不一直。它属于MySQL 5.1版本时期的过渡方案。因此,如果你现在还使用mixed的话,那你的过渡时间也太久了……

备注:想要使用binlog flashback工具,需要将binlog_format设置为row才行。

3、工具推荐

项目一:mysqlbinlog_flashback

项目作者:赖亿@58到家

github项目地址:https://github.com/58daojia-dba/mysqlbinlog_flashback

也可在github.com上搜索“mysqlbinlog_flashback

项目介绍:产生在线mysqlbinlog的回滚的sql,现在已经在阿里的rds上,db为utf8字符集的生产环境下使用。其他环境没有在生产环境下使用,请小心。

项目使用反馈:laiyi@daojia.com

项目二:binlog2sql 

项目作者:曹单锋

github项目地址:https://github.com/danfengcao/binlog2sql

也可在github.com上搜索“binlog2sql

项目介绍:从MySQL binlog解析出你要的SQL。根据不同选项,你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

项目反馈:danfengcao.info@gmail.com

应用场景

  • flashback,数据快速回滚;
  • 主从切换后数据不一致的修复;
  • 从binlog生成标准SQL,再自行二次开发;

5、使用方法

两个软件的使用上都比简单,都是在 https://github.com/noplay/python-mysql-replication 项目基础上进行的二次开发。

两个项目中都有详细的使用说明,感谢两位作者细心的整理,我们这里不再进行赘述,请自行到作者的项目上查看,如果对你帮助,请记得给个 star 哟!

6、flashback总结

社区里做这块的工具比较多,不开源的这里不在讨论,开源产品随着时间的前进,作者有可能忙于其它事情,没来的及更新也会失效过期。所以使用中需要有一定自我修订能力,也希望各位使用者也能加入到开源的大家庭中,共同维护这些项目。

这两款工具开发时侧重点不同,所以使用中也需要注意一下:

  • mysqlbinlog_flashback 更便重于阿里云 RDS环境的使用。
  • binlog2sql  便重于通常MySQL的处理。从代码上来看,该项目更简洁一点。

在具体使用中及项目定制中那个更合适,只有使用后,找到适合自已就可以。 这也是开源的魅力。 如果对这两个软件有兴趣深度交流的,也可以加到QQ群: 529671799  (两位作者已在)来交流吧。

又搞飞机了,号称有五重备份的GitLab居然也歇了

0、导读

《炉石传说》游戏数据库回档事件刚过去几天,GitLab又来凑热闹了

今天下午开始,朋友圈被GitLab误删数据事件给刷爆了。截止发稿时还是离线维护状态。

中国农历春节前才刚刚出了《炉石传说》游戏数据库回档事件(猜测是因为认为误操作导致数据丢失并且备份也失效)。农历春节都还没过去,GitLab也来给大家增加谈资了。

先来看下官方发布的消息:

不过好在这次误删除的数据不是最关键的代码仓库数据,也算是不幸中的万幸。

GitLab号称有五重备份:

  • 每24小时LVM快照备份;
  • 每24小时常规备份;
  • S3备份;
  • 应该是调用了pg_dump的脚本自动备份;
  • Azure备份(只对 NFS 启用,非数据库备份);

从披露的消息并没看到他们如何确认备份数据的有效性,这也是最可怕的地方:对备份机制过分信任,却可能没有可靠的备份恢复测试及验证机制,这是非常危险的行为。之前我在 今天你检查备份了吗? 这篇文章中强调过:备份文件务必进行恢复测试。此外,重要操作命令一定要想清楚、看清楚了再回车确认,发生事故时再怨天尤人,怪老板没给加薪、没发过年红包、过度加班疲劳等都是扯淡的理由,还是自觉面壁去吧~~

和服务器打交道的都要谨慎,这不仅仅是DBA该具备的素质,也不用搞什么“世界备份日”,最重要的是做好备份、并想进一切手段避免误操作,时刻保持敬畏之心

春节假期马上就要结束了,祝大家在新的一年里搬砖更轻松,最好还是能天天抱金砖,hoho~~

祝愿你的2017年,一点都不像2016年

去年4月(此刻,已到2017年,所以是去年),我尾随好友兼合伙人吴炳锡,也辞职了。我们一起合作发起了“知数堂”这个在线培训品牌,立志打造圈内最有良心、最有品质的品牌,当然,我们现在离这个目标还远的很,但我们一直在努力。

这将近一年以来,也受到各种各样的机会诱惑(或者说干扰),比如合作,比如投资,比如是否要开线下实体课程等等。我个人是比较谨慎保守的,而且之前定下的目标是,无论如何,先自己折腾一年看看能做成什么样再做打算。现在回头来看,我们做的并不算出色,但知数堂依然获得了大家的肯定,也有了很大突破,我们的学员遍布全球各地华人区,众多优秀学员分布在互联网、集成商、电信、银行、制造业、物流等各个行业,包括腾讯、淘宝、京东、乐视、去哪儿、滴滴、猎豹、58、微博、金山云、聚美、苏宁、恩墨、沃趣、爱可生、东方龙马、37玩、宝存、人人贷、中移动、顺丰、德邦、美的、新东方、平安金融等众多知名企业。截止当前,也超过10位学员通过了MySQL OCP认证,对我们而言,都是令人非常欣慰的成就。

在对外合作或投资这件事上,如果只是维持现状并有小动作的话,我们并不算缺钱,更多的是缺乏品牌建设以及市场营销能力,这可能也是大多数技术出身创业者的短板吧。不过这也不完全是坏事,正因为如此,我和吴炳锡老师才能更专注在我们课程质量以及学员配套服务建设上。

对我而言,2016是如此的重要,这一年,我下定决心走出来自己做,老实说压力还是很大的,不管是经济压力,还是工作压力,当然更多的是时间压力,一天就24小时,如何有效利用这点时间,并做着自己不擅长的事情,像做文案、P图、写通告、十分粗糙的营销等等,以前不用操心的事情,现在神马鸡毛蒜皮的事都得管、都得做,估计幸好在坚持跑步,才没那么快长白发,哈哈。

说到跑步,2016年相比上一年跑的少了,所以几次参赛的成绩都不太理想 😓

知数堂之外,我还兼职卖老家的铁观音茶叶。承蒙圈中各路朋友不吝支持,今年整体卖的还不错,尤其是下半年拿到一批好货,就在上周,已经全部售罄,识货的朋友都叫好,还想再来点,可惜已经缺货。好茶就像好机会,不抓住这波,可能就至少要再等一年,而且还不能保证同样价位的茶叶口感、档次完全一样,但,老叶茶馆会秉承和知数堂一样最有良心、最有品质的品牌目标为大家伙找到靠谱的茶叶。我还梦想着有一天,能靠卖茶叶养家,不再做苦逼的IT技术男。

一句话,想要靠谱的MySQL、Python培训,以及靠谱的铁观音茶叶(明年开拓红茶等其他品类),请来找知数堂培训/老叶茶馆,您就放心好了

2016年我还轮值主持了ACMUG的活动规划,今年我们跑了深圳、武汉、北京、厦门、上海,最后又回到北京。经过几次活动建设,使得ACMUG的影响力达到空前。当然,这是整个主席团的集体努力还有赞助商、合作伙伴、志愿者们的共同付出,不是紧靠一两个人就能做好。2017年,我们将继续传播开源,传递快乐,倡导向上、开放、友好的理念。

另外,我在2016年还搞定了2套房子。别想多了,当然都是有贷款的哟,一个是收新房简单配置家具家电出租了事,一个是买二手房用于将来娃娃读书用(也就是学区房😭),还好这个学区房环境不错,挨着2个公园,平时跑步锻炼非常方便,超值了。

自从我和吴炳锡老师一起经营知数堂,把她从“某个不知名的培训机构”打造成在圈子里还算略有名气的品牌。在做这些事的过程中,我也开始特别关注非技术之外的事情,包括营销文案等方面。如果您对这个也有兴趣,推荐关注:李叫兽、小马宋等。总之,会更加关注一些新鲜的事情,也会尝试向90后、00后那一代人学习,毕竟这个社会未来属于他们的。当遇到60后、70后说我们是垮掉的一代时,努力创造属于我们的成绩就可以,别的不用太在意。一句话,开心就好

2016年很精彩,大家都在抓黑天鹅、跟热点事件,我则总是慢半拍,慢热型的,也不太擅长炒作自己或标新立异,更多的是埋头做事,这算不算也是一种匠心精神呢,哈哈。

明年是农历鸡年,也是我的本命年,希望在新的一年里,能继续做好知数堂,为技术圈培养更多可靠的人才,也能卖出更多茶叶 O(∩_∩)O哈哈~

新的一年里,我最大的目标只有一个:全程马拉松能稳定跑进4小时30分,也就是从跑渣渣晋级到跑渣,嘿!

最后,祝愿各位的2017年,一点都不像你的2016年

今天你检查备份了吗?

0、导读

《炉石传说》游戏数据库回档事件反思

本文约900字,阅读时间约5分钟。

今天引爆各大技术群的事情就是网易游戏《炉石传说》游戏数据库发生宕机并引发数据丢失事故,最终决定回档并后续补偿玩家损失。详情可见官网公告:http://hs.blizzard.cn/articles/16/8565

我以前也在搜狐畅游(http://www.changyou.com,NASDAQ:CYOU)负责游戏数据库维护,也遇到过因为服务器故障最终导致回档的事故,不过都没像这次炉石搞这么大动作。在这里我并不想借机调侃消费他们或搞营销,只想和大家一起聊聊作为DBA,应该注意哪些事。

我们从公告的内容中,我们看到了几个问题:

  1. 公告发布时间是2017.1.18 18点,决定回档到2017.1.14 15:20,中间这段时间难道一直都在尝试恢复数据库,就不能快速做出决策尽快直接回档吗,这是在考验游戏玩家的耐心,很容易引发玩家的“群体事件”;
  2. 因为供电意外导致故障,并造成数据库损坏,如果也用MySQL数据库的话,看起来应该是没开启双1设置,并且有可能还在使用老式的锂电池BBU。所以断电后很容易导致阵列卡cache中的数据丢失,数据库也跟着损坏,以前没少才踩这个坑;
  3. 连备份数据库也发生故障,有点不可思议,这样就容易让人产生是人为事故的联想了。不过,我多年前也发生过类似的情况,不过那次是因为用mysqldump备份时指定了错误的字符集,并且在做备份恢复测试时没严格测试数据的有效性,致使发生故障时不能正常恢复,结果也悲剧了。作为不了解内情的局外人,只能以官方公告为准,无要无端臆测;

关于服务器可靠性以及数据库备份,有几点建议:

  1. 必须定期全备,并且优先推荐物理备份,逻辑备份通常相对更慢。一般至少每天一次全备;
  2. 每小时一次增备或差异备份,我以前的做法是开binlog,并且利用last_update_time列特征每小时做一次差异备份。这样我要恢复的话,一般最多只损失不到一个小时的数据;
  3. 备份文件务必进行恢复测试,如果有多个备份集,可以采用随机抽取的方式做恢复测试,但一定要保证所有实例的备份最终都会被验证一次;
  4. 必须监控服务器硬件健康状况,包括CPU、内存、阵列卡、阵列卡电池等部件,以及服务器温度等。我们曾经有在哈尔滨及西安某机房的服务器,一到夏天就很容易因为温度过高而引发自动重启😓😓 我们的解决方案就是利用监控,提前预警,及早通知机房打开机柜门并且安排散热,比如很low的放着风扇对服务器吹啊吹 😓😓

快过年了,做运维的同学应该也都差不多做完全服巡检了吧,先祝大家春节快乐,鸡年吉祥,新的一年服务器宕机率减少99%😄😄

FAQ系列 | 纳尼,mysqldump导出的数据居然少了40万?

0、导读

用mysqldump备份数据时,加上 -w 条件选项过滤部分数据,发现导出结果比实际少了40万,什么情况?

本文约1500字,阅读时间约5分钟。

1、问题

我的朋友小文前几天遇到一个怪事,他用mysqldump备份数据时,加上了 -w 选项过滤部分数据,发现导出的数据比实际上少了40万。

要进行备份表DDL见下:

CREATE TABLE `oldbiao` (

`aaaid` int(11) NOT NULL,

`bbbid` int(11) NOT NULL,

`cccid` int(11) NOT NULL,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`dddid` int(11) DEFAULT NULL,

KEY `index01` (`ccccid`),

KEY `index02` (`dddid`,`time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

顺便吐槽下,这个表DDL设计的真是low啊。没主键,允许NULL。

mysqldump备份指令增加的 -w/–where 选项是:

-w “time>‘2016-08-01 00:00:00′”

加上这个参数的作用是:只备份 2016-8-1 之后的所有数据,相当于执行了下面这样的SQL命令:

SELECT SQL_NO_CACHE * FROM t WHERE time>‘2016-08-01 00:00:00′

然后把导出的SQL文件恢复后,再随机抽查下数据看看是否有遗漏的。不查不知道,一查吓一跳,发现 2016-12-12 下午的数据是缺失的。经过仔细核查,发现比原数据大概少了40万条记录。

百思不得其解的小文请我帮忙排查问题。

2、排查

既然是少了一部分数据,那就要先定位到底是丢失了的是哪部分数据。

那么,如何定位呢?

搞数据库的人,应该都知道折半查找法,这是计算机科学里比较基础的概念之一。我们就利用这种方法来快速定位。

经过排查,发现是缺少的数据有个特点,根据时间排序,发现最早的数据是 2016-8-1 8点的,而我们上面设定的条件则是 2016-8-1 0点开始的所有数据,整整差了8个小时。

看到8小时这个特点,我想你应该大概想到什么原因了吧。对,没错,就是因为时区的因素导致的。

经过排查,发现是因为原先写数据时,是以 0时区 时间写入的,但执行mysqldump备份时则使用的是本地 东8区 的时间,所以就有了8小时的差距。

2、解决

知道了问题所在,方法就简单了。有两个方法:

1、修改mysqldump中的where条件时间值,减去8个小时。建议采用该方法。

mysqldump … -w “time>‘2016-07-31 16:00:00′”

2、修改MySQL全局时区,从 0时区 改成 东8区,并且mysqldump加上 –skip-tz-utc 选项。这种方法需要修改MySQL的全局时区,可能会导致更多的业务问题,因此强烈不建议使用

mysqldump … –skip-tz-utc -w “time>‘2016-08-01 08:00:00′”

问题暂且按下,我们先来看下时区因素怎么影响查询结果的。

先看下系统本地时间:

[yejr@imsyql]$ date -R

Wed, 21 Dec 2016 14:04:51 +0800

测试表DDL:

CREATE TABLE `t1` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`c1` timestamp NULL DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

查看MySQL的时区设置:
   

图1

然后写入一条数据:

图2

第一次备份,用本地时间条件去过滤:

mysqldump -w “dt >= ‘2016-12-21 14:00:00′”

这种情况下,显然是没有结果的。

图3

第二次备份,用本地时间减去8小时再去过滤:

mysqldump -w “dt >= ‘2016-12-21 06:00:00′”

这种就可以备份出数据了。

图4

此外,我们注意到mysqldump的 –tz-utc 选项,它是和时区设定有关系的:

–tz-utc

SET TIME_ZONE=’+00:00′ at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones.

(Defaults to on; use –skip-tz-utc to disable.)

这个选项的作用,就是以 0时区  备份数据,把所有时间都转换成 0时区 的数据。比如本来是在 东8区(+08:00) 的时间 14:00:00,转换成 0时区 后,会变成 06:00:00,原来是 西8区(-08:00) 的时间14:00:00,则转换成 22:00:00。这个选项是默认启用的。

在上面第一次备份时没有数据,就是因为MySQL里本身存储的就是 0时区 的数据,mysqldump也设定了转换成 0时区,我们传递进去的参数却是 东8区 的时间,因此没有数据。

3、总结

本来只想简单写一下的,结果啰里啰嗦写了好多。

其实我们只需要注意一点,服务器在哪里,就是用哪里的时区,也就是 SYSTEM 时区,在做SQL查询以及mysqldump备份数据时,也使用服务器上的时间,而不使用我们本地时间。

图5

新闻 | MariaDB Columnstore 1.0.6 GA发布

0、导读

在昨天(2016.12.14),MariaDB正式发布了MariaDB Columnstore 1.0版本

1、关于MariaDB Columnstore

MariaDB Columnstore的前身其实是InfiniDB,大家可能还记得在2014年9月份时,InfiniDB公司宣布关门后,MariaDB接手了这个项目。

在10月15日的杭州阿里云栖大会上,我有幸和MariaDB Columnstore的主要开发者Daniel Lee同台做分享。Daniel是位华人,中文说的很流利,此前他在InfiniDB公司,随着InfiniDB被MariaDB接管而进入MariaDB继续工作,继续负责InfiniDB的开发,只不过项目被命名为MariaDB Columnstore。

2、MariaDB Columnstore 1.0.6 GA发布

在10月26日,MariaDB Columnstore刚刚发布了1.0.5 beta版本。在昨天,MariaDB Columnstore发布了1.0.6 GA版本,这个动作不可谓不快,可见MariaDB对这块市场需求的重视。

就我个人而言,以往InfiniDB的经验来看其实它还是比较“难用”的,哈哈。希望在MariaDB的生态理念下这方面能有所改进。

我是挺想对MariaDB Columnstore做个测试的,可惜一直没有比较完整的时间来做这事,有兴趣的同学可以先尝鲜试试哈。

Columnstore的官方资料可见:

https://mariadb.com/kb/en/mariadb/mariadb-columnstore/

3、其他媒体关注报道

In addition, early ColumnStore user Andrew Ernst from IHME penned this blog: 
IHME Believes Open Source MariaDB ColumnStore Is The Future of Data Warehousing

The news cycle began in Europe and is working its way to the U.S. – we expect more coverage.

US
UK
European/Non-English:

 

找到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过后的二进制包吧,下载起来更快一些~~~

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