MySQL 8.4 Release Notes
本节通过示例简要介绍了 Performance Schema 的使用方法。有关更多示例，请参见 第 29.19 节，“使用 Performance Schema 诊断问题”。This section briefly introduces the Performance Schema with examples that show how to use it. For additional examples, see Section 29.19, “Using the Performance Schema to Diagnose Problems”.
性能模式（Performance Schema）默认启用。要显式启用或禁用它，请以 performance_schema 变量设置为适当的值来启动服务器。例如，在服务器 my.cnf 文件中使用这些行：The Performance Schema is enabled by default. To enable or disable
it explicitly, start the server with the
performance_schema variable set
to an appropriate value. For example, use these lines in the
server my.cnf file:
[mysqld] performance_schema=ON
当服务器启动时，它会检测 performance_schema 并尝试初始化性能模式。要验证初始化是否成功，请使用以下语句：When the server starts, it sees
performance_schema and attempts
to initialize the Performance Schema. To verify successful
initialization, use this statement:
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
值为 ON 表示性能模式已成功初始化并可以使用。值为 OFF 表示发生了一些错误。请检查服务器错误日志以获取错误详情。A value of ON means that the Performance Schema
initialized successfully and is ready for use. A value of
OFF means that some error occurred. Check the
server error log for information about what went wrong.
性能模式（Performance Schema）作为存储引擎实现，因此您可以在信息模式（Information Schema）的 ENGINES 表或 SHOW ENGINES 语句的输出中看到它：The Performance Schema is implemented as a storage engine, so you
can see it listed in the output from the Information Schema
ENGINES table or the
SHOW ENGINES statement:
mysql>SELECT * FROM INFORMATION_SCHEMA.ENGINESWHERE ENGINE='PERFORMANCE_SCHEMA'\G*************************** 1. row *************************** ENGINE: PERFORMANCE_SCHEMA SUPPORT: YES COMMENT: Performance Schema TRANSACTIONS: NO XA: NO SAVEPOINTS: NO mysql>SHOW ENGINES\G... Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO ...
PERFORMANCE_SCHEMA 存储引擎操作 performance_schema 数据库中的表。您可以将 performance_schema 设为默认数据库，这样对其表的引用就不需要限定数据库名称：The PERFORMANCE_SCHEMA storage engine
operates on tables in the performance_schema
database. You can make performance_schema the
default database so that references to its tables need not be
qualified with the database name:
mysql> USE performance_schema;
Performance Schema 表存储在 performance_schema 数据库中。关于该数据库及其表结构的信息，可以像查询其他数据库一样，通过从 INFORMATION_SCHEMA 数据库中选择数据或使用 SHOW 语句来获取。例如，可以使用以下任一语句来查看存在哪些 Performance Schema 表：Performance Schema tables are stored in the
performance_schema database. Information about
the structure of this database and its tables can be obtained, as
for any other database, by selecting from the
INFORMATION_SCHEMA database or by using
SHOW statements. For example, use
either of these statements to see what Performance Schema tables
exist:
mysql>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'performance_schema';+------------------------------------------------------+ | TABLE_NAME | +------------------------------------------------------+ | accounts | | cond_instances | ... | events_stages_current | | events_stages_history | | events_stages_history_long | | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | | events_statements_current | | events_statements_history | | events_statements_history_long | ... | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | host_cache | | hosts | | memory_summary_by_account_by_event_name | | memory_summary_by_host_by_event_name | | memory_summary_by_thread_by_event_name | | memory_summary_by_user_by_event_name | | memory_summary_global_by_event_name | | metadata_locks | | mutex_instances | | objects_summary_global_by_type | | performance_timers | | replication_connection_configuration | | replication_connection_status | | replication_applier_configuration | | replication_applier_status | | replication_applier_status_by_coordinator | | replication_applier_status_by_worker | | rwlock_instances | | session_account_connect_attrs | | session_connect_attrs | | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | socket_instances | | socket_summary_by_event_name | | socket_summary_by_instance | | table_handles | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | | threads | | users | +------------------------------------------------------+ mysql>SHOW TABLES FROM performance_schema;+------------------------------------------------------+ | Tables_in_performance_schema | +------------------------------------------------------+ | accounts | | cond_instances | | events_stages_current | | events_stages_history | | events_stages_history_long | ...
随着额外监控实现的推进，Performance Schema 表的数量会随时间增加。The number of Performance Schema tables increases over time as implementation of additional instrumentation proceeds.
performance_schema 数据库的名称为小写，其内表的名称也是小写。查询时应使用小写名称。The name of the performance_schema database is
lowercase, as are the names of tables within it. Queries should
specify the names in lowercase.
要查看单个表的结构，请使用 SHOW CREATE TABLE：To see the structure of individual tables, use
SHOW CREATE TABLE:
mysql> SHOW CREATE TABLE performance_schema.setup_consumers\G
*************************** 1. row ***************************
Table: setup_consumers
Create Table: CREATE TABLE `setup_consumers` (
`NAME` varchar(64) NOT NULL,
`ENABLED` enum('YES','NO') NOT NULL,
PRIMARY KEY (`NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
也可以通过从表中选择数据来查看表结构，例如 INFORMATION_SCHEMA.COLUMNS，或者使用诸如 SHOW
COLUMNS 的语句。Table structure is also available by selecting from tables such as
INFORMATION_SCHEMA.COLUMNS or by
using statements such as SHOW
COLUMNS.
performance_schema 数据库中的表可以根据其中的信息类型进行分组：当前事件、事件历史和摘要、对象实例以及设置（配置）信息。以下示例说明了这些表的一些用途。有关每组表的详细信息，请参见 第 29.12 节，“性能模式表说明”。Tables in the performance_schema database can
be grouped according to the type of information in them: Current
events, event histories and summaries, object instances, and setup
(configuration) information. The following examples illustrate a
few uses for these tables. For detailed information about the
tables in each group, see
Section 29.12, “Performance Schema Table Descriptions”.
起初，并非所有工具和消费者都已启用，因此性能模式不会收集所有事件。要启用所有这些并启用事件计时，请执行两条语句（行数可能因 MySQL 版本而异）：Initially, not all instruments and consumers are enabled, so the performance schema does not collect all events. To turn all of these on and enable event timing, execute two statements (the row counts may differ depending on MySQL version):
mysql>UPDATE performance_schema.setup_instrumentsSET ENABLED = 'YES', TIMED = 'YES';Query OK, 560 rows affected (0.04 sec) mysql>UPDATE performance_schema.setup_consumersSET ENABLED = 'YES';Query OK, 10 rows affected (0.00 sec)
要查看服务器当前正在做什么，请检查 events_waits_current 表。该表包含每个线程的一行数据，显示每个线程最近监控到的事件：To see what the server is doing at the moment, examine the
events_waits_current table. It
contains one row per thread showing each thread's most recent
monitored event:
mysql>SELECT *FROM performance_schema.events_waits_current\G*************************** 1. row *************************** THREAD_ID: 0 EVENT_ID: 5523 END_EVENT_ID: 5523 EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK::mutex SOURCE: thr_lock.c:525 TIMER_START: 201660494489586 TIMER_END: 201660494576112 TIMER_WAIT: 86526 SPINS: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL INDEX_NAME: NULL OBJECT_TYPE: NULL OBJECT_INSTANCE_BEGIN: 142270668 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL OPERATION: lock NUMBER_OF_BYTES: NULL FLAGS: 0 ...
该事件表明线程 0 正在等待 86,526 皮秒以获取 THR_LOCK::mutex 上的锁，这是 mysys 子系统中的一个互斥锁。前几列提供了以下信息：This event indicates that thread 0 was waiting for 86,526
picoseconds to acquire a lock on
THR_LOCK::mutex, a mutex in the
mysys subsystem. The first few columns provide
the following information:
ID 列表示事件来自哪个线程以及事件编号。The ID columns indicate which thread the event comes from and the event number.
EVENT_NAME表示被检测的内容，SOURCE表示包含检测代码的源文件。EVENT_NAMEindicates what was instrumented andSOURCEindicates which source file contains the instrumented code.计时器列显示事件的开始和结束时间以及所花费的时间。如果事件仍在进行中，
TIMER_END和TIMER_WAIT的值为NULL。计时器值为近似值，以皮秒表示。有关计时器和事件时间收集的信息，请参见 第 29.4.1 节，“性能模式事件计时”。The timer columns show when the event started and stopped and how long it took. If an event is still in progress, theTIMER_ENDandTIMER_WAITvalues areNULL. Timer values are approximate and expressed in picoseconds. For information about timers and event time collection, see Section 29.4.1, “Performance Schema Event Timing”.
历史表包含与当前事件表相同类型的行，但行数更多，并显示服务器最近的操作“recently”而不是“currently.” events_waits_history和events_waits_history_long表分别包含每个线程最近的 10 个事件和最近的 10,000 个事件。例如，要查看线程 13 生成的最近事件的信息，请执行以下操作：The history tables contain the same kind of rows as the
current-events table but have more rows and show what the server
has been doing “recently” rather than
“currently.” The
events_waits_history and
events_waits_history_long tables
contain the most recent 10 events per thread and most recent
10,000 events, respectively. For example, to see information for
recent events produced by thread 13, do this:
mysql>SELECT EVENT_ID, EVENT_NAME, TIMER_WAITFROM performance_schema.events_waits_historyWHERE THREAD_ID = 13ORDER BY EVENT_ID;+----------+-----------------------------------------+------------+ | EVENT_ID | EVENT_NAME | TIMER_WAIT | +----------+-----------------------------------------+------------+ | 86 | wait/synch/mutex/mysys/THR_LOCK::mutex | 686322 | | 87 | wait/synch/mutex/mysys/THR_LOCK_malloc | 320535 | | 88 | wait/synch/mutex/mysys/THR_LOCK_malloc | 339390 | | 89 | wait/synch/mutex/mysys/THR_LOCK_malloc | 377100 | | 90 | wait/synch/mutex/sql/LOCK_plugin | 614673 | | 91 | wait/synch/mutex/sql/LOCK_open | 659925 | | 92 | wait/synch/mutex/sql/THD::LOCK_thd_data | 494001 | | 93 | wait/synch/mutex/mysys/THR_LOCK_malloc | 222489 | | 94 | wait/synch/mutex/mysys/THR_LOCK_malloc | 214947 | | 95 | wait/synch/mutex/mysys/LOCK_alarm | 312993 | +----------+-----------------------------------------+------------+
当新的事件被添加到历史表中时，如果表已满，较旧的事件将被丢弃。As new events are added to a history table, older events are discarded if the table is full.
汇总表提供了所有事件随时间的聚合信息。该组中的表以不同方式汇总事件数据。要查看哪些工具被执行次数最多或等待时间最长，可以按 COUNT_STAR 或 SUM_TIMER_WAIT 列对 events_waits_summary_global_by_event_name 表进行排序，这两列分别对应对所有事件计算的 COUNT(*) 或 SUM(TIMER_WAIT) 值：Summary tables provide aggregated information for all events over
time. The tables in this group summarize event data in different
ways. To see which instruments have been executed the most times
or have taken the most wait time, sort the
events_waits_summary_global_by_event_name
table on the COUNT_STAR or
SUM_TIMER_WAIT column, which correspond to a
COUNT(*) or SUM(TIMER_WAIT)
value, respectively, calculated over all events:
mysql>SELECT EVENT_NAME, COUNT_STARFROM performance_schema.events_waits_summary_global_by_event_nameORDER BY COUNT_STAR DESC LIMIT 10;+---------------------------------------------------+------------+ | EVENT_NAME | COUNT_STAR | +---------------------------------------------------+------------+ | wait/synch/mutex/mysys/THR_LOCK_malloc | 6419 | | wait/io/file/sql/FRM | 452 | | wait/synch/mutex/sql/LOCK_plugin | 337 | | wait/synch/mutex/mysys/THR_LOCK_open | 187 | | wait/synch/mutex/mysys/LOCK_alarm | 147 | | wait/synch/mutex/sql/THD::LOCK_thd_data | 115 | | wait/io/file/myisam/kfile | 102 | | wait/synch/mutex/sql/LOCK_global_system_variables | 89 | | wait/synch/mutex/mysys/THR_LOCK::mutex | 89 | | wait/synch/mutex/sql/LOCK_open | 88 | +---------------------------------------------------+------------+ mysql>SELECT EVENT_NAME, SUM_TIMER_WAITFROM performance_schema.events_waits_summary_global_by_event_nameORDER BY SUM_TIMER_WAIT DESC LIMIT 10;+----------------------------------------+----------------+ | EVENT_NAME | SUM_TIMER_WAIT | +----------------------------------------+----------------+ | wait/io/file/sql/MYSQL_LOG | 1599816582 | | wait/synch/mutex/mysys/THR_LOCK_malloc | 1530083250 | | wait/io/file/sql/binlog_index | 1385291934 | | wait/io/file/sql/FRM | 1292823243 | | wait/io/file/myisam/kfile | 411193611 | | wait/io/file/myisam/dfile | 322401645 | | wait/synch/mutex/mysys/LOCK_alarm | 145126935 | | wait/io/file/sql/casetest | 104324715 | | wait/synch/mutex/sql/LOCK_plugin | 86027823 | | wait/io/file/sql/pid | 72591750 | +----------------------------------------+----------------+
这些结果显示 THR_LOCK_malloc 互斥锁在使用频率以及线程等待获取它的时间方面都是“热点”。These results show that the THR_LOCK_malloc
mutex is “hot,” both in terms of how often it is used
and amount of time that threads wait attempting to acquire it.
Note
THR_LOCK_malloc 互斥锁仅在调试构建中使用。在生产构建中，它并不活跃，因为它不存在。The THR_LOCK_malloc mutex is used only in
debug builds. In production builds it is not hot because it is
nonexistent.
实例表记录了哪些类型的对象被监控。被监控的对象在服务器使用时会产生事件。这些表提供事件名称以及说明性注释或状态信息。例如，file_instances 表列出了文件 I/O 操作的监控实例及其相关联的文件：Instance tables document what types of objects are instrumented.
An instrumented object, when used by the server, produces an
event. These tables provide event names and explanatory notes or
status information. For example, the
file_instances table lists instances
of instruments for file I/O operations and their associated files:
mysql>SELECT *FROM performance_schema.file_instances\G*************************** 1. row *************************** FILE_NAME: /opt/mysql-log/60500/binlog.000007 EVENT_NAME: wait/io/file/sql/binlog OPEN_COUNT: 0 *************************** 2. row *************************** FILE_NAME: /opt/mysql/60500/data/mysql/tables_priv.MYI EVENT_NAME: wait/io/file/myisam/kfile OPEN_COUNT: 1 *************************** 3. row *************************** FILE_NAME: /opt/mysql/60500/data/mysql/columns_priv.MYI EVENT_NAME: wait/io/file/myisam/kfile OPEN_COUNT: 1 ...
配置表用于配置和显示监控特性。例如，setup_instruments 列出了可以收集事件的一组工具，并显示其中哪些已启用：Setup tables are used to configure and display monitoring
characteristics. For example,
setup_instruments lists the set of
instruments for which events can be collected and shows which of
them are enabled:
mysql>SELECT NAME, ENABLED, TIMEDFROM performance_schema.setup_instruments;+---------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +---------------------------------------------------+---------+-------+ ... | stage/sql/end | NO | NO | | stage/sql/executing | NO | NO | | stage/sql/init | NO | NO | | stage/sql/insert | NO | NO | ... | statement/sql/load | YES | YES | | statement/sql/grant | YES | YES | | statement/sql/check | YES | YES | | statement/sql/flush | YES | YES | ... | wait/synch/mutex/sql/LOCK_global_read_lock | YES | YES | | wait/synch/mutex/sql/LOCK_global_system_variables | YES | YES | | wait/synch/mutex/sql/LOCK_lock_db | YES | YES | | wait/synch/mutex/sql/LOCK_manager | YES | YES | ... | wait/synch/rwlock/sql/LOCK_grant | YES | YES | | wait/synch/rwlock/sql/LOGGER::LOCK_logger | YES | YES | | wait/synch/rwlock/sql/LOCK_sys_init_connect | YES | YES | | wait/synch/rwlock/sql/LOCK_sys_init_slave | YES | YES | ... | wait/io/file/sql/binlog | YES | YES | | wait/io/file/sql/binlog_index | YES | YES | | wait/io/file/sql/casetest | YES | YES | | wait/io/file/sql/dbopt | YES | YES | ...
要了解如何解释工具名称，请参阅 第 29.6 节，“性能模式工具命名约定”。To understand how to interpret instrument names, see Section 29.6, “Performance Schema Instrument Naming Conventions”.
要控制是否为某个工具收集事件，请将其 ENABLED 值设置为 YES 或 NO。例如：To control whether events are collected for an instrument, set its
ENABLED value to YES or
NO. For example:
mysql>UPDATE performance_schema.setup_instrumentsSET ENABLED = 'NO'WHERE NAME = 'wait/synch/mutex/sql/LOCK_mysql_create_db';
性能模式使用收集到的事件来更新 performance_schema 数据库中的表，这些表充当事件信息的“消费者”。setup_consumers 表列出了可用的消费者及其启用状态：The Performance Schema uses collected events to update tables in
the performance_schema database, which act as
“consumers” of event information. The
setup_consumers table lists the
available consumers and which are enabled:
mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_cpu | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | YES |
| events_transactions_history | YES |
| events_transactions_history_long | NO |
| events_waits_current | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+----------------------------------+---------+
要控制性能模式是否将某个消费者作为事件信息的目标，请设置其 ENABLED 值。To control whether the Performance Schema maintains a consumer as
a destination for event information, set its
ENABLED value.
有关 setup 表以及如何使用它们来控制事件收集的更多信息，请参见 第 29.4.2 节，“性能模式事件过滤”。For more information about the setup tables and how to use them to control event collection, see Section 29.4.2, “Performance Schema Event Filtering”.
有一些杂项表格不属于前面任何一个组。例如，performance_timers 列出了可用的事件计时器及其特性。有关计时器的信息，请参见 第 29.4.1 节，“Performance Schema 事件计时”。There are some miscellaneous tables that do not fall into any of
the previous groups. For example,
performance_timers lists the
available event timers and their characteristics. For information
about timers, see Section 29.4.1, “Performance Schema Event Timing”.