Performance Schema 仪器等待,这是需要时间的事件。在事件层次结构中,等待事件嵌套在阶段事件中,阶段事件嵌套在语句事件中,语句事件嵌套在事务事件中。
这些表存储等待事件:
events_waits_current
:每个线程的当前等待事件。events_waits_history
:每个线程结束的最近等待事件。events_waits_history_long
:最近全局结束的等待事件(跨所有线程)。
以下部分描述了等待事件表。还有汇总有关等待事件信息的汇总表;请参阅 第 25.12.15.1 节,“等待事件摘要表”。
有关三个等待事件表之间关系的更多信息,请参阅 第 25.9 节,“当前和历史事件的性能模式表”。
配置等待事件收集
控制是否收集等待事件,设置相关instruments和consumer的状态:
该
setup_instruments
表包含名称以 开头的工具wait
。使用这些工具来启用或禁用单个等待事件类的收集。该
setup_consumers
表包含名称与当前和历史等待事件表名称相对应的消费者值。使用这些消费者来过滤等待事件的集合。
默认情况下启用一些等待工具;其他人被禁用。例如:
Press CTRL+C to copymysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'wait/io/file/innodb%'; +--------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +--------------------------------------+---------+-------+ | wait/io/file/innodb/innodb_data_file | YES | YES | | wait/io/file/innodb/innodb_log_file | YES | YES | | wait/io/file/innodb/innodb_temp_file | YES | YES | +--------------------------------------+---------+-------+ mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'wait/io/socket/%'; +----------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +----------------------------------------+---------+-------+ | wait/io/socket/sql/server_tcpip_socket | NO | NO | | wait/io/socket/sql/server_unix_socket | NO | NO | | wait/io/socket/sql/client_connection | NO | NO | +----------------------------------------+---------+-------+
默认情况下禁用等待消费者:
Press CTRL+C to copymysql> SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE 'events_waits%'; +---------------------------+---------+ | NAME | ENABLED | +---------------------------+---------+ | events_waits_current | NO | | events_waits_history | NO | | events_waits_history_long | NO | +---------------------------+---------+
要在服务器启动时控制等待事件收集,请在您的my.cnf
文件中使用如下行:
使能够:
Press CTRL+C to copy[mysqld] performance-schema-instrument='wait/%=ON' performance-schema-consumer-events-waits-current=ON performance-schema-consumer-events-waits-history=ON performance-schema-consumer-events-waits-history-long=ON
禁用:
Press CTRL+C to copy[mysqld] performance-schema-instrument='wait/%=OFF' performance-schema-consumer-events-waits-current=OFF performance-schema-consumer-events-waits-history=OFF performance-schema-consumer-events-waits-history-long=OFF
要在运行时控制等待事件收集,请更新
setup_instruments
和
setup_consumers
表:
使能够:
Press CTRL+C to copyUPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'wait/%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_waits%';
禁用:
Press CTRL+C to copyUPDATE performance_schema.setup_instruments SET ENABLED = 'NO', TIMED = 'NO' WHERE NAME LIKE 'wait/%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'NO' WHERE NAME LIKE 'events_waits%';
要仅收集特定的等待事件,请仅启用相应的等待工具。要仅为特定等待事件表收集等待事件,请启用等待工具,但仅启用与所需表相对应的等待消费者。
该setup_timers
表包含一行,其NAME
值
wait
指示等待事件计时的单位。默认单位是CYCLE
:
Press CTRL+C to copymysql> SELECT * FROM performance_schema.setup_timers WHERE NAME = 'wait'; +------+------------+ | NAME | TIMER_NAME | +------+------------+ | wait | CYCLE | +------+------------+
要更改计时单位,请修改
TIMER_NAME
值:
Press CTRL+C to copyUPDATE performance_schema.setup_timers SET TIMER_NAME = 'NANOSECOND' WHERE NAME = 'wait';
有关配置事件收集的其他信息,请参阅第 25.3 节,“性能模式启动配置”和第 25.4 节,“性能模式运行时配置”。