MySQL 服务器性能剖析

最新推荐文章于 2021-02-27 08:38:33 发布

kikajack

最新推荐文章于 2021-02-27 08:38:33 发布

阅读量582

点赞数

分类专栏：数据库文章标签： mysql 性能

本文链接：https://blog.csdn.net/kikajack/article/details/80082449

版权

数据库专栏收录该内容

36 篇文章 5 订阅

订阅专栏

这是《高性能 MySQL（第三版）》第三章的读书笔记。

关于服务，常见的问题有：

如何确认服务器是否发挥了最大性能
找出执行慢的语句，为何执行慢
为何在用户端发生间歇性的停顿、卡死

通过性能剖析（profiling）分析服务器的性能并找出花费时间最多的地方，有助于解决上述问题。

1. 性能优化简介

性能通常可以认为就是响应时间（Latency，延迟），而性能优化就是减少响应时间。

要想优化性能，首先需要知道性能的瓶颈在哪里。这就需要用到测试了。

2. 性能测试

2.1 对应用程序进行性能测试

每种语言都有专门的测试程序，对于 PHP，可以使用的有：

xhprof：轻量小巧，可在生成环境部署。参考这里。
xdebug：检测范围大，开销大，适合测试环境。

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 0

SHOW 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)