利用performance_schema进行故障诊断(mysql金字塔法则读书笔记)

不念不忘少年蓝@ 2022-10-30 09:26 5阅读 0赞

instrunments:生产者，用于采集mysql中各种操作产生的事件信息，对应配置表中的配置项，我们可以称之为采集配置项。

consumers:消费者，对应的消费者用于存储来自instruments采集的数据，对应配置表的配置项，我们可以称之为消费存储配置项。

启用所有等待事件的instruments:

use performance\_schema

update  setup\_instruments set enabled='yes',timed='yes' where name like 'wait/%';

select \* from setup\_instruments where name like 'wait/%';

启用等待事件的consumers

update setup\_consumers set enabled='yes' where name like '%wait%';

select \* from setup\_consumers where name like '%wait%';

实时统计当前等待事件：

create view sys.test\_waits as select sum(timer\_wait) as timer\_wait ,sum(number\_of\_bytes) as number\_of\_bytes,event\_name,operation from events\_waits\_current where event\_name !='idle' group by event\_name,operation;

select sys.format\_time(timer\_wait),sys.format\_bytes(number\_of\_bytes),event\_name,operation from sys.test\_waits where sys.format\_time(timer\_wait) not regexp 'ns|us' order by timer\_wait desc;

select thread\_id,event\_name,sys.format\_time(timer\_wait),index\_name,nesting\_event\_type,operation,number\_of\_bytes from events\_waits\_current where event\_name !='idle' order by timer\_wait desc;

找出谁持有全局读锁：

通过performance\_schema.metadata\_locks 表来排查谁持有全局读锁，全局读锁在该表中通常记录着同一个会话的object\_type为global和commit、lock\_type都为shared的两把显式锁,  LOCK\_TYPE: INTENTION\_EXCLUSIVE 意向排他锁

select \* from performance\_schema.metadata\_locks where owner\_thread\_id!=sys.ps\_thread\_id(connection\_id())\\G;

找出 OWNER\_THREAD\_ID为内部线程id,可以查询threads表找出对应会话id：

select \* from performance\_schema.threads where THREAD\_ID in (31,30)\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

OBJECT\_TYPE: GLOBAL

OBJECT\_SCHEMA: NULL

OBJECT\_NAME: NULL

OBJECT\_INSTANCE\_BEGIN: 140514110025712

LOCK\_TYPE: SHARED

LOCK\_DURATION: EXPLICIT

LOCK\_STATUS: GRANTED

SOURCE: lock.cc:1110

OWNER\_THREAD\_ID: 31 持有锁的内部线程id为31

OWNER\_EVENT\_ID: 43

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

OBJECT\_TYPE: COMMIT

OBJECT\_SCHEMA: NULL

OBJECT\_NAME: NULL

OBJECT\_INSTANCE\_BEGIN: 140514110303360

LOCK\_TYPE: SHARED

LOCK\_DURATION: EXPLICIT

LOCK\_STATUS: GRANTED

SOURCE: lock.cc:1194

OWNER\_THREAD\_ID: 31  持有锁的内部线程id为31

OWNER\_EVENT\_ID: 97

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

OBJECT\_TYPE: GLOBAL

OBJECT\_SCHEMA: NULL

OBJECT\_NAME: NULL

OBJECT\_INSTANCE\_BEGIN: 140514110036384

LOCK\_TYPE: INTENTION\_EXCLUSIVE n意向排他锁

LOCK\_DURATION: STATEMENT

LOCK\_STATUS: PENDING  状态为pending，表示正在等待被授权

SOURCE: sql\_base.cc:3190

OWNER\_THREAD\_ID: 30 被阻塞

OWNER\_EVENT\_ID: 2507

3 rows in set (0.04 sec)

找出谁持有MDL锁：

一个shared\_write锁处于granted状态，其余是shared\_upgradable、execlusive锁，shared\_upgradable处于granted状态，exclusive处于pending状态

select \* from performance\_schema.metadata\_locks where owner\_thread\_id!=sys.ps\_thread\_id(connection\_id())\\G;

通过information\_schema.innodb\_trx表确认源阻塞线程是否存在一个没有提交的事务：

select \* from information\_schema.innodb\_trx\\G;

通过performance\_schema.events\_statements\_current来查询某个线程正在执行或者最后一次执行完成的语句事件信息：

select \* from performance\_schema.events\_statements\_current where thread\_id=11\\G;

找出谁持有表级锁：

找出持有表 READ EXTERNAL表级锁的内部线程id

select \* from performance\_schema.table\_handles where owner\_thread\_id!=0\\G;

找出线程正在执行什么sql:

select \* from performance\_schema.events\_statements\_current where thread\_id= 30\\G;

找出processlist\_id来进行kill

select \* from performance\_schema.threads where THREAD\_ID =30\\G;

找出谁持有行级锁：

mysql8

select \* from  performance\_schema.data\_locks\\G;

mysql 5.7

select \* from sys.innodb\_lock\_waits\\G;

查询最近的topsql语句：

select thread\_id,event\_name,source,sys.format\_time(timer\_wait),sys.format\_time(lock\_time),sql\_text,current\_schema,message\_text,rows\_affected,rows\_sent,rows\_examined from  performance\_schema.events\_statements\_history where current\_schema!='performance\_schema' order by timer\_wait desc limit 10\\G;

统计后sql:

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 performance\_schema.events\_statements\_summary\_by\_digest where schema\_name is not null order by count\_star desc limit 10\\G;

查看最近执行失败的sql语句：

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 performance\_schema.events\_statements\_history where  mysql\_errno=1064\\G;

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 ,errors from performance\_schema.events\_statements\_history where errors>0\\G;

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\_errors,first\_seen,last\_seen from performance\_schema.events\_statements\_summary\_by\_digest where sum\_errors!=0\\G;

查看sql语句执行阶段和进度信息：

use performance\_schema

update  setup\_instruments set enabled='yes',timed='yes' where name like 'stage/%';

update setup\_consumers set enabled='yes' where name like '%stage%';

查看语句执行全过程以及每个过程时间开销等。

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语句执行进度信息：

select  \* from sys.session where conn\_id!=connection\_id()\\G;

查询最近的事务执行信息：

use performance\_schema

update  setup\_instruments set enabled='yes',timed='yes' where name like 'transaction%';

update setup\_consumers set enabled='yes' where name like '%transaction%';

select thread\_id,event\_name,state,trx\_id,gtid,source,timer\_wait,access\_mode,isolation\_level,autocommit,nesting\_event\_id,nesting\_event\_type from performance\_schema.events\_transactions\_current\\G;

查询多线程复制报错详情：

show slave status\\G;

select \* from performance\_schema.replication\_applier\_status\_by\_worker where last\_error\_message!=''\\G