溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
在上一篇 《按 host 分組統(tǒng)計(jì)視圖|全方位認(rèn)識(shí) sys 系統(tǒng)庫(kù)》 中，我們介紹了sys 系統(tǒng)庫(kù)中按 host 分組統(tǒng)計(jì)的視圖，類似地，本期的內(nèi)容將為大家介紹按照 user 進(jìn)行分類統(tǒng)計(jì)的視圖。下面請(qǐng)跟隨我們一起開始 sys 系統(tǒng)庫(kù)的系統(tǒng)學(xué)習(xí)之旅吧。
查看活躍連接中按用戶分組的總執(zhí)行時(shí)間、平均執(zhí)行時(shí)間、總的IOS、總的內(nèi)存使用量、表掃描數(shù)量等統(tǒng)計(jì)信息，默認(rèn)按照總延遲時(shí)間(執(zhí)行時(shí)間)降序排序。數(shù)據(jù)來(lái)源：performance_schema.accounts、sys.x$user_summary_by_statement_latency、sys.x$user_summary_by_file_io、sys.x$memory_by_user_by_current_bytes
下面我們看看使用該視圖查詢返回的結(jié)果。
# 不帶x$前綴的視圖 admin@localhost : sys 12:54:32> select * from user_summary limit 1\G *************************** 1. row *************************** user: admin statements: 90530 statement_latency: 2.09 h statement_avg_latency: 83.12 ms table_scans: 498 file_ios: 60662 file_io_latency: 31.05 s current_connections: 4 total_connections: 1174 unique_hosts: 2 current_memory: 85.34 MiB total_memory_allocated: 7.21 GiB 1 row in set (0.04 sec) # 帶x$前綴的視圖 admin@localhost : sys 12:55:48> select * from x$user_summary limit 1\G *************************** 1. row *************************** user: admin statements: 90752 statement_latency: 7524792139504000 statement_avg_latency: 82915992369.3583 table_scans: 500 file_ios: 60662 file_io_latency: 31053125849250 current_connections: 4 total_connections: 1174 unique_hosts: 2 current_memory: 89381384 total_memory_allocated: 7755173436 1 row in set (0.02 sec)
視圖字段含義如下：
user：客戶端訪問(wèn)用戶名。如果在performance_schema表中user列為NULL，則假定為后臺(tái)線程，該字段為'background',如果為前臺(tái)線程，則該字段對(duì)應(yīng)具體的用戶名
statements：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句總數(shù)量
statement_latency：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句總延遲時(shí)間(執(zhí)行時(shí)間)
statement_avg_latency：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句中，平均每個(gè)語(yǔ)句的延遲時(shí)間(執(zhí)行時(shí)間)(SUM(stmt.total_latency/SUM(stmt.total))
table_scans：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句發(fā)生表掃描總次數(shù)
file_ios：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句產(chǎn)生的文件I/O事件總次數(shù)
file_io_latency：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句產(chǎn)生的文件I/O事件的總延遲時(shí)間(執(zhí)行時(shí)間)
current_connections：對(duì)應(yīng)用戶的當(dāng)前連接數(shù)
total_connections：對(duì)應(yīng)用戶的歷史總連接數(shù)
unique_hosts：對(duì)應(yīng)用戶來(lái)自不同主機(jī)(針對(duì)主機(jī)名去重)連接的數(shù)量
current_memory：對(duì)應(yīng)用戶的連接當(dāng)前已使用的內(nèi)存分配量
total_memory_allocated：對(duì)應(yīng)用戶的連接的歷史內(nèi)存分配量
PS：該視圖只統(tǒng)計(jì)文件IO等待事件信息("wait/io/file/%")
按照用戶分組的文件I/O延遲時(shí)間、IOS統(tǒng)計(jì)信息，默認(rèn)按照總文件I/O時(shí)間延遲時(shí)間(執(zhí)行時(shí)間)降序排序。數(shù)據(jù)來(lái)源：performance_schema.events_waits_summary_by_user_by_event_name
下面我們看看使用該視圖查詢返回的結(jié)果。
# 不帶x$前綴的視圖 admin@localhost : sys 12:56:18> select * from user_summary_by_file_io limit 3; +------------+-------+------------+ | user | ios | io_latency | +------------+-------+------------+ | admin | 30331 | 15.53 s | | background | 10119 | 2.49 s | | qfsys | 281 | 4.69 ms | +------------+-------+------------+ 3 rows in set (0.01 sec) # 帶x$前綴的視圖 admin@localhost : sys 12:56:21> select * from x$user_summary_by_file_io limit 3; +------------+-------+----------------+ | user | ios | io_latency | +------------+-------+----------------+ | admin | 30331 | 15526562924625 | | background | 10122 | 2489231563125 | | qfsys | 281 | 4689150375 | +------------+-------+----------------+ 3 rows in set (0.00 sec)
視圖字段含義如下：
user：客戶端用戶名。如果在performance_schema表中user列為NULL，則假定為后臺(tái)線程，該字段為'background',如果為前臺(tái)線程，則該字段對(duì)應(yīng)具體的用戶名
ios：對(duì)應(yīng)用戶的文件I/O事件總次數(shù)
io_latency：對(duì)應(yīng)用戶的文件I/O事件的總延遲時(shí)間(執(zhí)行時(shí)間)
PS：該視圖只統(tǒng)計(jì)文件IO等待事件信息("wait/io/file/%")
按照用戶和事件類型(事件名稱)分組的文件I/O延遲和IOS統(tǒng)計(jì)信息，默認(rèn)情況下按照用戶名和總文件I/O時(shí)間延遲時(shí)間(執(zhí)行時(shí)間)降序排序。數(shù)據(jù)來(lái)源：performance_schema.events_waits_summary_by_user_by_event_name
下面我們看看使用該視圖查詢返回的結(jié)果。
# 不帶x$前綴的視圖 admin@localhost : sys 12:56:24> select * from user_summary_by_file_io_type limit 3; +-------+-------------------------------------+-------+---------+-------------+ | user | event_name | total | latency | max_latency | +-------+-------------------------------------+-------+---------+-------------+ | admin | wait/io/file/sql/io_cache | 27955 | 10.53 s | 67.61 ms | | admin | wait/io/file/innodb/innodb_log_file | 912 | 2.14 s | 28.22 ms | | admin | wait/io/file/sql/binlog | 879 | 2.05 s | 31.75 ms | +-------+-------------------------------------+-------+---------+-------------+ 3 rows in set (0.00 sec) # 帶x$前綴的視圖 admin@localhost : sys 12:56:48> select * from x$user_summary_by_file_io_type limit 3; +-------+-------------------------------------+-------+----------------+-------------+ | user | event_name | total | latency | max_latency | +-------+-------------------------------------+-------+----------------+-------------+ | admin | wait/io/file/sql/io_cache | 27955 | 10534662677625 | 67608294000 | | admin | wait/io/file/innodb/innodb_log_file | 912 | 2143870695375 | 28216455000 | | admin | wait/io/file/sql/binlog | 879 | 2054976453000 | 31745275125 | +-------+-------------------------------------+-------+----------------+-------------+ 3 rows in set (0.01 sec)
視圖字段含義如下：
user：客戶端用戶名。如果在performance_schema表中user列為NULL，則假定為后臺(tái)線程，該字段為'background',如果為前臺(tái)線程，則該字段對(duì)應(yīng)具體的用戶名
EVENT_NAME：文件I/O事件名稱
total：對(duì)應(yīng)用戶發(fā)生的文件I/O事件總次數(shù)
latency：對(duì)應(yīng)用戶的文件I/O事件的總延遲時(shí)間(執(zhí)行時(shí)間)
max_latency：對(duì)應(yīng)用戶的單次文件I/O事件的最大延遲時(shí)間(執(zhí)行時(shí)間)
PS：該視圖只統(tǒng)計(jì)文件IO等待事件信息("wait/io/file/%")
按用戶分組的階段事件統(tǒng)計(jì)信息，默認(rèn)情況下按照用戶名和階段事件總延遲時(shí)間(執(zhí)行時(shí)間)降序排序。數(shù)據(jù)來(lái)源：performance_schema.events_stages_summary_by_user_by_event_name
下面我們看看使用該視圖查詢返回的結(jié)果。
# 不帶x$前綴的視圖 admin@localhost : sys 12:56:51> select * from user_summary_by_stages limit 3; +------------+-------------------------------+-------+---------------+-------------+ | user | event_name | total | total_latency | avg_latency | +------------+-------------------------------+-------+---------------+-------------+ | background | stage/innodb/buffer pool load | 1 | 12.56 s | 12.56 s | +------------+-------------------------------+-------+---------------+-------------+ 1 row in set (0.01 sec) # 帶x$前綴的視圖 admin@localhost : sys 12:57:10> select * from x$user_summary_by_stages limit 3; +------------+-------------------------------+-------+----------------+----------------+ | user | event_name | total | total_latency | avg_latency | +------------+-------------------------------+-------+----------------+----------------+ | background | stage/innodb/buffer pool load | 1 | 12561724877000 | 12561724877000 | +------------+-------------------------------+-------+----------------+----------------+ 1 row in set (0.00 sec)
視圖字段含義如下：
user：客戶端用戶名。如果在performance_schema表中user列為NULL，則假定為后臺(tái)線程，該字段為'background',如果為前臺(tái)線程，則該字段對(duì)應(yīng)具體的用戶名
EVENT_NAME：階段事件名稱
total：對(duì)應(yīng)用戶的階段事件的總次數(shù)
total_latency：對(duì)應(yīng)用戶的階段事件的總延遲時(shí)間(執(zhí)行時(shí)間)
avg_latency：對(duì)應(yīng)用戶的階段事件的平均延遲時(shí)間(執(zhí)行時(shí)間)
按照用戶分組的語(yǔ)句統(tǒng)計(jì)信息，默認(rèn)情況下按照語(yǔ)句總延遲時(shí)間(執(zhí)行時(shí)間)降序排序。數(shù)據(jù)來(lái)源：performance_schema.events_statements_summary_by_user_by_event_name
下面我們看看使用該視圖查詢返回的結(jié)果。
# 不帶x$前綴的視圖 admin@localhost : sys 12:57:13> select * from user_summary_by_statement_latency limit 3; +------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+ | user | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+ | admin | 45487 | 1.05 h | 45.66 m | 19.02 s | 6065 | 17578842 | 1544 | 258 | | qfsys | 9 | 929.43 ms | 928.68 ms | 0 ps | 5 | 0 | 0 | 0 | | background | 0 | 0 ps | 0 ps | 0 ps | 0 | 0 | 0 | 0 | +------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+ 3 rows in set (0.00 sec) # 帶x$前綴的視圖 admin@localhost : sys 12:57:34> select * from x$user_summary_by_statement_latency limit 3; +------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+ | user | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+ | admin | 45562 | 3762457232413000 | 2739502018445000 | 19019928000000 | 6068 | 17579421 | 1544 | 259 | | qfsys | 9 | 929429421000 | 928682487000 | 0 | 5 | 0 | 0 | 0 | | background | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+ 3 rows in set (0.00 sec)
視圖字段含義如下：
user：客戶端用戶名。如果在performance_schema表中user列為NULL，則假定為后臺(tái)線程，該字段為'background',如果為前臺(tái)線程，則該字段對(duì)應(yīng)具體的用戶名
total：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句總數(shù)量
total_latency：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句總延遲時(shí)間(執(zhí)行時(shí)間)
max_latency：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句單次最大延遲時(shí)間(執(zhí)行時(shí)間)
lock_latency：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句鎖等待的總時(shí)間
rows_sent：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句返回給客戶端的總數(shù)據(jù)行數(shù)
rows_examined：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句從存儲(chǔ)引擎讀取的總數(shù)據(jù)行數(shù)
rows_affected：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句影響的總數(shù)據(jù)行數(shù)
full_scans：對(duì)應(yīng)用戶執(zhí)行的語(yǔ)句的全表掃描總次數(shù)
按用戶和語(yǔ)句事件類型（事件類型名稱為語(yǔ)句事件的event_name截取最后一部分字符串，也是語(yǔ)句command類型字符串類似）分組的語(yǔ)句統(tǒng)計(jì)信息，默認(rèn)情況下按照用戶名和對(duì)應(yīng)語(yǔ)句的總延遲時(shí)間(執(zhí)行時(shí)間)降序排序。數(shù)據(jù)來(lái)源：performance_schema.events_statements_summary_by_user_by_event_name
下面我們看看使用該視圖查詢返回的結(jié)果。
# 不帶x$前綴的視圖 admin@localhost : sys 12:57:38> select * from user_summary_by_statement_type limit 3; +-------+-------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+ | user | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +-------+-------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+ | admin | alter_table | 2 | 56.56 m | 43.62 m | 0 ps | 0 | 0 | 0 | 0 | | admin | select | 3662 | 5.53 m | 2.02 m | 4.73 s | 6000 | 17532984 | 0 | 148 | | admin | insert | 1159 | 36.04 s | 337.22 ms | 14.23 s | 0 | 0 | 1159 | 0 | +-------+-------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+ 3 rows in set (0.00 sec) # 帶x$前綴的視圖 admin@localhost : sys 12:57:50> select * from x$user_summary_by_statement_type limit 3; +-------+-------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+ | user | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +-------+-------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+ | admin | alter_table | 2 | 3393877088372000 | 2617456143674000 | 0 | 0 | 0 | 0 | 0 | | admin | select | 3663 | 331756087959000 | 121243627173000 | 4733109000000 | 6003 | 17533557 | 0 | 149 | | admin | insert | 1159 | 36041502943000 | 337218573000 | 14229439000000 | 0 | 0 | 1159 | 0 | +-------+-------------+-------+------------------+------------------+----------------+-----------+---------------+---------------+------------+ 3 rows in set (0.00 sec)
視圖字段含義如下：
user：客戶端用戶名。如果在performance_schema表中user列為NULL，則假定為后臺(tái)線程，該字段為'background',如果為前臺(tái)線程，則該字段對(duì)應(yīng)具體的用戶名
statement：語(yǔ)句事件名稱的最后一部分字符串，與語(yǔ)句的command類型字符串類似
其他字段含義與 user_summary_by_statement_latency,x$user_summary_by_statement_latency 視圖的字段含義相同
本期內(nèi)容就介紹到這里，本期內(nèi)容參考鏈接如下：
https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-statement-type.html
https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-file-io.html
https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-file-io-type.html
https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-stages.html
https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-statement-latency.html
https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary.html
| 作者簡(jiǎn)介
羅小波·沃趣科技高級(jí)數(shù)據(jù)庫(kù)技術(shù)專家
IT從業(yè)多年，歷任運(yùn)維工程師，高級(jí)運(yùn)維工程師，運(yùn)維經(jīng)理，數(shù)據(jù)庫(kù)工程師，曾參與版本發(fā)布系統(tǒng)，輕量級(jí)監(jiān)控系統(tǒng)，運(yùn)維管理平臺(tái)，數(shù)據(jù)庫(kù)管理平臺(tái)的設(shè)計(jì)與編寫，熟悉MySQL的體系結(jié)構(gòu)時(shí)，InnoDB存儲(chǔ)引擎，喜好專研開源技術(shù)，追求完美。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
