在上一篇《按 host 分组统计视图|全方位认识 sys 系统库》中,我们介绍了sys 系统库中按 host 分组统计的视图,类似地,本期的内容将为大家介绍按照 user 进行分类统计的视图。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧。
查看活跃连接中按用户分组的总执行时间、平均执行时间、总的IOS、总的内存使用量、表扫描数量等统计信息,默认按照总延迟时间(执行时间)降序排序。数据来源:performance_schema.accounts、sys.xuser_summary_by_statement_latency、sys.xuser_summary_by_file_io、sys.x
下面我们看看使用该视图查询返回的结果。
# 不带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)
视图字段含义如下:
PS:该视图只统计文件IO等待事件信息("wait/io/file/%")
按照用户分组的文件I/O延迟时间、IOS统计信息,默认按照总文件I/O时间延迟时间(执行时间)降序排序。数据来源:performance_schema.events_waits_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带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)
视图字段含义如下:
PS:该视图只统计文件IO等待事件信息("wait/io/file/%")
按照用户和事件类型(事件名称)分组的文件I/O延迟和IOS统计信息,默认情况下按照用户名和总文件I/O时间延迟时间(执行时间)降序排序。数据来源:performance_schema.events_waits_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带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)
视图字段含义如下:
PS:该视图只统计文件IO等待事件信息("wait/io/file/%")
按用户分组的阶段事件统计信息,默认情况下按照用户名和阶段事件总延迟时间(执行时间)降序排序。数据来源:performance_schema.events_stages_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带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)
视图字段含义如下:
按照用户分组的语句统计信息,默认情况下按照语句总延迟时间(执行时间)降序排序。数据来源:performance_schema.events_statements_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带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)
视图字段含义如下:
按用户和语句事件类型(事件类型名称为语句事件的event_name截取最后一部分字符串,也是语句command类型字符串类似)分组的语句统计信息,默认情况下按照用户名和对应语句的总延迟时间(执行时间)降序排序。数据来源:performance_schema.events_statements_summary_by_user_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带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)
视图字段含义如下:
本期内容就介绍到这里,本期内容参考链接如下:
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
"翻过这座山,你就可以看到一片海!"。坚持阅读我们的"全方位认识 sys 系统库"系列文章分享,你就可以系统地学完它。 谢谢你的阅读,我们下期不见不散!
| 作者简介
《千金良方——MySQL性能优化金字塔法则》、《数据生态:MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构,擅长数据库的整体调优,喜好专研开源技术,并热衷于开源技术的推广,在线上线下做过多次公开的数据库专题分享,发表过近100篇数据库相关的研究文章。