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)
培訓課件
（收費20元）
聯絡方式
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-2663807/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql 5.7後使用sys資料庫下的表查詢資料庫效能狀況MySql資料庫
- MySQL 5.7 performance_schema庫和sys庫常用SQLMySqlORM
- MySQL預設資料庫之sys庫MySql資料庫
- 遷移MySQL 5.7資料庫MySql資料庫
- shell監控mysql 5.7資料庫MySql資料庫
- phpStudy2018 升級資料庫 MySQL5.7PHP資料庫MySql
- MySQL資料庫SYS CPU高的可能性分析MySql資料庫
- MySQL資料庫之mysql5.7基礎 檢視一個資料庫中的所有表MySql資料庫
- 【資料庫】mysql5.6升級至5.7（物理方式）資料庫MySql
- Homestead 中 sys 資料庫 和 #MySQL50#lost+found 資料庫 是幹嘛的?資料庫MySql
- 破解 MySQL5.7 資料庫的 root 登入密碼MySql資料庫密碼
- Centos7系統如何安裝MySQL5.7資料庫？CentOSMySql資料庫
- 故障分析 | MySQL 5.7 使用臨時表導致資料庫 CrashMySql資料庫
- 初探oceanbase和newsql資料庫SQL資料庫
- 寶塔安裝mysql5.7資料庫怎麼連不上MySql資料庫
- 資料庫管理-第120期 初探Halo資料庫（202301201）資料庫
- MySQL sys庫常用SQL彙總大全MySql
- 【MySQL資料庫】MySQL5.7安裝與配置、視覺化工具安裝和破解MySql資料庫視覺化
- MySQL 5.7 主庫崩潰切備庫MySql
- [資料庫]50道經典SQL練習題,使用MySQL5.7解答資料庫MySql
- [資料庫]【MySQL】MySQL資料庫規範總結資料庫MySql
- MySQL 5.7主從新增新從庫MySql
- MonetDB列存資料庫架構初探資料庫架構
- （二)oralce資料庫中sys_guid()和newid()資料庫GUI
- 資料庫（MySQL）資料庫MySql
- MYSQL資料庫MySql資料庫
- 資料庫-MySQL資料庫MySql
- 資料庫 MySQL資料庫MySql
- 初探MySQL資料備份及備份原理MySql
- Mysql5.7利用frm與ibd恢復資料MySql
- Mysql資料庫-資料模型MySql資料庫模型
- MySQL資料庫資料管理MySql資料庫
- MySQL預設資料庫之mysql庫MySql資料庫
- MySQL資料庫遷移與MySQL資料庫批量恢復MySql資料庫
- 【MySQL 資料庫】MySQL目錄MySql資料庫
- 寶塔資料庫恢復 mysql資料庫丟失恢復 mysql資料庫刪除庫恢復 寶塔mysql資料庫恢復資料庫MySql
- MySQL 資料庫操作MySql資料庫
- MySQL資料庫（二）MySql資料庫