MySQL 服务器性能剖析
这是《高性能 MySQL（第三版）》第三章的读书笔记。
关于服务，常见的问题有：
- 如何确认服务器是否发挥了最大性能
- 找出执行慢的语句，为何执行慢
- 为何在用户端发生间歇性的停顿、卡死
通过性能剖析（profiling）分析服务器的性能并找出花费时间最多的地方，有助于解决上述问题。
1. 性能优化简介
性能通常可以认为就是响应时间（Latency，延迟），而性能优化就是减少响应时间。
要想优化性能，首先需要知道性能的瓶颈在哪里。这就需要用到测试了。
2. 性能测试
2.1 对应用程序进行性能测试
每种语言都有专门的测试程序，对于 PHP，可以使用的有：
2.2 对数据库进行性能测试
2.2.1 分析服务器负载
通过分析服务器负载，定位需要优化的单条查询。
捕获 MySQL 的查询到日志文件
MySQL 中，慢查询日志是开销最低、精度最高的测量查询时间的工具。运行时间超过 long_query_time 值语句会被记录到慢查询日志中。long_query_time 单位是秒，默认值为 10，可以设置为 0 来捕获所有的查询。慢查询日志需要手动开启，开启后会略微影响性能。慢查询日志可以写入文件或数据库，但写入到数据库时会影响性能且时间粒度退化为秒级。
MySQL 中还有另外一种查询日志，叫通用日志，但可用信息太少且资源消耗大，基本不用。
慢查询日志相关参数如下，使用可以参考 这里：
- slow_query_log：是否开启慢查询日志，1 开启，0 关闭。
- log-slow-queries：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。默认给一个缺省的文件 host_name-slow.log
- slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。默认给一个缺省的文件 host_name-slow.log
- long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
- log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
- log_output：日志存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中。MySQL 数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系统的专用日志表中比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，建议优先记录到文件。
分析查询日志
分析慢查询日志可以使用 MySQL 自带的 mysqldumpslow 工具。要用慢查询日志生成分析报告可以使用 pt-query-digest 工具。
2.2.2 分析单条查询
定位到需要优化的单条查询后，开始优化。
SHOW PROFILE（以被 performance_schema 替代）
都废弃了，别研究了。
–使用前先通过 have_profiling 参数查看当前 MySQL是否支持 profile：–
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1287 | '@@have_profiling' is deprecated and will be removed in a future release. |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
–默认禁用，可以通过服务器变量在会话级别启用：–
mysql> SET profiling = 1;Performace Schema
Performace Schema 是目前 MySQL（5.7 及以上版本）主推的性能测试工具，默认开启。首次安装 MySQL 后，你会发现其默认安装了几个数据库，其中的 performace_schema 就是用于配置 Performace Schema 的。
SHOW STATUS
SHOW STATUS 命令返回一些服务器级别的全局计数器或某个会话级别的计数器。可以显示活动频繁程度，但是无法给出消耗的时间。
mysql> show global status;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients | 275 |
| Aborted_connects | 80872 |
| Binlog_cache_disk_use | 0 |
...
| Threads_running | 1 |
| Uptime | 6661773 |
| Uptime_since_flush_status | 6661773 |
| validate_password_dictionary_file_last_parsed | 2018-01-31 15:35:09 |
| validate_password_dictionary_file_words_count | 0 |
+-----------------------------------------------+--------------------------------------------------+
355 rows in set (0.03 sec)2.3 诊断间歇问题
应用偶尔停顿、数据库间歇性的慢查询的可能原因有：
- 应用通过接口从运行很慢的外部服务获取数据
- memcached 中的重要缓存条目过期，导致大量请求落到 MySQL 以重新生成缓存条目
- DNS 查询偶尔超时
- 互斥锁争用或内部删除查询缓存的算法效率太低，MySQL 的查询缓存导致服务短暂停顿
- 并发度超过阈值时，InnoDB 的扩展性限制导致查询计划的优化需要很长时间
解决间歇性问题有常见的套路和工具，定位好问题才能进行之后的操作。
2.3.1 单条查询问题还是服务器问题
SHOW GLOBAL STATUS
SHOW STATUS 或 SHOW SESSION STATUS 显示当前会话级别的信息，SHOW GLOBAL STATUS 显示服务器级别的信息。完整差异可以参考 SHOW GLOBAL STATUS vs SHOW STATUS。
以较高的频率执行 SHOW GLOBAL STATUS 命令捕获数据，问题出现时可以通过某些计数器（Threads_running、Threads_connected、Questions、Queries）的“尖刺”和“凹陷”来发现问题。SHOW GLOBAL STATUS 命令的用法简单且执行时不需要特殊权限（登录数据库的用户都可以使用）、对服务器影响小。
下面示例通过 -i1 选项每秒捕捉一次数据，输出给 awk 计算并输出每秒的查询数、Threads_connected、Threads_running：
[root@VM_120_242_centos ~]# mysqladmin -uroot -p ext -i1 | awk '/Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%d %d %d\n", q, tc, $4}'
Enter password:
1554866 1 1
1 1 1
1 1 1下面示例通过 -i1 选项每秒捕捉一次数据，通过 -r 选项展示差值，输出给 awk 计算并输出每秒的查询数：
# mysqladmin -uroot -p ext -i1 -r | awk '/Queries/{q=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%d %d %d\n", q, tc, $4}'
Enter password:
1554856 1 1
1 0 0
1 0 0
1 0 0SHOW PROCESSLIST
不停的捕获 SHOW PROCESSLIST 的输出，可以观察是否有大量线程处于不正常状态或有不正常特征。例如查询很少会长时间处于“statictics”状态。
示例，通过命令尾部使用 \G 替换分号可以垂直输出结果，通过 sort|uniq|sort 命令可以计算某个列值出现的次数：
[root@VM_120_242_centos ~]# mysql -uroot -p -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn
Enter password:
2 State:
1 State: starting除了通过命令行外，也可以直接查询 INFORMATION_SCHEMA 数据库中的 PROCESSLIST 表。
使用查询日志
需要开启慢查询日志，并在全局级别设置 long_query_time 为 0，然后重置所有连接以使新的全局设置生效。
好的工具可以帮助诊断问题，否则就要去几百 GB 的日志文件中查找问题。下面示例的一行代码可以根据 MySQL 每秒将当前时间写入日志中的模式统计每秒的查询数量：
awk '/^# Time:/{print $3, $4, c; c=0}/^# User/{c++}' slow-query.log理解发现的问题
建议刚开始诊断问题时，先使用 SHOW STATUS 和 SHOW PROCESSLIST。这两种方法开销低，且可以通过 shell 或反复查询来收集数据。分析慢查询日志则比较困难。
2.3.2 捕获诊断数据
诊断间歇性问题，需要收集尽可能多的数据。需要两个工具：
- 触发器，即区分问题的方法
- 收集诊断数据的工具
诊断触发器
触发器需要避免“误报”和“漏检”。
通常将触发器阈值调整为正常情况的一倍左右。比如 Threads_running 在正常情况下是 10，则触发器的阈值可以设置为 20。对于 Threads_connected 如果正常值为 150，则阈值可以是 300。
另外，还需要设置持续时间。比如 Threads_running 连续 3 秒钟超过 20，则认为是异常情况。可以使用“pt-stalk”工具。
需要收集的数据类型
确定诊断触发器后，可以开启进程收集数据。数据应该尽可能多，包括系统状态、CPU 利用率、磁盘使用率和可用空间、内存利用率、ps 命令的输出采样，以及从 MySQL 获得的信息，包括 SHOW STATUS、SHOW INNODB STATUS、SHOW PROCESSLIST。
Linux 上可用的服务器内部诊断工具有 oprofile、strace（生产环境中使用有风险）。要剖析查询可用 tcpdump。
可以通过 GDB 堆栈跟踪分析等待原因。跟踪时先启动 GDB，然后附加（attach）到 mysqld 进程，将所有线程的堆栈都存储起来。然后利用脚本汇总类似的堆栈信息，再利用 sort|uniq|sort 排序。
解释结果数据
2.3.3 案例
2.4 其他分析工具
2.4.1 USER_STATISTICS 表
INFORMATION_SCHEMA 库中的表，存储了各个数据库的信息及所有活动的统计信息，可以得知哪个数据库的哪个表、哪个索引使用的最频繁。
表索引的信息。SHOW INDEX FROM schemaname.tablename; 命令从这个表获取结果。
mysql> SHOW INDEX FROM szhuizhong.users;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | UserID | A | 1460 | NULL | NULL | | BTREE | | |
| users | 0 | Account_index | 1 | Account | A | 1460 | NULL | NULL | | BTREE | | |
| users | 1 | CorpID | 1 | FromID | A | 2 | NULL | NULL | YES | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)5. 总结
要解决问题，首先要搞清楚问题。所有的查询记录都要记录到日志中，然后用 pt-query-digest 工具生成系统级别的剖析报告。
