个人经验~ 利用5.7的sys库更好的排查问题

墨蓝 2022-01-09 05:57 110阅读 0赞

一 简介：今天我们讲讲如何利用5.7的sys新库进行问题的排查  
二 描述  
1 Sys库所有的数据源来自：performance\_schema和information\_schema。目标是把performance\_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。  
2 mysql对于系统性能和sql语句状态的收集是非常少的,基本排查手段都要靠经验判断,5.7的sys特性能周期性的收集系统状况和sql的状态,有利于DBA更好的对问题进行判断,集成了视图,本身不存储数据  
三 具体sql语句应用  
一 IO相关  
1查看表访问量前10top信息  
select table\_schema,table\_name,sum(io\_read\_requests+io\_write\_requests) io from schema\_table\_statistics group by table\_schema,table\_name order by io desc limit 10;  
2 查看文件访问量前10top信息  
select file,avg\_read+avg\_write as avg\_io from io\_global\_by\_file\_by\_bytes order by avg\_io desc limit 10;  
3 查看具体表的操作类型延迟统计  
select \* from schema\_table\_statistics where table\_schema ='' and table\_name=''(增删查改操作延时)  
4 慢查询扫描全表  
select \* from schema\_tables\_with\_full\_table\_scans（最新的全表扫描相关信息）

5 通过mysql线程 ID 来跟算

1 iotop定位具体的mysql线程 获得线程的PID

2 select name,type,thread\_id,processlst\_id from performance\_schema.threads where thread\_os\_id='PID'; ->一般用户导致的调研的线程就是one\_connections

3 select \* from sys.processlist where processlist\_id='' 或者直接show processlist过滤  
小结 可以代替pt-iopfile工具,直接查询非常方便定位IO问题  
二 内存相关  
select \* from innodb\_buffer\_stats\_by\_table  
select \* from innodb\_buffer\_stats\_by\_schema  
1 分别从库和表角度分析占用内存比,分析内存问题

select event\_name, SUM\_NUMBER\_OF\_BYTES\_ALLOC from pefromance\_schema.memory\_summary\_by\_thread\_by\_event\_name order by SUM\_NUMBER\_OF\_BYTES\_ALLOC desc limit 20;

2 从线程角度分析内存占用

event\_name | SUM\_NUMBER\_OF\_BYTES\_ALLOC |  
\+-------------------------------------------+---------------------------+  
| memory/sql/Filesort\_buffer::sort\_keys | 13942381710536 |  
| memory/memory/HP\_PTRS | 5413962191080 |  
| memory/sql/thd::main\_mem\_root | 1095525347280 |  
| memory/mysys/IO\_CACHE | 471119309576 |

注意点:1 mysql默认是无法查询到相关内存数据的,需要打开数据收集,具体执行语句

UPDATE setup\_instruments SET ENABLED = 'YES',TIMED = 'YES' where NAME like '%memory%';

2 配置文件添加

performance\_schema=1

performance\_schema\_instrument='%=on'

三 等待操作  
1 select \* from waits\_global\_by\_latency（事件等待）  
2 select \* from innodb\_lock\_waits （锁等待,未来information的相关表将被废弃）  
四 应用程序角度  
select \* from host\_summary order by (file\_io\_latency,total\_connections,current\_memory)  
小结 这个视图可以从内存 IO,sql执行延迟等角度综合进行排查,定位具体的应用主机  
五 总结  
1 定位具体的host,user,schema,table,DML操作 能帮你快速的解决大多数问题  
2 从用户和系统内存两方面来分析内存问题  
3 缺少cpu级别的信息收集,比较遗憾

转载于:https://www.cnblogs.com/danhuangpai/p/10660989.html