高性能 MySQL 第四版（GPT 重译）（一）(3)https://developer.aliyun.com/article/1484263
你可能还想了解读取和写入的字节数和行数。为此，使用全局状态变量 Handler_*：
mysql> WITH rows_read AS (SELECT SUM(VARIABLE_VALUE) AS rows_read -> FROM global_status -> WHERE 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_written -> FROM global_status -> WHERE VARIABLE_NAME IN ('Handler_write')) -> SELECT * FROM rows_read, rows_written\G *************************** 1\. row *************************** rows_read: 169358114082 rows_written: 33038251685 1 row in set (0.00 sec)
检查元数据锁
元数据锁用于保护数据库对象定义免受修改。任�� SQL 语句都会设置共享元数据锁：SELECT、UPDATE 等。它们不会影响其他需要共享元数据锁的语句。但是，它们会阻止那些改变数据库对象定义的语句（如 ALTER TABLE 或 CREATE INDEX）启动，直到锁被释放。虽然大多数由元数据锁冲突引起的问题影响表，但锁本身是为任何数据库对象设置的，如 SCHEMA、EVENT、TABLESPACE 等。
元数据锁会一直保持直到事务结束。如果使用多语句事务，这会使故障排除变得更加困难。哪个语句正在等待锁通常是明确的：DDL 语句会隐式提交事务，因此它们是新事务中唯一的语句，并且你会在进程列表中找到它们处于"等待元数据锁"状态。然而，持有锁的语句可能会在进程列表中消失，如果它是仍然打开的多语句事务的一部分。
performance_schema 中的 metadata_locks 表保存了不同线程当前设置的锁的信息，还保存了等待锁的锁请求信息。这样，你可以轻松地识别哪个线程不允许你的 DDL 请求启动，并决定是否要终止此语句或等待其执行完成。
要启用元数据锁仪表化，需要启用 wait/lock/metadata/sql/mdl 仪表。
以下示例显示了一个线程，在进程列表中以 ID 5 可见，持有了线程 processlist_id=4 正在等待的锁：
mysql> SELECT processlist_id, object_type, -> lock_type, lock_status, source -> FROM metadata_locks JOIN threads ON (owner_thread_id=thread_id) -> WHERE object_schema='employees' AND object_name='titles'\G *************************** 1\. row *************************** processlist_id: 4 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
检查内存使用情况
要在 performance_schema 中启用内存仪表化，需要启用 memory 类的仪表。一旦启用，你可以找到有关 MySQL 内部结构如何使用内存的详细信息。
直接使用 performance_schema
Performance Schema 将内存使用统计信息存储在以memory_summary_前缀开头的摘要表中。内存使用聚合在 Table 3-8 中描述。
表 3-8. 内存使用的聚合参数
| 聚合参数 | 描述 |
global |
每个事件名称的全局 |
thread |
每个线程：包括后台线程和用户线程 |
account |
用户账户 |
host |
主机 |
user |
用户名 |
例如，要找到使用大部分内存的 InnoDB 结构，请执行以下查询：
mysql> SELECT EVENT_NAME, -> CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS CURRENT_MB, -> HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS HIGH_MB -> FROM performance_schema.memory_summary_global_by_event_name -> WHERE EVENT_NAME LIKE 'memory/innodb/%' -> ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10; +----------------------------+--------------+--------------+ | EVENT_NAME | CURRENT_MB | HIGH_MB | +----------------------------+--------------+--------------+ | memory/innodb/buf_buf_pool | 130.68750000 | 130.68750000 | | memory/innodb/ut0link_buf | 24.00006104 | 24.00006104 | | memory/innodb/buf0dblwr | 17.07897949 | 24.96951294 | | memory/innodb/ut0new | 16.07891273 | 16.07891273 | | memory/innodb/sync0arr | 6.25006866 | 6.25006866 | | memory/innodb/lock0lock | 4.85086060 | 4.85086060 | | memory/innodb/ut0pool | 4.00003052 | 4.00003052 | | memory/innodb/hash0hash | 3.69776917 | 3.69776917 | | memory/innodb/os0file | 2.60422516 | 3.61988068 | | memory/innodb/memory | 1.23812866 | 1.42373657 | +----------------------------+--------------+--------------+ 10 rows in set (0,00 sec)
使用 sys 模式
sys模式具有视图，允许您以更好的方式获取内存统计信息。它们还支持按host、user、thread或global进行聚合。视图memory_global_total包含一个单一值，显示了被检测内存的总量：
mysql> SELECT * FROM sys.memory_global_total; +-----------------+ | total_allocated | +-----------------+ | 441.84 MiB | +-----------------+ 1 row in set (0,09 sec)
聚合视图将字节转换为需要的千字节、兆字节和千兆字节。视图memory_by_thread_by_current_bytes有一个user列，可能取以下值之一：
NAME@HOST
常规用户账户，比如sveta@oreilly.com。
系统用户，比如sql/main或innodb/*
此类“用户名”的数据来自threads表，当您需要了解特定线程在做什么时非常方便。
视图memory_by_thread_by_current_bytes中的行按照当前分配的内存量降序排序，因此您将轻松找到占用大部分内存的线程：
mysql> SELECT thread_id tid, user, -> current_allocated ca, total_allocated -> FROM sys.memory_by_thread_by_current_bytes LIMIT 9; +-----+----------------------------+------------+-----------------+ | tid | user | ca | total_allocated | +-----+----------------------------+------------+-----------------+ | 52 | sveta@localhost | 1.36 MiB | 10.18 MiB | | 1 | sql/main | 1.02 MiB | 4.95 MiB | | 33 | innodb/clone_gtid_thread | 525.36 KiB | 24.04 MiB | | 44 | sql/event_scheduler | 145.72 KiB | 4.23 MiB | | 43 | sql/slave_sql | 48.74 KiB | 142.46 KiB | | 42 | sql/slave_io | 20.03 KiB | 232.23 KiB | | 48 | sql/compress_gtid_table | 13.91 KiB | 17.06 KiB | | 25 | innodb/fts_optimize_thread | 1.92 KiB | 2.00 KiB | | 34 | innodb/srv_purge_thread | 1.56 KiB | 1.64 KiB | +-----+----------------------------+------------+-----------------+ 9 rows in set (0,03 sec)
上面的示例是在笔记本电脑上进行的；因此，数字并不描述生产服务器的情况。仍然清楚的是，本地连接使用了大部分内存，其次是主服务器进程。
当你需要找到占用最多内存的用户线程时，内存工具非常方便。在下面的示例中，一个用户连接分配了 36 GB 的 RAM，即使在现代高内存系统中也相当巨大：
mysql> SELECT * FROM sys.memory_by_thread_by_current_bytes -> ORDER BY current_allocated desc\G *************************** 1\. row *************************** thread_id: 152 user: lj@127.0.0.1 current_count_used: 325 current_allocated: 36.00 GiB current_avg_alloc: 113.43 MiB current_max_alloc: 36.00 GiB total_allocated: 37.95 GiB ...
检查变量
Performance Schema 将变量检测提升到一个新水平。它为以下内容提供了检测：
- 服务器变量
- 全局
- 会话，适用于所有当前打开的会话
- 来源，所有当前变量值的来源
- 状态变量
- 全局
- 会话，适用于所有当前打开的会话
- 按照聚合
- 主机
- 用户
- 账户
- 线程
- 用户变量
警告
在 5.7 版本之前，服务器和状态变量在information_schema中被检测。这种检测是有限的：它只允许跟踪全局和当前会话值。其他会话中的变量和状态信息，以及用户变量的信息是不可访问的。然而，出于向后兼容性的原因，MySQL 5.7 使用information_schema来跟踪变量。要启用对变量的performance_schema支持，您需要将配置变量show_compatibility_56设置为0。这个要求，以及information_schema中的变量表，在 8.0 版本中不再存在。
全局变量值存储在表global_variables中。当前会话的会话变量存储在表session_variables中。这两个表只有两列，列名自明：VARIABLE_NAME和VARIABLE_VALUE。
variables_by_thread表有一个额外的列，THREAD_ID，指示变量所属的线程。这使您可以找到将会话变量值设置为与默认配置不同的线程。
在下面的示例中，具有THREAD_ID=84的线程将变量tx_isolation设置为SERIALIZABLE，这可能导致事务获取的锁比使用默认级别时更多：
mysql> SELECT * FROM variables_by_thread -> WHERE VARIABLE_NAME='tx_isolation'; +-----------+---------------+-----------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+---------------+-----------------+ | 71 | tx_isolation | REPEATABLE-READ | | 83 | tx_isolation | REPEATABLE-READ | | 84 | tx_isolation | SERIALIZABLE | +-----------+---------------+-----------------+ 3 rows in set, 3 warnings (0.00 sec)
下面的示例找到所有具有与当前活动会话不同的会话变量值的线程：
mysql> SELECT vt2.THREAD_ID AS TID, vt2.VARIABLE_NAME, -> vt1.VARIABLE_VALUE AS MY_VALUE, -> vt2.VARIABLE_VALUE AS OTHER_VALUE -> FROM performance_schema.variables_by_thread vt1 -> JOIN performance_schema.threads t USING(THREAD_ID) -> JOIN performance_schema.variables_by_thread vt2 -> USING(VARIABLE_NAME) -> WHERE vt1.VARIABLE_VALUE != vt2.VARIABLE_VALUE -> AND t.PROCESSLIST_ID=@@pseudo_thread_id; +-----+--------------------+-------------------+--------------------+ | TID | VARIABLE_NAME | MY_VALUE | OTHER_VALUE | +-----+--------------------+-------------------+--------------------+ | 42 | max_allowed_packet | 67108864 | 1073741824 | | 42 | pseudo_thread_id | 22715 | 5 | | 42 | timestamp | 1626650242.678049 | 1626567255.695062 | | 43 | gtid_next | AUTOMATIC | NOT_YET_DETERMINED | | 43 | pseudo_thread_id | 22715 | 6 | | 43 | timestamp | 1626650242.678049 | 1626567255.707031 | +-----+--------------------+-------------------+--------------------+ 6 rows in set (0,01 sec)
全局和当前会话状态值分别存储在表global_status和session_status中。它们也只有两列：VARIABLE_NAME和VARIABLE_VALUE。
状态变量可以按用户帐户、主机、用户和线程进行聚合。在我看来，最有趣的聚合是按线程进行的，因为它可以快速识别哪个连接在服务器上造成了大部分资源压力。例如，以下代码片段清楚地显示了THREAD_ID=83的连接正在进行大部分写操作：
mysql> SELECT * FROM status_by_thread -> WHERE VARIABLE_NAME='Handler_write'; +-----------+---------------+----------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+---------------+----------------+ | 71 | Handler_write | 94 | | 83 | Handler_write | 4777777777 | -- Most writes | 84 | Handler_write | 101 | +-----------+---------------+----------------+ 3 rows in set (0.00 sec)
用户定义变量是通过SET @my_var = 'foo'创建的，并在表user_variables_by_thread中进行跟踪：
mysql> SELECT * FROM user_variables_by_thread; +-----------+---------------+----------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+---------------+----------------+ | 71 | baz | boo | | 84 | foo | bar | +-----------+---------------+----------------+ 2 rows in set (0.00 sec)
当您需要找出内存消耗的来源时，此工具非常有用，因为每个变量都需要字节来保存其值。您还可以使用此信息解决与持久��接、使用用户定义变量相关的棘手问题。最后但同样重要的是，此表是唯一的方法来查找您在自己会话中定义的变量。
表variables_info不包含任何变量值。相反，它包含有关服务器变量来源以及其他文档的信息，例如变量的默认最小值和最大值。SET_TIME列包含最新变量更改的时间戳。SET_HOST和SET_USER列标识设置变量的用户帐户。例如，要查找自服务器启动以来动态更改的所有变量，请运行：
mysql> SELECT * FROM performance_schema.variables_info -> WHERE VARIABLE_SOURCE = 'DYNAMIC'\G *************************** 1\. row *************************** VARIABLE_NAME: foreign_key_checks VARIABLE_SOURCE: DYNAMIC VARIABLE_PATH: MIN_VALUE: 0 MAX_VALUE: 0 SET_TIME: 2021-07-18 03:14:15.560745 SET_USER: NULL SET_HOST: NULL *************************** 2\. row *************************** VARIABLE_NAME: sort_buffer_size VARIABLE_SOURCE: DYNAMIC VARIABLE_PATH: MIN_VALUE: 32768 MAX_VALUE: 18446744073709551615 SET_TIME: 2021-07-19 02:37:11.948190 SET_USER: sveta SET_HOST: localhost 2 rows in set (0,00 sec)
可能的VARIABLE_SOURCE值包括：
COMMAND_LINE
在命令行上设置的变量
COMPILED
编译默认值
PERSISTED
从特定服务器的mysqld-auto.cnf选项文件设置
也有许多变量选项，设置在不同的选项文件中。我不会讨论它们全部：它们要么是自描述的，要么可以在用户参考手册中轻松查找。细节的数量也随着版本的增加而增加。
检查最频繁的错误
除了特定的错误信息，performance_schema还提供摘要表，通过用户、主机、帐户、线程以及全局按错误编号聚合错误。所有聚合表的结构与events_errors_summary_global_by_error表中使用的结构类似：
mysql> USE performance_schema; mysql> 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的列，依此类推。
例如，要查找所有运行导致错误超过 10 次的语句的帐户，请运行：
mysql> SELECT * FROM -> performance_schema.events_errors_summary_by_account_by_error -> WHERE SUM_ERROR_RAISED > 10 AND USER IS NOT NULL -> ORDER BY SUM_ERROR_RAISED DESC\G *************************** 1\. row *************************** USER: sveta HOST: localhost ERROR_NUMBER: 3554 ERROR_NAME: ER_NO_SYSTEM_TABLE_ACCESS SQL_STATE: HY000 SUM_ERROR_RAISED: 60 SUM_ERROR_HANDLED: 0 FIRST_SEEN: 2021-07-18 03:14:59 LAST_SEEN: 2021-07-19 02:50:13 1 row in set (0,01 sec)
错误摘要表对于查找哪些用户帐户、主机、用户或线程发送了最多错误查询并执行操作可能很有用。它们还可以帮助处理像ER_DEPRECATED_UTF8_ALIAS这样的错误，这可能表明一些经常使用的查询是为以前的 MySQL 版本编写的，需要更新。
检查性能模式本身
您可以使用与自己模式相同的工具和消费者检查性能模式本身。只需注意，默认情况下，如果将performance_schema设置为默认数据库，则不会跟踪对其的查询。如果需要检查对performance_schema的查询，首先需要更新setup_actors表。
一旦更新了setup_actors表，所有工具都可以使用。例如，要查找在performance_schema中分配了大部分内存的前 10 个消费者，请运行：
mysql> SELECT SUBSTRING_INDEX(EVENT_NAME, '/', -1) AS EVENT, -> CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS CURRENT_MB, -> HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS HIGH_MB -> FROM performance_schema.memory_summary_global_by_event_name -> WHERE EVENT_NAME LIKE 'memory/performance_schema/%' -> ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10; +----------------------------------------+-------------+-------------+ | EVENT | CURRENT_MB | HIGH_MB | +----------------------------------------+-------------+-------------+ | events_statements_summary_by_digest | 39.67285156 | 39.67285156 | | events_statements_history_long | 13.88549805 | 13.88549805 | | events_errors_summary_by_thread_by_... | 11.81640625 | 11.81640625 | | events_statements_summary_by_thread... | 9.79296875 | 9.79296875 | | events_statements_history_long.dige... | 9.76562500 | 9.76562500 | | events_statements_summary_by_digest... | 9.76562500 | 9.76562500 | | events_statements_history_long.sql_... | 9.76562500 | 9.76562500 | | memory_summary_by_thread_by_event_name | 7.91015625 | 7.91015625 | | events_errors_summary_by_host_by_error | 5.90820313 | 5.90820313 | | events_errors_summary_by_account_by... | 5.90820313 | 5.90820313 | +----------------------------------------+-------------+-------------+ 10 rows in set (0,00 sec)
或使用sys模式：
mysql> SELECT SUBSTRING_INDEX(event_name, '/', -1), current_alloc -> FROM sys.memory_global_by_current_bytes -> WHERE event_name LIKE 'memory/performance_schema/%' LIMIT 10; +---------------------------------------------------+---------------+ | SUBSTRING_INDEX(event_name, '/', -1) | 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.82 MiB | | events_statements_summary_by_thread_by_event_name | 9.79 MiB | | events_statements_history_long.digest_text | 9.77 MiB | | events_statements_summary_by_digest.digest_text | 9.77 MiB | | events_statements_history_long.sql_text | 9.77 MiB | | memory_summary_by_thread_by_event_name | 7.91 MiB | | events_errors_summary_by_host_by_error | 5.91 MiB | | events_errors_summary_by_account_by_error | 5.91 MiB | +---------------------------------------------------+---------------+ 10 rows in set (0,00 sec)
performance_schema还支持SHOW ENGINE PERFORMANCE_SCHEMA STATUS语句：
mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G *************************** 1\. row *************************** Type: performance_schema Name: events_waits_current.size Status: 176 *************************** 2\. row *************************** Type: performance_schema Name: events_waits_current.count Status: 1536 *************************** 3\. row *************************** Type: performance_schema Name: events_waits_history.size Status: 176 *************************** 4\. row *************************** Type: performance_schema Name: events_waits_history.count Status: 2560 … *************************** 244\. row *************************** Type: performance_schema Name: (pfs_buffer_scalable_container).count Status: 17 *************************** 245\. row *************************** Type: performance_schema Name: (pfs_buffer_scalable_container).memory Status: 1904 *************************** 246\. row *************************** Type: performance_schema Name: (max_global_server_errors).count Status: 4890 *************************** 247\. row *************************** Type: performance_schema Name: (max_session_server_errors).count Status: 1512 *************************** 248\. row *************************** Type: performance_schema Name: performance_schema.memory Status: 218456400 248 rows in set (0,00 sec)
在其输出中，您将找到诸如存储在消费者中的特定事件数量或特定指标的最大值等细节。最后一行包含性能模式当前占用的字节数。
总结
性能模式是一个经常受到批评的功能。MySQL 的早期版本实现不够优化，导致资源消耗较高。通常建议只需关闭它。
它也被认为难以理解。启用一个仪器只是在服务器中启用一个额外的代码片段，记录数据并将其提交给消费者。消费者只是存储在内存中的表，您需要使用标准 SQL 向表提出正确的问题，以找到您要查找的内容。了解性能模式如何管理自己的内存将帮助您意识到 MySQL 并非内存泄漏；它只是将消费者数据保留在内存中，并且只在重新启动时释放该内存。
我在这里的建议很简单：您应该保持性能模式启用，动态启用仪器和消费者，以帮助您解决可能存在的任何问题——查询性能、锁定、磁盘 I/O、错误等。您还应该利用sys模式作为解决最常见问题的捷径。这样做将为您提供一种直接从 MySQL 内部测量性能的可访问方式。
