作者归档:叶金荣

关于叶金荣

ORACLE ACE(MySQL),国内最早的MySQL推广者,资深MySQL专家,10余年MySQL经验,擅长MySQL性能优化、架构设计、故障排查

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

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 直达

优化系列 | DELETE子查询改写优化

0、导读

有个采用子查询的DELETE执行得非常慢,改写成SELECT后执行却很快,最后把这个子查询DELETE改写成JOIN优化过程

1、问题描述

朋友遇到一个怪事,一个用子查询的DELETE,执行效率非常低。把DELETE改成SELECT后执行起来却很快,百思不得其解。

下面就是这个用了子查询的DELETE了:

[yejr@imysql.com]mydb > EXPLAIN delete from trade_info where id in (

select id from (

select a.id from trade_info a, order_info b, user c where

b.buyer = c.id and c.itv_account=’90000248′ and a.order_id = b.id) temp)\G

delete1

几个表的DDL是这样的:

delete2

上面这个SQL的执行耗时是:31.74秒

Query OK, 5 rows affected (31.74 sec)

如果我们把DELETE改写成SELECT的话,执行耗时仅是:0秒,来对比看下执行计划:

[yejr@imysql.com]mydb >EXPLAIN select id from trade_info where

id in (

select id from (

select a.id from trade_info a, order_info b, user c where

b.buyer = c.id and c.itv_account=’90000248′ and a.order_id = b.id) temp)\G

delete3

可以看到,trade_info 表从的全表扫描(type=ALL)变成了基于主键的等值查询(type=eq_ref),计划扫描数据量也从571万变成了1条,而且还可以避免回表,这2个SQL对比代价相差巨大。

2、优化思路

既然这个SQL把DELETE改成SELECT后执行效率就可以获得很大提升,除此外没特别区别,可能是查询优化器方面有些不足,导致无法直接优化,就得另想办法了。

我们的思路是把基于子查询的DELETE简化改写成多表JOIN后DELETE(一般来说,子查询效率比较低的话,可以考虑改写成JOIN),多表DELETE的语法课参考:https://dev.mysql.com/doc/refman/5.7/en/delete.html#idm140469624466800,例如这样的:

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

参照上面的形式,改写之后的SQL变成了下面这样:

DELETE trade_info

FROM

trade_info,

(

SELECT

a.id

FROM

trade_info a

JOIN order_info b ON a.order_id = b.id

JOIN user c ON b.buyer = c.id

WHERE

c.itv_account = ‘90000248’

) t2 where trade_info.id = t2.id;

delete4

可以看到新的SQL执行效率相对就高很多了,不需要再扫描571万条记录,执行耗时只需:0.01秒。

Query OK, 5 rows affected (0.01 sec)

3、其他建议

虽然MySQL 5.6及以上的版本对子查询做了优化,但从本案例的结果来看,在一些情况下还是不如意。

因此,如果发现有些子查询SQL效率比较差的话,可以尝试改写成JOIN形式,看看是否有所提升。此外,也要勇于怀疑查询优化器个别情况下存在不足,想办法绕过这些坑。

 

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

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

FAQ系列 | 从MySQL 5.6到5.7复制错误解决

0、导读

在MySQL 5.7下采用多源复制方式,从5.6复制数据过来,会有问题吗?

1、问题描述

Q群里有位朋友想尝鲜5.7的多源复制,于是用MySQL 5.7版本作为slave,把MySQL 5.6作为master,想要将数据进行汇总,发现此路不通。

他在my.cnf中设置了2个选项,开启并发复制:

slave_parallel_type    = LOGICAL_CLOCK

slave_parallel_workers = 4

启动复制线程后,结果在错误日志中不断有类似下面的信息:

Transaction is tagged with inconsistent logical timestamps: sequence_number (823267087) <= last_committed (1301275374434324336)

执行 SHOW SLAVE STATUS\G 查看状态:

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Last_Errno: 1756

Last_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).

Last_SQL_Errno: 1756

Last_SQL_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 160523 18:49:05

*************************** 2. row ***************************

Slave_IO_State: Waiting for master to send event

Last_Errno: 1756

Last_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).

Skip_Counter: 0

Last_SQL_Errno: 1756

Last_SQL_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).

2、原因分析

上面这些错误提示可以看到,主要原因是:slave端采用了基于 LOGICAL_CLOCK 类型的并行复制,但master端的binlog格式并不支持这种方式,所以slave端无法正确读取binlog并行apply。

3、解决方案

虽然仍旧可以采用MySQL 5.7作为slave,但就无法开启基于 LOGICAL_CLOCK 类型的并行复制了。需要改回传统模式就可以了:

slave_parallel_type    = DATABASE

此外,在MySQL复制方案中,强烈建议不要让主从大版本不一样,很容易出现各种各样的问题。

 

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

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

在线分享《如何针对业务做DB优化》(附PPT、录音、视频地址)

知数堂资深MySQL讲师,前新媒传信首席DBA、MySQL中国用户组(ACMUG)主席吴炳锡老师在5月19日晚上20:30做了在线分享《如何针对业务做DB优化》,受到了很多朋友的肯定和支持。也要再次感谢KVM虚拟化实践社区&Ceph中国社区的鼎力支持。

1、分享资料下载

百度云盘链接:http://pan.baidu.com/s/1mhSgnlm 密码:2ycr  ,欢迎转存并再次分享。

如何针对业务做DB优化.001 如何针对业务做DB优化.002 如何针对业务做DB优化.0032、本次分享PPT

这次的PPT我也在slideshare上放了一份。