mysql host summary_host_summary,x$ host_summary

按照主机分组的语句执行时间、次数、相关的文件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)