11.performance_schema_01

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                          |
+----------------------------------------+

 # 查看与performance_schema相关的system  variables，这些system variables 用于限定consumers表的存储限制，他们都是只读变量，需要在mysql启动之前就设置好这些变量的值

root@mysqldb 22:54:  [(none)]> show variables like '%performance_schema%';
+----------------------------------------------------------+-------+
| Variable_name                                            | Value |
+----------------------------------------------------------+-------+
| performance_schema                                       | ON    |
| performance_schema_accounts_size                         | -1    |
| performance_schema_digests_size                          | 10000 |
| performance_schema_error_size                            | 4890  |
| performance_schema_events_stages_history_long_size       | 10000 |
| performance_schema_events_stages_history_size            | 10    |
| performance_schema_events_statements_history_long_size   | 10000 |
| performance_schema_events_statements_history_size        | 10    |
| performance_schema_events_transactions_history_long_size | 10000 |

(1) performance_scheam=on ：控制performance_scheam功能的开关

(2) performance_schema_digests_size=10000：控制events_statements_summary_by_digest表中最大行数

(3) performance_schema_events_statements_history_long_size=10000

　 控制events_statements_history_long表中的最大行数，该参数控制所有会话在events_statements_history_long表中能够存放的总事件记录数，超过这个限制之后，最早的记录将被覆盖。

(4) perfomance_statements_history表中单个线程(会话)的最大行数。这个参数控制单个会话在events_statements_history表中能够存放的事件记录数，超过这个限制之后，单个会话最早的记录将被覆盖。