月度归档:2017年03月

从MySQL开发规范处看创业

作者:唐勇,深圳市环球易购,MySQL DBA。个人爱好:看书、跑步、看电影、旅行、倒腾文字、NBA

导读

MySQL是时下热度仅次于Oracle的关系型数据库,因为便捷高效的特点风靡整个DB行业。而创业呢,政策层面的“双创”让多少热血青年跳进了创业这个”火坑“,从去年的内容创业到共享经济,从罗辑思维、吴晓波频道到摩拜单车、ofo。只要提及创业,必讲互联网,必讲商业模式、大数据、DAU、变现、人工智能、认知、消费升级、中产崛起。但是这些概念和认知与MySQL的开发规范又有什么关系呢,还真真有关系,并且能联系起来,你不信啊,你自己往下看咯。

首先第一条,表的存储引擎必须选择InnoDB(MyISAM存储引擎已经被时代淘汰了,既然InnoDB能满足99%以上的业务场景,你还有什么理由去返古)

创业观点解读:创业必须紧跟时代潮流,违背或者对抗时代潮流,是很难进行下去的

第二条,每一个Innodb表都必须要有主键

创业观点解读:每一个创业组织,都必须要有自己的核心竞争力,跟风是活不长久的,因为你没办法在客户的心智中占据一个位置

第三条,为了通用,表的字符集都选择utf-8

创业观点解读:在创业的过程中,不要试图自己去创造一些效率工具,就用别人用过的现成工具,因为你的重心在于实用性,而不是创造性

第四条,根据业务设计索引,单表的索引个数最好不要超过5个

创业观点解读:别人在做的事情,未必就是你以后要做的事情。创业应该是基于一个痛点,一个问题,你只有提供产品为用户解决了一个问题,才会有目标消费群体

第五条,尽量使用复合索引,而不是添加新的索引

创业观点解读:创业过程中尽量不要使用”空降兵“,从创业团队中内部挖掘,提升他们的才智和领导力。

第六条,不要在索引列上使用数学运算和函数运算

创业观点解读:创业的目的应该是很简单的,或者为了一个梦想,或者为了解决一个问题,别把实现财务自由、走上人生巅峰、迎娶白富美这些YY的观点加进去,这样的话,你都没法一心一意去创业

第七条,禁止使用select *,要查询数据后面必须紧跟字段值

创业观点解读:创业头脑风暴的时候,不要想着去满足所有人的需求,去解决所有问题,聚集于一个垂直领域并做到行业领先就够了

第八条,注意组合索引的顺序,以便利用索引的最左原则

创业观点解读:创业时,要紧跟着政策走,在中国这个特色社会,低着头创业,会死得很难看

第九条,一张表的字段个数最好不要超过50个

创业观点解读:在创业初具规模时,先不要想着多元化发展,要先扎实做好自己的老本行,多元化发展那是成为行业领导者之后才能想的事情

第十条,禁止在主库上执行sum,count等复杂的统计分析语句(既然做了从库,为什么还要到主库去查呢)

创业观点解读:能自己活下去的,就不要随便去融资,融资不只是股权上的再分配,也有可能是公司控制权的倾斜

后记

唐勇同学的创业心得和MySQL开发规范结合的非常好,我想这些创业观点对大多数的创业者也是很有帮助的。

备注

1、唐勇同学是知数堂的早期学员;

在MySQL 5.7下排查内存泄露和OOM问题全过程

0、导读

我的个人网站后台使用的是MySQL 5.7版本,前段时间经常被oom-kill,借助5.7的新特性,经过一番排查,终于抓到这只鬼。

1、问题现象

我的网站前段时间经常时不时就抽风一下,提示数据库无法连接,提示:

建立数据库连接时出错

本想反正是个人网站,挂就挂了,无所谓啦;也可能是VPS配置太低,访问量一大就容易出问题,忍忍算啦。

后来启荣大师说了一句话:看那木匠做的烂门 😓(⊙﹏⊙)b

于是下决心解决问题,不能再被鄙视啦,作为一个DBA,不能容忍数据库无缘无故挂掉,哪怕是个人VPS也不行 O(∩_∩)O~

2、问题排查

首先,加了个cron任务,每分钟自动检测mysqld进程是否存活,如果没有,就启动之。这也是我们在企业里解决问题的指导思想:尽快找到问题,但在还没确认问题之前,优先保证服务可用性,不管你用啥方法。

接下来,再看MySQL自身的日志,看看能不能找到什么线索。然并卵,啥也没找到,应该是被异常kill的,所以只有mysqld启动过程记录,并没有异常关闭的过程记录。

再排查系统日志,终于看到mysqld进程因为OOM给kill了:图1

可以看到,mysqld进程消耗了最多内存,被oom-killer选中,给干掉了。

既然是OOM,那我们再看下当时系统整体内存消耗情况:图2

是不是有明显的内存泄露迹象?不清楚的同学可以先看下面这篇文章普及下:

好了,现在我们已经基本明确mysqld进程是因为内存泄露,导致消耗大量内存,最终被oom-kill了。

那么,我们如何知道mysqld进程究竟什么原因消耗掉内存的,都用哪里去了呢?还好,MySQL 5.7的P_S(performance_schema的简称)集成了这样的功能,能帮助我们很方便的了解这些信息。因此我们执行下面的SQL,就能找到MySQL里到底谁消耗了更多内存:

yejr@imysql> select event_name,SUM_NUMBER_OF_BYTES_ALLOC  from

memory_summary_global_by_event_name

order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;

图3

我们注意到 “memory/innodb/mem0mem” 消耗的内存最大。

再执行下面的SQL,查看都有哪些内部线程消耗了更多内存:

yejr@imysql>select event_name, SUM_NUMBER_OF_BYTES_ALLOC from

memory_summary_by_thread_by_event_name

order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 20;

图4

我们注意到,上面的结果中,有很多非MySQL内部后台线程(thread_id 值比较大)用到了 “memory/innodb/mem0mem”,并且内存消耗也比较大。看到这种现象,我的第六感告诉我,应该是并发线程太高或者线程分配不合理所致。因为前端是PHP短连接,不是长连接,不应该有这么多thread,推测是多线程连接或线程池引起的。

于是排查了一圈和线程、连接数相关的参数选项及状态,基本确认应该是开了线程池(thread pool),导致了内存泄露,持续消耗内存,最终mysqld进程消耗过多内存,被系统给oom-kill了。

下面是我的thread pool相关设置:

图5

当我们把线程池功能关闭后,内存泄露的问题也随之消失,mysqld进程再也没有被oom-kill了,看起来确实是解决的问题。

经过几次反复测试,最终观察到以下结论:

  • 同时开着P_S和thread pool会导致发生内存泄露
  • 同时开着P_S和thread pool,不过采用”one-thread-per-connection”模式而非”pool-of-threads”模式(只用到extra port功能),不会发生内存泄露;
  • 只开着P_S,不开thread pool,不会发生内存泄露;
  • 不开P_S,只开thread pool,也不会发生内存泄露;
  • 同时关闭P_S和thread pool,也不会发生内存泄露;

交代下,我的MySQL版本是:

5.7.17-11-log Percona Server (GPL), Release 11, Revision f60191c

更早之前用官方的5.7.13版本也是有问题的。

3、结论及建议

在前端应用经常有突发短连接或相似场景中,开启线程池对缓解用户连接请求排队有很大帮助,可以避免MySQL连接瞬间被打满、打爆的问题。但线程池也并非适用于全部场景,像本案例遇到的问题就不适合了(我当初开thread pool更多是为了extra port功能)。

上面我们提到,从MySQL 5.7版本开始,新增了很多有用的视图,可以帮助我们进一步了解MySQL内部发生的一些事情。在P_S中新增了下面这几个内存相关的视图:

  • memory_summary_by_account_by_event_name
  • memory_summary_by_host_by_event_name
  • memory_summary_by_thread_by_event_name
  • memory_summary_by_user_by_event_name
  • memory_summary_global_by_event_name

看视图的名字,就知道这可以帮助我们分别从账号(包含授权主机信息)、主机线程用户(不包含授权主机信息)、整体全局等多个角度查看内存消耗统计。

除了内存统计,还有和事务、复制相关的一些视图,并且原来有些视图也进一步增强。

在MySQL 5.7中,还集成了sys schema,关于sys schema大家可以看本文下方的推荐链接。sys schema主要是对Information_schema以及Performance_Schema的视图进一步增强处理,提高结果的可读性。比如,我们可以查看当前实例总消耗的内存,以及内存主要由哪些部分给占用了,也可以透过sys schema来查看:图6

建议大家应该花些时间好好再深入理解下I_S、P_S、sys schema,对我们这些非底层代码的MySQL DBA而言,这些都是很好的辅助手段。

延伸阅读

重装上阵 | 最方便可靠的MySQL my.cnf生成工具

关注我网站(http://imysql.com)的亲们应该都知道,有个my.cnf配置文件生成器功能,2008年8月20日开始上线提供服务至今,历经了5.1到5.7多个版本。

当初上线时,本着简单了事、能用就行的原则,只提供了基本的功能,但界面那个丑啊,我有无数次想改版,但也一直懒得动工。老的界面是像下面这样的 … (ノへ ̄、)捂脸

今天上班时,我终于再也忍不住了,于是找到了一个简单大方的表单模板直接照搬过来用,感谢下面这位同学的无私分享。

同时顺便把一些功能逻辑也重新整理了下,简单重操了许久没动手的PHP,还好基本上还能玩得转,嘿。

改版之后的界面会好看一些了(不过还是比较素 😓)

填写必要的选项值后,直接提交即可直接生成my.cnf配置文件,并且输出下载。下载文件我设定的名字是 my.cnf-5.7.txt,用 .txt 后缀是为方便大家下载到本地后先用文本编辑器打开,确认符合您的预期后再正式使用。

这份配置文件,有以下几个地方建议您关注下:

  • 已默认关闭了query cache,如果您觉得确实有需要,可自行打开;
  • 客户端连接超时我设置600秒,如果您觉得太长或太短,也请自行调整;
  • 默认打开innodb status输出到log中,以及innodb lock输出到log中,如果觉得烦人,可自行关闭;
  • 把binlog-format默认改为row格式,如果不符合您的预期,请自行调整;
  • 把table lock的默认timeout时长(lock_wait_timeout)设置为3600秒,这个值之前的默认值是1天,我觉得太长了;
  • 几乎启用了全部Performance Schema功能,方便后续定位性能问题;
  • 启用了InnoDB Metric的大多数检测模块,方便对InnoDB进行监控;

好了,先介绍到这里,祝大家用的愉快,点击文末“阅读原文”即可直接开始使用,用得不爽的地方尽管在后面给我留言。

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

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

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