host_summary_by_statement_latency,x$host_summary_by_statement_latency - xiaoboluo768/mysql-system-schema GitHub Wiki
-
按照主机和事件名称分组的语句事件摘要信息，默认按照总执行时间降序排序。数据来源：events_statements_summary_by_host_by_event_name
-
host_summary_by_statement_latency和x$host_summary_by_statement_latency视图字段含义如下：
- host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
- total：语句总执行次数
- total_latency：语句总执行时间
- max_latency：语句单个最大执行时间
- lock_latency：语句总锁等待时间
- rows_sent：语句返回给客户端的总数据行数
- rows_examined：语句从存储引擎层读取的总数据行数
- rows_affected：语句执行时受影响（DML会返回数据发生变更的受影响行数，select等不会产生数据变更的语句执行时不会有受影响行数返回）的总数据行数
- full_scans：语句全表扫描总次数
-
视图定义语句
# host_summary_by_statement_latency
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW host_summary_by_statement_latency (
host, total, total_latency, max_latency, lock_latency, rows_sent, rows_examined, rows_affected, full_scans
) AS
SELECT IF(host IS NULL, 'background', host) AS host,
SUM(count_star) AS total,
sys.format_time(SUM(sum_timer_wait)) AS total_latency,
sys.format_time(MAX(max_timer_wait)) AS max_latency,
sys.format_time(SUM(sum_lock_time)) AS lock_latency,
SUM(sum_rows_sent) AS rows_sent,
SUM(sum_rows_examined) AS rows_examined,
SUM(sum_rows_affected) AS rows_affected,
SUM(sum_no_index_used) + SUM(sum_no_good_index_used) AS full_scans
FROM performance_schema.events_statements_summary_by_host_by_event_name
GROUP BY IF(host IS NULL, 'background', host)
ORDER BY SUM(sum_timer_wait) DESC;
# x$host_summary_by_statement_latency
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW x$host_summary_by_statement_latency (
host, total, total_latency, max_latency, lock_latency, rows_sent, rows_examined, rows_affected, full_scans
) AS
SELECT IF(host IS NULL, 'background', host) AS host,
SUM(count_star) AS total,
SUM(sum_timer_wait) AS total_latency,
MAX(max_timer_wait) AS max_latency,
SUM(sum_lock_time) AS lock_latency,
SUM(sum_rows_sent) AS rows_sent,
SUM(sum_rows_examined) AS rows_examined,
SUM(sum_rows_affected) AS rows_affected,
SUM(sum_no_index_used) + SUM(sum_no_good_index_used) AS full_scans
FROM performance_schema.events_statements_summary_by_host_by_event_name
GROUP BY IF(host IS NULL, 'background', host)
ORDER BY SUM(sum_timer_wait) DESC;
- 视图查询信息示例
root@localhost : sys 12:40:19> select * from host_summary_by_statement_latency limit 3;
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| localhost | 3447 | 539.61 ms | 89.37 ms | 131.90 ms | 3023 | 40772 | 0 | 108 |
| 192.168.2.122 | 9 | 13.22 ms | 12.55 ms | 0 ps | 5 | 0 | 0 | 0 |
| background | 0 | 0 ps | 0 ps | 0 ps | 0 | 0 | 0 | 0 |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)
root@localhost : sys 12:40:36> select * from x$host_summary_by_statement_latency limit 3;
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| localhost | 3528 | 544883806000 | 89365202000 | 132140000000 | 3026 | 41351 | 0 | 109 |
| 192.168.2.122 | 9 | 13218739000 | 12550251000 | 0 | 5 | 0 | 0 | 0 |
| background | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)
上一篇： host_summary_by_stages,x$host_summary_by_stages视图 |
下一篇： host_summary_by_statement_type,x$host_summary_by_statement_type视图