一 简介：今天我们讲讲如何利用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级别的信息收集,比较遗憾