性能架构快速入门
本节简要介绍性能模式,并提供示例说明如何使用它。有关其他示例,请参见“使用性能模式诊断问题”。
默认情况下,性能模式处于启用状态。要明确启用或禁用它,请在将performance_schema
变量设置为适当值的情况下启动服务器。例如,在服务器my.cnf
文件中使用以下行:
[mysqld] performance_schema=ON
服务器启动时,它将看到performance_schema
并尝试初始化性能模式。要验证初始化是否成功,请使用以下语句:
mysql>SHOW VARIABLES LIKE 'performance_schema'; +-------------------- +------- + | Variable_name | Value | +-------------------- +------- + | performance_schema | ON | +-------------------- +------- +
值ON
表示性能模式已成功初始化并且可以使用。值OFF
表示发生了一些错误。检查服务器错误日志,以获取有关发生问题的信息。
Performance Schema是作为存储引擎实现的,因此您将在INFORMATION_SCHEMA.ENGINES
表或SHOW ENGINES
语句的输出中看到它:
mysql>SELECT *FROM INFORMATION_SCHEMA.ENGINES 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: YESComment : PerformanceSchema Transactions:NO XA :NO Savepoints:NO ...
在PERFORMANCE_SCHEMA
存储引擎上的表进行操作performance_schema
数据库。您可以performance_schema
建立默认数据库,以便对其表的引用无需使用数据库名称进行限定:
mysql>USE performance_schema;
性能架构表存储在performance_schema
数据库中。与任何其他数据库一样,可以通过从INFORMATION_SCHEMA
数据库中选择或使用SHOW
语句来获取有关此数据库及其表的结构的信息。例如,使用以下任一语句来参见存在哪些性能模式表:
mysql>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 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
数据库的名称是小写的,其中的表的名称也是小写的。查询应以小写形式指定名称。
要参见单个表的结构,请使用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
。
performance_schema
数据库中的表可以根据其中的信息类型进行分组:当前事件,事件历史记录和摘要,对象实例以及设置(配置)信息。以下示例说明了这些表的一些用法。有关每个组中表的详细信息,请参见“性能模式表说明”。
最初,并非所有工具和使用者都已启用,因此性能架构不会收集所有事件。要打开所有这些功能并启用事件计时,请执行两个语句(行数可能因MySQL版本而异):
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
表。每个线程包含一行,显示每个线程的最新监视事件:
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
子系统中的互斥锁的锁定。前几列提供以下信息:
- ID列指示事件来自哪个线程以及事件编号。
EVENT_NAME
指示已检测的内容,并SOURCE
指示哪个源文件包含已检测的代码。- 计时器列显示事件何时开始和停止以及花费了多长时间。如果事件仍在进行中,则
TIMER_END
和TIMER_WAIT
值为NULL
。计时器值是近似值,以皮秒表示。有关计时器和事件时间收集的信息,请参见“性能架构事件计时”。
历史表中包含同一种行作为当前事件表,但有更多的行,并显示服务器已经做什么“近日”而非“目前。”在events_waits_history
和events_waits_history_long
表包含每个线程的最近10个事件和最近10,000个事件,分别。例如,要参见线程13产生的最近事件的信息,请执行以下操作:
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 | +---------- +----------------------------------------- +------------ +
当新事件添加到历史表中时,如果表已满,则旧事件将被丢弃。
摘要表提供了一段时间内所有事件的汇总信息。该组中的表以不同的方式总结了事件数据。要参见执行次数最多或等待时间最多的工具,请对或列events_waits_summary_global_by_event_name
上的表进行排序,这些表分别对应于针对所有事件计算的或值:COUNT_STAR
SUM_TIMER_WAIT
COUNT(*)
SUM(TIMER_WAIT)
mysql>SELECT EVENT_NAME, COUNT_STARFROM performance_schema.events_waits_summary_global_by_event_nameORDER BY COUNT_STARDESC 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_WAITDESC 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
互斥体在使用频率和线程等待尝试获取它的时间上都是“热”的。
注意该
THR_LOCK_malloc
互斥仅用于调试版本。在生产环境中,它不存在,因为它不存在。
实例表记录了要检测的对象的类型。服务器使用检测对象时,将产生一个事件。这些表提供事件名称和说明性注释或状态信息。例如,下file_instances
表列出了用于文件I / O操作及其相关文件的工具实例:
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
列出了可以收集事件的一组工具,并显示了启用了哪些工具:
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 | ...
要了解如何解释仪器名称,请参见“性能模式仪器命名约定”。
要控制是否为仪器收集事件,请将其ENABLED
值设置为YES
或NO
。例如:
mysql>UPDATE performance_schema.setup_instrumentsSET ENABLED = 'NO'WHERE NAME = 'wait/synch/mutex/sql/LOCK_mysql_create_db';
性能架构使用收集的事件来更新performance_schema
数据库中的表,这些表充当事件信息的“使用者”。下setup_consumers
表列出了可用的使用者和已启用的使用者:
mysql>SELECT *FROM performance_schema.setup_consumers; +---------------------------------- +--------- + | NAME | ENABLED | +---------------------------------- +--------- + | events_stages_current | NO | | events_stages_history | NO | | events_stages_history_long | 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
值。
有关设置表以及如何使用它们控制事件收集的更多信息,请参见“性能架构事件过滤”。
有些杂项表不属于前面的任何组。例如,performance_timers
列出可用的事件计时器及其特征。有关计时器的信息,请参见“性能模式事件计时”。