焦大自媒体——互联网创业及qg111钱柜娱乐平台营销媒体!
qg111钱柜娱乐平台排名

mysql系统分析实战分享

时间:2016-10-08 23:25 来源:网络投稿 作者:caoz
那个,其实,我很多年没碰过技术了,但还是觉得有必要把之前一些处理过的技术问题拿出来,其实每个问题,都是很小的问题,如果单独说原因和答案都非常简单,但关键是,遇到问题的思考方式和分析方法。 mysql系统 依然是,高手请无视,针对一些初入技术职场的童鞋,希望能对各位遇到问题时候的思考方式有所帮助。   案例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语句,可能索引的设计方案会有重大的调整和改变。
围观: 次 | 责任编辑:焦大
回到顶部