mysql 5.7中崭新的 mysql sys schema
声明:本文内容来自于《MySQL运维内参》,笔者读后整理的笔记,仅供学习使用。
以下内容都是MySQL 5.7的新功能
一、 Performance Schema的改进
1、元数据库锁
select * from performance_schema.metadata_locks;
通过该表可以看到:
a)、哪些会话拥有元数据锁;
b)、哪些会话正在等待元数据锁;
c)、哪些请求由于死锁被杀掉,或者锁等待超时而被丢弃。
2、进程跟踪
select * from performance_schema.events_stages_current;
通过该表可以跟踪长时间操作的进度(比如ALTER TABLE):
备注:stages是阶段的意思。
3、查看未提交事务
select * from performance_schema.events_transactions_current;
从mysql 5.7开始,新增events_transactions_current表,通过该表查看当前线上的事务的状态,如果线上数据库遇到undo log大量增长,数据库性能急剧下降,可以通过该表查看当前是否存在处于未提交状态的事务。如果发现的确有大量的事务的state处于active状态,这时可以确定数据库的事务未提交。
二、SYS库的介绍
performation schema使用起来不方便,mysql 5.7用sys库来解决这个问题。
mysql sys库本身不采集和存储什么信息,而是将performance schema和infomation schema的数据以更加容易理解的方式总结归档出的视图。
在sys库中,没有x$前缀的视图提供了更加友好且易读的数据;x$前缀的视图提供了原始数据,需要加工才好看。
1)、
主机相关信息:以host_summary开头的视图,从主机、文件时间类型、语句类型角度展示文件IO延迟的信息;
2)、
innodb buffer pool和锁的相关信息:以innodb开头的视图,汇总了innodb buffer page信息和事务等待锁的信息;
3)、
io使用情况:以io开头的视图,总结了io使用者的信息,包括等待io的情况、io使用量情况;
4)、
内存使用情况:以memory开头的视图,从主机、线程、用户、事件的角度展示了内存使用情况;
5)、
连接与会话信息:processlist和session总结了连接与会话信息;
6)、
表相关信息:以schema_table开头的视图,从全表扫描、innodb缓冲池等方面展示了表统计信息;
7)、
索引信息:其中包含index的视图,统计了索引使用情况,以及重复索引和未使用索引情况;
8)、
语句相关信息:以statement开头的视图,统计的规范化后的语句使用情况,包括错误数、警告数、执行全表扫描的、使用临时表、执行排序等信息;
9)、
用户的相关信息:以user开头的视图,统计了用户使用文件IO、执行的语句统计信息等;
10)、
等待事件相关信息:以wait开头的视图,从主机和事件角度展示等待事件的延迟情况;
11)、
锁信息:innodb_lock_waits和schema_table_lock_waits展示了锁信息
三、重点视图与应用场景
1、查看表访问量
场景:查看每张表的读写次数
select table_schema, table_name, io_read_requests+io_write_requests as io_to_request from sys.schema_table_statistics group by table_schema, table_name order by io_to_request desc limit 10;
+--------------+----------------------+---------------+
| table_schema | table_name | io_to_request |
+--------------+----------------------+---------------+
| oa_2016 | form_trigger_record | 84115 |
| oa_2016 | form_log | 46547 |
| oa_2016 | ctp_content_all | 43784 |
| oa_2016 | org_relationship | 31541 |
2、冗余索引和未使用的索引
通过sys库中的schema_index_statistics 和schema_redundant_indexes两个视图,可以看到哪些索引没有被使用过或者使用率低。
3、表自增id监控
场景:知道哪张表有自增主键,并且监控自增主键是否快要超过阈值
(root@localhost)[sys]> select * from sys.schema_auto_increment_columns \G;
*************************** 1. row ***************************
table_schema: oa_2016
table_name: ctp_content_all
column_name: id
data_type: bigint
column_type: bigint(20)
is_signed: 1
is_unsigned: 0
max_value: 9223372036854775807
auto_increment: 9223369109454088264
auto_increment_ratio: 1.0000
4、查看实例消耗的磁盘IO
(root@localhost)[sys]> select file,avg_read+avg_write as avg_io from sys.io_global_by_file_by_bytes order by avg_io desc limit 10;
+------------------------------------------------+--------+
| file | avg_io |
+------------------------------------------------+--------+
| @@datadir/oa_2016/portal_link_space.frm | 1668 |
| @@datadir/oa_2016/pro_eipusercustomsort.frm | 1633 |
| @@datadir/oa_2016/ctp_dr_url_map.frm | 1585 |
| @@datadir/oa_2016/office_auto_applyinfo.frm | 1583 |
| @@datadir/oa_2016/edoc_exchange_turn_rec.frm | 1521 |
| @@datadir/oa_2016/portal_portlet_property.frm | 1495 |
| @@datadir/oa_2016/showpost_info.frm | 1472 |
| @@datadir/oa_2016/cip_agent.frm | 1448 |
| @@datadir/oa_2016/thirdparty_portal_config.frm | 1437 |
| @@datadir/oa_2016/portal_link_option.frm | 1391 |
+------------------------------------------------+--------+
DBA可以通过该查询大致的了解磁盘IO消耗在哪里,哪些文件消耗的最多。DBA可以根据这些信息,针对某表,某库进行优化。
5、监控全表扫描的sql语句
通过sys.statements_with_full_table_scans来看全表扫描的sql语句:
root@localhost)[sys]> select * from sys.statements_with_full_table_scans limit 2 \G;
*************************** 1. row ***************************
query: SELECT `newsreply0_` . `id` AS ... e_sum` DESC , `newsreply0_` .
db: oa_2016
exec_count: 5
total_latency: 997.89 us
no_index_used_count: 5
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 0
rows_examined: 0
rows_sent_avg: 0
rows_examined_avg: 0
first_seen: 2018-01-05 08:28:42
last_seen: 2018-01-10 10:44:40
digest: 207b3ea3c98e4253780a722248f64965
通过上面可以看到,该sql语句总共执行了5次,有5次都没有使用索引,总共消耗了997.89 us。
6、操作风险
sys库的视图来源于performace_schema和information_schema,我们知道performace_schema开启后,数据库会有10%性能下降:
(root@localhost)[sys]> show variables like '%performance%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| performance_schema | ON |
所以,我们查询sys或者performance_schema时,要谨慎操作。
7参考
参考《mysql运维内参》一书
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28916011/viewspace-2150040/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28916011/viewspace-2150040/