MySQL 官方手册 参考资料1 参考资料2 参考资料3

MySQL 的 Performance Schema 功能使用 performance_schema 数据库，Performance Schema 默认启用，可以关闭。performace_schema 是 MySQL 5.7 中默认包含的数据库，其中的表可以分为这几类：

配置相关的 Setup 表实例相关的 Instance 表Wait Event 等待事件表Stage Event 阶段事件表Statement Event 表Connection 客户信息表Summary 汇总表

performance_schema 数据库中的表有：

mysql> use performance_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------------------------------------------+ | Tables_in_performance_schema | +------------------------------------------------------+ | accounts | | cond_instances | | 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 | | 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 | | events_transactions_current | | events_transactions_history | | events_transactions_history_long | | events_transactions_summary_by_account_by_event_name | | events_transactions_summary_by_host_by_event_name | | events_transactions_summary_by_thread_by_event_name | | events_transactions_summary_by_user_by_event_name | | events_transactions_summary_global_by_event_name | | 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 | | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | global_status | | global_variables | | host_cache | | hosts | | 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 | | metadata_locks | | mutex_instances | | objects_summary_global_by_type | | performance_timers | | prepared_statements_instances | | replication_applier_configuration | | replication_applier_status | | replication_applier_status_by_coordinator | | replication_applier_status_by_worker | | replication_connection_configuration | | replication_connection_status | | replication_group_member_stats | | replication_group_members | | rwlock_instances | | session_account_connect_attrs | | session_connect_attrs | | session_status | | session_variables | | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | | socket_instances | | socket_summary_by_event_name | | socket_summary_by_instance | | status_by_account | | status_by_host | | status_by_thread | | status_by_user | | table_handles | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | | threads | | user_variables_by_thread | | users | | variables_by_thread | +------------------------------------------------------+ 87 rows in set (0.00 sec)

1. 配置相关的 5 个 Setup 表

1.1 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)

setup_actors

setup_actors 表用于配置监控哪些 user，默认情况下监控所有用户线程。

mysql> select * from setup_actors; +------+------+------+---------+---------+ | HOST | USER | ROLE | ENABLED | HISTORY | +------+------+------+---------+---------+ | % | % | % | YES | YES | +------+------+------+---------+---------+ 1 row in set (0.05 sec)

setup_consumers

setup_consumers 表用于配置事件的消费者类型，即收集的事件最终会写入到哪些统计表中。

consumer 不是平级的，存在多级层次关系。具体如下表：

global_instrumentation |– thread_instrumentation |– events_waits_current |– events_waits_history |– events_waits_history_long |– events_stages_current |– events_stages_history |– events_stages_history_long |– events_statements_current |– events_statements_history |– events_statements_history_long |– events_transactions_current |– events_transactions_history |– events_transactions_history_long |– statements_digest 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 | NO | | events_waits_history | NO | | events_waits_history_long | NO | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ 15 rows in set (0.00 sec)

setup_instruments

setup_instruments 表用于配置一条条具体的 instrument，主要包含：idle、transaction、stage/xxx、statement/xxx、wait/memory/xxx。

mysql> select * from setup_instruments; +--------------------------------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +--------------------------------------------------------------------------------+---------+-------+ | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO | | wait/synch/mutex/sql/LOCK_des_key_file | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO | ... | stage/sql/After create | NO | NO | | stage/sql/allocating local table | NO | NO | | stage/sql/preparing for alter table | NO | NO | ... | statement/sql/select | YES | YES | | statement/sql/create_table | YES | YES | | statement/sql/create_index | YES | YES | ... | statement/abstract/relay_log | YES | YES | | transaction | NO | NO | | wait/io/socket/sql/server_tcpip_socket | NO | NO | | wait/io/socket/sql/server_unix_socket | NO | NO | | wait/io/socket/sql/client_connection | NO | NO | | idle | YES | YES | | memory/performance_schema/mutex_instances | YES | NO | | memory/performance_schema/rwlock_instances | YES | NO | ... +--------------------------------------------------------------------------------+---------+-------+ 1020 rows in set (0.04 sec) mysql> select name,count(*) from setup_instruments group by LEFT(name,5); +-------------------------------------------+----------+ | name | count(*) | +-------------------------------------------+----------+ | idle | 1 | | memory/performance_schema/mutex_instances | 376 | | stage/sql/After create | 129 | | statement/sql/select | 193 | | transaction | 1 | | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | 320 | +-------------------------------------------+----------+ 6 rows in set (0.00 sec)

setup_objects

setup_objects 表用于配置监控对象，默认情况下不监控 mysql，performance_schema 和 information_schema 三个库中的表，而其它库中的的所有表都监控。

mysql> select * from setup_objects; +-------------+--------------------+-------------+---------+-------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED | +-------------+--------------------+-------------+---------+-------+ | EVENT | mysql | % | NO | NO | | EVENT | performance_schema | % | NO | NO | | EVENT | information_schema | % | NO | NO | | EVENT | % | % | YES | YES | | FUNCTION | mysql | % | NO | NO | | FUNCTION | performance_schema | % | NO | NO | | FUNCTION | information_schema | % | NO | NO | | FUNCTION | % | % | YES | YES | | PROCEDURE | mysql | % | NO | NO | | PROCEDURE | performance_schema | % | NO | NO | | PROCEDURE | information_schema | % | NO | NO | | PROCEDURE | % | % | YES | YES | | TABLE | mysql | % | NO | NO | | TABLE | performance_schema | % | NO | NO | | TABLE | information_schema | % | NO | NO | | TABLE | % | % | YES | YES | | TRIGGER | mysql | % | NO | NO | | TRIGGER | performance_schema | % | NO | NO | | TRIGGER | information_schema | % | NO | NO | | TRIGGER | % | % | YES | YES | +-------------+--------------------+-------------+---------+-------+ 20 rows in set (0.01 sec)

setup_timers

setup_timers 表用于配置每种类型指令的统计时间单位。MICROSECOND 表示统计单位是微妙，CYCLE 表示统计单位是时钟周期，时间度量与 CPU 的主频有关，NANOSECOND 表示统计单位是纳秒，关于每种类型的具体含义，可以参考 performance_timer 这个表。由于 wait 类包含的都是等待事件，单个 SQL 调用次数比较多，因此选择代价最小的度量单位 CYCLE。但无论采用哪种度量单位，最终统计表中统计的时间都会装换到皮秒。

mysql> select * from setup_timers; +-------------+-------------+ | NAME | TIMER_NAME | +-------------+-------------+ | idle | MICROSECOND | | wait | CYCLE | | stage | NANOSECOND | | statement | NANOSECOND | | transaction | NANOSECOND | +-------------+-------------+ 5 rows in set (0.00 sec)

1.2 配置

默认情况下，setup_instruments 表只开启了 memory、statement 和 wait/io 部分的指令，setup_consumers 表中只开启了 events_statements_current、events_statements_history、statements_digest、thread_instrumentation 和 global_instrumentation 这六个 consumer。

有两种方式可以修改默认配置：

通过 update 语句直接修改配置表来开启需要的选项，修改实时生效。这种方式必需得启动服务器后才可以修改，并且无法持久化，重启后，又得重新设置一遍。使用 my.cnf 配置文件（5.6.4 版本及以上），格式如下： # 设置采集的 instrument # performance_schema_instrument='instrument_name=value' performance_schema_instrument='wait/%' # 打开 wait 类型下面所有的指令 performance_schema_instrument='%=on' # 打开所有指令 # 设置 consumer # performance_schema_consumer_xxx=value performance_schema_consumer_events_waits_current=on performance_schema_consumer_events_waits_history=on # 打开 events_waits_history consumer，注意需要打开所有的上级 consumer # 设置统计表大小 performance_schema_events_waits_history_size=20 performance_schema_events_waits_history_long_size=15000

注意，要启用 consumer，需要确保这个 consumer 的所有上级 consumer 都已经启用。

所有的 performance_schema 表均采用 PERFORMANCE_SCHEMA 存储引擎，表中的所有数据存储在内存中，表的大小在系统初始化时已经固定好，因此占用的内存是一定的。可以通过配置来定制具体每个表的记录数。

2. Instance 表

主要包含了 5 张表：cond_instances，file_instances，mutex_instances，rwlock_instances 和 socket_instances：

cond_instances：条件等待对象实例，表中记录了系统中使用的条件变量的对象，OBJECT_INSTANCE_BEGIN为对象的内存地址。比如线程池的timer_cond实例的name为：wait/synch/cond/threadpool/timer_condfile_instances：文件实例，表中记录了系统中打开了文件的对象，包括ibdata文件，redo文件，binlog文件，用户的表文件等，比如redo日志文件：/u01/my3306/data/ib_logfile0。open_count显示当前文件打开的数目，如果重来没有打开过，不会出现在表中。mutex_instances：互斥同步对象实例，表中记录了系统中使用互斥量对象的所有记录，其中name为：wait/synch/mutex/*。比如打开文件的互斥量：wait/synch/mutex/mysys/THR_LOCK_open。LOCKED_BY_THREAD_ID显示哪个线程正持有mutex，若没有线程持有，则为NULL。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 的缺陷是，只能记录持有写锁的线程，对于读锁则无能为力。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

3. 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）

4. 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)

5. 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。

6. Connection 表

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

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

8. 其它表

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

9. 示例

转载于:https://www.cnblogs.com/kika/p/10851679.html

相关资源：数据结构—成绩单生成器