如何看待mysql 5.7 sys數據庫表

如何看待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數據庫表問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。