3 服务器性能剖析
3 服务器性能剖析
1 概述
最常碰到的三个性能相关的服务请求是。本小节主要针对这三个问题描述。
- 如何确认服务器是否达到了性能最佳的状态。
- 找出某条语句为什么执行不够快的。
- 诊断被用户描述成“停顿”、“堆积”或者“卡死”的某些间歇性疑难故障。
2 性能优化简介
这里只是大概的说明下，这块每个人理解的可能不一样，如性能等，而且根据实际情况，优化的关注点也可能不一样。
数据库的性能用操作的响应时间来度量，单位是每个查询花费的时间。
一些注意要点整理如下。
- 性能优化并不是降低 CPU 利用率，有时候消耗更多的资源能加快查询速度，比如新版本的 InnoDB 引擎对资源的利用率上升了。
- 每秒查询量是吞吐量的优化，只是性能优化的一部分。
- 无法测量就无法有效地优化，因此我们应该测量时间花在什么地方（重要）。 有两种比较常见的情况会导致不合适的策略：(1) 在错误的时间启动和停止测量（比如定位慢查询就应该在慢查询的开始和结束时间范围内，否则超出范围就无效了）；(2) 测量的是聚合后的信息，而不是目标活动本身。
- 任务中，一些运行不频繁或者很短的子任务对整体响应时间的影响很小，通常可以忽略不计。
2.1 通过性能剖析进行优化
性能剖析一般有两个步骤。
- 测量任务所花费的时间。
- 对结果进行统计和排序，将重要的任务排到前面。
当基于执行时间的分析发现一个任务需要花费太多时间的时候，应该深入去分析一下，可能会发现某些“执行时间”实际上是在等待。
2.2 理解性能剖析
MySQL 的性能剖析（profile）将最重要的任务展示在前面，但有时候没显示出来的信息也很重要，如下。
- 值得优化的查询。性能剖析不会自动给出哪些查询值得花时间去优化。
- 异常情况。某些任务即使没有出现在性能剖析输出的前面也需要优化。比如某些任务执行次数很少，但每次执行都非常慢，严重影响用户体验。因为其执行频率低，所以总的占比响应时间并不突出。
- 未知的未知。有些任务可能没有测量到。
- 被掩藏的细节。性能剖析无法显示所有响应时间的分布。
3 对应用程序进行性能剖析
应用程序性能瓶颈可能有很多因素。
- 外部资源，比如调用了外部的服务。
- 应用需要处理大量的数据，比如分析一个超大的文件。
- 在循环中执行昂贵的操作，比如滥用正则表达式。
- 使用了低效的算法。
注意性能剖析会让服务变慢，但是收益是巨大的。
4 剖析 MySQL 查询
对查询进行性能剖析有两种方式，每种方式都有各自的问题。
性能剖析可以分析出哪些查询时主要的压力来源。
4.1 剖析服务器过载
主要有如下一些要点。
- 1 捕获 MySQL 的查询到日志文件中。
- 1.1 可以更改配置，从而记录每个查询的执行时间，高版本的 MySQL 支持的精度是微秒级。
- 1.2 通过抓取 TCP 网络包，然后根据 MySQL 的客户端/服务端通信协议进行解析。
- 2 分析查询日志。
- 2.1 利用慢查询日志捕获服务器上的所有查询，并且进行分析。
- 2.2 为了节约时间，应该生成一个剖析报告，根据报告可以找到哪些特别需要关注的部分。生成剖析报告的根据如
pt-query-digest。
至此，我们可以确定需要优化的查询。
4.2 剖析单条查询
有以下方法方法。
- 使用 SHOW PROFILE。
- 使用 SHOW STATUS。
- 使用慢查询日志。
- 使用 Performance Schema。
4.2.1 使用 SHOW PROFILE
SHOW PROFILE 命令是在 MySQL 5.1 以后的版本引入的，默认是禁用的，但可以通过服务器遍历在会话（连接）级别动态地修改。
mysql > SET profiling = 1;
然后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。
当一条查询提交给服务器时，此工具会记录剖析信息到一张临时表（MySQL 8 看起来好像没有用临时表了），并且给查询赋予一个从 1 开始的整数标识。一下是一个示例。
mysql> set profiling = 1;
mysql> select * from mysql.user;
mysql> show profiles;
+----------+------------+--------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------+
| 1 | 0.01382575 | select * from mysql.user |
+----------+------------+--------------------------+
# 查看耗时明细，这个没有提供 order by，不方便查看
mysql> show profile for query 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.013400 |
| Executing hook on transaction | 0.000024 |
| starting | 0.000016 |
| checking permissions | 0.000008 |
| Opening tables | 0.000145 |
| init | 0.000010 |
| System lock | 0.000010 |
| optimizing | 0.000005 |
| statistics | 0.000015 |
| preparing | 0.000016 |
| executing | 0.000102 |
| end | 0.000005 |
| query end | 0.000004 |
| waiting for handler commit | 0.000008 |
| closing tables | 0.000008 |
| freeing items | 0.000027 |
| cleaning up | 0.000024 |
+--------------------------------+----------+
# 使用 Performance Schema，分组后，order by 查询，看起来更方便。
mysql>
SELECT
state,
sum( duration ) AS total_r,
round( 100 * sum( duration ) / ( SELECT sum( duration ) FROM information_schema.profiling WHERE query_id = @query_id ), 2 ) AS pct_r,
count(*) AS calls,
sum( duration ) / count(*) AS "R/call"
FROM
information_schema.profiling
WHERE
query_id = @query_id
GROUP BY
state
ORDER BY
total_r DESC;
+--------------------------------+----------+-------+-------+--------------+
| state | total_r | pct_r | calls | R/call |
+--------------------------------+----------+-------+-------+--------------+
| starting | 0.013416 | 97.03 | 2 | 0.0067080000 |
| Opening tables | 0.000145 | 1.05 | 1 | 0.0001450000 |
| executing | 0.000102 | 0.74 | 1 | 0.0001020000 |
| freeing items | 0.000027 | 0.20 | 1 | 0.0000270000 |
| Executing hook on transaction | 0.000024 | 0.17 | 1 | 0.0000240000 |
| cleaning up | 0.000024 | 0.17 | 1 | 0.0000240000 |
| preparing | 0.000016 | 0.12 | 1 | 0.0000160000 |
| statistics | 0.000015 | 0.11 | 1 | 0.0000150000 |
| init | 0.000010 | 0.07 | 1 | 0.0000100000 |
| System lock | 0.000010 | 0.07 | 1 | 0.0000100000 |
| checking permissions | 0.000008 | 0.06 | 1 | 0.0000080000 |
| waiting for handler commit | 0.000008 | 0.06 | 1 | 0.0000080000 |
| closing tables | 0.000008 | 0.06 | 1 | 0.0000080000 |
| optimizing | 0.000005 | 0.04 | 1 | 0.0000050000 |
| end | 0.000005 | 0.04 | 1 | 0.0000050000 |
| query end | 0.000004 | 0.03 | 1 | 0.0000040000 |
+--------------------------------+----------+-------+-------+--------------+
4.2.2 使用 SHOW STATUS
MySQL 的 SHOW STATUS 命令返回了一些计数器。既有服务器级别的全局计数器，也有基于某个连接的会话级别的计数器。我们可以在执行完查询后观察某些计数器的值来帮助我们分析问题。
mysql> flush status;
mysql> select * from mysql.user;
mysql> show status where variable_name like 'Handler%' or variable_name like 'Created';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 5 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
4.2.3 使用慢查询日志
和 SHOW PROFILE 类似，只不过是看记录的慢查询日志文件进行分析的。
4.2.4 使用 Performance Schema
通过 Performance Schema，我们可以实现以表的形式分析慢查询等。
4.3 使用性能剖析
通过前面的剖析报告后，我们可以结合 EXPLAIN 来分析。但是有可能也很难定位到问题，比如慢查询日志中没有执行计划或者详细的时间信息，对于偶尔记录到的这几次查询异常慢的问题，很难知道其原因在哪里，因为信息有限。可能是系统中有其他东西消耗了资源，比如正在备份，也可能是某种类型的锁或者征用阻塞了查询的进度。这些间歇性问题我们接着讨论。
5 诊断间歇性问题
间歇性的问题比如系统偶尔停顿或者慢查询，很难诊断。有些幻影问题只在没有注意到的时候才发生，而且无法确认如何重现。
一些间歇性数据库性能问题的实际案例如下。
- 应用通过 curl 从一个运行得很慢的外部服务来获取汇率报价的数据。
- memcached 缓存中的一些重要条目过期，导致大量请求落到 MySQL 以重新生成缓存条目。
- DNS 查询偶尔会有超时现象。
- 可能是由于互斥锁争用，或者内部删除查询缓存的算法效率太低的缘故，MySQL 的查询缓存有时候会导致服务有短暂的停顿。
- 当并发度超过某个阈值时，InnoDB 的扩展性限制导致查询计划的优化需要很长的时间。
5.1 单条查询问题还是服务器问题
如果服务器上所有的程序都突然变慢，又突然变好，每一条查询也都变慢了，那么慢查询可能就不一定是原因，而是由于其他问题导致的结果。
我们可以通过下面几种技术来判断是单条查询问题还是服务器问题导致慢查询的。
使用
SHOW GLOBAL STATUS。实际上是以较高的频率比如一秒执行一次SHOW GLOBAL STATUS命令捕获数据。使用
SHOW PROCESSLIST。是通过不停地捕获SHOW PROCESSLIST的输出，来观察是否有大量线程处于不正常的状态或者有其他不正常的特征。
$ mysql -e 'SHOW PROCESSLIST\G' | grep State:| sort| uniq -c| sort -rn
3 State:
1 State: Waiting on empty queue
1 State: starting
- 使用查询日志。
5.2 捕获诊断数据
略。
6 其他剖析工具
这里只是简单的列举以下。
- 使用 USER_STATISTICS 表。Percona Server 和 MariaDB 支持的。
- 使用 strace
参考文献
- [高性能 MySQL]