实例:面对未知环境的MySQL性能问题,如何诊断

内容来源:2018 年 5 月 20 日,爱可生技术服务总监洪斌在“PHPCon China 2018 技术峰会”进行《MySQL性能诊断方法与实践》演讲分享。IT 大咖说(微信id:itdakashuo)作为独家视频合作方,经主办方和讲者审阅授权发布。

阅读字数:2852 | 8分钟阅读

摘要

本次演讲将介绍性能诊断方法论,以及观测工具在MySQL性能分析过程中的运用,并通过实际案例展示面对未知环境的性能问题,该如何诊断。

获取嘉宾演讲视频及PPT,扫一扫下方二维码即可。

性能诊断的方法

几个定律

Little’s law是一个证明排队论的定律,我们可以将计算系统宏观的建模成排队系统,系统中有这样一个公式——请求量等于到达率乘响应时间。业界一般讨论的性能指标有KPS、吞吐量、响应时间等,其中关键的是响应时间(延时)的指标和变化以及对吞吐量的影响。

Amdahl’s Law是为了证明并行计算对性能扩展所能带来的影响。上图中的绿线就是Amdahl所计算的并发和吞吐量之间的关系,从图中可以看出整个曲线最终会趋近于一个常数,这表示后续无论系统资源和并发如何增长吞吐量都是恒定。不过真实的情况并不会这样,由此引出第三个定律——Universal Scalability Law,它指出随着资源和并发的增加性能会不升而降,图中通过红色线条表示,当到达某一时候后性能会急剧下降。因此我们在实际工作中会设法找到最优点,而不是通过不断的增加资源和并发来提升性能。

这些基础理论帮我们界定出了性能的边界,对如何提升性能有更深入的认识。

通用方法

USE方法包含三部分使用率、饱和率、错误。任何资源都可以理解为一个队列系统,这个系统中也会有使用率、饱和率,当队列饱和无法处理请求的时候会进入错误阶段,分为逻辑错误和压力过大造成的错误。

基于USE方法在进行性能分析时首先会查看系统所处阶段,比如资源没有饱和的情况下,就不用再去新增资源,而是应该找寻其他影响性能的因素;出现错误的时候,可能是因为压力过大。通过这样的方法我们在资源层面分析性能问题时就有了清晰的脉络。

火焰图方法是用来分析应用程序在CPU和非CPU两个方面的消耗时间,通过on-cpu和off-cpu的绘图方式能够直观的看出每个调用栈在不同阶段耗费的时间。

正常情况下性能无法通过单一指标来衡量,需要用到基线,也就是历史数据,这样才能判断出性能变化的好坏。

MySQL体系结构

MySQL整体上分为两大部分Connectors和Server。下方的Server端又被分为计算层和存储层,计算层负责所有连接的处理,包括SQL解析、SQL执行计算以及SQL优化等。MySQL的Server层的优点在于拥有抽象接口能够对接各种存储引擎,只要该引擎符合接口规范。

解决MySQL问题时要分析故障点具体在哪一层,针对不同层面选择不同的优化方式 。

快速诊断

当系统出现问题但还不能定位具体原因的时候,需要进行系统级的快速判断,这里列出一些常规的执行流程。

首先使用top命令判断主机负载以及cpu消耗情况。之后通过USE方法查看是否存在错误,比如oom-killer或tcp drop等。接下来是虚拟内存的情况,主要检查r、free、si、so、us、sy、id、wa、st列。然后检查进程的cpu使用率,多核利用情况;内存使用情况;网络吞吐量;tcp连接情况等。

MySQL诊断工具

对于数据库来说不仅要从系统层面找问题,还要从自身内部来分析。首先当然就是查看日志,不同的日志能够提供不同的信息,错误日志中有服务挂了或重启后的详细的信息和记录,slow日志中记录了超过一定阈值的查询和SQL请求,general日志一般不会开启,只有在故障重现的时候才会用到。

除了日志外还可以通过命令查看MySQL内部的状态信息,使用show procersslist就能看到当前MySQL的任务分布情况和线程处理状态。存储引擎的状态也要检测,通过show engine InnoDB status命令获取,主要关注点在事务状态和事务队列上。还可以使用Explain查看执行计算,对SQL进一步优化。最后是performance schema,它提供了更详细的内部状态,并且能通过SQL的方式出查询出来。

在出现实际问题后,诊断步骤大致如下。首先是结合快速诊断检查系统全局资源负载,然后检查MySQL错误日志和当前MySQL在做什么,接着查看InnoDB的事务情况,最后要检查下MySQL的复制状态。

InnoDB

InnoDB是MySQL中很重要的一个部分,开发者在使用的时候有几点需要注意。InnoDB表必须要有主键或唯一索引,组件应使用较小数据类型且有序,其次是要避免大事务(运行时间长或变更记录多)。

上图列出的是一些比较重要的参数。在并发有一定量的情况下,开发者一般都会将max_connection设置的比较大,不过这个值过大是会产生负面影响的。首先我们要理解并发和并行是完全不同的两个概念,就拿银行网点来说,并发可以理解为当前有多少顾客在存取款,并行则是有多少柜台在处理业务。由于并行量固定,所以当并发过多之后就要排队。正因为有这种情况,max_connection才不会设置的过大,通常来说不会超过1千。

Innodb_buffer_pool_size的配置同样也不是越多越好,这取决于数据量,一般建议设置在50-60之间,后续如果不够会再增加。Innodb_flush_neighbors是关于IO刷盘的参数,主要用在有机械硬盘的场景下,如果用的是SSD可以将它设置为0。Innodb_log_file_size的大小取决于当前是否在频繁刷新log日志,设置过大会影响启动效率,因此建议设为1-2G。

数据库的优化最重要的还是在于SQL优化,实现更好的物理设计包括表设计、索引设计、数据分布等等。

Note

优化的核心实际上是如何“少做事”,做的越多越复杂就意味着效率的降低,在优化之前要设法简化流程。另外切勿盲目追求最优配置模板,存在这样一个原则——在不知道参数含义的情况下不要随意改动它,只有在明确知道该参数能够解决问题的时候才去调整。还有就是避免过早优化,在遇到问题的时候在做优化。

观测工具用法

BPF是一个包过滤系统,用来解决抓包的性能问题,在tcp上的网络调试方面用的较多。Tcpdump和linux底层对接的就是BPF,在内核中BPF有一个虚拟机,能够接收一定的指令,这些指令可以提高抓包的性能。在3.18版本的时候,linux对BPF进行了扩展,从原来的抓包场景扩展到了更广的范围。

目前BPF的应用还不算太多,主要是因为它对内核的版本要求较高,liunx内核要在4.4以上。一般我们也不会直接使用BPF,而是使用社区中的Bcc(BPF工具集),它结合BPF的能力做了很多命令行式的工具,更方便使用。如果要用在Mysql上还需要进行编译。

Bcc提供的工具基本上能够覆盖系统的各个层面,具体介绍可以在项目的Github上查看。

以上为今天的分享内容,谢谢大家!

IT大咖说 | 关于版权

本文由“IT大咖说(ID:itdakashuo)”原创,转载时请注明作者、出处及微信公众号。投稿、约稿、转载请加微信:ITDKS10(备注:投稿),茉莉小姐姐会及时与您联系!

感谢您对IT大咖说的热心支持!

原文发布于微信公众号 - IT大咖说(itdakashuo)

原文发表时间:2018-08-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏信达雅

分布式内存网格中的聚合查询

近年来,我们看到越来越多的应用程序不再构建在关系型数据库上,而是建立在分布式环境上。发生这种情况是因为它们需要可扩展性和高可用性,而且还需要能够提供高吞吐量和低...

34210
来自专栏腾讯架构师的专栏

云计算时代的数据库核弹头 : Tencent MySQL ( TXSQL)

作为腾讯规模最大的 MySQL 数据库服务,CDB 在腾讯云上也是最受欢迎的关系型数据库产品。CDB 不仅具备备份回档、监控、快速扩容等数据库运维的全套解决方案...

6890
来自专栏全华班

基于PHP爬虫的博客小程序

小程序后端是基于 Wext-server-thinkphp3.2 实现的数据爬虫,使用 ThinkPHP3.2 框架开发。Wext-server-thinkph...

3051
来自专栏美团技术团队

MyFlash——美团点评的开源MySQL闪回工具

由于运维、DBA的误操作或是业务bug,我们在操作中时不时会出现误删除数据情况。早期要想恢复数据,只能让业务人员根据线上操作日志,构造误删除的数据,或者DBA使...

39212
来自专栏华仔的技术笔记

iOS应用架构谈 本地持久化方案及动态部署

嗯,你们要的大招。跟着这篇文章一起也发布了CTPersistance和CTJSBridge这两个库,希望大家在实际使用的时候如果遇到问题,就给我提issue或者...

4027
来自专栏铭毅天下

Elasticsearch全文检索实战小结——复盘我带的第二个项目

一、项目概述 这是一个被我称之为“没有枪、没有炮,硬着头皮自己造”的项目。项目是和其它公司合作的三个核心模块开发。 使用ES的目的是: 1)、采集数据、网...

72010
来自专栏13blog.site

微信公众号问题:{"errcode":40125,"errmsg":"invalid appsecret, view more at http:\/\/t.cn\/LOEdzVq, hints: [

在调试微信公众号授权登录时遇到了这个错误,着实是心烦了半天,公众号相关开发以前是经常做的,很久没有接触了,而且遇到了这么个以前没遇到的问题。 {"errcode...

6506
来自专栏搜云库

手把手教你 MongoDB 的安装与详细使用(一)

MongoDB 是由C++语言编写的,是一个基于分布式文件存储的开源数据库系统。

5047
来自专栏架构之美

五分钟学会分布式事务

1622
来自专栏AI2ML人工智能to机器学习

Shiny: R语言来建立开源交互式数据分析微服务的神器

先来说个应用场景: 假设你需要快速Prototype一个数据分析的服务, 而且需要给业务客户一定的自由度来理解数据分析的强大, 例如更换数据, 更换分析手段。 ...

1853

扫码关注云+社区

领取腾讯云代金券