MySQL sys性能监控

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/
 
posted @   cdrcsy  阅读(27)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示