mysql系统分析实战分享
发布日期：2016-10-08 14:02:36 +0000
上班了上班了，干活了干活了。
那个，其实，我很多年没碰过技术了，但还是觉得有必要把之前一些处理过的技术问题拿出来，其实每个问题，都是很小的问题，如果单独说原因和答案都非常简单，但关键是，遇到问题的思考方式和分析方法。
依然是，高手请无视，针对一些初入技术职场的童鞋，希望能对各位遇到问题时候的思考方式有所帮助。
案例1：诡异的链接过多
当时情况是这样，突然有一天，数据库出现链接过多错误，导致网站报错。 熟悉mysql并操作过高并发系统的朋友知道，数据连接过多属于很常见的问题。但当时的情况是，访问量并不在高峰，按理说不应该有这样的问题。
看了一下数据库服务器的负载，很低，并不存在cpu或内存跑满的问题。
慢查询日志没有异常的SQL，更没有锁表。
于是就进入数据库做一下 show processlist的查询。
有些朋友可能会问，链接过多你还能看show processlist么，那个，mysql里root比普通用户多一个链接许可，所以，记得程序切忌用root链接，保留一个给系统分析师用。
意外发现，几乎所有的SQL停留在sleep状态，而且很多链接都持续了好几秒，甚至十几秒。
这里说明一下，如果是用数据中间件链接池来操作，从中间件到数据库存在固定数字的sleep链接是正常的，但从程序端到中间件，除非你是长连接，并且需要保持数据库频繁操作的应用，否则，通常不建议数据库保持连接，也就是不应该出现太多sleep操作。
我们的场景就是普通的web应用，php程序而已，都是短链接，按理说，程序执行完就应该释放的，所以这个问题就有点意外。
当然，这个和代码的设计也有关系，因为系统用的开源软件改写的，涉及数据库操作还是蛮多的，一般情况下，数据库操作完应该及时关闭，但由于一般认为php代码执行时间很短，所以在代码架构有点复杂的情况下，很多都是默认整个程序执行完再关闭。那么现在问题来了，到底php发生了什么问题。
我们去web服务器，看日志，发现访问量并没有异常，也没有针对我们的攻击行为，但确实很多php程序执行时间较长，web连接数也明显多于异常，即便是数据库重启，问题依然会重现，那么这时候，我们工程师就在最常用的php代码里设置断点，去看代码到底卡在哪个环节上执行时间很长，结果，发现是我们的一个非常重要的常识盲点。原来执行时间最长的，是在最后代码数据都执行完，输出执行 echo 的环节。
在本地做性能测试，压力测试的时候，我们知道echo 这种语句是基本没有开销的，也不太可能成为一种负载的来源，但这下我们明白了，echo原来不仅仅是php执行输出，也包含了网络传输的时间开销。只有客户端接收到传输内容后，echo执行才结束。
而那天的问题，其实是因为同机房有其他公司服务器被Ddos，导致机房出口拥堵，按理说这只是websever的问题，但因为webserver本身有轮询机制，而且设置的连接数较大，虽然访问较慢，但没有崩溃，而因为php代码里mysql链接没有及时释放，在php执行echo的时间等待较长，导致mysql链接过多崩溃。
知道这个问题，解决就简单了，因为开源系统封装了输出template的对象，我们就在这个对象执行的时候，先执行mysql_close(); 这样只改了一行代码，问题就解决了。
但后来发现出了bug，bug的理由很无厘头，居然部分template 的伪码里有数据库操作，但这个问题解决也简单，因为毕竟这样的场景很少， 而且mysql对象也被封装了，我们就在query方法里加了一行代码，如果没有数据库连接，就重建一个。 这样，这个重建过程只出现在极少数template里有mysql操作的场景，对整体系统基本没有性能干扰。
这个案例说来挺简单，就是数据库连接没有及时释放造成的，但因为触动了一个思维盲区，所以印象深刻。
线上的程序做断点日志分析是最常用的分析诡异问题的方法。基于断点日志分析，我们可以通过类似二分法，逐步递进直到精确定位具体到每一行代码的执行时间开销。
这里还要提醒一个常见问题，线上环境很多问题是在测试环境里很难重现的，所以遇到诡异问题，应该可以在线上做一些日志分析和代码的调试，当然这样可能会有一定的风险，但很多公司的流程和规范，开发工程师只能在线下测试性能和压力承受能力，针对线上很多现实的问题没有办法完整实测。
大公司可能会把测试环境做的更好更规范，以及有更有经验的工程师和分析师来解决问题，但创业公司，我建议要给程序员和分析人员一些线上应急处理的权限，否则真的会束手无策，经验值都是靠犯错和解决问题来积累的。
案例2：看似正常的负载过高
当时有个新业务数据增长很快，该业务的数据库服务器每天处理数百万次数据查询请求，uptime比较高，经常在5-6的样子，cpu负荷较重，运维负责人就发邮件，申请更换更好的服务器，增加资源。
按理说，这是个合理请求，负载也确实很高，业务也确实增长，但我这个人天性财迷抠门，总觉得这个数字不应该是极限，就登录到数据库服务器看了一下，很简单，我的方法就是先刷show processlist，连续刷几遍，看数据库都在执行啥，开销都集中在什么状态，这一看还真就发现问题了，居然经常看到有些mysql进程停留在 storing result to query cache 上。
这事我就纳闷了，因为按常规，这个状态应该是基本没有时间开销的，也就是show processlist看到是小概率事件的。
所以就要验证一下，执行 set profiling=1，然后从show processlist复制一条执行一次，然后执行 show profiles for query 1; 结果意外发现，常规来说执行开销最大的sending data （这个开销可不是输出数据哦，其实是io寻址）只有0.002秒，而 storing result to query cache 却执行了 0.005秒的样子，千分之五秒，一般人可能就无视了吧，但整个SQL执行不到0.01秒，这个开销比例蛮大的了。
那个，其实这个问题的责任者呢，是我自己，我觉得query cache是个好东西啊，所以开始配置服务器的时候，还是我自己做的配置，因为服务器内存够大，我就把query cache设置的比较大，结果SQL的反馈结果内容较多的情况下，就出现了query cache的碎片化比较严重，反而导致了query cache存储额外的开销，我在数据库里直接操作将query cache内容重置的命令，再执行这个SQL，用profiling去分析，发现这个开销就没有了，负载瞬间显著下降了60%左右。
然后我跟运维负责人说，半夜没人的时候把数据库的启动参数，query cache那块设置回默认值，重启一下数据库，于是就没再追加预算和服务器投入。
这个案例本身是我自己的乌龙，因为没有明确理解query cache的读取和存储逻辑，自以为是的调高了参数，在SQL返回值较大的情况下，导致了严重碎片化，带来了额外的开销，虽然每次开销都极其微小，但由于系统的请求频次非常高，所以系统不必要的负载就比较大。
那么这个案例里，需要分享的方法是，showprocesslist+一定的敏感度，再配合用set profiling去分析具体的开销，是非常重要的一种分析查询性能的方法。
案例3：io性能的优化案例
这个案例又是我的错，唉，我发现我犯的错误还是蛮多的，不过我们工程师解决方案非常经典，所以也列在这里以供参考。
还是一个非常高并发的业务场景，最开始呢，为了达到查询的最优化，数据结构还是我设计的，使用了复合索引，确保每次查询的索引命中率极高，但这个业务场景有一个问题，就是除了查询请求很高之外，数据的插入请求基本上是同频次的。（大部分场景都是数据插入后随之查询，个别会有单独查询场景），所以插入请求巨大，数据库的io压力特别大。
结果我们工程师也是受到我的影响吧，抠门的很，也是尽可能在有限资源下挖潜。结果怎么做的呢? 说来简单，索引降级，把两个字段的复合索引降到单键索引了。
单纯从查询而言，这一降级其实是牺牲了效率，但是牺牲的并不大，但从更新而言，从复合索引降级到单键索引，索引更新的io负载就有了明显的降低，由于查询的负载开销远低于更新的负载开销，所以这一降级，在查询与更新同频的场景下，就变得效果特别好。
这个案例需要分享的经验是，索引的建立，不但要考虑查询的语句，更新的语句，也要考虑业务场景中相关的频次，在更新频次远低于查询频次时，和更新频次与查询频次相当时，同样的数据结构，同样的SQL语句，可能索引的设计方案会有重大的调整和改变。
相关旧文
Mysql也有了很多的版本迭代，很多之前遇到的问题和瓶颈也许现在已经在系统中顺畅解决，但我觉得，一些思路和方法依然值得分享。
当然，这些都不是什么高大上的技术和解决方案，都是实战中，屌丝创业团队面临一些实际问题的响应和处理能力。很多草根创业团队，其实都是栽倒类似这样的问题上的。