如何看待mysql 5.7 sys数据库表
短信预约 -IT技能 免费直播动态提醒
如何看待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数据库表问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/7.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
