如何看待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*fromhost_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.35s|425.55us|386|14512|1.07s|5|33|2|0bytes|0bytes||three57|10|1.00m|6.00s|0|12|326.38us|0|1|1|0bytes|0bytes||two57|10|1.00m|6.00s|0|12|44.42us|0|1|1|0bytes|0bytes|+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+3rowsinset(0.01sec)
host_summary_by_file_io表
----------
基于主机名称进行分组,显示每个主机名称的IO数量及IO延迟
----------
mysql>select*fromhost_summary_by_file_io;+------------+------+------------+|host|ios|io_latency|+------------+------+------------+|background|3333|1.14s||localhost|7256|536.72ms||three57|12|326.38us||two57|12|44.42us|+------------+------+------------+4rowsinset(0.01sec)
host_summary_by_file_io_type表
----
某个主机下到底哪个子组件的IO产生最多,然后进行具体性分析
----
mysql>select*fromhost_summary_by_file_io_type;+------------+--------------------------------------+-------+---------------+-------------+|host|event_name|total|total_latency|max_latency|+------------+--------------------------------------+-------+---------------+-------------+|background|wait/io/file/innodb/innodb_log_file|323|738.16ms|19.08ms||background|wait/io/file/innodb/innodb_data_file|1423|380.97ms|21.19ms||background|wait/io/file/sql/binlog_index|31|12.76ms|11.59ms||background|wait/io/file/sql/binlog|31|6.52ms|2.14ms||background|wait/io/file/sql/FRM|1404|951.13us|29.74us||background|wait/io/file/sql/casetest|15|399.98us|340.60us||background|wait/io/file/myisam/kfile|41|93.75us|33.20us||background|wait/io/file/sql/ERRMSG|5|59.83us|25.11us||background|wait/io/file/myisam/dfile|53|53.63us|4.03us||background|wait/io/file/mysys/cnf|5|18.89us|6.34us||background|wait/io/file/sql/pid|3|16.42us|10.14us||background|wait/io/file/mysys/charset|3|13.50us|6.53us||background|wait/io/file/sql/global_ddl_log|2|3.15us|1.87us||localhost|wait/io/file/innodb/innodb_log_file|74|182.02ms|16.42ms||localhost|wait/io/file/sql/binlog|95|180.14ms|15.37ms||localhost|wait/io/file/sql/file_parser|438|76.83ms|7.99ms||localhost|wait/io/file/innodb/innodb_data_file|47|35.92ms|8.78ms||localhost|wait/io/file/sql/FRM|2511|24.19ms|10.98ms||localhost|wait/io/file/csv/metadata|8|10.64ms|6.13ms|
host_summary_by_statement_latency表
----
每个主机的 延迟,以及最大延迟,延迟的构成子组件
----
mysql>select*fromsys.host_summary_by_statement_latency;+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+|host|total|total_latency|max_latency|lock_latency|rows_sent|rows_examined|rows_affected|full_scans|+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+|two57|10|1.00m|1.00m|0ps|5|0|0|0||three57|10|1.00m|1.00m|0ps|5|0|0|0||localhost|9455|3.73s|2.07s|100.57ms|3521|179048|21|197||background|0|0ps|0ps|0ps|0|0|0|0|+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+4rowsinset(0.01sec)
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*fromsys.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|0bytes|0bytes|0bytes|0bytes||6|innodb/io_write_thread|0|0bytes|0bytes|0bytes|0bytes||7|innodb/io_write_thread|0|0bytes|0bytes|0bytes|0bytes||8|innodb/io_write_thread|0|0bytes|0bytes|0bytes|0bytes||9|innodb/io_write_thread|0|0bytes|0bytes|0bytes|0bytes||10|innodb/page_cleaner_thread|0|0bytes|0bytes|0bytes|0bytes||11|innodb/io_read_thread|0|0bytes|0bytes|0bytes|0bytes||12|innodb/io_log_thread|0|0bytes|0bytes|0bytes|0bytes||13|innodb/io_ibuf_thread|0|0bytes|0bytes|0bytes|0bytes||15|innodb/srv_master_thread|0|0bytes|0bytes|0bytes|0bytes||16|innodb/srv_purge_thread|0|0bytes|0bytes|0bytes|0bytes||17|innodb/srv_worker_thread|0|0bytes|0bytes|0bytes|0bytes||18|innodb/srv_worker_thread|0|0bytes|0bytes|0bytes|0bytes||19|innodb/srv_worker_thread|0|0bytes|0bytes|0bytes|0bytes||20|innodb/srv_monitor_thread|0|0bytes|0bytes|0bytes|0bytes||21|innodb/srv_error_monitor_thread|0|0bytes|0bytes|0bytes|0bytes||22|innodb/srv_lock_timeout_thread|0|0bytes|0bytes|0bytes|0bytes||23|innodb/dict_stats_thread|0|0bytes|0bytes|0bytes|0bytes||24|innodb/buf_dump_thread|0|0bytes|0bytes|0bytes|0bytes||25|sql/signal_handler|0|0bytes|0bytes|0bytes|0bytes||26|sql/compress_gtid_table|0|0bytes|0bytes|0bytes|0bytes||31|group_rpl/THD_applier_module_receiver|0|0bytes|0bytes|0bytes|0bytes||32|group_rpl/THD_certifier_broadcast|0|0bytes|0bytes|0bytes|0bytes||33|sql/slave_sql|0|0bytes|0bytes|0bytes|0bytes||66|root@localhost|0|0bytes|0bytes|0bytes|0bytes||67|root@localhost|0|0bytes|0bytes|0bytes|0bytes||68|root@localhost|0|0bytes|0bytes|0bytes|0bytes||1|sql/main|0|0bytes|0bytes|0bytes|0bytes||2|sql/thread_timer_notifier|0|0bytes|0bytes|0bytes|0bytes||3|innodb/io_read_thread|0|0bytes|0bytes|0bytes|0bytes||4|innodb/io_read_thread|0|0bytes|0bytes|0bytes|0bytes|+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+31rowsinset(0.04sec)
关于如何看待mysql 5.7 sys数据库表问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注亿速云行业资讯频道了解更多相关知识。
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。