月度归档:2015年11月

MySQLer马拉松跑团招募成员啦

说正事前,先容我先扯扯。

关于晨跑

大概2个月前,我调整了下跑步时间。以前是下班后约上同事去公司附近的公园跑步,之后再回去吃饭。现在则改成了早晨6点起来,独自一人在附近的公园跑步。相对于前者,后者的好处多多:

  1. 通过早起反过来督促早睡,搞IT的都习惯晚睡,这点非常不好。晨跑结束后,洗个热水澡,一整天精神都会很好;
  2. 跑步时间更充裕,6点起来,9点半上班,中间有大段的时间,现在我一般都持续跑10公里约1个小时多一点;
  3. 由于晨跑的时间比较充裕,跑步过程中可以安心思考一些事情,不像夜跑,可能要非常专注看路什么的,今天的这篇短文就是晨跑中构思起来的;

关于晨跑的建议:一开始可能无法早起,可以先定个闹钟,强迫自己坚持早起1周,1周后估计就基本上能适应了,然后就可以开始愉快健康的晨跑之旅了。

关于跑步烧钱这事

在以前,单纯的以为跑步很省钱,没想到越来越烧钱。。。
从2012年开始跑到现在,大概败了这些东西:

  1. 买了3双鞋,约1K
  2. 运动手表,约1.7k
  3. 小腿套、髌骨贴、腰包、紧身衣,约0.5k
  4. 数次外出参赛,合计约3k

加起来也不是小数目了,看来以后少外出参赛,自己平时多跑跑就好了,哈哈。

重要的事:MySQLer马拉松跑团招募

想要搞这个跑团的出发点是鼓励各位MySQLer多运动、多健身、防猝死,同时也和CMUG组织结合起来,希望以后有机会拉到商业赞助,为跑团成员外出参赛提供一定额度的赞助,嘿嘿。

怎么参加MySQLer马拉松跑团

当然了,我们也欢迎各位运维圈同行都来加入,不光是MySQLer。
可以加入QQ群 112718255,或者扫描下面的二维码加入

或者,也可以参加我们的悦跑圈跑团“17173约跑团”,ID号是:15377(一个账号只能加入一个跑团),加入请备注姓名、所在城市或所在公司。我们跑团本周已经跃居福建本省排名第4(本月本省排名12),还在持续上升中,嘿!

关于其他

  1. 趁年轻多运动,不管是跑步、游泳还是其他,省得老了以后给下一代添麻烦;
  2. 并且培养一个业余爱好,不管是运动,还是摄影、书画、下棋等等,以后退休了可以有事做,不至于一下子闲下来不适应。不过,搞IT的,除了财富自由了提前退休的,有几个人能捱到自然退休年龄呢,哈哈;
  3. 每个年龄段都有一些该做的事,比如中学时好好读书,大学时除了好好读书外,还应该多参加社会实践,更应该好好谈场纯粹的恋爱,30岁左右结婚生娃(现在该再加上一条,35岁左右生二娃,哈哈哈),做个普通人,挺好的,嗯。

更多阅读:我为什么要参加马拉松

利用event为zabbix数据表定期添加和删除分区

导读

利用MySQL的event来自动维护表分区。

我们去年就开始把zabbix数据库改成用TokuDB来支撑,并且启用了表分区(详情见:迁移Zabbix数据库到TokuDB)。这样做的好处很明显,较早的历史数据可以通过删除分区快速废弃掉。要知道,zabbix数据表默认是没有针对时间字段创建索引的,因此如果执行删除的SQL命令,其效率会很差,而直接删除分区就快多了。

先看history表的分区规则:

CREATE TABLE history (
 itemid bigint(20) unsigned NOT NULL,
 clock int(11) NOT NULL DEFAULT '0',
 value double(16,4) NOT NULL DEFAULT '0.0000',
 ns int(11) NOT NULL DEFAULT '0',
 KEY history_1 (itemid,clock)
 ) ENGINE=TokuDB DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_QUICKLZ
 PARTITION BY RANGE (clock)
 (PARTITION p20150531 VALUES LESS THAN (1433088000) ENGINE = TokuDB,
 ...
 PARTITION p20160411 VALUES LESS THAN (1460390400) ENGINE = TokuDB);

对这个表,我们每天要的是:创建一个新的分区,而后删除N个月前的历史旧分区。这个工作可以通过系统的cron来实施,也可以利用MySQL自身的event来做,在这里我们选择用event,没什么特殊的原因,只是想顺便尝试下event而已,呵呵。

一个定期调度的event写起来并不难,下面是参考样例,相信很快就能看明白:

delimiter $$$
 drop event if exists zabbix_alter_partition_daily;
 CREATE EVENT zabbix_alter_partition_daily
 ON SCHEDULE EVERY 1 DAY -- 每天执行
 DO
 begin

 -- 记日志
 insert into zlogs select 0, now(), date_format(date_sub(now(), INTERVAL 180 DAY),
 " ALTER TABLE history DROP PARTITION p%Y%m%d");

 -- 删除history表180天前的旧分区
 -- 用PREPARE & EXECUTE 准备和执行删除的SQL
 SET @drop_p_stmt = date_format(date_sub(now(), INTERVAL 180 DAY)," ALTER TABLE history DROP PARTITION p%Y%m%d");
 PREPARE drop_p_stmt FROM @drop_p_stmt;
 EXECUTE drop_p_stmt;

 -- 创建history表30天后的新分区
 insert into zlogs select 0, now(), concat(
 date_format(date_add(now(), INTERVAL 180 DAY)," ALTER TABLE history ADD PARTITION ( PARTITION p%Y%m%d VALUES LESS THAN "),
 "(", 
 unix_timestamp( date_add(date_format(now(), "%Y%m%d"), INTERVAL 31 DAY) ),
 "))");

 SET @add_p_stmt = concat(
 date_format(date_add(now(), INTERVAL 30 DAY)," ALTER TABLE history ADD PARTITION ( PARTITION p%Y%m%d VALUES LESS THAN "),
 "(",
 unix_timestamp( date_add(date_format(now(), "%Y%m%d"), INTERVAL 31 DAY) ),
 "))");

 PREPARE add_p_stmt FROM @add_p_stmt;
 EXECUTE add_p_stmt;

 end $$$
 delimiter ;

FAQ系列 | 如何保证主从复制数据一致性

导读

MySQL主从复制环境中,如何才能保证主从数据的一致性呢?

关于主从复制

现在常用的MySQL高可用方案,十有八九是基于 MySQL的主从复制(replication)来设计的,包括常规的一主一从、双主模式,或者半同步复制(semi-sync replication)。

我们常常把MySQL replication说成是MySQL同步(sync),但事实上这个过程是异步(async)的。大概过程是这样的:

  1. 在master上提交事务后,并且写入binlog,返回事务成功标记;
  2. 将binlog发送到slave,转储成relay log;
  3. 在slave上再将relay log读取出来应用。

步骤1和步骤3之间是异步进行的,无需等待确认各自的状态,所以说MySQL replication是异步的。

MySQL semi-sync replication在之前的基础上做了加强完善,整个流程变成了下面这样:

  1. 首先,master和至少一个slave都要启用semi-sync replication模式;
  2. 某个slave连接到master时,会主动告知当前自己是否处于semi-sync模式;
  3. 在master上提交事务后,写入binlog后,还需要通知至少一个slave收到该事务,等待写入relay log并成功刷新到磁盘后,向master发送“slave节点已完成该事务”确认通知;
  4. master收到上述通知后,才可以真正完成该事务提交,返回事务成功标记;
  5. 在上述步骤中,当slave向master发送通知时间超过rpl_semi_sync_master_timeout设定值时,主从关系会从semi-sync模式自动调整成为传统的异步复制模式。

半同步复制看起来很美好有木有,但如果网络质量不高,是不是出现抖动,触发上述第5条的情况,会从半同步复制降级为普通复制;此外,采用半同步复制,会导致master上的tps性能下降非常严重,最严重的情况下可能会损失50%以上。

这样来看,除非需要非常严格保证数据一致性等迫不得已的场景,就不太建议使用半同步复制了。当然了,事实上我们也可以通过加强程序端的逻辑控制,来避免主从数据不一致时发生逻辑错误,比如说如果在从上读取到的数据和主不一致的话,那么就触发主从间的一次数据修复工作。或者,我们也可以用 pt-table-checksum & pt-table-sync 两个工具来校验并修复数据,只要运行频率适当,是可行的。

真想要提高多节点间的数据一致性,可以考虑采用PXC方案。现在已知用PXC规模较大的有qunar、sohu,如果团队里初期没有人能比较专注PXC的话,还是要谨慎些,毕竟和传统的主从复制差异很大,出现问题时需要花费更多精力去排查解决。

如何保证主从复制数据一致性

上面说完了异步复制、半同步复制、PXC,我们回到主题:在常规的主从复制场景里,如何能保证主从数据的一致性,不要出现数据丢失等问题呢?

在MySQL中,一次事务提交后,需要写undo、写redo、写binlog,写数据文件等等。在这个过程中,可能在某个步骤发生crash,就有可能导致主从数据的不一致。为了避免这种情况,我们需要调整主从上面相关选项配置,确保即便发生crash了,也不能发生主从复制的数据丢失。

1. 在master上修改配置

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

上述两个选项的作用是:保证每次事务提交后,都能实时刷新到磁盘中,尤其是确保每次事务对应的binlog都能及时刷新到磁盘中,只要有了binlog,InnoDB就有办法做数据恢复,不至于导致主从复制的数据丢失。

2. 在slave上修改配置

master_info_repository = "TABLE"
relay_log_info_repository = "TABLE"
relay_log_recovery = 1

上述前两个选项的作用是:确保在slave上和复制相关的元数据表也采用InnoDB引擎,受到InnoDB事务安全的保护,而后一个选项的作用是开启relay log自动修复机制,发生crash时,会自动判断哪些relay log需要重新从master上抓取回来再次应用,以此避免部分数据丢失的可能性。

通过上面几个选项的调整,就可以确保主从复制数据不会发生丢失了。但是,这并不能保证主从数据的绝对一致性,因为,有可能设置了ignore\do\rewrite等replication规则,或者某些SQL本身存在不确定因素,或者人为在slave上修改数据,最终导致主从数据不一致。这种情况下,可以采用pt-table-checksumpt-table-sync 工具来进行数据的校验和修复。

FAQ系列 | 提问的正确姿势

导读

怎么向叶老湿提问才能得到回复?

你造吗?

我的QQ好友已超过2000人,其中至少有一半是曾经有过技术交流,或者找我帮助解决MySQL相关问题的。

这样大致平均算下来,每天约要帮忙解答3-5个问题(每个问题至少交互3-5次)。

这对我来说,已然成了不小的额外“工作量”。因此,并不是所有的问题我都能回复,有些是能力不够,有些则是没兴趣回答,大概只有不到1/3的问题我会正面回复。

先说下哪些是我没能力回答的吧,大概有下面这样的:

  1. 想和我交流MySQL源码的,不好意思,我就一运维DBA,源码方面我并不擅长,倒是可以找阿里云RDS团队或其他同行聊聊;
  2. 直接问我“这个SQL执行很慢,怎么优化呢”,也不好意思,只有这种一句话的问题描述,我确实没能力回答。真想获得帮助的话,可以参考下方的“提问的正确姿势”;
  3. “你好,我想实现xxx功能,请问SQL该怎么写呢”,更不好意思了,写SQL还真不是我的强项,非要勉为其难让我写的话,麻烦先给我发个红包,谢谢。

有哪些是我没兴趣回答的呢?

  1. 发来一条消息,问“在吗”。这是被我一直诟病的最糟糕的打招呼方式,平白无故浪费别人时间。心情好的时候,我或许会回一个“你猜” 或 “不在”,没心情时则直接关闭对话框;
  2. 跟我说“老叶,能帮忙推荐一个牛一点的DBA吗,谢谢”。嗯,当然可以推荐,但麻烦您把大概要求说一下总可以吧,如果不是熟人,我的做法也是直接关闭对话框;
  3. “请问MySQL里xxx命令是什么用途呢”,抱歉,我不是人肉查询机,麻烦自己看手册。

那么,提问的正确姿势是什么呢?
先举几个例子吧:
Q: 这个SQL为什么很慢?
A: 这是个什么SQL呢?
Q: 这是zabbix后台的一个SQL。
……尼妹,贴一下原始SQL、表DDL、执行计划能死啊?

还是以这个SQL效率问题为例,我期望得到的回复是这样的:

  1. 相关表DDL长什么样,执行 SHOW CREATE TABLE 即可查看;
  2. 这个SQL的执行计划是怎样的,用 EXPLAIN 解析下就行了(包含完整的SQL);
  3. 必要的话,还可以提供SQL执行时的PROFILE结果;

另一种情况,如果是性能上存在问题的话,建议提供下面几个信息:

  1. (负载较高时)在服务器上执行top后的截屏;
  2. (负载较高时)运行vmstat 1 50后的截屏;
  3. (负载较高时)提供PROCESSLIST根据耗时排序后的截屏;
  4. 提供服务器硬件配置信息,MySQL配置文件;
    上述这些信息可以打成一个压缩包发出来。

当有了上面这3个信息,相信技术群里有很多人都可以帮到你了。

最后,还有一个绝招,那就是加入老叶的密圈成为付费粉丝,在这里,我会竭尽所能回答你的问题。

FAQ系列 | MySQL索引之主键索引

导读

在MySQL里,主键索引和辅助索引分别是什么意思,有什么区别?

上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别。

1、主键索引

主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。

在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则:

  1. 采用一个没有业务用途的自增属性列作为主键;
  2. 主键字段值总是不更新,只有新增或者删除两种操作;
  3. 不选择会动态更新的类型,比如当前时间戳等。

这么做的好处有几点:

  1. 新增数据时,由于主键值是顺序增长的,innodb page发生分裂的概率降低了;可以参考以往的分享“[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键”;
  2. 业务数据有变更时,不修改主键值,物理存储位置发生变化的概率降低了,innodb page中产生碎片的概率也降低了。

MyISAM表因为是堆组织表,主键类型设计方面就可以不用这么讲究了。

2、辅助索引

辅助索引,就是我们常规所指的索引,原文是SECONDARY KEY。辅助索引里还可以再分为唯一索引非唯一索引

唯一索引其实应该叫做唯一性约束,它的作用是避免一列或多列值存在重复,是一种约束性索引。

3、主键索引和辅助索引的区别

在MyISAM引擎中,唯一索引除了key值允许存在NULL外,其余的和主键索引没有本质性区别。也就是说,在MyISAM引擎中,不允许存在NULL值的唯一索引,本质上和主键索引是一回事

而在InnoDB引擎中,主键索引和辅助索引的区别就很大了。主键索引会被选中作为聚集索引,而唯一索引和普通辅助索引间除了唯一性约束外,在存储上没本质区别

从查询性能上来说,在MyISAM表中主键索引和不允许有NULL的唯一索引的查询性能是相当的在InnoDB表通过唯一索引查询则需要多一次从辅助索引到主键索引的转换过程InnoDB表基于普通索引的查找代价更高,因为每次检索到结果后,还需要至少再多检索一次才能确认是否还有更多符合条件的结果,主键索引和唯一索引就不需要这么做了。

经过测试,对100万行数据的MyISAM做随机检索(整数类型),主键和唯一索引的效率基本一样,普通索引的检索效率则慢了30%以上。换成InnoDB表的话,唯一索引比主键索引效率约慢9%,普通索引比主键索引约慢了50%以上。

 

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

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

 

我为什么要参加马拉松

为什么要跑步

搞IT的人苦逼,IT中搞运维的人更苦逼,长年累月7*24精神高度紧张,相信有很多同行也多多少少存在神经衰弱的问题吧。

一般来说,通过运动可以有效地缓解神经衰弱的毛病,而跑步或游泳的效果最佳,所以我就以这两项运动为主。

建议: 如果神经衰弱比较严重的话,最好是去就医,听听专业的医生怎么说。

另外,前几年有数次IT同行猝死的报道,这个太吓人了。尤其是有了小孩后,就更加注意要锻炼了,这也是对家人负责的一种做法。

我的跑步经历

就我个人而言,游泳和跑步都尝试过,跑步坚持的最久,却已经有很久没游泳了(嫌太麻烦了,虽然泳池里可以看到很多让人想入非非的身材,哈哈)。

我在北京期间的跑步次数不多,里程也不长。是2012年来到福州后,跑步的频率才高起来的,这种状态一直持续到现在。跑步的运动量,也从最初的1.5公里,提升到3公里,再到5公里,到10公里,直到目前的半程21公里。很多人不知道,我以前可是连1公里都跑不完的,读书时有次考1公里长跑,我连着练习了1星期才勉强考试通过。

我的马拉松历程

大概是2014年的10月份,在一位热衷参加马拉松的同事带动下,我报名参加了武夷山马拉松半程赛。第一次参加比赛状态比较兴奋,不过也没什么经验,不怎么懂得调整,最后的成绩马马虎虎吧。

此后参加厦门2015年马拉松。那次非常惨,赛前3天我意外被被传染病毒,先是高烧,而后上吐下泻,跑步当天也还在拉肚子。而且那次参赛的人非常多,赛道很窄,挤得人山人海的,我们从发令枪响到通过起点计时器,已经过去了30多分钟,一路上也完全跑不开,需要来回躲闪各种人。跑完后人差点虚脱,脚踝也由此受伤,大概过了将近半年才基本恢复。

2015年里,我又陆续参加了厦门15公里越野赛、福州马尾半程马拉松、三明泰宁半程马拉松,以及上周末的武夷山半程马拉松。相比一年前的成绩,大概只提高了5分钟(还有待进一步提高)。

我的马拉松收获

参加马拉松跑步,给我的收获有这么几点:

  1. 首先是身体上的变化,提高了抵抗力、免疫力,不像以前那么容易生病了,冬天也不会那么怕冷,整个人的精神状态也好了很多(简单说,就是变帅了,哈哈);
  2. 锻炼了意志力和耐力,遇到一些困难,也能比较从容面对;
  3. 提升自信心,想想从1公里都跑不完的状态提升到能跑完半马,还是给了自己极大的激励,比其他什么激励都来得管用;
  4. 提升整体生活质量,尤其是睡眠质量,神经衰弱早就拜拜了;

我对也想跑步的同学几点建议

下面几点仅个人看法哈,未必专业:

  1. 找个伙伴,相互督促,只有一个人跑很容易松懈下来;
  2. 选择早起跑步,相对于晚上跑步,时间更从容,也正好以此迫使自己早睡早起;
  3. 换一双合脚的跑鞋,不用特别好,跑步时穿着不难受,不伤膝盖就好;
  4. 配备带有心率监测功能的运动手表等设备,可以记录日常跑步数据,有助于提升跑步成绩以及更科学跑步,避免对身体造成伤害(心率突然太高或太低都容易猝死);
  5. 尽量每天都跑,每次跑量不求多(跑量可以逐渐增加),而是依据身体最舒服的状态来跑,别强撸;
  6. 适当参加一些比赛,不求名次、重在参与,利用参赛机会顺便小小旅游下,也是极好的。

最后祝愿大家都有个好身体,好心情,好工作 :)

FAQ系列 | EXPLAIN执行计划中要重点关注哪些要素

导读

EXPLAIN的结果中,有哪些关键信息值得注意呢?

MySQL的EXPLAIN当然和ORACLE的没法比,不过我们从它输出的结果中,也可以得到很多有用的信息。

总的来说,我们只需要关注结果中的几列:

列名 备注
type 本次查询表联接类型,从这里可以看到本次查询大概的效率
key 最终选择的索引,如果没有索引的话,本次查询效率通常很差
key_len 本次查询用于结果过滤的索引实际长度,参见另一篇分享(FAQ系列-解读EXPLAIN执行计划中的key_len
rows 预计需要扫描的记录数,预计需要扫描的记录数越小越好
Extra 额外附加信息,主要确认是否出现 Using filesort、Using temporary 这两种情况

首先看下 type 有几种结果,分别表示什么意思:

类型 备注
ALL 执行full table scan,这事最差的一种方式
index 执行full index scan,并且可以通过索引完成结果扫描并且直接从索引中取的想要的结果数据,也就是可以避免回表,比ALL略好,因为索引文件通常比全部数据要来的小
range 利用索引进行范围查询,比index略好
index_subquery 子查询中可以用到索引
unique_subquery 子查询中可以用到唯一索引,效率比 index_subquery 更高些
index_merge 可以利用index merge特性用到多个索引,提高查询效率
ref_or_null 表连接类型是ref,但进行扫描的索引列中可能包含NULL值
fulltext 全文检索
ref 基于索引的等值查询,或者表间等值连接
eq_ref 表连接时基于主键或非NULL的唯一索引完成扫描,比ref略好
const 基于主键或唯一索引唯一值查询,最多返回一条结果,比eq_ref略好
system 查询对象表只有一行数据,这是最好的情况

上面几种情况,从上到下一次是最差到最好

再来看下Extra列中需要注意出现的几种情况:

关键字 备注
Using filesort 将用外部排序而不是按照索引顺序排列结果,数据较少时从内存排序,否则需要在磁盘完成排序,代价非常高,需要添加合适的索引
Using temporary 需要创建一个临时表来存储结果,这通常发生在对没有索引的列进行GROUP BY时,或者ORDER BY里的列不都在索引里,需要添加合适的索引
Using index 表示MySQL使用覆盖索引避免全表扫描,不需要再到表中进行二次查找数据,这是比较好的结果之一。注意不要和type中的index类型混淆
Using where 通常是进行了全表/全索引扫描后再用WHERE子句完成结果过滤,需要添加合适的索引
Impossible WHERE 对Where子句判断的结果总是false而不能选择任何数据,例如where 1=0,无需过多关注
Select tables optimized away 使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询,例如MIN()\MAX(),这种也是比较好的结果之一

再说下,5.6开始支持optimizer trace功能,看样子在执行计划方面是要逐渐和ORACLE看齐 :)

 

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

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠