performance_schema初相识

监控MySQL Server运行时资源消耗、资源等待,information_schema关注Server运行的元数据信息,performance_schema通过事件来实现监控,事件可以是函数调用、操作系统等待、或者sql语句的解析排序等阶段 其他特点:
  • 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)
上一篇:BiFunction接口笔记


下一篇:Performance Tuning MySQL