MySQL - MySQL使用profiles、performance_schema实现性能监控 - 《个人博客 - PHP/Java/Git》

Profile
- 使用方法
Performance Schema
- 使用方法

Profile

官方文档：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
show profile 和 show profiles 用于查看当前会话 SQL 语句的资源使用情况。
语法：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]
type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

type 的可选值

ALL displays all information（显示所有）
BLOCK IO displays counts for block input and output operations（显示块输入和输出的操作次数）
CONTEXT SWITCHES displays counts for voluntary and involuntary context switches（显示上下文主动和被动的切换次数）
CPU displays user and system CPU usage times（显示用户CPU和系统CPU使用时间）
IPC displays counts for messages sent and received（显示发送和接收的消息数量）
MEMORY is not currently implemented（未实现该功能）
PAGE FAULTS displays counts for major and minor page faults（显示主要和次要的页错误数量）
SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs（显示源码中的函数名称，以及函数所在文件的名称和行号）
SWAPS displays swap counts（显示交换的次数）
使用方法
此工具默认是禁用的，可以通过服务器变量在会话级别动态的修改，查看是否开启，0 为未开启，1 为开启：
```
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
```
开启：
```
mysql> set profiling = 1;
```
随意执行几条测试 sql： ```sql mysql> drop table if exists t_user;

mysql> create table t_user(id int, username varchar(12), password varchar(255));

mysql> select * from t_user;

随后使用 `show profiles` 命令查看刚才执行的 sql 耗时列表：
```sql
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                    |
+----------+------------+--------------------------------------------------------------------------+
|        1 | 0.00101300 | SELECT @@profiling                                                       |
|        2 | 0.02040150 | drop table if exists t_user                                              |
|        3 | 0.01721925 | create table t_user(id int, username varchar(12), password varchar(255)) |
|        4 | 0.00167575 | select * from t_user                                                     |
+----------+------------+--------------------------------------------------------------------------+

使用 show profile 命令查看单条 sql 的详细信息，配置 for query 查看指定的 sql：

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000511 |
| checking permissions | 0.000046 |
| Opening tables       | 0.000137 |
| init                 | 0.000069 |
| optimizing           | 0.000020 |
| executing            | 0.000066 |
| end                  | 0.000010 |
| query end            | 0.000011 |
| closing tables       | 0.000039 |
| freeing items        | 0.000069 |
| cleaning up          | 0.000037 |
+----------------------+----------+

使用 type 显示指定的分析内容，例如显示 cpu 分析信息：

mysql> show profile cpu for query 2;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000145 | 0.000080 |   0.000081 |
| Executing hook on transaction  | 0.000013 | 0.000005 |   0.000006 |
| starting                       | 0.000055 | 0.000028 |   0.000029 |
| checking permissions           | 0.013659 | 0.009681 |   0.000000 |
| waiting for handler commit     | 0.000035 | 0.000033 |   0.000000 |
| waiting for handler commit     | 0.005581 | 0.002485 |   0.000000 |
| query end                      | 0.000830 | 0.000864 |   0.000000 |
| closing tables                 | 0.000011 | 0.000010 |   0.000000 |
| waiting for handler commit     | 0.000017 | 0.000018 |   0.000000 |
| freeing items                  | 0.000035 | 0.000035 |   0.000000 |
| cleaning up                    | 0.000020 | 0.000020 |   0.000000 |
+--------------------------------+----------+----------+------------+

Performance Schema

官方文档：https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html

performance_schema 是 mysql 自带的一个库，提供了一种在运行时检查服务器内部执行情况的方法。它使用PERFORMANCE_SCHEMA 存储引擎实现，主要侧重于记录性能数据。
持续且不显眼地进行，开销很少。
即使 performance_schema 在内部发生故障，也不会影响数据库的正常运行。
performance_schema 中的事件只记录在本地的 performance_schema 中，其下的这些表中数据发生变化时不会被写入 binlog 中，也不会通过复制机制被复制到其他节点。
使用方法
查看是否开启，ON 为开启，OFF 为关闭：
```
mysql> SHOW VARIABLES like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
```
如果没有开启，需要要配置文件中修改：
```
[mysqld]
performance_schema=ON
```
performance_schema 库中的表分类： ```sql — 语句事件记录表，这些表记录了语句事件信息，当前语句事件表events_statements_current — 历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long — 以及聚合后的摘要表summary — summary表还可以根据帐号(account)，主机(host)，程序(program)，线程(thread)，用户(user)和全局(global)再进行细分) mysql> show tables like ‘%statement%’;

— 等待事件记录表，与语句事件类型的相关记录表类似： mysql> show tables like ‘%wait%’;

— 阶段事件记录表，记录语句执行的阶段事件的表 mysql> show tables like ‘%stage%’;

— 事务事件记录表，记录事务相关的事件的表 mysql> show tables like ‘%transaction%’;

— 监控文件系统层调用的表 mysql> show tables like ‘%file%’;

— 监视内存使用的表 mysql> show tables like ‘%memory%’;

— 动态对performance_schema进行配置的配置表 mysql> show tables like ‘%setup%’;

<a name="BTbRU"></a>
#### 实战操作
```sql
-- 1、哪类的SQL执行最多？
SELECT DIGEST_TEXT, COUNT_STAR, FIRST_SEEN, LAST_SEEN
FROM events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC;
-- 2、哪类SQL的平均响应时间最多？
SELECT DIGEST_TEXT, AVG_TIMER_WAIT
FROM events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC;
-- 3、哪类SQL排序记录数最多？
SELECT DIGEST_TEXT, SUM_SORT_ROWS
FROM events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC;
-- 4、哪类SQL扫描记录数最多？
SELECT DIGEST_TEXT, SUM_ROWS_EXAMINED
FROM events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC;
-- 5、哪类SQL使用临时表最多？
SELECT DIGEST_TEXT, SUM_CREATED_TMP_TABLES, SUM_CREATED_TMP_DISK_TABLES
FROM events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC;
-- 6、哪类SQL返回结果集最多？
SELECT DIGEST_TEXT, SUM_ROWS_SENT
FROM events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC;
-- 7、哪个表物理IO最多？
SELECT file_name, event_name, SUM_NUMBER_OF_BYTES_READ, SUM_NUMBER_OF_BYTES_WRITE
FROM file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;
-- 8、哪个表逻辑IO最多？
SELECT object_name, COUNT_READ, COUNT_WRITE, COUNT_FETCH, SUM_TIMER_WAIT
FROM table_io_waits_summary_by_table
ORDER BY sum_timer_wait DESC;
-- 9、哪个索引访问最多？
SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
FROM table_io_waits_summary_by_index_usage
ORDER BY SUM_TIMER_WAIT DESC;
-- 10、哪个索引从来没有用过？
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_STAR = 0
  AND OBJECT_SCHEMA <> 'mysql'
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;
-- 11、哪个等待事件消耗时间最多？
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT
FROM events_waits_summary_global_by_event_name
WHERE event_name != 'idle'
ORDER BY SUM_TIMER_WAIT DESC;
-- 12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息
SELECT EVENT_ID, sql_text
FROM events_statements_history
WHERE sql_text LIKE '%count(*)%';
-- 12-2、查看每个阶段的时间消耗
SELECT event_id, EVENT_NAME, SOURCE, TIMER_END - TIMER_START
FROM events_stages_history_long
WHERE NESTING_EVENT_ID = 1553;
-- 12-3、查看每个阶段的锁等待情况
SELECT event_id,
       event_name,
       source,
       timer_wait,
       object_name,
       index_name,
       operation,
       nesting_event_id
FROM events_waits_history_long WHERE nesting_event_id = 1553;

MySQL使用profiles、performance_schema实现性能监控

Profile

使用方法

Performance Schema

使用方法