标签：latency statement MySQL sys 监控 mysql current schema
1.基础介绍
MySQL 5.7引入了sys schema，有了它，我们排查分析一些问题时将更得心应手。sys schema里主要存储的是视图、存储过程、函数等。
在mysql的5.7版本中，性能模式是默认开启的，如果想要显式的关闭的话需要修改配置文件，不能直接进行修改。
查看performance_schema的属性
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.01 sec)
--在配置文件中修改performance_schema的属性值，on表示开启，off表示关闭
[mysqld]
performance_schema=ON
（1）视图：用于结果的汇总展示及配置持久化。
查看sys schema视图：
select table_name , table_type, engine from information_schema.tables where table_schema = 'sys' order by table_name;
+-----------------------------------------------+------------+--------+
| table_name | table_type | engine |
+-----------------------------------------------+------------+--------+
| host_summary | VIEW | NULL |
| host_summary_by_file_io | VIEW | NULL |
| host_summary_by_file_io_type | VIEW | NULL |
| host_summary_by_stages | VIEW | NULL |
对于每一个视图，都有两种形式：第一种是便于人类阅读的形式，格式化了时间单位和字节单位，另外一种以”x$”开头的视图名称，是为了便于工具处理。如下所示：
mysql> select table_name from information_schema.tables where table_schema = 'sys' and table_name like '%memory%';
+-------------------------------------+
| table_name |
+-------------------------------------+
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_bytes |
| memory_global_total |
| x$memory_by_host_by_current_bytes |
| x$memory_by_thread_by_current_bytes |
| x$memory_by_user_by_current_bytes |
| x$memory_global_by_current_bytes |
| x$memory_global_total |
+-------------------------------------+
10 rows in set (0.00 sec)
（2）存储过程：用于对Performance schema的控制及收集；
大部分存储过程都是PS开头的，PS是performance schema的简写，这些存储过程都是用来控制performance schema统计的行为。
（3）函数：对于Performance schema的配置及数据格式化。
这些视图的信息来自哪里呢？视图中的信息均来自performance schema和information schema中的统计信息。
show function status where db = 'sys';
show PROCEDURE status where db='sys';
2、sys_config 表
CREATE TABLE `sys_config` (
`variable` varchar(128) NOT NULL comment '配置选项名称',
`value` varchar(128) DEFAULT NULL comment '配置选项值',
`set_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '该行配置修改的时间',
`set_by` varchar(128) DEFAULT NULL comment '配置信息修改者，如果从被安装没有修改过，那么这个数据应该为NULL',
PRIMARY KEY (`variable`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表中默认数据为
variable value set_time set_by
diagnostics.allow_i_s_tables OFF 2015-11-20 16:04:38 NULL
diagnostics.include_raw OFF 2015-11-20 16:04:38 NULL
statement_performance_analyzer.limit 100 2015-11-20 16:04:38 NULL
statement_performance_analyzer.view 2015-11-20 16:04:38 NULL
statement_truncate_len 64 2016-01-22 17:00:16 NULL
以上值的会话变量为@sys.+表中variable字段，譬如：@sys.statement_truncate_len 可以set @sys.statement_truncate_len = 32 临时改变值，在会话中会一直使用这个值，如果想要恢复使用表的默认值，只需要将这个会话值设置为null；set @sys.statement_truncate_len = null;
diagnostics.allow_i_s_tables
diagnostics.include_raw
这两个值默认为OFF ，前者如果开启表示允许diagnostics() 存储过程执行扫描information_schema.tables 表，如果表很多，那么可能会很耗性能，后者开启将会从metrics视图输出未加工处理的数据 。
statement_performance_analyzer.limit 视图在没有加limit限制时，返回的最大行数
这个表非默认选项还有一个@sys.debug参数
可以手动加入
INSERT INTO sys_config (variable, value) VALUES('debug', 'ON');
UPDATE sys_config SET value = 'OFF' WHERE variable = 'debug';
SET @sys.debug = NULL;
sys schema介绍：
1. 主机相关：以host_summary开头，主要汇总了IO延迟的信息。
2. Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
3. I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。
4. 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况
5. 连接与会话信息：processlist和session相关视图，总结了会话相关信息。
6. 表相关：以schema_table开头的视图，展示了表的统计信息。
7. 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
8. 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
9. 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。
10. 等待事件相关信息：以wait开头，展示等待事件的延迟情况。
3、基本视图介绍
host_summary 总体视图host维度
host_summary_by_file_io
host_summary_by_file_io_type
host_summary_by_stages
host_summary_by_statement_latency
host_summary_by_statement_type
字段名 意义
host 从哪个客户机上连过来。如果是NULL，表示内部的进程
statements 该客户机共执行了多少语句
statement_latency 该客户机发来等待语句执行的时间
statement_avg_latency 该客户机等待语句执行的平均时间
table_scans 该客户机发生全表扫描的次数
file_ios 该客户机上IO事件请求的次数
file_io_latency 该客户机请求等待IO的时间
current_connections 该客户机当前的连接数
total_connections 该客户机连接DB共有多少次
unique_user 该客户机上有几个不同用户名的帐户连接过来
current_memory 该客户机上当前连接占用的内存
total_memory_allocated 该客户机上的请求总共使用的内存量（历史累计值）
IO视图：
io_by_thread_by_latency
io_global_by_file_by_bytes
io_global_by_file_by_latency
io_global_by_wait_by_bytes
io_global_by_wait_by_latency
latest_file_io
file 被操作的文件名
count_read 总共有多少次读
total_read 总共读了多少字节
avg_read 平均每次读的字节数
count_write 总共有多少次写
total_written 总共写了多少字节
avg_write 平均每次写的字节大小
total 读和写总共的IO大小
write_pct 写请求占总IO请求中的百分比（就是通常所说的读写比）
user_summary 总体视图，user维度
user_summary_by_file_io
user_summary_by_file_io_type
user_summary_by_stages
user_summary_by_statement_latency
user_summary_by_statement_type
user 客户端连接过来的用户名，如果是NULL，表示内部进程
statements 该用户执行了多少SQL
statement_latency 该用户执行SQL的总延迟时间
statement_avg_latency 该用户执行SQL的平均延迟时间
table_scans 该用户执行SQL时发生全表扫描的次数
file_ios 该用户发生的IO请求总量
file_io_latency 该用户发生的IO请求总延迟时间
current_connections 该用户当前的连接
total_connections 该用户总的连接数
unique_hosts 该用户从几个不同客户机连接过来
current_memory 该用户当前占用的内存
total_memory_allocated 该用户总共申请到的内存（历史累计值）
内存总体视图
memory_by_host_by_current_bytes
memory_by_thread_by_current_bytes
memory_by_user_by_current_bytes
memory_global_by_current_bytes
memory_global_total
total_allocated Server 总共分配出去的内存大小
thread_id MySQL内部线程ID，可以和session视图中的thd_id关联
user 当前线程是哪个用户创建
current_count_used 当前线程正在使用且未释放的内存块（内存块不是固定大小的）
current_allocated 当前线程正在使用且未释放的内存块大小（字节）
current_avg_alloc 当前线程每个内存块平均分配内存大小（字节）
current_max_alloc 当前线程单次曾经分配的最多内存大小（字节）
total_allocated 当前线程总共分配的内存大小
statement_analysis 视图
statements_with_errors_or_warnings
statements_with_full_table_scans
statements_with_runtimes_in_95th_percentile
statements_with_sorting
statements_with_temp_tables
query 格式化后的SQL（将SQL中的参数替换成？）
db 在哪个DB中执行，如果为NULL表示在任何DB
full_scan 是否使用了全表扫描
exec_count 该SQL被执行的总次数
err_count 发生错误的次数
warn_count 发生警告的次数
total_latency 总共发生延迟的时间
max_latency 最大延迟的时间
avg_latency 平均延迟的时间
lock_latency 因锁等待占用的总时间
rows_sent 执行该SQL返回的总行数
rows_sent_avg 执行该SQL平均返回的行数
rows_examined 执行该SQL扫描的总行数
rows_examined_avg 执行该SQL平均每次扫描的行数
tmp_tables 该SQL生成内存临时表的总次数
tmp_disk_tables 该SQL生成磁盘文件临时表的总次数
rows_sorted 该SQL总共排序的行数
sort_merge_passes 用于排序中合并的总次数
digest 该语句的HASH值
first_seen 该SQL最早出现的时间
last_seen 该SQL最近出现的时间
processlist视图
thd_id 内部线程ID
conn_id 连接的ID（对应show processlist中的"Id"列）
user 该线程创建的用户名
db 连接的DB，如果是NULL表示后台线程
command Client 发起命令的类型提示
state 命令的状态
time 基于上面state停留的时间
current_statement 该线程执行的语句
statement_latency 语句运行总共占用时间
progress 该语句运行完成的百分比
lock_latency 该语句用于锁等待的时间
rows_examined 该语句扫描的行数
rows_send 该语句返回的行数
rows_affected 该语句影响到的行数(写入语句)
tmp_tables 形成内存临时表的次数
tmp_disk_tables 形成磁盘临时表的次数
full_scan 全表描扫的次数
last_statement 上一条被执行的SQL
last_statement_latency 上一条被执行的SQL时延
current_memory 当前线程占用的内存
last_wait 上一次等待事件
last_wait_latency 上一次等待时间时延
source 上一次等待事件对应的源码位置
trx_latency 事务时延
trx_state 当前事务状态
pid 对应到系统里的pid
program_name 连接进来的标识名
session视图
session视图和processlist视图基本一样，只是把后台线程过滤掉。
innodb视图
innodb_buffer_stats_by_schema
innodb_buffer_stats_by_table
innodb_lock_waits
object_schema schema名
allocated 该schema上分配的buffer pool大小（字节）
data 该schema中缓存的数据大小（字节）
pages 该schema中缓存的data page数
pages_hashed 该schema中分配了多少hashed page
pages_old 该schema中的old page数量
rows_cached 该schema中缓存的row data数量
innodb_buffer_stats_by_table视图
和innodb_buffer_stats_by_schema基本一致
wait等待事件
wait_classes_global_by_avg_latency
wait_classes_global_by_latency
waits_by_host_by_latency
waits_by_user_by_latency
waits_global_by_latency
events 等待事件名称
total 次数
total_latency 总延迟
max_latency 最大延迟
min_latency 最小延迟
information_schema 库中新增了三个关于锁的表，亦即 innodb_trx、innodb_locks 和 innodb_lock_waits；
innodb_trx 表记录当前运行的所有事务；
innodb_locks 表记录当前出现的锁；
innodb_lock_waits 表记录锁等待的对应关系；
SHOW ENGINE INNODB STATUS；
SHOW FULL PROCESSLIST；
启用 InnoDB Lock Monitor；
3.举例
（1）ps_truncate_all_tables 的作用就是truncate所有performance schema中summary相关的表。
（2）查询：每个客户端IP过来的连接消耗了多少资源：
mysql> select * from host_summary;
（3）查看某个数据文件上发生了多少IO请求：，读多，还是写的多
mysql> select * from io_global_by_file_by_bytes;
哪个表上的IO请求最多？
mysql> select * from io_global_by_file_by_bytes where file like '%ibd' order by total desc limit 10;
（4）用户消耗的资源，查看哪个用户消耗了多少资源：
mysql> select * from user_summary;
（5）查看总共分配了多少内存？
mysql> select * from memory_global_total;
（6）据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的
查看当前连接情况：
mysql> select host, current_connections, statements from host_summary;
（7）查看当前正在执行的SQL：和执行show full processlist的效果相当，但更强大，更多信息。
mysql> show processlist;
mysql> select conn_id,user,db,command,time,state,statement_latency,progress,lock_latency,rows_examined,rows_sent,tmp_tables,tmp_disk_tables,full_scan,current_memory from session;
（8）数据库中哪些SQL被频繁执行？查询TOP 10最热SQL：
mysql> select db,exec_count,query from statement_analysis order by exec_count desc limit 10;
增加了零时表，磁盘零时表，发送行，平均延迟，排序，等等。
mysql> select db,exec_count,query,full_scan,avg_latency,max_latency,rows_sent_avg,tmp_tables,tmp_disk_tables,rows_sorted,sort_merge_passes from statement_analysis order by exec_count desc limit 10;
（9）哪个表被访问的最多 ？
先访问statement_analysis，根据热门SQL排序找到相应的数据表。
（10）哪些语句延迟比较严重 ？
查看 statement_analysis 中 avg_latency的最高的SQL：
mysql> select db,exec_count,query,full_scan,avg_latency,max_latency,rows_sent_avg,tmp_tables,tmp_disk_tables,rows_sorted,sort_merge_passes from statement_analysis order by avg_latency desc limit 10;
（11）哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表 ？
查看 statement_analysis 中哪个SQL的 tmp_tables 、tmp_disk_tables 值大于0即可：
mysql> select db, query,tmp_tables,tmp_disk_tables from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;
（12）哪个表占用了最多的buffer pool ？
mysql> select * from innodb_buffer_stats_by_table order by allocated desc limit 10;
使用原始数据，然后自己来处理输出：
mysql> select object_schema,object_name,(allocated/(1024*1024)) as allocated,(data/(1024*1024)) as data,pages,pages_hashed,pages_old,rows_cached from x$innodb_buffer_stats_by_table order by allocated desc limit 10;
以上allocated、data单位为M。
（13）每个库（database）占用多少buffer pool？
mysql> select * from innodb_buffer_stats_by_schema order by allocated desc limit 10;
（14）每个连接分配多少内存 ?
利用session表和memory_by_thread_by_current_bytes分配表进行关联查询：
mysql> select b.user, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated,current_statement from memory_by_thread_by_current_bytes a, session b where a.thread_id = b.thd_id;
（15）MySQL内部有多个线程在运行？MySQL内部的线程类型及数量：
mysql> select user, count(*) from processlist group by user;
（16）全表扫描的SQL语句
查询语句，db，执行次数，总延迟，未使用索引百分比，平均行发送，平均行扫描，首次执行时间，最后执行时间，digest。
取出执行次数最多，延迟最严重的top 20.
mysql> select query,db,exec_count,total_latency,no_index_used_pct,rows_sent_avg,rows_examined_avg,first_seen,last_seen,digest from statements_with_full_table_scans order by exec_count desc,total_latency desc limit 20;
（18）没有正确关闭数据库连接的用户
SELECT ess.user, ess.host
, (a.total_connections - a.current_connections) - ess.count_star as not_closed
, ((a.total_connections - a.current_connections) - ess.count_star) * 100 /
(a.total_connections - a.current_connections) as pct_not_closed
FROM performance_schema.events_statements_summary_by_account_by_event_name ess
JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host)
WHERE ess.event_name = 'statement/com/quit'
AND (a.total_connections - a.current_connections) > ess.count_star ;
三、故障：
mysql> select * from host_summary;
1356 - View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
可能是权限：
1.定义者：DEFINER=`mysql.sys`@`localhost`
2.使用的用户dba，具有超级权限。
检查了host_summary本身就是invoke定义，那么使用root也无法打开。
--检查该视图本身。
mysql> show create view host_summary\G
或者：SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'host_summary';
直接执行其中的视图：
select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',。。。。。
[Err] 1305 - FUNCTION sys.format_time does not exist
原来是函数不存在。
方案1：
一种直接到base目录下寻找，只修补需要的函数等。通过SQL执行。
cd /usr/local/mysql_3307/
[root@mysql mysql_3307]# cat share/mysql_sys_schema.sql | grep 'FUNCTION format_time'
DELIMITER $
CREATE DEFINER='mysql.sys'@'localhost' FUNCTION format_time ( picoseconds TEXT ) RETURNS TEXT CHARSET UTF8
COMMENT '\n Description\n \n Takes a raw picoseconds value, and converts it to a human readable form.\n \n Picoseconds are the precision that all latency values are printed in \n within Performance Schema, however are not user friendly when wanting\n to scan output from the command line.\n \n Parameters\n \n picoseconds (TEXT): \n The raw picoseconds value to convert.\n \n Returns\n \n TEXT\n \n Example\n \n mysql> select format_time(342342342342345);\n +------------------------------+\n | format_time(342342342342345) |\n +------------------------------+\n | 00:05:42 |\n +------------------------------+\n 1 row in set (0.00 sec)\n \n mysql> select format_time(342342342);\n +------------------------+\n | format_time(342342342) |\n +------------------------+\n | 342.34 us |\n +------------------------+\n 1 row in set (0.00 sec)\n \n mysql> select format_time(34234);\n +--------------------+\n | format_time(34234) |\n +--------------------+\n | 34.23 ns |\n +--------------------+\n 1 row in set (0.00 sec)\n '
SQL SECURITY INVOKER DETERMINISTIC NO SQL
BEGIN
IF picoseconds IS NULL THEN RETURN NULL; ELSEIF picoseconds >= 604800000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 604800000000000000, 2), ' w');
ELSEIF picoseconds >= 86400000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 86400000000000000, 2), ' d');
ELSEIF picoseconds >= 3600000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 3600000000000000, 2), ' h');
ELSEIF picoseconds >= 60000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 60000000000000, 2), ' m');
ELSEIF picoseconds >= 1000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000000, 2), ' s');
ELSEIF picoseconds >= 1000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000, 2), ' ms');
ELSEIF picoseconds >= 1000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000, 2), ' us');
ELSEIF picoseconds >= 1000 THEN RETURN CONCAT(ROUND(picoseconds / 1000, 2), ' ns');
ELSE RETURN CONCAT(picoseconds, ' ps');
END IF;
END $
DELIMITER ;
方案2：
直接整个sys修复。
cp /data/mysql_3307/share/mysql_sys_schema.sql /data/mysql_3307/share/mysql_sys_schema.sql.bak
echo '$$$' >> /data/mysql_3307/share/mysql_sys_schema.sql.bak
mysql -uroot -p -S /data/mysql_3307/mysql.sock --delimiter='$$$' </data/mysql_3307/share/mysql_sys_schema.sql.bak
参考链接：https://www.osso.nl/blog/mysql-sys-schema-mysqldump-failure/
标签：latency,
statement,
MySQL,
sys,
监控,
mysql,
current,
schema
From： https://www.cnblogs.com/rcsy/p/18387625