浅谈MySQL5.7 sys schema

  在安装mysql 5.7.9后,进入data目录下我们会明显发现与5.6的不同,没有了test schema,并且,新增了一个sys schema
[root@liukaiy data]# pwd
/usr/local/mysql/data
[root@liukaiy data]# ls
auto.cnf        ib_logfile0  mysql               performance_schema
ib_buffer_pool  ib_logfile1  mysqld_safe.pid     sys
ibdata1         ibtmp1       mysql_upgrade_info

test schema我们就没必要研究了,没意义。现在来谈一下sys schema

  mysql5.7.7开始提供 SYS Schema,从SYS database中,可以获取mem page、io 、latch/mutex/lock等各种性能数据,方便做peformance turning 和 troubleshooting。比如可以方便获取2个sql发生 lock block,用户占用的资源等信息。
  感受就是,越来越像oracle了。

=============
登录mysql,查看数据库中的schema
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

    使用show tables可以看到sys schema中的object。sys schema中包含很多描述数据库性能的视图,大部分视图是成对的,其中一个成员没有x$前缀,而另一个是有x$前缀的,两者的内容完全相同,区别是没有x$前缀的视图提供更人性化输出。

查看会话的状态

processlist和session视图
    sys schema的processlist和session视图提供了比show processlist和INFORMATION_SCHEMA PROCESSLIST 更全面的信息;
    session视图去掉了后台线程的信息,只保留了用户会话

mysql> select * from session limit 1\G
*************************** 1. row ***************************
                thd_id: 41
               conn_id: 15
                  user: root@localhost
                    db: sys
               command: Sleep
                 state: NULL
                  time: 5833
     current_statement: NULL
     statement_latency: NULL
              progress: NULL
          lock_latency: 671.00 us
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 1
       tmp_disk_tables: 1
             full_scan: YES
        last_statement: select * from sys.schema_table_lock_waits
last_statement_latency: 1.21 ms
        current_memory: 0 bytes
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: NULL
             trx_state: NULL
        trx_autocommit: NULL
                   pid: 22981
          program_name: mysql
1 row in set (0.11 sec)

查看哪些用户占用了大量的资源

mysql> show tables like 'user%';
+-----------------------------------+
| Tables_in_sys (user%)             |
+-----------------------------------+
| user_summary                      |
| 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    |
+-----------------------------------+
6 rows in set (0.00 sec)
mysql> select * from user_summary\G
*************************** 1. row ***************************
                  user: root
            statements: 3331        //用户执行的SQL语句总数量
     statement_latency: 2.86 s        //该用户执行SQL总的等待时间,即累计的SQL执行时间
 statement_avg_latency: 857.30 us    //单次SQL的平均执行时间
           table_scans: 67    //全表扫描次数
              file_ios: 23626    //io次数
       file_io_latency: 1.72 s    //io消耗的总时间
   current_connections: 1    //该用户的当前连接会话数
     total_connections: 9    //该用户连接会话总数
          unique_hosts: 1    //用户发起的连接中唯一主机的数量,即从几个主机发起过数据库连接
        current_memory: 0 bytes    //当前被分配的内存
total_memory_allocated: 0 bytes    //总共被分配的内存
*************************** 2. row ***************************
                  user: background
            statements: 0
     statement_latency: 0 ps
 statement_avg_latency: 0 ps
           table_scans: 0
              file_ios: 2127
       file_io_latency: 161.74 ms
   current_connections: 26
     total_connections: 30
          unique_hosts: 0
        current_memory: 0 bytes
total_memory_allocated: 0 bytes
2 rows in set (0.02 sec)
    通过上述查询,可以简单看到每个用户占用的连接、内存、io等资源


如果想要进一步查询某项指标的话,可以查看对应的视图,如
mysql> select * from user_summary_by_statement_latency\G
*************************** 1. row ***************************
         user: root
        total: 3411
total_latency: 2.87 s
  max_latency: 648.56 ms
 lock_latency: 515.50 ms
    rows_sent: 1477
rows_examined: 23042
rows_affected: 408
   full_scans: 68
*************************** 2. row ***************************
         user: background
        total: 0
total_latency: 0 ps
  max_latency: 0 ps
 lock_latency: 0 ps
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0
2 rows in set (0.01 sec)

    这个视图概述了每个用户执行SQL的大体统计信息,本人简单分析如下:
user    用户名
total    该用户总共执行的SQl语句的数量
total_latency    执行SQL总共的等待时间
max_latency    单次执行的最长等待时间
lock_latency    锁等待累计时间
rows_sent    该用户执行SQL累积返回的行数
rows_examined    未执行用户SQL存储引擎读取的总行数
rows_affected    被用户SQL语句影响的总行数
full_scans    该用户执行的SQL中造成全表扫描的总次数

如果想了解各个用户占用的IO带宽,可查询如下视图
mysql> select * from user_summary_by_file_io\G
*************************** 1. row ***************************
      user: root
       ios: 23655
io_latency: 1.72 s
*************************** 2. row ***************************
      user: background
       ios: 2144
io_latency: 162.43 ms
2 rows in set (0.01 sec)
  这个视图相对比较简单,列举的是每个用户下的io事件次数,以及io等待的累计时间

除此之外,我们还可以根据waits_by_user_by_latency视图查看每个用户的等待事件
mysql> desc waits_by_user_by_latency;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| user          | varchar(32)         | YES  |     | NULL    |       |
| event         | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| avg_latency   | text                | YES  |     | NULL    |       |
| max_latency   | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

通过sys schema查询innodb的锁信息
    
innodb_lock_waits
    mysql 5.7.7开始,sys schema中提供了innodb_lock_waits视图,通过这个视图,我们可以更直观清晰地查看当前事务间的阻塞关系。在此列举几个受关注的列,如下:
waiting_trx_id    等待事务的ID
waiting_trx_age    等待的时间
waiting_query    等待锁资源的SQL
waiting_lock_mode    请求锁的模式 
blocking_trx_id    阻塞事务的ID
blocking_trx_age    阻塞事务被执行的时间
blocking_query    阻塞事务正在执行的SQL
blocking_lock_mode    阻塞的锁模式
locked_table    被加锁的表
locked_index    被加锁的索引

接下来简单模拟两个事务间的阻塞

1)甲会话中执行
mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | liu  |
|  2 | kai  |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from t1 where id=2 for update;
+----+------+
| id | name |
+----+------+
|  2 | kai  |
+----+------+
1 row in set (0.00 sec)

2)乙会话执行如下SQL
mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from liu.t1 where id=2;

3)丙会话中查询事务间阻塞
mysql> select waiting_trx_id,waiting_trx_age,waiting_query,waiting_lock_mode,blocking_trx_id,blocking_trx_age,blocking_query,blocking_lock_mode,locked_table,locked_index from sys.innodb_lock_waits\G
*************************** 1. row ***************************
    waiting_trx_id: 1439
   waiting_trx_age: 00:07:53
     waiting_query: delete from liu.t1 where id=2
 waiting_lock_mode: X
   blocking_trx_id: 1438
  blocking_trx_age: 00:08:11
    blocking_query: NULL
blocking_lock_mode: X
      locked_table: `liu`.`t1`
      locked_index: PRIMARY
1 row in set (0.00 sec)

  我们发现,之前需要在information_schema中连接多个视图的查询,现在只需要查询这一个视图就能清晰地获取我们需要的信息了(以前只能通过information_schema中的,InnoDB_trx、INNODB_LOCKS、INNODB_LOCK_WAITS查询

schema_table_lock_waits 
  5.7.9中新增的视图,通过这个视图可以查询阻塞会话的metadata lock信息
mysql> desc schema_table_lock_waits;
+------------------------------+---------------------+------+-----+---------+-------+
| Field                        | Type                | Null | Key | Default | Extra |
+------------------------------+---------------------+------+-----+---------+-------+
| object_schema                | varchar(64)         | YES  |     | NULL    |       |
| object_name                  | varchar(64)         | YES  |     | NULL    |       |
| waiting_thread_id            | bigint(20) unsigned | NO   |     | NULL    |       |
| waiting_pid                  | bigint(20) unsigned | YES  |     | NULL    |       |
| waiting_account              | text                | YES  |     | NULL    |       |
| waiting_lock_type            | varchar(32)         | NO   |     | NULL    |       |
| waiting_lock_duration        | varchar(32)         | NO   |     | NULL    |       |
| waiting_query                | longtext            | YES  |     | NULL    |       |
| waiting_query_secs           | bigint(20)          | YES  |     | NULL    |       |
| waiting_query_rows_affected  | bigint(20) unsigned | YES  |     | NULL    |       |
| waiting_query_rows_examined  | bigint(20) unsigned | YES  |     | NULL    |       |
| blocking_thread_id           | bigint(20) unsigned | NO   |     | NULL    |       |
| blocking_pid                 | bigint(20) unsigned | YES  |     | NULL    |       |
| blocking_account             | text                | YES  |     | NULL    |       |
| blocking_lock_type           | varchar(32)         | NO   |     | NULL    |       |
| blocking_lock_duration       | varchar(32)         | NO   |     | NULL    |       |
| sql_kill_blocking_query      | varchar(31)         | YES  |     | NULL    |       |
| sql_kill_blocking_connection | varchar(25)         | YES  |     | NULL    |       |
+------------------------------+---------------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
每行记录描述的意义一览便知,本文不一一赘述

        MySQL5.7 新特性诸多,单单是sys schema这一个特性又岂是三言两语能说明白的,本文只是略微列举一二,希望能引起各位看客朋友们探索的欲望。

上一篇:Oracle undo机制拙见


下一篇:Python学习之运算符