【mysql】performance_schema的应用

一、利用等待事件排查MySQL的性能问题

• 安装sysbench性能测试工具

• 准备数据：使用的数据库（sysbench_test）需要自己提前创建，然后就是如果使用的是云服务器，最好将mysql.user表中的host由localhost改为%，并且要关闭防火墙。

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=peng --mysql-password=123456 --mysql-host=localhost --mysql-port=3306 --mysql-db=sysbench_test --tables=10 --table_size=50000 --threads=10 --time=60 prepare

• 开始测试

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=peng --mysql-password=123456 --mysql-host=localhost --mysql-port=3306 --mysql-db=sysbench_test --tables=10 --table_size=50000 --threads=10 --time=60 --report-interval=3 run

• 测试的结果：我使用的是2核2G的云服务器，所以性能很慢
  一些重点的数据如tps(transaction per second)=9.52，qps(query per second)=190.39，最大延迟时间为284。
  

• 使用performance_schema查看等待事件找出性能慢的原因。
  1、打开wait事件的instruments和consumers，保证可以采集等待事件的信息

update setup_instruments set enabled='YES',timed='YES' where name like 'wait/%';
update setup_consumers set enabled='YES' where name like '%wait%';

2、使用sysbench进行压力测试
3、创建一个视图查看等待事件的信息

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

二、锁问题的排查

1、排查全局读锁

对于全局锁来说并不是数据库层面的，通过innodb_locks来排查通常无法实现，全局锁通常在server层面，可以通过performance_schema的metadata_locks表来查看。对于持有全局读锁的进程，在该表中通常会记录着同一个会话的OBJECT_TYPE为global和commit，LOCK_TYPE都为shared的两把显式锁。

• 会话一添加全局锁,查看进程id

flush table with read lock;
select connection_id();

• 会话二查看进程id，修改某个数据表的数据，修改操作被阻塞。

select connection_id();
update 

• 查看mysql进程状态，可以看到438进程在等全局锁
  
• 排查全局锁：innodb层面并没有锁的显示
  
• 排查全局锁，在metadata_locks表中可以看到内部线程号为442的会话的OBJECT_TYPE为GLOBAL和COMMIT，LOCK_TPYE均为SHARED，并且锁的状态是GRANTED（被授予的），也就是全局锁由这个进程所加。而内部线程号为463的会话锁的状态是PENDING(在等待的)，也就是被阻塞了。

# 排除当前进程持有的锁
select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id());

• 查看417和438进程对应的线程号分别为442和463，也就对应了上面metadata_locks表中的数据。
  

2、排查MDL锁

MDL锁是表级的元数据锁（也是在server层的锁），在对数据库表进行增删改查和修改数据库表的属性时会自动添加MDL。

• 会话一中开启一个事务，修改peng_db.onpiece的数据，不提交事务。

begin;
update peng_db.onepiece set birthday = now();

• 会话二中增加peng_db.onpiece表的字段（修改表的元信息），被阻塞。

alter table peng_db.onepiece add column gender tinyint(1) default 1 comment '1-男生，2-女生';

• 查看线程状态
  会话二在等待MDL（表级元数据锁）。
  
• 查看performance_schema.metadata_locks表
  471线程拥有onepiece表的写锁，这也导致了472线程的排他锁的状态为PENDING(在等待中)。
  而且上面通过show preocesslist命令可以看到446进程（对应的内部线程号为471）处于空闲状态，也就是事务没有提交，这时可以提交事务放出MDL锁，让447进程（对应的内部线程号为472）能够获得onepiece表的锁从而完成更新表的操作。
  * 查看事务未提交线程执行的语句信息。（使用events_statements_current表查看正在执行的语句）

 select * from performance_schema.events_statements_current where thread_id=471\G;

mysql> select * from performance_schema.events_statements_current where thread_id=471\G;
*************************** 1. row ***************************
              THREAD_ID: 471
               EVENT_ID: 46
           END_EVENT_ID: 126
             EVENT_NAME: statement/sql/update
                 SOURCE:
            TIMER_START: 21334060865866000
              TIMER_END: 21334061206786000
             TIMER_WAIT: 340920000
              LOCK_TIME: 168000000
               SQL_TEXT: update peng_db.onepiece set birthday=now()
                 DIGEST: 8bf1a317296334f10cb85151c1b3dbdb
            DIGEST_TEXT: UPDATE `peng_db` . `onepiece` SET `birthday` = NOW ( )
            ············
           MESSAGE_TEXT: Rows matched: 2  Changed: 2  Warnings: 0

3、排查表级锁

具体操作不再演示了。

• 查询表级别锁的信息(table_handles)
  给sysbench_sbtest2添加写锁。查询performance_schema.table_handles表的信息。
  给sysbench_sbtest1添加读锁。查询performance_schema.table_handles表的信息。
  

4、行级锁排查

三、查看最近执行的SQL语句

• events_statements_history：保存历史执行语句的信息。该表默认是开启了consumers的，存储的语句的数量最大为10个。
• events_statements_history_long：也是保存历史执行语句，默认关闭consumers，存储最大行数为10000行。
  
  
• 在优化SQL时我们会优先优化执行次数最多的SQL语句，可以使用events_statements_history_long表对SQL_TEXT字段计数，还有一种方法就是查看events_statements_summary_by_digest 表的数据。

events_statements_summary_by_digest 表的一些重要字段

SCHEMA_NAME：操作的数据库名字
DIGEST_TEXT：SQL语句
COUNT_STAR：SQL执行次数
SUM_TIMER_WAIT,SUM_TIMER_WAIT,SUM_TIMER_WAIT,SUM_TIMER_WAIT：等待时间

• events_statements_summary_by_digest 表中的SQL语句记录SQL语句时最大存储1024个字节，超过1024字节的会截取1024字节进行hash运算，所以为了追求数据的完整性和准确性还是推荐使用慢查询日志分析。

四、查看最近执行失败的语句

• 其实前面查询最近执行语句时也会查询出失败的语句。也可以通过错误号(MYSQL_ERRNO) 准确查询错误的语句。而且执行错误的语句的EVNET_NAME一般为statement/sql/error（不是一定的），并且其MESSAGE_TEXT字段内容为错误提示。

select THREAD_ID,EVENT_NAME,MYSQL_ERRNO,SQL_TEXT,MESSAGE_TEXT from events_statements_history where MYSQL_ERRNO=1064\G ;

• 当不知道错误号时可以通过errors属性查询。

 select THREAD_ID,EVENT_NAME,MYSQL_ERRNO,SQL_TEXT,MESSAGE_TEXT,errors from events_statements_history where errors>0\G ;

• 当然如果你对错误的信息不是很感兴趣的话也可以通过events_statements_summary_by_digest的NUM_ERRORS字段查询，值得注意的是有些错误的SQL语句并不存储在这个表中，例如==select * from;==就不会被存储。（events_statements_summary_by_digest具体存储哪些类型的语句我还在查资料中）。

select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ERRORS,FIRST_SEEN,LAST_SEEN from events_statements_summary_by_digest where SUM_ERRORS!=0 order by LAST_SEEN desc\G;

五、查询SQL语句的执行进度和阶段

对于一些SQL语句，例如百万级别数据的插入会执行较长的时间，或者某个SQL语句操作的表或数据行的锁无法获得导致被阻塞，检测这种类型的语句也是有必要的。对应的就是stage（阶段时间的检测）。

• 开启阶段事件相关的instruments和consumers

update setup_instruments set enabled='yes',timed='yes' where name like 'stage/%';
update setup_consumers set enabled='yes' where name like '%stage%';

• 关闭其他线程的时间采集

update threads set instrumented = 'NO' where thread_id!=sys.ps_thread_id(connection_id());

• 清空阶段事件的收集表。
  
• 查看DML（data manipulation language：数据操纵语言）语句的执行阶段
  
• 查看DDL（database defined language：数据库定于语言）语句的执行阶段
  

六、最近的事务执行信息

对于事务来说有正在执行状态、回滚状态、已提交状态。通过performance_schema.evnets_transactions_*表可以查询事务的相关信息，包括开始事务的线程ID，事务的安全隔离级别等等，但是无法看到事务开启的时间，这要借助information.innodb_trx这个表来查询。

• 开启事务事件的instruments和consumers。(默认未开启)

update setup_instruments set enabled='yes',timed='yes' where name like 'transaction';
update setup_consumers set enabled='yes' where name like '%transaction%';

• 清除transaction表中的数据

truncate events_transactions_current;
truncate events_transactions_history;
truncate events_transactions_history_long; 

• 使用后evnets_transactions_current数据库表查看活跃的事务。
  （evnets_transactions_current表中只会存放活跃事务的信息，或者存放最后一个一已提交或回滚的事务信息。）

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;

• 使用后evnets_transactions_history数据库表查看已提交和回滚的事务

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\G;