mysql | mysql 之 performance_schema介绍

本系列文章所使用的数据库版本为 MySQL 官方 5.7.17版本

｜1、 什么是performance_schema

MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况，它具有以下特点：

1. 提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息

2. performance_schema通过监视server的事件来实现监视server内部运行情况， “事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。

3. performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。

4. performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。

5. 当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动。

6. PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同

7. 收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）

8. performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据）

9. MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。

performance_schema实现机制遵循以下设计目标：

1. 启用performance_schema不会导致server的行为发生变化。例如，它不会改变线程调度机制，不会导致查询执行计划（如EXPLAIN）发生变化

2. 启用performance_schema之后，server会持续不间断地监测，开销很小。不会导致server不可用

3. 在该实现机制中没有增加新的关键字或语句，解析器不会变化

4. 即使performance_schema的监测机制在内部对某事件执行监测失败，也不会影响server正常运行

5. 如果在开始收集事件数据时碰到有其他线程正在针对这些事件信息进行查询，那么查询会优先执行事件数据的收集，因为事件数据的收集是一个持续不断的过程，而检索(查询)这些事件数据仅仅只是在需要查看的时候才进行检索。也可能某些事件数据永远都不会去检索

6. 需要很容易地添加新的instruments监测点

7. instruments(事件采集项)代码版本化：如果instruments的代码发生了变更，旧的instruments代码还可以继续工作。

8. 注意：MySQL sys schema是一组对象（包括相关的视图、存储过程和函数），可以方便地访问performance_schema收集的数据。同时检索的数据可读性也更高(例如：performance_schema中的时间单位是皮秒，经过sys schema查询时会转换为可读的us,ms,s,min,hour,day等单位)，sys schem在5.7.x版本默认安装

MySQL Performance-Schema中总共包含52个表，主要分为几类：Setup表，Instance表，Wait Event表，Stage Event表Statement Event表，Connection表和Summary表。本章就每种类型的表做详细的描述。

Instance表
instance中主要包含了5张表：cond_instances，file_instances，mutex_instances，rwlock_instances和socket_instances。
(1)cond_instances:条件等待对象实例
表中记录了系统中使用的条件变量的对象，OBJECT_INSTANCE_BEGIN为对象的内存地址。比如线程池的timer_cond实例的name为：wait/synch/cond/threadpool/timer_cond

(2)file_instances:文件实例
表中记录了系统中打开了文件的对象，包括ibdata文件，redo文件，binlog文件，用户的表文件等，比如redo日志文件：/u01/my3306/data/ib_logfile0。open_count显示当前文件打开的数目，如果重来没有打开过，不会出现在表中。

(3)mutex_instances:互斥同步对象实例
表中记录了系统中使用互斥量对象的所有记录，其中name为：wait/synch/mutex/*。比如打开文件的互斥量：wait/synch/mutex/mysys/THR_LOCK_open。LOCKED_BY_THREAD_ID显示哪个线程正持有mutex，若没有线程持有，则为NULL。

(4)rwlock_instances: 读写锁同步对象实例
表中记录了系统中使用读写锁对象的所有记录，其中name为 wait/synch/rwlock/*。WRITE_LOCKED_BY_THREAD_ID为正在持有该对象的thread_id，若没有线程持有，则为NULL，READ_LOCKED_BY_COUNT为记录了同时有多少个读者持有读锁。通过 events_waits_current 表可以知道，哪个线程在等待锁；通过rwlock_instances知道哪个线程持有锁。rwlock_instances的缺陷是，只能记录持有写锁的线程，对于读锁则无能为力。

(5)socket_instances:活跃会话对象实例
表中记录了thread_id,socket_id,ip和port，其它表可以通过thread_id与socket_instance进行关联，获取IP-PORT信息，能够与应用对接起来。
event_name主要包含3类：
wait/io/socket/sql/server_unix_socket，服务端unix监听socket
wait/io/socket/sql/server_tcpip_socket，服务端tcp监听socket
wait/io/socket/sql/client_connection，客户端socket

Wait Event表
Wait表主要包含3个表，events_waits_current，events_waits_history和events_waits_history_long，通过thread_id+event_id可以唯一确定一条记录。current表记录了当前线程等待的事件，history表记录了每个线程最近等待的10个事件，而history_long表则记录了最近所有线程产生的10000个事件，这里的10和10000都是可以配置的。这三个表表结构相同，history和history_long表数据都来源于current表。current表和history表中可能会有重复事件，并且history表中的事件都是完成了的，没有结束的事件不会加入到history表中。
THREAD_ID:线程ID
EVENT_ID：当前线程的事件ID，和THREAD_ID组成一个Primary Key。
END_EVENT_ID:当事件开始时，这一列被设置为NULL。当事件结束时，再更新为当前的事件ID。
SOURCE：该事件产生时的源码文件
TIMER_START, TIMER_END, TIMER_WAIT：事件开始/结束和等待的时间，单位为皮秒（picoseconds）

OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE视情况而定
对于同步对象(cond, mutex, rwlock),这个3个值均为NULL
对于文件IO对象，OBJECT_SCHEMA为NULL，OBJECT_NAME为文件名，OBJECT_TYPE为FILE
对于SOCKET对象，OBJECT_NAME为该socket的IP:SOCK值
对于表I/O对象，OBJECT_SCHEMA是表的SCHEMA名，OBJECT_NAME是表名，OBJECT_TYPE为TABLE或者TEMPORARY TABLE
NESTING_EVENT_ID：该事件对应的父事件ID
NESTING_EVENT_TYPE：父事件类型(STATEMENT, STAGE, WAIT)
OPERATION:操作类型（lock, read, write）

Stage Event表

   Stage表主要包含3个表，events_stages_current，events_stages_history和events_stages_history_long,通过thread_id+event_id可以唯一确定一条记录。表中记录了当前线程所处的执行阶段，由于可以知道每个阶段的执行时间，因此通过stage表可以得到SQL在每个阶段消耗的时间。

THREAD_ID：线程ID
EVENT_ID：事件ID
END_EVENT_ID：刚结束的事件ID
SOURCE：源码位置
TIMER_START, TIMER_END, TIMER_WAIT：事件开始/结束和等待的时间，单位为皮秒（picoseconds）
NESTING_EVENT_ID：该事件对应的父事件ID
NESTING_EVENT_TYPE：父事件类型(STATEMENT, STAGE, WAIT)

Statement Event表
Statement表主要包含3个表，events_statements_current，events_statements_history和events_statements_history_long。通过thread_id+event_id可以唯一确定一条记录。Statments表只记录最顶层的请求，SQL语句或是COMMAND，每条语句一行，对于嵌套的子查询或者存储过程不会单独列出。event_name形式为statement/sql/，或statement/com/
SQL_TEXT：记录SQL语句
DIGEST:对SQL_TEXT做MD5产生的32位字符串。如果为consumer表中没有打开statement_digest选项，则为NULL。
DIGEST_TEXT：将语句中值部分用问号代替，用于SQL语句归类。如果为consumer表中没有打开statement_digest选项，则为NULL。
CURRENT_SCHEMA：默认的数据库名
OBJECT_SCHEMA,OBJECT_NAME,OBJECT_TYPE：保留字段，全部为NULL
ROWS_AFFECTED：影响的数目
ROWS_SENT:返回的记录数
ROWS_EXAMINED:读取的记录数目
CREATED_TMP_DISK_TABLES:创建物理临时表数目
CREATED_TMP_TABLES:创建临时表数目
SELECT_FULL_JOIN:join时，第一个表为全表扫描的数目
SELECT_FULL_RANGE_JOIN:join时，引用表采用range方式扫描的数目
SELECT_RANGE:join时，第一个表采用range方式扫描的数目
SELECT_SCAN:join时，第一个表位全表扫描的数目
SORT_ROWS:排序的记录数目
NESTING_EVENT_ID,NESTING_EVENT_TYPE，保留字段，为NULL。

Connection表
Connection表记录了客户端的信息，主要包括3张表：users，hosts和account表，accounts包含hosts和users的信息。
USER:用户名
HOST:用户的IP

Summary表
Summary表聚集了各个维度的统计信息包括表维度，索引维度，会话维度，语句维度和锁维度的统计信息。
(1).wait-summary表
events_waits_summary_global_by_event_name
场景：按等待事件类型聚合，每个事件一条记录。
events_waits_summary_by_instance
场景：按等待事件对象聚合，同一种等待事件，可能有多个实例，每个实例有不同的内存地址，因此
event_name+object_instance_begin唯一确定一条记录。
events_waits_summary_by_thread_by_event_name
场景：按每个线程和事件来统计,thread_id+event_name唯一确定一条记录。
COUNT_STAR：事件计数
SUM_TIMER_WAIT：总的等待时间
MIN_TIMER_WAIT：最小等待时间
MAX_TIMER_WAIT：最大等待时间
AVG_TIMER_WAIT：平均等待时间

(2).stage-summary表
events_stages_summary_by_thread_by_event_name
events_stages_summary_global_by_event_name
与前面类似

(3).statements-summary表
events_statements_summary_by_thread_by_event_name表和events_statements_summary_global_by_event_name表与前面类似。对于events_statements_summary_by_digest表，
FIRST_SEEN_TIMESTAMP：第一个语句执行的时间
LAST_SEEN_TIMESTAMP：最后一个语句执行的时间
场景：用于统计某一段时间内top SQL

(4).file I/O summary表
file_summary_by_event_name [按事件类型统计]
file_summary_by_instance [按具体文件统计]
场景：物理IO维度
FILE_NAME:具体文件名,比如：/u01/my3306/data/tcbuyer_0168/tc_biz_order_2695.ibd
EVENT_NAME:事件名，比如：wait/io/file/innodb/innodb_data_file
COUNT_STAR，SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT
统计IO操作
COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ, SUM_NUMBER_OF_BYTES_READ
统计读
COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE
统计写
COUNT_MISC,SUM_TIMER_MISC,MIN_TIMER_MISC,AVG_TIMER_MISC,MAX_TIMER_MISC
统计其他IO事件，比如create，delete，open，close等

(5).Table I/O and Lock Wait Summaries-表
table_io_waits_summary_by_table
根据wait/io/table/sql/handler，聚合每个表的I/O操作，[逻辑IO]
COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT
统计IO操作
COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT
统计读
COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE, MAX_TIMER_WRITE
统计写
COUNT_FETCH,SUM_TIMER_FETCH,MIN_TIMER_FETCH,AVG_TIMER_FETCH, MAX_TIMER_FETCH
与读相同
COUNT_INSERT,SUM_TIMER_INSERT,MIN_TIMER_INSERT,AVG_TIMER_INSERT,MAX_TIMER_INSERT
INSERT统计，相应的还有DELETE和UPDATE统计。

(6).table_io_waits_summary_by_index_usage
与table_io_waits_summary_by_table类似，按索引维度统计

(7).table_lock_waits_summary_by_table
聚合了表锁等待事件，包括internal lock 和 external lock。
internal lock通过SQL层函数thr_lock调用，OPERATION值为：
read normal
read with shared locks
read high priority
read no insert
write allow write
write concurrent insert
write delayed
write low priority
write normal

external lock则通过接口函数handler::external_lock调用存储引擎层，
OPERATION列的值为：
read external
write external

(8).Connection Summaries表
events_waits_summary_by_account_by_event_name
events_waits_summary_by_user_by_event_name
events_waits_summary_by_host_by_event_name
events_stages_summary_by_account_by_event_name
events_stages_summary_by_user_by_event_name
events_stages_summary_by_host_by_event_name
events_statements_summary_by_account_by_event_name
events_statements_summary_by_user_by_event_name
events_statements_summary_by_host_by_event_name

(9).socket-summaries表
socket_summary_by_instance
socket_summary_by_event_name

其它表
performance_timers: 系统支持的统计时间单位
threads: 监视服务端的当前运行的线程