如何看待mysql5.7sys数据库表-创新互联

如何看待mysql 5.7 sys数据库表，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

引子

       mysql自mysql 5.6引入了performance_schema数据库，对于监控及调优数据库提供了极大的便利。但是performance_schema数据库中有些数据仍显粗放，不易利用，需要数据库同学们进行再次聚合开发。mysql 5.7开始，增加内置数据库sys,对于performance_schema数据库的相关表进行二次开发及封装。便于运维小伙伴直接使用，极大提升了运维的效率。

     sys数据库由一系列的表构成。下列罗列一些重要表，方便大家入门，好有个直观感受。

sys数据库表

• host_summary表

-----

  显示以主机名称分组的 SQL语句的数量，文件IO的数量，即文件IO的延迟，当前的连接会话数量，连接对应数据库用户数量，所对应的内存分配数量

（注：由此可知每个主机的负载分布情况，可以通过基本纵向对比，知道，每个主机节点的负载的变化趋势）

-----

mysql> select * from host_summary;
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host      | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| localhost |      17264 | 7.35 s            | 425.55 us             |         386 |    14512 | 1.07 s          |                   5 |                33 |            2 | 0 bytes        | 0 bytes                |
| three57   |         10 | 1.00 m            | 6.00 s                |           0 |       12 | 326.38 us       |                   0 |                 1 |            1 | 0 bytes        | 0 bytes                |
| two57     |         10 | 1.00 m            | 6.00 s                |           0 |       12 | 44.42 us        |                   0 |                 1 |            1 | 0 bytes        | 0 bytes                |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
3 rows in set (0.01 sec)

• host_summary_by_file_io表

----------

基于主机名称进行分组，显示每个主机名称的IO数量及IO延迟

----------

mysql> select * from host_summary_by_file_io;
+------------+------+------------+
| host       | ios  | io_latency |
+------------+------+------------+
| background | 3333 | 1.14 s     |
| localhost  | 7256 | 536.72 ms  |
| three57    |   12 | 326.38 us  |
| two57      |   12 | 44.42 us   |
+------------+------+------------+
4 rows in set (0.01 sec)

• host_summary_by_file_io_type表

----

某个主机下到底哪个子组件的IO产生最多，然后进行具体性分析

----

mysql> select * from host_summary_by_file_io_type;
+------------+--------------------------------------+-------+---------------+-------------+
| host       | event_name                           | total | total_latency | max_latency |
+------------+--------------------------------------+-------+---------------+-------------+
| background | wait/io/file/innodb/innodb_log_file  |   323 | 738.16 ms     | 19.08 ms    |
| background | wait/io/file/innodb/innodb_data_file |  1423 | 380.97 ms     | 21.19 ms    |
| background | wait/io/file/sql/binlog_index        |    31 | 12.76 ms      | 11.59 ms    |
| background | wait/io/file/sql/binlog              |    31 | 6.52 ms       | 2.14 ms     |
| background | wait/io/file/sql/FRM                 |  1404 | 951.13 us     | 29.74 us    |
| background | wait/io/file/sql/casetest            |    15 | 399.98 us     | 340.60 us   |
| background | wait/io/file/myisam/kfile            |    41 | 93.75 us      | 33.20 us    |
| background | wait/io/file/sql/ERRMSG              |     5 | 59.83 us      | 25.11 us    |
| background | wait/io/file/myisam/dfile            |    53 | 53.63 us      | 4.03 us     |
| background | wait/io/file/mysys/cnf               |     5 | 18.89 us      | 6.34 us     |
| background | wait/io/file/sql/pid                 |     3 | 16.42 us      | 10.14 us    |
| background | wait/io/file/mysys/charset           |     3 | 13.50 us      | 6.53 us     |
| background | wait/io/file/sql/global_ddl_log      |     2 | 3.15 us       | 1.87 us     |
| localhost  | wait/io/file/innodb/innodb_log_file  |    74 | 182.02 ms     | 16.42 ms    |
| localhost  | wait/io/file/sql/binlog              |    95 | 180.14 ms     | 15.37 ms    |
| localhost  | wait/io/file/sql/file_parser         |   438 | 76.83 ms      | 7.99 ms     |
| localhost  | wait/io/file/innodb/innodb_data_file |    47 | 35.92 ms      | 8.78 ms     |
| localhost  | wait/io/file/sql/FRM                 |  2511 | 24.19 ms      | 10.98 ms    |
| localhost  | wait/io/file/csv/metadata            |     8 | 10.64 ms      | 6.13 ms     |

• host_summary_by_statement_latency表

----

每个主机的 延迟，以及大延迟，延迟的构成子组件

----

mysql> select * from sys.host_summary_by_statement_latency;
+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host       | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| two57      |    10 | 1.00 m        | 1.00 m      | 0 ps         |         5 |             0 |             0 |          0 |
| three57    |    10 | 1.00 m        | 1.00 m      | 0 ps         |         5 |             0 |             0 |          0 |
| localhost  |  9455 | 3.73 s        | 2.07 s      | 100.57 ms    |      3521 |        179048 |            21 |        197 |
| background |     0 | 0 ps          | 0 ps        | 0 ps         |         0 |             0 |             0 |          0 |
+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
4 rows in set (0.01 sec)

• memory_by_thread_by_current_bytes表

----

各个线程的内存分配的性能对比（注：各种MYSQL线程：IO READ THREAD,IO WRITE THREAD,

PAGE_CLEANER THREAD,IBUF THREAD,WORKER_THREAD,MONITOR THREAD,LOCK TIMEOUT

 THREAD,DUMP THREAD,用于组复制的 接受线程及用于组复制的认证广播线程），SLAVE的SQL线程，

MAIN THREAD

(注：这样就了解哪个线程消耗的内存最多，进行纵向对比，就知道 线程的消耗历史，以及是否出现性能问题

----

mysql> select * from sys.memory_by_thread_by_current_bytes;
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user                                  | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
|         5 | innodb/io_read_thread                 |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         6 | innodb/io_write_thread                |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         7 | innodb/io_write_thread                |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         8 | innodb/io_write_thread                |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         9 | innodb/io_write_thread                |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        10 | innodb/page_cleaner_thread            |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        11 | innodb/io_read_thread                 |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        12 | innodb/io_log_thread                  |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        13 | innodb/io_ibuf_thread                 |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        15 | innodb/srv_master_thread              |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        16 | innodb/srv_purge_thread               |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        17 | innodb/srv_worker_thread              |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        18 | innodb/srv_worker_thread              |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        19 | innodb/srv_worker_thread              |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        20 | innodb/srv_monitor_thread             |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        21 | innodb/srv_error_monitor_thread       |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        22 | innodb/srv_lock_timeout_thread        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        23 | innodb/dict_stats_thread              |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        24 | innodb/buf_dump_thread                |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        25 | sql/signal_handler                    |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        26 | sql/compress_gtid_table               |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        31 | group_rpl/THD_applier_module_receiver |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        32 | group_rpl/THD_certifier_broadcast     |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        33 | sql/slave_sql                         |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        66 | root@localhost                        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        67 | root@localhost                        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        68 | root@localhost                        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         1 | sql/main                              |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         2 | sql/thread_timer_notifier             |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         3 | innodb/io_read_thread                 |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         4 | innodb/io_read_thread                 |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
31 rows in set (0.04 sec)

关于如何看待mysql 5.7 sys数据库表问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注创新互联-成都网站建设公司行业资讯频道了解更多相关知识。