526互联

11.performance_schema_01

发布时间 2023-06-27 22:54:01作者: 站在巨人的肩上Z

1.Mysql的performance_schema是运行在较低级别的用于监控mysql server运行过程中资源消耗、资源等待的一个功能。

2.查看当前是否支持

root@mysqldb 21:14:  [performance_schema]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

3.查看performance_schema是否开启(8.0默认启用)

root@mysqldb 21:18:  [performance_schema]> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

4.performance_shcema表分类

   按照监视的不同维度进行分类，如:1) 按照数据库对象进行分组、按照不同事件类型进行分组、或者按照事件分组之后再进一步按照账号、主机、程序、线程、用户等进行细分。

  • 按照事件类型分组记录性能事件数据的表。语句事件记录表，记录语句事件信息的表。比如event_statements_current(当前语句事件)、event_statements_history(历史语句事件表)、event_statements_history_long(长语句历史事件表)等等
root@mysqldb 22:36:  [performance_schema]> show tables like 'events_statement%';
+----------------------------------------------------+
| Tables_in_performance_schema (events_statement%)   |
+----------------------------------------------------+
| events_statements_current                          |
| events_statements_histogram_by_digest              |
| events_statements_histogram_global                 |
| 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     |
+----------------------------------------------------+
12 rows in set (0.00 sec)
  • 等待事件记录表
root@mysqldb 22:39:  [performance_schema]> show tables like 'events_wait%';
+-----------------------------------------------+
| Tables_in_performance_schema (events_wait%)   |
+-----------------------------------------------+
| events_waits_current                          |
| events_waits_history                          |
| events_waits_history_long                     |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name    |
| events_waits_summary_by_instance              |
| events_waits_summary_by_thread_by_event_name  |
| events_waits_summary_by_user_by_event_name    |
| events_waits_summary_global_by_event_name     |
+-----------------------------------------------+
9 rows in set (0.01 sec)
  • 阶段事件记录表：记录语句执行阶段事件的表
root@mysqldb 22:42:  [performance_schema]> show tables like 'events_stage%';
+------------------------------------------------+
| Tables_in_performance_schema (events_stage%)   |
+------------------------------------------------+
| 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     |
+------------------------------------------------+
8 rows in set (0.00 sec)
  • 监视文件系统层调用的表
root@mysqldb 22:42:  [performance_schema]> show tables like '%file%';
+---------------------------------------+
| Tables_in_performance_schema (%file%) |
+---------------------------------------+
| file_instances                        |
| file_summary_by_event_name            |
| file_summary_by_instance              |
+---------------------------------------+
3 rows in set (0.00 sec)
  • 监视内存使用的表
root@mysqldb 22:43:  [performance_schema]> 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进行配置的配置表
root@mysqldb 22:44:  [performance_schema]> show tables like '%setup%';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_threads                          |
+----------------------------------------+