老司机带你体验SYS库多种新玩法

96
Big_Tree
2017.09.10 17:30* 字数 622

知数堂@田帅萌出品, 例子、说明来自鄙人的屌丝机。

导读

MySQL5.7的新特性中,非常突出的特性之一就是sys库,不仅可以通过sys库完成MySQL信息的收集,还可以用来监控和排查问题。
但你知道吗,sys库其实还有很多种玩法,有的或许单看表名就知道了,但有一些,还是需要老司机带路哦~

一.用户、连接类

  1. 查看每个客户端IP过来的连接消耗资源情况。
root@localhost [sys]>select * from host_summary\G
*************************** 1. row ***************************
                  host: localhost
            statements: 504
     statement_latency: 350.92 ms
 statement_avg_latency: 696.27 us
           table_scans: 9
              file_ios: 694
       file_io_latency: 162.42 ms
   current_connections: 1
     total_connections: 2
          unique_users: 1
        current_memory: 0 bytes
total_memory_allocated: 0 bytes
1 row in set (0.01 sec)
  1. 查看每个用户消耗资源情况
    root@localhost [sys]>select * from user_summary\G
    *************************** 1. row ***************************
                      user: repl
                statements: 74
         statement_latency: 7.33 w #等待时间
     statement_avg_latency: 16.64 h  #执行语句平均延迟时间
               table_scans: 0
                  file_ios: 105123  #io时间总数
           file_io_latency: 2.04 s  #文件io延迟
       current_connections: 1  #当前连接数
         total_connections: 7  #总连接数
              unique_hosts: 1
            current_memory: 0 bytes
    total_memory_allocated: 0 bytes
  1. 查看当前连接情况(有多少连接就应该有多少行)
[sys]>select host, current_connections, statements from host_summary\G
*************************** 1. row ***************************
               host: 123.207.x.x
current_connections: 1
         statements: 74
*************************** 2. row ***************************
               host: 182.148.x.x
current_connections: 0
         statements: 2416
*************************** 3. row ***************************
               host: localhost
current_connections: 1
         statements: 11716884
3 rows in set (0.03 sec)
  1. 查看当前正在执行的SQL和执行show full processlist的效果相当。
[sys]>select conn_id, user, current_statement, last_statement from session\G
*************************** 1. row ***************************
          conn_id: 3
             user: root@localhost
current_statement: select conn_id, user, current_ ... t, last_statement from session
   last_statement: NULL
1 row in set (0.04 sec)

二.SQL 和io类

  1. 查看发生IO请求前三名的文件。
[sys]>select * from io_global_by_file_by_bytes order by total desc limit 3\G
*************************** 1. row ***************************
         file: @@datadir/mysql/help_topic.ibd
   count_read: 61
   total_read: 976.00 KiB
     avg_read: 16.00 KiB
  count_write: 0
total_written: 0 bytes
    avg_write: 0 bytes
        total: 976.00 KiB
    write_pct: 0.00
*************************** 2. row ***************************
         file: @@datadir/sakila/film_category.ibd
   count_read: 6
   total_read: 96.00 KiB
     avg_read: 16.00 KiB
  count_write: 0
total_written: 0 bytes
    avg_write: 0 bytes
        total: 96.00 KiB
    write_pct: 0.00

三.buffer pool 、内存

  1. 查看总共分配了多少内存
root@localhost [sys]>select * from memory_global_total\G
*************************** 1. row ***************************
total_allocated: 141.71 MiB
1 row in set (0.00 sec)
  1. 每个库(database)占用多少buffer pool。
select * from innodb_buffer_stats_by_schema order by allocated desc limit 2;
+---------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+-----------+-----------+-------+--------------+-----------+-------------+
| InnoDB System | 7.77 MiB  | 6.95 MiB  |   497 |            0 |        15 |        6249 |
| exercise      | 32.00 KiB | 605 bytes |     2 |            0 |         2 |          16 |
+---------------+-----------+-----------+-------+--------------+-----------+-------------+
2 rows in set (0.81 sec)
  1. 统计innodb 引擎的 innodb缓存

下面的pages指在buffer pool中的page数量,pages_old指在LUR 列表中出于后37%位置的page,当出现buffer page不够用,那么就会征用这些page所占的空间。37%是默认位置,可以自定义

root@localhost [sys]>select * from innodb_buffer_stats_by_schema limit 2;
+---------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | allocated  | data       | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+------------+-------+--------------+-----------+-------------+
| sbtest        | 308.12 MiB | 258.72 MiB | 19720 |        18070 |      7099 |      414149 |
| InnoDB System | 7.77 MiB   | 6.96 MiB   |   497 |            0 |        15 |        6256 |
+---------------+------------+------------+-------+--------------+-----------+-------------+
2 rows in set (0.49 sec)
  1. 统计每张表具体在InnoDB中具体的情况,比如占多少页

其实和第3例的pages的总数都是相等的,你可以借用sum(pages)运算验证一下哦!

root@localhost [sys]>select * from innodb_buffer_stats_by_table limit 2;
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
| sbtest        | sbtest2     | 61.81 MiB | 51.82 MiB |  3956 |         3619 |      3003 |      249879 |
| sbtest        | sbtest4     | 61.73 MiB | 51.77 MiB |  3951 |         3616 |         0 |      249932 |
+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+
2 rows in set (1.19 sec)
  1. 查询每个连接分配了多少内存(我这台没什么业务,所以有点不堪),利用session表和memory_by_thread_by_current_bytes分配表进行关联查询。
root@localhost [sys]>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\G
*************************** 1. row ***************************
              user: repl@123.207.x.x
current_count_used: 0
 current_allocated: 0 bytes
 current_avg_alloc: 0 bytes
 current_max_alloc: 0 bytes
   total_allocated: 0 bytes
 current_statement: NULL

四.字段、索引、锁

  1. 查看表自增字段最大值和当前值,有时候做数据增长的监控,可以作为参考,你使用时,不一定要加条件语句。
select * from schema_auto_increment_columns where table_name in ("payment","sbtest2","test1");
+--------------+------------+-------------+-----------+----------------------+-----------+-------------+------------+----------------+----------------------+
| table_schema | table_name | column_name | data_type | column_type          | is_signed | is_unsigned | max_value  | auto_increment | auto_increment_ratio |
+--------------+------------+-------------+-----------+----------------------+-----------+-------------+------------+----------------+----------------------+
| sakila       | payment    | payment_id  | smallint  | smallint(5) unsigned |         0 |           1 |      65535 |          16050 |               0.2449 |
| sbtest       | sbtest2    | id          | int       | int(10) unsigned     |         0 |           1 | 4294967295 |         999997 |               0.0002 |
| zf           | test1      | id          | int       | int(11)              |         1 |           0 | 2147483647 |              4 |               0.0000 |
+--------------+------------+-------------+-----------+----------------------+-----------+-------------+------------+----------------+----------------------+
3 rows in set (0.04 sec)
  1. MySQL索引使用情况统计
root@localhost [sys]>select * from schema_index_statistics limit 3;
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| sbtest       | sbtest4    | PRIMARY    |       6170545 | 41.27 s        |             0 | 0 ps           |        26099 | 2.90 s         |        12975 | 0 ps           |
| sbtest       | sbtest1    | PRIMARY    |       6179516 | 41.52 s        |             0 | 0 ps           |        25999 | 2.99 s         |        13045 | 0 ps           |
| sbtest       | sbtest3    | PRIMARY    |       6122425 | 40.31 s        |             0 | 0 ps           |        26093 | 3.68 s         |        12731 | 0 ps           |
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
3 rows in set (0.00 sec)
  1. MySQL中有哪些冗余索引和无用索引

库中展示没有冗余索引,所以没有数据,当有联合索引idx_abc(a,b,c)和idx_a(a),那么就算idx_a就算冗余索引了。

root@localhost [sys]>select * from schema_redundant_indexes;
Empty set (0.00 sec)

root@localhost [sys]>select * from schema_unused_indexes limit 3;
+---------------+-------------+---------------------+
| object_schema | object_name | index_name          |
+---------------+-------------+---------------------+
| exercise      | products    | Manufacturer        |
| sakila        | actor       | idx_actor_last_name |
| sakila        | address     | idx_fk_city_id      |
+---------------+-------------+---------------------+
3 rows in set (0.01 sec)
  1. 查看INNODB 锁信息

在未来的版本将被移除,可以采用其他方式

root@localhost [sys]>select * from innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2017-09-10 16:36:39
                    wait_age: 00:00:12
               wait_age_secs: 12
                locked_table: `zf`.`test1`
                locked_index: PRIMARY
                 locked_type: RECORD  #锁类型
              waiting_trx_id: 3657662  #正在等待的事务id
         waiting_trx_started: 2017-09-10 16:36:39
             waiting_trx_age: 00:00:12
     waiting_trx_rows_locked: 1  #等待锁住的行数
   waiting_trx_rows_modified: 0
                 waiting_pid: 4806  #等待锁的id即第一章的4例中的conn_id,
               waiting_query: update test1 set name='wwwwwww' where id=3  #等待的语句
             waiting_lock_id: 3657662:124:3:4
           waiting_lock_mode: X
             blocking_trx_id: 3657661
                blocking_pid: 4805  #持有锁的conn_id
              blocking_query: NULL
            blocking_lock_id: 3657661:124:3:4
          blocking_lock_mode: X  #阻塞锁模式
        blocking_trx_started: 2017-09-10 16:36:03
            blocking_trx_age: 00:00:48
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 4805  #杀死产生阻塞的语句(query)
sql_kill_blocking_connection: KILL 4805  #杀死产生阻塞语句的会话id(session)
1 row in set, 3 warnings (0.00 sec)
#咋们来看看警告什么的是什么
root@localhost [sys]>show warnings ;
+---------+------+-----------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                       |
+---------+------+-----------------------------------------------------------------------------------------------+
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release. |
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release.      |
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release.      |
+---------+------+-----------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
  1. 查看库级别的锁信息,这个需要先打开MDL锁的监控:
root@localhost [sys]>use performance_schema;
root@localhost [sys]>UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
#也可以在my.cnf中设置,不过需要重启给个链接
#https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
root@localhost [sys]>select * from  schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: zf
                 object_name: t_mobile
           waiting_thread_id: 4873
                 waiting_pid: 4848
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table t_mobile add age1 int
          waiting_query_secs: 16
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 4872
                blocking_pid: 4847
            blocking_account: root@localhost
          blocking_lock_type: SHARED_WRITE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 4847
sql_kill_blocking_connection: KILL 4847
*************************** 2. row ***************************
               object_schema: zf
                 object_name: t_mobile
           waiting_thread_id: 4873
                 waiting_pid: 4848
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table t_mobile add age1 int
          waiting_query_secs: 16
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 4873
                blocking_pid: 4848
            blocking_account: root@localhost
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 4848
sql_kill_blocking_connection: KILL 4848
2 rows in set (0.00 sec)

五.线程类

  1. MySQL内部有多个线程在运行,线程类型及数量。
root@localhost [sys]>select user, count(*) from processlist group by user;
+---------------------------------+----------+
| user                            | count(*) |
+---------------------------------+----------+
| innodb/buf_dump_thread          |        1 |
| innodb/dict_stats_thread        |        1 |
| innodb/io_ibuf_thread           |        1 |
| innodb/io_log_thread            |        1 |
| innodb/io_read_thread           |        4 |
| innodb/io_write_thread          |        4 |
| innodb/page_cleaner_thread      |        1 |
| innodb/srv_error_monitor_thread |        1 |
| innodb/srv_lock_timeout_thread  |        1 |
| innodb/srv_master_thread        |        1 |
| innodb/srv_monitor_thread       |        1 |
| innodb/srv_purge_thread         |        1 |
| innodb/srv_worker_thread        |        3 |
| repl@123.207.36.131             |        1 |
| root@localhost                  |        1 |
| sql/compress_gtid_table         |        1 |
| sql/main                        |        1 |
| sql/signal_handler              |        1 |
| sql/thread_timer_notifier       |        1 |
+---------------------------------+----------+
19 rows in set (0.06 sec)
MySQL高可用
Web note ad 1