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_cond
- file_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:监视服务端的当前运行的线程