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博客

mysql 5.7中崭新的 mysql sys schema

最新文章