MySQL中Performance Schema库的详解（下）

昨天说了关于SQL语句相关的，今天来说说性能相关的，如果没有看过上篇请点传送门https://blog.csdn.net/2301_80479959/article/details/144693574?fromshare=blogdetail&sharetype=blogdetail&sharerId=144693574&sharerefer=PC&sharesource=2301_80479959&sharefrom=from_link

检查读写性能

Performance Schema中的statement类型的插桩对于理解工作负载是受读还是受写限制非常有用。

统计各类型语句的执行次数

select EVENT_NAME, count(EVENT_NAME) 
from events_statements_history_long group by EVENT_NAME;

SELECT查询的数量大于任何其他查询的数量。这表明该场景中的大多数查询都是读查询

语句延迟情况

SELECT EVENT_NAME, COUNT(EVENT_NAME) AS count, SUM(LOCK_TIME / 1000000) AS latency_ms 
FROM events_statements_history 
GROUP BY EVENT_NAME 
ORDER BY latency_ms DESC;#结果如下
+--------------------------+-------+------------+
| EVENT_NAME               | count | latency_ms |
+--------------------------+-------+------------+
| statement/sql/select     |     2 |   295.0000 |
| statement/com/Field List |     7 |     0.0000 |
| statement/sql/error      |     1 |     0.0000 |
+--------------------------+-------+------------+

读取和写入的字节数和行数

WITH rowS_read AS (SELECT SUM(VARIABLE_VALUE) AS rowS_readFROM global_statusWHERE VARIABLE_NAME IN ('Handler_read_first', 'Handler_read_key', 'Handler_read_next', 'Handler_read_last', 'Handler_read_prev', 'Handler_read_rnd', 'Handler_read_rnd_next')
),
rowS_written AS (SELECT SUM(VARIABLE_VALUE) AS rowS_writtenFROM global_statusWHERE VARIABLE_NAME = 'Handler_write'
)
SELECT *
FROM rowS_read, rowS_written\G
​
#结果如下
*************************** 1. row ***************************rowS_read: 18472 #表示自数据库启动以来，通过不同的处理器（handler）读取的行总数为18,472。rowS_written:    #24 表示自数据库启动以来，通过处理器写入的行总数为24。
1 row in set (0.01 sec)

检查元数据锁

performance_schema中的metadata_locks表包含关于当前由不同线程设置的锁的信息，以及处于等待状态的锁请求信息。通过这种方式，可以轻松确定哪个线程阻塞了DDL请求，你可以决定是终止该语句还是等待它完成执行。

要启用元数据锁监测，需要启用wait/lock/meta-data/sql/mdl插桩。

例如：

SELECT processlist_id, object_type, lock_type, lock_status, source
FROM metadata_locks
JOIN threads ON metadata_locks.owner_thread_id = threads.thread_id
WHERE object_schema = 'employees' AND object_name = 'titles'\G
​
#结果如下
*************************** 1. row ***************************
processlist_id: 4       #进程列表ID
object_type: TABLE      #对象类型
lock_type: EXCLUSIVE    #锁类型
lock_statuS: PENDING -- waits   #锁状态
source:mdl.cc:3263
*************************** 2. row ***************************
processlist_id: 5
object_type: TABLE
lock_type: SHARED_READ
lock_status: GRANTED -- holds
source:sql_parse.cc:5707
​
在ID为5的进程列表中可见的线程持有processlist_id=4的线程正在等待的锁

检查内存使用情况

使用sys schema

使用Sys schema中的视图可以更好地获取内存统计信息，可以按host、user、thread或global进行聚合。memory_global_total视图包含一个单独的值

例如：

select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 426.80 MiB      |
+-----------------+
1 row in set (0.00 sec)

视图memory_by_thread_by_current_bytes中的行是按照当前分配的内存降序排序的，所以很容易就能找到哪个线程占用了大部分内存

例如：

SELECT thread_id AS tid, user, current_allocated AS ca, total_allocated
FROM sys.memory_by_thread_by_current_bytes LIMIT 10;
​
#結果如下
+-----+--------------------------------------+------------+-----------------+
| tid | user                                 | ca         | total_allocated |
+-----+--------------------------------------+------------+-----------------+
|   1 | sql/main                             | 977.01 KiB | 4.72 MiB        |
|  48 | root@localhost                       | 942.46 KiB | 34.94 MiB       |
|  33 | innodb/clone_gtid_thread             | 410.10 KiB | 20.54 MiB       |
|  42 | sql/event_scheduler                  | 16.38 KiB  | 31.62 KiB       |
|  46 | sql/compress_gtid_table              | 13.80 KiB  | 16.78 KiB       |
|  25 | innodb/fts_optimize_thread           | 1.79 KiB   | 1.88 KiB        |
|  13 | innodb/page_flush_coordinator_thread | 1.38 KiB   | 5.91 KiB        |
|  40 | innodb/srv_worker_thread             | 1.29 KiB   | 14.29 KiB       |
|  38 | innodb/srv_worker_thread             | 1.29 KiB   | 9.74 KiB        |
|  34 | innodb/srv_purge_thread              | 1.26 KiB   | 1.35 KiB        |
+-----+--------------------------------------+------------+-----------------+
10 rows in set (0.03 sec)

检查最常见的错误

除了特定错误信息，performance_schema还提供摘要表，可以按用户、主机、账户、线程和错误号聚合错误信息。所有的聚合表都有类似于events_errors_summary_global_by_error表的结构：

例如：

use performance_schema
show create table events_errors_summary_global_by_error \G
​
#结果如下
*************************** 1. row ***************************Table: events_errors_summary_global_by_error
Create Table: CREATE TABLE `events_errors_summary_global_by_error` (`ERROR_NUMBER` int DEFAULT NULL,`ERROR_NAME` varchar(64) DEFAULT NULL,`SQL_STATE` varchar(5) DEFAULT NULL,`SUM_ERROR_RAISED` bigint unsigned NOT NULL,`SUM_ERROR_HANDLED` bigint unsigned NOT NULL,`FIRST_SEEN` timestamp NULL DEFAULT '0000-00-00 00:00:00',`LAST_SEEN` timestamp NULL DEFAULT '0000-00-00 00:00:00',UNIQUE KEY `ERROR_NUMBER` (`ERROR_NUMBER`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

可通过列ERROR_NUMBER、ERROR_NAME和SQL_STATE来识别错误。SUM_ERROR_RAISED是错误发生的次数。SUM_ERROR_HANDLED是错误被处理的次数。FIRST_SEEN和LAST_SEEN是错误第一次发生和最后一次发生的时间戳。

有些聚合表有额外的列。表events_errors_summary_by_thread_by_error有一个名为THREAD_ID的列，标识了引发错误的线程，表events_errors_summary_by_host_by_error有一个名为HOST的列，依此类推。

例如：

检查performance_schema的相关信息

使用sys schema检查performance_schema中消耗内存最多的10个表

SELECT SUBSTRING_INDEX(event_name, '/', -1) AS event_name_part, current_alloc
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE 'memory/performance_schema/%'
LIMIT 10;
​
#结果如下
+---------------------------------------------------+---------------+
| event_name_part                                   | current_alloc |
+---------------------------------------------------+---------------+
| events_statements_summary_by_digest               | 39.67 MiB     |
| events_statements_history_long                    | 13.89 MiB     |
| events_errors_summary_by_thread_by_error          | 11.93 MiB     |
| events_statements_summary_by_thread_by_event_name | 9.79 MiB      |
| events_statements_summary_by_digest.digest_text   | 9.77 MiB      |
| events_statements_history_long.digest_text        | 9.77 MiB      |
| events_statements_history_long.sql_text           | 9.77 MiB      |
| table_handles                                     | 9.06 MiB      |
| memory_summary_by_thread_by_event_name            | 7.91 MiB      |
| events_errors_summary_by_host_by_error            | 5.96 MiB      |
+---------------------------------------------------+---------------+
10 rows in set (0.00 sec)

获取performance_schema的相关信息

mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS \G
​
#结果如下
*************************** 1. row ***************************Type: performance_schemaName: events_waits_current.size
Status: 176
*************************** 2. row ***************************Type: performance_schemaName: events_waits_current.count
Status: 1536
*************************** 3. row ***************************Type: performance_schemaName: events_waits_history.size
Status: 176
*************************** 4. row ***************************Type: performance_schemaName: events_waits_history.count
Status: 2560
*************************** 5. row ***************************Type: performance_schemaName: events_waits_history.memory
Status: 450560

在输出中可以发现一些细节，比如消费者表中存储了多少特定事件，或者特定度量的最大值。最后一行包含Performance Schema当前占用的字节数。

小结

Performance Schema将语句指标存储在events_statements_current、events_statements_history和events_statements_history_long表中`

而sys提供了可用于查找有问题语句的视图，statements_with_errors_or_warnings列出了带有错误和警告的所有语句，statements_with_full_table_scans列出了需要全表扫描的所有语句

prepared_statements_instances表包含服务器中存在的所有预处理语句，metadata_locks表包含关于当前由不同线程设置的锁的信息，events_errors_summary_global_by_error表可以按用户、主机、账户、线程和错误号聚合错误信息