host_summary,x$ host_summary - xiaoboluo768/mysql-system-schema GitHub Wiki
-
按照主机分组的语句执行时间、次数、相关的文件I/O延迟、连接数和内存分配大小等摘要信息，数据来源：performance_schema.accounts、sys.x$host_summary_by_statement_latency、sys.x$host_summary_by_file_io
-
host_summary和x$host_summary视图字段含义如下：
- host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
- statements：语句总执行次数
- statement_latency：语句总等待时间（执行时间）
- statement_avg_latency：语句的平均等待时间(执行时间)
- table_scans：语句的表扫描总次数
- file_ios：文件I/O事件总次数
- file_io_latency：文件I/O事件总等待时间（执行时间）
- current_connections：当前连接数
- total_connections：总历史连接数
- unique_users：不同（去重）用户数量
- current_memory：当前内存使用量
- total_memory_allocated：总的内存分配量
-
视图定义语句
# host_summary
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW host_summary (
host, statements, statement_latency, statement_avg_latency, table_scans, file_ios, file_io_latency, current_connections, total_connections, unique_users
) AS
SELECT IF(accounts.host IS NULL, 'background', accounts.host) AS host,
SUM(stmt.total) AS statements,
sys.format_time(SUM(stmt.total_latency)) AS statement_latency,
sys.format_time(IFNULL(SUM(stmt.total_latency) / NULLIF(SUM(stmt.total), 0), 0)) AS statement_avg_latency,
SUM(stmt.full_scans) AS table_scans,
SUM(io.ios) AS file_ios,
sys.format_time(SUM(io.io_latency)) AS file_io_latency,
SUM(accounts.current_connections) AS current_connections,
SUM(accounts.total_connections) AS total_connections,
COUNT(DISTINCT accounts.user) AS unique_users
FROM performance_schema.accounts
LEFT JOIN sys.x$host_summary_by_statement_latency AS stmt ON accounts.host = stmt.host
LEFT JOIN sys.x$host_summary_by_file_io AS io ON accounts.host = io.host
GROUP BY IF(accounts.host IS NULL, 'background', accounts.host);
# x$host_summary
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW x$host_summary (
host, statements, statement_latency, statement_avg_latency, table_scans, file_ios, file_io_latency, current_connections, total_connections, unique_users
) AS
SELECT IF(accounts.host IS NULL, 'background', accounts.host) AS host,
SUM(stmt.total) AS statements,
SUM(stmt.total_latency) AS statement_latency,
SUM(stmt.total_latency) / SUM(stmt.total) AS statement_avg_latency,
SUM(stmt.full_scans) AS table_scans,
SUM(io.ios) AS file_ios,
SUM(io.io_latency) AS file_io_latency,
SUM(accounts.current_connections) AS current_connections,
SUM(accounts.total_connections) AS total_connections,
COUNT(DISTINCT accounts.user) AS unique_users
FROM performance_schema.accounts
LEFT JOIN sys.x$host_summary_by_statement_latency AS stmt ON accounts.host = stmt.host
LEFT JOIN sys.x$host_summary_by_file_io AS io ON accounts.host = io.host
GROUP BY IF(accounts.host IS NULL, 'background', accounts.host);
- 视图查询信息示例
root@localhost : sys 12:38:11> select * from host_summary limit 1\G;
*************************** 1. row ***************************
host: 192.168.2.122
statements: 9
statement_latency: 13.22 ms
statement_avg_latency: 1.47 ms
table_scans: 0
file_ios: 11
file_io_latency: 53.33 us
current_connections: 1
total_connections: 1
unique_users: 1
current_memory: 0 bytes
total_memory_allocated: 0 bytes
1 row in set (0.01 sec)
ERROR:
No query specified
root@localhost : sys 12:38:14> select * from x$host_summary limit 1\G;
*************************** 1. row ***************************
host: 192.168.2.122
statements: 9
statement_latency: 13218739000
statement_avg_latency: 1468748777.7778
table_scans: 0
file_ios: 11
file_io_latency: 53332848
current_connections: 1
total_connections: 1
unique_users: 1
current_memory: 0
total_memory_allocated: 0
1 row in set (0.01 sec)
上一篇： sys schema 视图 | 下一篇： host_summary_by_file_io,x$host_summary_by_file_io视图