高性能mysql 之 性能剖析

Mysql  /  houtizong 发布于 3年前   122

1 定义性能优化  mysql服务器性能，此处定义为 响应时间。  在解释性能优化之前，先来消除一个误解，很多人认为，性能优化就是降低cpu的利用率或者减少对资源的使用。  这是一个陷阱。  资源时用来消耗并用来工作的，所以有时候消耗更多的资源能够加快查询速度，保持cpu忙绿，这是必要的。很多时候发现  编译进了新版本的InnoDB之后，cpu利用率上升的很厉害，这并不代表性能出现了问题。反而说新版本的InnoDB对cpu的利用率上升了。  查询的响应时间更能体现性能是不是有所提升。cpu利用上升，之势一种现象，而不是很好的性能指标。    还有一种理解，即性能优化仅仅是提升每秒查询量。这种误解，把吞吐量理解为性能优化，这恰恰是性能优化的倒数。  吞吐量可以看做 性能优化的副产品，因为每条查询执行的时间被缩短了，所以单位时间内可以执行更多的查询，而这刚好是  吞吐量的定义。  2 测量时间都花在哪了  优化性能，即缩短查询时间，首先必须知道时间都花在哪了。  3 优化方法怎么样  优化，很多人都会想到必须需要大量修改配置和sql或者策略。我们这里的方法完全相反，会把90%的精力放在测量时间花在哪里。  如果通过测量没有找到答案，一般都是测量的方法出现了问题。  4 对性能进行剖析  对性能进行剖析的步骤一般是 1 测量任务所花费的时间 2 对结果进行统计和排序将重要的排在前面。  第一部分 剖析服务器负载    1 学会使用工具new relic 这个软件。  2 或者使用企业监控器 enterprise monitor，她可以捕获发送给服务器的查询。  3 mysql 高级版本中 已经将慢查询日志的精度从秒提升到微妙级别，I/O开销也可以忽略不计，所以可以开启慢查询日志。    慢查询日志分析工具 pt-query-digest  第二部分 剖析单条查询    1 查看profile mysql>show profiles;2 设置profiling mysql>set profiling=1;3 查看profiles mysql> show profiles;4 查看单条sql 执行过程 mysql> show profile for query 2;  第三部分 使用show global status    这个方法实际上就是以较高的频率捕获数据，问题出现时，则可以通过某些计数器的尖刺或者凸现来发现。这个方法比较简单，对服务器影响也比较小，所以是一个花费时间不多却能很好解决问题的方法，命令如下shell> mysqladmin -uroot -r -i 1 -proot extended-status | awk '/Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%5d %5d %5d\n",q,tc,$4}' 这段shell 会每秒捕捉一次mysql数据库线程连接 ，打印数据库 每秒查询数、线程连接数、正在执行的线程数。这三个数据对于服务器级别偶尔停顿的敏感性很高，正常情况下 数据是稳定的，偶尔出现的尖刺必须引起足够的重视，根据实践，两方面的原因的可能性比较大一是服务器内部遇到了某种瓶颈，导致新查询在开始执行前需要获取老查询正在等待的锁而造成堆积，第二种原因是服务器可能突然遭到了大范围的查询请求，比如前端缓存失效。以上的这条命令可以执行几小时或者几天，然后绘制成图表，分析尖刺的原因。5 学会使用工具  1 pt-mysql-summary：本地安装、然后远程收集MySQL Server的基本信息  2 分析processlist结果    shell> grep State:processlist.txt |sort |uniq -c| sort -rn  3 在linux 服务器安装iostat 观察服务器I/O 队列详情。  4 参照 profile 提供的报表  5 如果怀疑mysql短时间写入大量文件到临时表或者排序文件 ，则需要使用命令lsof 观察服务器打开的文件句柄。  6 new relic 可以很好地解决这类问题6 性能剖析这一章节总结如下  1 我们认为定义性能最有效的方法是响应时间。  2 如果无法测量，就无法有效的优化，所以性能优化工作需要基于高质量、全方位及完整的响应时间测量。  3 大多数系统无法完整的测量，测量有时候也会有错误的结果。但也可以想办法绕过一些限制，并取得好结果。  4 完整的测量会产生大量需要分析的数据，所以需要用到剖析工具。  5 剖析汇报是一种汇总信息，掩盖和丢弃了太多细节，他不会告诉你缺少了什么，所以完全依赖剖析报告也是不明智的。  6 有两种消耗时间的操作：工作或者等待。大多数剖析工具只能测量因为工作而消耗的时间，所以等待分析有时候是很有用的补充，    尤其是当cpu利用率很低但工作却一直无法完成的时候。  7 优化和提升是两回事，当继续提升的成本超过收益的时候，应当停止优化。  8 注意你的直觉。决策应当尽量基于数据而不是感觉。