本节通过示例简要介绍性能模式，展示如何使用它。有关其他示例，请参见第27.19节“使用性能模式诊断问题”．

默认情况下，性能架构是启用的。要显式启用或禁用它，请使用performance_schema变量设置为适当的值。例如，在服务器中使用这些行my.cnf文件:

(mysqld) performance_schema =

当服务器启动时，它会看到performance_schema并尝试初始化性能架构。要验证初始化是否成功，使用以下语句:

显示“performance_schema”变量;+--------------------+-------+ | Variable_name |值  | +--------------------+-------+ | performance_schema |上  | +--------------------+-------+

的值在表示性能模式已成功初始化，可以使用了。的值从表示发生了一些错误。查看服务器错误日志，了解哪里出了问题。

的输出中列出了性能架构，因此可以将其实现为存储引擎INFORMATION_SCHEMA。引擎表或显示引擎声明:

SELECT * FROM INFORMATION_SCHEMA。引擎WHERE 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默认数据库，因此对其表的引用不需要用数据库名限定:

mysql >使用performance_schema;

性能模式表存储在performance_schema数据库。和其他数据库一样，可以通过从INFORMATION_SCHEMA数据库或使用显示语句。例如，使用这些语句中的任何一个来查看存在哪些性能模式表:

SELECT TABLE_NAME FROM INFORMATION_SCHEMAWHERE TABLE_SCHEMA = 'performance_schema';+------------------------------------------------------+ | TABLE_NAME  | +------------------------------------------------------+ | 账户| | 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 | |主机| | 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 | | | |  | +------------------------------------------------------+ 从performance_schema mysql >显示表;+------------------------------------------------------+ | Tables_in_performance_schema  | +------------------------------------------------------+ | 账户| | cond_instances | | events_stages_current | | events_stages_history | | events_stages_history_long |…

性能模式表的数量随着时间的推移而增加，因为附加工具的实现正在进行。

的名称performance_schema数据库是小写的，其中的表名也是小写的。查询应该用小写字母指定名称。

要查看各个表的结构，请使用显示创建表：

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。列或者通过使用诸如显示列．

表performance_schema数据库可以根据其中的信息类型进行分组:当前事件、事件历史和摘要、对象实例和设置(配置)信息。下面的例子说明了这些表的一些用法。有关每组表的详细信息，请参见第27.12节“性能模式表说明”．

最初，并不是所有的工具和使用者都被启用，因此性能模式不会收集所有事件。要打开所有这些并启用事件计时，执行两个语句(行数可能根据MySQL版本不同而不同):

mysql >更新performance_schema。setup_instrumentsSET ENABLED = 'YES', TIMED = 'YES'; Query OK, 560 rows affected (0.04 sec) mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'; Query OK, 10 rows affected (0.00 sec)

要查看服务器当前正在做什么，请检查events_waits_current表格它包含每个线程的一行，显示每个线程最近监视的事件:

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:互斥的互斥锁mysys子系统。前几列提供以下信息:

历史表包含与当前事件表相同的行类型，但有更多的行，并显示服务器一直在做什么”最近”而不是”目前。”的events_waits_history而且events_waits_history_long表分别包含每个线程最近的10个事件和最近的10,000个事件。例如，要查看线程13产生的最近事件的信息，可以这样做:

SELECT EVENT_ID, EVENT_NAME, TIMER_WAIT FROM performance_schema。WHERE THREAD_ID = 13;+----------+-----------------------------------------+------------+ | EVENT_ID | EVENT_NAME | TIMER_WAIT  | +----------+-----------------------------------------+------------+ | 86 |等待/同步/互斥/ mysys / THR_LOCK::互斥| 686322 | 87 | |等待/同步/互斥/ mysys / THR_LOCK_malloc | 320535 | 88 | |等待/同步/互斥/ mysys / THR_LOCK_malloc | 339390 | 89 | |等待/同步/互斥/ mysys / THR_LOCK_malloc | 377100 | 90 | |等待/同步/互斥/ sql / LOCK_plugin | 614673 | 91 | |等待/同步/互斥/ 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 | +----------+-----------------------------------------+------------+

当新的事件被添加到历史表中时，如果表已满，旧的事件将被丢弃。

汇总表提供了一段时间内所有事件的聚合信息。该组中的表以不同的方式总结事件数据。要查看哪些工具被执行次数最多或等待时间最长，请对events_waits_summary_global_by_event_name表上的COUNT_STAR或SUM_TIMER_WAIT列，对应于aCOUNT (*)或总和(TIMER_WAIT)值，分别在所有事件上计算:

mysql> SELECT EVENT_NAME, COUNT_STAR FROM 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_WAIT FROM performance_schema.events_waits_summary_global_by_event_name ORDER 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互斥对象是”热,”无论是使用它的频率，还是线程等待获取它的时间。

实例表记录了检测对象的类型。当服务器使用仪器化对象时，会产生一个事件。这些表提供事件名称和解释性说明或状态信息。例如,file_instances表列出了文件I/O操作的工具实例及其相关文件:

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列出可以收集事件的工具集，并显示哪些工具是启用的:

SELECT NAME, ENABLED, TIMED FROM performance_schema.setup_instruments;+---------------------------------------------------+---------+-------+ | 名字| |定时启用  | +---------------------------------------------------+---------+-------+ ...| stage/sql/end | NO | NO | | stage/sql/ execution | 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/sync /mutex/sql/LOCK_global_read_lock | YES | YES | | wait/sync /mutex/sql/LOCK_global_system_variables | YES | YES | | wait/sync /mutex/sql/LOCK_lock_db | YES | YES | | wait/sync /mutex/sql/LOCK_manager | YES | YES |…| wait/sync /rwlock/sql/LOCK_grant | YES | YES | | wait/sync /rwlock/sql/LOGGER::LOCK_logger | YES | YES | | wait/sync /rwlock/sql/LOCK_sys_init_connect | YES | YES | | wait/sync /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 |…

要了解如何解释乐器名称，请参阅第27.6节，“性能模式仪器命名约定”．

若要控制是否为仪器收集事件，请设置其启用价值是的或没有．例如:

mysql >更新performance_schema。setup_instrumentsSET ENABLED = 'NO' WHERE NAME = 'wait/synch/mutex/sql/LOCK_mysql_create_db';

中的表使用收集的事件performance_schema数据库，它们充当”消费者”的事件信息。的setup_consumers表中列出了可用的消费者和已启用的消费者:

SELECT * FROM performance_schema.setup_consumers;+----------------------------------+---------+ | 启用名字|  | +----------------------------------+---------+ | events_stages_current没有| | | events_stages_history没有| | | events_stages_history_long没有| | | events_statements_current |是| | events_statements_history |是| | events_statements_history_long没有| | | events_transactions_current |是| | events_transactions_history |是| | events_transactions_history_long没有| | | events_waits_current没有| | | events_waits_history | ||events_waits_history_long|没有||global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+

若要控制性能模式是否将使用者维护为事件信息的目的地，请设置其启用价值。

有关设置表以及如何使用它们控制事件收集的详细信息，请参见第27.4.2节“性能模式事件过滤”．

有一些杂项表不属于前面的任何组。例如,performance_timers列出可用的事件计时器及其特征。有关计时器的信息，请参见第27.4.1节“性能模式事件定时”．