MySQL-performance_schema
参考文献
- 千金良方: MySQL性能优化金字塔法则
performance_schema
1 | mysql> show tables ; |
1 | mysql> show variables like 'performance_schema'; |
performance_schema
中的表按照事件类型分类
语句事件记录表
- 记录语句事件信息的表
1 | mysql> show tables like 'events_statement%'; |
等待事件记录表
1 | mysql> show tables like 'events_wait%'; |
阶段事件记录表
1 | mysql> show tables like 'events_stage%'; |
事务事件记录表
1 | mysql> show tables like 'events_transaction%'; |
监视文件系统层调用的表
1 | mysql> show tables like '%file%'; |
监视内存使用的表
1 | mysql> show tables like '%memory%'; |
动态对performance_schema
进行配置的配置表
1 | mysql> show tables like '%setup%'; |
performance_schema
配置
instruments
:生产者,用于采集MySQL中各种操作产生的事件信息,对应配置表中的配置项,可
以称之为事件采集配置项。以下提及的生产者均统称为instruments
。consumers
:消费者,对应的消费者表用于存储来自instruments
采集的数据,对应配置表中的配置项,
可以称之为消费存储配置项。以下提及的消费者均统称为consumers
。
performance_timers
表
performance_timers
表中记录了Server
中有哪些可用的事件计时器(注意:该表中的配置项不支持增、删、改,是只读的。记录了哪些计时器就表示当前的版本支持这些计时器)
1 | mysql> select * from performance_timers; |
setup_consumers
表
-
setup_consumers
表中列出了consumers
可配置列表项(注意:该表不支持增加和删除记录,只支持修改和查询记录)。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22mysql> select * from setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_cpu | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | YES |
| events_transactions_history | YES |
| 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 |
+----------------------------------+---------+
16 rows in set (0.01 sec)- ENABLED:是否启用consumers,有效值为YES或NO,此字段可以使用UPDATE语句修改。如果需要禁用consumers,就将ENABLED字段设置为NO,当设置为NO时,Server不会对consumers表的内容新增和删除进行维护,并且也会关闭consumers对应的instruments(如果instruments发现采集数据没有任何consumers消费的话)
setup_instruments
表
setup_instruments
表中列出了instruments
可配置列表项,即代表哪些事件支持被收集。
常用的监控场景
-
监控metadata locks(MDL元数据锁)需要打
'wait/lock/metadata/sql/mdl' instruments
,在
performance_schema.metadata_locks
表中可以查询到MDL
锁信息1
2
3
4
5
6
7mysql> select * from setup_instruments where name like 'wait/lock/metadata/sql/mdl%';
+----------------------------+---------+-------+------------+-------+------------+---------------+
| NAME | ENABLED | TIMED | PROPERTIES | FLAGS | VOLATILITY | DOCUMENTATION |
+----------------------------+---------+-------+------------+-------+------------+---------------+
| wait/lock/metadata/sql/mdl | YES | YES | | NULL | 0 | NULL |
+----------------------------+---------+-------+------------+-------+------------+---------------+
1 row in set (0.00 sec) -
监控表锁需要打开
'wait/io/table/sql/handler'instruments
,在performance_schema.table_handles
表中会记录当前打开了哪些表1
2
3
4
5
6
7mysql> select * from setup_instruments where name like 'wait/io/table/sql/handler%';
+---------------------------+---------+-------+------------+-------+------------+---------------+
| NAME | ENABLED | TIMED | PROPERTIES | FLAGS | VOLATILITY | DOCUMENTATION |
+---------------------------+---------+-------+------------+-------+------------+---------------+
| wait/io/table/sql/handler | YES | YES | | NULL | 0 | NULL |
+---------------------------+---------+-------+------------+-------+------------+---------------+
1 row in set (0.00 sec) -
监控查询语句
top number
,需要打开'statement/sql/select' instruments
然后打开
events_statements_xxx
表,通过查询performance_schema.events_statements_xxx
表的SQL_TEXT
字段可以看到原始的SQL
语句,查询TIMER_WAIT
字段可以知道总的响应时间,查询LOCK_TIME
字段可以知道加锁时间(注意时间单位是皮秒,需要除以1 000 000 000 000
单位才是秒
1 | mysql> select * from setup_instruments where name like 'statement/sql/select'; |
1 | mysql> select * from events_statements_current\G |
setup_actors
表
setup_actors
表用于配置是否为新的前台Server线程(与客户端连接相关联的线程)启用监视和历史事件日志记录。在默认情况下,此表的最大行数为100。可以使用系统变performance_schema_setup_actors_size
,在Server启动之前更改此表的最大配置行数
1 | mysql> select * from setup_actors; |
- HOST:一个具体的字符串名称(是能够解析为IP地址的主机名或DNS域名),或者使用“%”表示任意主机。
- USER:一个具体的字符串名称,或者使用“%”表示任意用户。
- ROLE:当前未使用,在MySQL 8.0中才启用角色功能。
- ENABLED:是否启用与HOST、USER、ROLE匹配的前台线程的监控功能,其有效值为YES或NO。
- HISTORY:是否启用与HOST、USER、ROLE匹配的前台线程的历史事件记录功能,其有效值为YES或NO。
setup_objects
表
setup_objects
表控制performance_schema是否监控特定对象。在默认情况下,此表的最大行数为100行。若要更改表行数,则可以在Server启动之前修改系统变量performance_schema_setup_objects_size
的值。
1 | mysql> select * from setup_objects; |
threads
表
threads
表对于每个Server线程都会生成一行线程相关信息,例如:显示是否启用监控和历史事件记录功能
示例
找出谁持有全局读锁
1 | mysql> select connection_id(); |
找出谁持有MDL
锁
- MDL锁记录对应的
instruments
为wait/lock/metadata/sql/mdl
,默认未启用;对应的consumers
为
performance_schema.metadata_locks
,在setup_consumers
中只受全局配置项global_instrumentation
控制,默认已启用
1 | mysql> show processlist; |
找出谁持有表级锁
- 表级锁对应的
instruments
(wait/lock/table/sql/handler
)默认已启用,对应的consumers
为performance_schema.table_handles
,在setup_consumers
中只受全局配置项global_ instrumentation
控制,默认已启用。所以,在默认情况下,只需要设置系统配置参数performance_schema=ON
即可
1 | mysql> show processlist; |
找出谁持有行级锁
1 | mysql> show processlist; |
查看最近的TOP SQL
语句
1 | mysql> select thread_id,event_name,sys.format_time(timer_wait),sys.format_time(lock_time),sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined from events_statements_history where current_schema!='performance_schema' order by timer_wait desc limit 10\G |
1 | mysql> select SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, sys.format_time(SUM_TIMER_WAIT) as sum_time, sys.format_time(MIN_TIMER_WAIT) as min_time, sys.format_time(AVG_TIMER_WAIT) as avg_time, sys.format_time(MAX_TIMER_WAIT) as max_time, sys.format_time(SUM_LOCK_TIME) as sum_lock_time, SUM_ROWS_AFFECTED, SUM_ROWS_SENT, SUM_ROWS_EXAMINED from events_statements_summary_by_digest where SCHEMA_NAME is not null order by COUNT_STAR desc limit 10\G |
查看最近执行失败的SQL语句
1 | mysql> select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time,sys.format_time(lock_time) as lock_time,sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined,mysql_errno from events_statements_history limit 10\G |
查看SQL
语句执行阶段和进度信息
-
首先需要进行配置启用,阶段事件默认并未启用
1
2
3
4
5
6
7
8
9
10mysql> use performance_schema;
Database changed
mysql> update setup_instruments set enabled='yes',timed='yes' where name like 'stage/%';
Query OK, 116 rows affected (0.01 sec)
Rows matched: 132 Changed: 116 Warnings: 0
mysql> update setup_consumers set enabled='yes' where name like '%stage%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0 -
开启一个会话,查看线程信息
1
2
3
4
5
6
7mysql> select sys.ps_thread_id(connection_id());
+-----------------------------------+
| sys.ps_thread_id(connection_id()) |
+-----------------------------------+
| 47 |
+-----------------------------------+
1 row in set (0.00 sec) -
先对之前旧的信息进行清理,避免干扰
1
2
3
4
5
6
7
8
9
10
11
12# 先关闭其他线程的事件记录功能,使用前面的步骤查询到thread_id
mysql> update performance_schema.threads set instrumented='NO' where thread_id!=47;
Query OK, 47 rows affected (0.00 sec)
Rows matched: 47 Changed: 47 Warnings: 0
# 清空阶段事件的三个表
mysql> truncate performance_schema.events_stages_current; truncate performance_schema.events_stages_history; truncate performance_schema.events_stages_history_long;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec) -
查看
events_stages_history_long
表1
mysql> select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time,work_completed,work_estimated from performance_schema.events_stages_history_long;
查看SQL
语句执行进度信息
1 | mysql> select * from sys.session where conn_id!=connection_id()\G |
查看最近的事务执行信息
-
首先需要进行配置启用,事务事件默认并未启用
1
2
3
4
5
6
7mysql> update setup_instruments set enabled='yes',timed='yes' where name like 'transaction';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> update setup_consumers set enabled='yes' where name like '%transaction%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 3 Changed: 1 Warnings: 0 -
执行清理,避免其他事务干扰
1
2
3
4
5
6mysql> truncate performance_schema.events_transactions_current; truncate performance_schema.events_transactions_history; truncate performance_schema.events_transactions_history_long;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec) -
查看活跃的事务
1
2
3
4
5
6
7
8
9
10
11
12
13
14mysql> select thread_id,event_name,state,trx_id,gtid,source,timer_wait,access_mode,isolation_level,autocommit,nesting_event_id,nesting_event_type from events_transactions_current\G
*************************** 1. row ***************************
thread_id: 534
event_name: transaction
state: COMMITTED
trx_id: 422087403820624
gtid: AUTOMATIC
source: handler.cc:1346
timer_wait: 219049000
access_mode: READ WRITE
isolation_level: REPEATABLE READ
autocommit: YES
nesting_event_id: 1681
nesting_event_type: STATEMENT -
查看历史的事务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> select thread_id,event_name,state,trx_id,gtid,source,timer_wait,access_mode,isolation_level,autocommit,nesting_event_id,nesting_event_type from events_transactions_history_long limit 1\G
*************************** 1. row ***************************
thread_id: 537
event_name: transaction
state: COMMITTED
trx_id: 422087403821432
gtid: AUTOMATIC
source: handler.cc:1346
timer_wait: 33152279000
access_mode: READ WRITE
isolation_level: REPEATABLE READ
autocommit: YES
nesting_event_id: 2237
nesting_event_type: STATEMENT
1 row in set (0.00 sec) -
如果一个事务长时间未提交(长时间处于
ACTIVE
状态),对于这种情况,虽然从events_transactions_current
表中可以查询到未提交的事务事件信息,但是并不能很直观地看到事务是什么时间点开始的,可以借助information_schema.innodb_trx
表来进行辅助判断.1
mysql> select * from information_schema.innodb_trx;
查看多线程复制报错详情
-
在启用了并行复制之后,一旦发生复制报错,通常通过
show slave status
语句无法查看到具体的报错详情(通过show slave status
语句只能查看到SQL线程的报错信息1
mysql> show slave status\G
-
根据报错提示查看
performance_schema.replication_applier_status_by_worker
表,在该表中详细记录了每一个Worker
线程的详细信息1
2mysql> select * from performance_schema.replication_applier_status_by_worker where last_error_message!=''\G
其他收集
1 | -- 1、哪类的SQL执行最多? |