Question

MySQL 5.5 开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。如

①提供进程等待的详细信息，包括锁、互斥变量、文件信息；

②保存历史的事件汇总信息，为提供 MySQL 服务器性能做出详细的判断；

③对于新增和删除监控事件点都非常容易，并可以改变 mysql 服务器的监控周期，例如（CYCLE、MICROSECOND）。通过该库得到数据库运行的统计信息，更好分析定位问题和完善监控信息。

表的存储引擎均为 PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为 PERFORMANCE_SCHEMA 的表。MySQL5.5 默认是关闭的，需要手动开启，在配置文件里添加：

[mysqld]
performance_schema=ON

查看是否开启：

    mysql> SHOW VARIABLES LIKE 'performance_schema';

   +--------------------+-------+
   | Variable_name    | Value |
   +--------------------+-------+
   | performance_schema | ON    |
   +--------------------+-------+
   1 row in set

   mysql> SHOW VARIABLES LIKE 'perf%';

   +--------------------------------------------------------+-------+
   | Variable_name                          | Value |
   +--------------------------------------------------------+-------+
   | performance_schema                       | ON    |
   | performance_schema_accounts_size               | 100   |
   | performance_schema_digests_size                | 10000 |
   | 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_waits_history_long_size    | 10000 |
   | performance_schema_events_waits_history_size       | 10    |
   | performance_schema_hosts_size                | 100   |
   | performance_schema_max_cond_classes            | 80    |
   | performance_schema_max_cond_instances            | 3504  |
   | performance_schema_max_file_classes            | 50    |
   | performance_schema_max_file_handles            | 32768 |
   | performance_schema_max_file_instances            | 7693  |
   | performance_schema_max_mutex_classes             | 200   |
   | performance_schema_max_mutex_instances           | 15906 |
   | performance_schema_max_rwlock_classes            | 40    |
   | performance_schema_max_rwlock_instances          | 9102  |
   | performance_schema_max_socket_classes            | 10    |
   | performance_schema_max_socket_instances          | 322   |
   | performance_schema_max_stage_classes             | 150   |
   | performance_schema_max_statement_classes         | 168   |
   | performance_schema_max_table_handles             | 4000  |
   | performance_schema_max_table_instances           | 12500 |
   | performance_schema_max_thread_classes            | 50    |
   | performance_schema_max_thread_instances          | 402   |
   | performance_schema_session_connect_attrs_size      | 512   |
   | performance_schema_setup_actors_size             | 100   |
   | performance_schema_setup_objects_size            | 100   |
   | performance_schema_users_size                | 100   |
   +--------------------------------------------------------+-------+
   31 rows in set

2.4.1 表详述

mysql> show tables from performance_schema;

+------------------------------------------------------+
105 rows in set (0.00 sec)

表格 performance_schema 数据库里的表详述

类别	表名	表简述
connection	accounts	账号
	threads	线程
	users	用户
instance	cond_instances	条件等待对象实例
	mutex_instances
	rwlock_instances
	file_instances
summary	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
event_statements	events_statements_current	通过 thread_id+event_id 可以唯一确定一条记录。
	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_thread_by_event_name
	events_statements_summary_by_user_by_event_name
	events_statements_summary_global_by_event_name
event_wait	events_waits_current	记录了当前线程等待的事件
	events_waits_history	记录了每个线程最近等待的 10 个事件
	events_waits_history_long	记录了最近所有线程产生的 10000 个事件
	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	按每个线程和事件来统计，thread_id+event_name 唯一确定一条记录。
	events_waits_summary_by_user_by_event_name
	events_waits_summary_global_by_event_name	按等待事件类型聚合，每个事件一条记录
	file_summary_by_event_name
	file_summary_by_instance
	host_cache
	hosts
	objects_summary_global_by_type
	performance_timers	系统支持的统计时间单位
	session_account_connect_attrs
	session_connect_attrs
setup	setup_actors	配置用户纬度的监控，默认监控所有用户。
	setup_consumers	配置 events 的消费者类型，即收集的 events 写入到哪些统计表中。
	setup_instruments	配置具体的 instrument，主要包含 4 大类：idle、stage/xxx、statement/xxx、wait/xxx：
	setup_objects	配置监控对象，默认对 mysql，performance_schema 和 information_schema 中的表都不监控，而其它 DB 的所有表都监控。
	setup_timers	配置每种类型指令的统计时间单位。
	socket_instances
	socket_summary_by_event_name
	socket_summary_by_instance
	table_io_waits_summary_by_index_usage
	table_io_waits_summary_by_table
	table_lock_waits_summary_by_table

备注：

1. 大致可分类为 setup 启动时配置、summary 统计表、instance 实例、envet_wait 事件等待等等。
2. setup 开头的为配置表，有 5 张。

2.4.2 应用场景

1. 哪个 SQL 执行最多：

>SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN
FROM events_statements_summary_by_digest
ORDER BY COUNT_STAR desc LIMIT 1;

2. 哪个 SQL 平均响应时间最多：

>SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT desc LIMIT 1；

3. 哪个 SQL 扫描的行数最多：

SUM_ROWS_EXAMINED

4. 哪个 SQL 使用的临时表最多：

SUM_CREATED_TMP_DISK_TABLES、SUM_CREATED_TMP_TABLES

5. 哪个 SQL 返回的结果集最多：

SUM_ROWS_SENT

6. 哪个 SQL 排序数最多：

SUM_SORT_ROWS