- performance_schema不会随binlog同步到其他节点，只记录到本地Server
- setup_开头到表为配置表，更改配置表会立即生效
查看是否支持performance_schema存储引擎
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.01 sec)
Suppert为YES表示支持
查看是否开启performance_schema
mysql> show variables like 'PERFORMANCE_SCHEMA'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+ 1 row in set (0.23 sec)
5.7之后默认开启
performance_schema表分类
语句事件记录表：show tables like 'events_statement%';mysql> show tables like 'events_statement%'; +----------------------------------------------------+ | Tables_in_performance_schema (events_statement%) | +----------------------------------------------------+ | events_statements_current | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | +----------------------------------------------------+ 10 rows in set (0.00 sec)等待事件记录表：show tables like 'events_wait%'; 阶段事件记录表：show tables like 'events_stage%'; 事务事件记录表：show tables like 'events_transaction%'; 监视文件系统层调用的表：show tables like '%file%'; 监视内存使用的表：show tables like '%memory%';
mysql> show tables like '%memory%'; +-----------------------------------------+ | Tables_in_performance_schema (%memory%) | +-----------------------------------------+ | 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 | +-----------------------------------------+ 5 rows in set (0.00 sec)
动态对performance_schema进行配置的配置表：show tables like '%setup_%';
mysql> show tables like '%setup_%'; +-----------------------------------------+ | Tables_in_performance_schema (%setup_%) | +-----------------------------------------+ | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | +-----------------------------------------+ 5 rows in set (0.00 sec)
配置与使用：
打开等待事件的采集器配置开关mysql> update setup_instruments set enabled ='yes',timed='yes' where name like 'wait%'; Query OK, 0 rows affected (0.01 sec) Rows matched: 321 Changed: 0 Warnings: 0打开等待事件的保存表采集器配置项开关（默认并没有打开）
mysql> update setup_consumers set enabled ='yes' where name like '%wait%'; Query OK, 3 rows affected (0.05 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from 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 | NO | | events_transactions_history | NO | | events_transactions_history_long | NO | | events_waits_current | YES | | events_waits_history | YES | | events_waits_history_long | YES | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ 15 rows in set (0.00 sec)
events_waits_current该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件（正在做什么事） *_current表中每个线程只保留一条记录，且一旦线程完成工作，该表中就不会再记录该线程的事件信息了。 *_history表中记录每个线程已经执行完成的事件信息，但每个线程的事件信息只记录10条，再多就会被覆盖掉。 *_history_long表中记录所有线程的事件信息，但总记录数量是10000行，超过就会被覆盖掉。现在我们查看历史表events_waits_history中的记录。
mysql> select thread_id,event_id,event_name,timer_wait from events_waits_history order by timer_wait limit 22; #单位皮秒 +-----------+----------+-------------------------------------------+------------+ | thread_id | event_id | event_name | timer_wait | +-----------+----------+-------------------------------------------+------------+ | 19 | 363551 | wait/synch/mutex/innodb/flush_list_mutex | 27268 | | 3 | 908917 | wait/synch/mutex/innodb/buf_pool_mutex | 27268 | | 19 | 363552 | wait/synch/mutex/innodb/flush_list_mutex | 27268 | | 3 | 908920 | wait/synch/mutex/innodb/buf_dblwr_mutex | 28070 | | 3 | 908919 | wait/synch/mutex/innodb/buf_pool_mutex | 28070 | | 19 | 363543 | wait/synch/mutex/innodb/log_sys_mutex | 28070 | | 3 | 908915 | wait/synch/mutex/innodb/buf_dblwr_mutex | 28070 | | 3 | 908912 | wait/synch/mutex/innodb/buf_pool_mutex | 28872 | | 3 | 908914 | wait/synch/mutex/innodb/buf_pool_mutex | 28872 | | 3 | 908913 | wait/synch/mutex/innodb/flush_list_mutex | 31278 | | 3 | 908918 | wait/synch/mutex/innodb/flush_list_mutex | 31278 | | 1 | 6 | wait/synch/mutex/sql/LOCK_thread_cache | 32882 | | 671 | 1611 | wait/synch/mutex/sql/THD::LOCK_thd_data | 36892 | | 21 | 68499 | wait/synch/mutex/innodb/sync_array_mutex | 40100 | | 21 | 68503 | wait/synch/mutex/innodb/sync_array_mutex | 40100 | | 19 | 363545 | wait/synch/mutex/innodb/flush_list_mutex | 44912 | | 671 | 1606 | wait/synch/mutex/sql/THD::LOCK_thd_query | 47318 | | 21 | 68494 | wait/synch/mutex/innodb/sync_array_mutex | 48922 | | 671 | 1613 | wait/synch/mutex/sql/THD::LOCK_query_plan | 49724 | | 21 | 68496 | wait/synch/mutex/innodb/sync_array_mutex | 49724 | | 21 | 68500 | wait/synch/mutex/innodb/sync_array_mutex | 53734 | | 671 | 1608 | wait/synch/mutex/sql/THD::LOCK_query_plan | 56140 | +-----------+----------+-------------------------------------------+------------+ 22 rows in set (0.00 sec) mysql> select thread_id,event_id,event_name,timer_wait from events_waits_history order by timer_wait desc limit 22; +-----------+----------+-----------------------------------------------+------------+ | thread_id | event_id | event_name | timer_wait | +-----------+----------+-----------------------------------------------+------------+ | 1 | 1 | wait/io/socket/sql/server_tcpip_socket | 8647164 | | 1 | 4 | wait/io/socket/sql/server_unix_socket | 4390148 | | 23 | 2271 | wait/synch/mutex/innodb/recalc_pool_mutex | 658442 | | 23 | 2272 | wait/synch/mutex/innodb/recalc_pool_mutex | 627164 | | 23 | 2265 | wait/synch/mutex/innodb/recalc_pool_mutex | 555786 | | 23 | 2273 | wait/synch/mutex/innodb/recalc_pool_mutex | 536538 | | 23 | 2266 | wait/synch/mutex/innodb/recalc_pool_mutex | 533330 | | 22 | 22723 | wait/synch/mutex/innodb/lock_wait_mutex | 494032 | | 23 | 2268 | wait/synch/mutex/innodb/recalc_pool_mutex | 478794 | | 671 | 1633 | wait/synch/rwlock/sql/LOCK_grant | 468368 | | 23 | 2270 | wait/synch/mutex/innodb/recalc_pool_mutex | 455536 | | 21 | 68522 | wait/synch/mutex/innodb/log_sys_mutex | 445110 | | 23 | 2274 | wait/synch/mutex/innodb/recalc_pool_mutex | 425862 | | 23 | 2269 | wait/synch/mutex/innodb/recalc_pool_mutex | 384960 | | 671 | 1629 | wait/synch/mutex/sql/THD::LOCK_thd_data | 381752 | | 23 | 2267 | wait/synch/mutex/innodb/recalc_pool_mutex | 378544 | | 22 | 22725 | wait/synch/mutex/innodb/lock_wait_mutex | 378544 | | 1 | 2 | wait/synch/mutex/sql/LOCK_connection_count | 372930 | | 22 | 22727 | wait/synch/mutex/innodb/lock_wait_mutex | 247016 | | 22 | 22730 | wait/synch/mutex/innodb/lock_wait_mutex | 228570 | | 21 | 68519 | wait/synch/mutex/innodb/log_sys_mutex | 220550 | | 19 | 363656 | wait/synch/mutex/innodb/log_flush_order_mutex | 218144 | +-----------+----------+-----------------------------------------------+------------+ 22 rows in set (0.00 sec)
summary表提供所有事件的汇总信息。如按照用户、主机、线程等汇总，例如要查看哪些instruments占用的事件最多，则可以通过对events_waits_summary_global_by_event_name表的count_star或sum_timer_wait列进行查询。
SQL> select event_name, round(sum_timer_wait / 1000 / 1000 / 1000) as "sum_timer_wait(ms)" from events_waits_summary_global_by_event_name order by sum_timer_wait desc limit 10; +--------------------------------------+--------------------+ | event_name | sum_timer_wait(ms) | +--------------------------------------+--------------------+ | idle | 28754658 | | wait/io/file/innodb/innodb_data_file | 111524 | | wait/io/file/innodb/innodb_log_file | 15994 | | wait/io/file/sql/io_cache | 9996 | | wait/io/file/sql/binlog | 6869 | | wait/io/file/sql/FRM | 1982 | | wait/io/socket/sql/client_connection | 1059 | | wait/io/file/sql/slow_log | 890 | | wait/io/file/myisam/kfile | 115 | | wait/io/file/myisam/dfile | 101 | +--------------------------------------+--------------------+ 10 rows in set (0.01 sec)
instance表记录了哪些类型的对象会被检测。这些对象在被Server使用时，在该表中将会产生一条事件记录。例如，file_instances表列出了文件I/O操作及其关联文件名。
mysql> select * from file_instances limit 20; +-------------------------------------------------------------+--------------------------------------+------------+ | FILE_NAME | EVENT_NAME | OPEN_COUNT | +-------------------------------------------------------------+--------------------------------------+------------+ | /usr/local/mysql-5.7.32-el7-x86_64/share/english/errmsg.sys | wait/io/file/sql/ERRMSG | 0 | | /usr/local/mysql-5.7.32-el7-x86_64/share/charsets/Index.xml | wait/io/file/mysys/charset | 0 | | /data/mysql_data/ibdata1 | wait/io/file/innodb/innodb_data_file | 3 | | /data/redolog/ib_logfile0 | wait/io/file/innodb/innodb_log_file | 2 | | /data/redolog/ib_logfile1 | wait/io/file/innodb/innodb_log_file | 2 | | /data/undolog/undo001 | wait/io/file/innodb/innodb_data_file | 3 | | /data/undolog/undo002 | wait/io/file/innodb/innodb_data_file | 3 | | /data/undolog/undo003 | wait/io/file/innodb/innodb_data_file | 3 | | /data/undolog/undo004 | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/engine_cost.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/gtid_executed.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/help_category.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/help_keyword.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/help_relation.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/help_topic.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/innodb_index_stats.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/innodb_table_stats.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/plugin.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/server_cost.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /data/mysql_data/mysql/servers.ibd | wait/io/file/innodb/innodb_data_file | 3 | +-------------------------------------------------------------+--------------------------------------+------------+ 20 rows in set (0.00 sec)