status_by_account、status_by_host、status_by_user - xiaoboluo768/mysql-system-schema GitHub Wiki
-
这些表都有VARIABLE_NAME、VARIABLE_VALUE字段，除持之外，status_by_account表多了USRE和HOST字段，status_by_host表多了HOST字段，status_by_user表多了USER字段
- 注意：show_compatibility_56系统变量的值会影响这些表中的统计信息
-
在performance_schema中，每个状态变量摘要表都有一个或多个分组列，表示如何聚合状态变量值：
- status_by_account表：按照USER、HOST、VARIABLE_NAME列进行分组
- status_by_host表：按照HOST、VARIABLE_NAME列进行分组
- status_by_user表：按照USER、VARIABLE_NAME列进行分组
-
每个状态变量摘要表都按照如下列进行聚合：
- VARIABLE_VALUE：包括活跃和已终止的会话的状态变量聚合值
-
当会话终止时收集的account相关状态变量会添加到全局状态变量表的计数器和accounts表的相关计数器中。如果account分类关闭了收集而host和user分类开启了收集，则会针对主机和用户分类聚合相应的状态变量值，同时将会话状态添加到hosts和users表中的相关计数器中
-
如果将performance_schema_accounts_size，performance_schema_hosts_size和performance_schema_users_size系统变量分别设置为0，则不会收集帐户，主机和用户分类的统计信息
-
状态变量摘要表允许执行TRUNCATE TABLE语句，执行truncate语句时活动会话的状态变量不受影响：
- status_by_account：终止的会话在account聚合表中的状态变量值将被聚合到用户和主机聚合表中的状态变量计数器中，然后重置帐户聚合表中的状态变量值
- status_by_host：终止的会话对应的状态变量被重置
- status_by_user：终止的会话对应的状态变量被重置
-
FLUSH STATUS将会话状态从所有活动会话添加到全局状态变量，然后重置所有活动会话的状态变量值，并在按照account、host、user分类聚合表中重置已断开连接的状态变量值
-
表记录内容示例
# status_by_account表
admin@localhost : performance_schema 04:08:36> select * from status_by_account where USER is not null limit 5;
+-------+-----------+-------------------------+----------------+
| USER | HOST | VARIABLE_NAME | VARIABLE_VALUE |
+-------+-----------+-------------------------+----------------+
| admin | localhost | Bytes_received | 6049 |
| admin | localhost | Bytes_sent | 305705 |
| admin | localhost | Com_stmt_reprepare | 0 |
| admin | localhost | Created_tmp_disk_tables | 7 |
| admin | localhost | Created_tmp_tables | 17 |
+-------+-----------+-------------------------+----------------+
5 rows in set (0.00 sec)
# status_by_host表
admin@localhost : performance_schema 04:08:43> select * from status_by_host where HOST is not null limit 5;
+-----------+-------------------------+----------------+
| HOST | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+-------------------------+----------------+
| localhost | Bytes_received | 6113 |
| localhost | Bytes_sent | 306310 |
| localhost | Com_stmt_reprepare | 0 |
| localhost | Created_tmp_disk_tables | 7 |
| localhost | Created_tmp_tables | 17 |
+-----------+-------------------------+----------------+
5 rows in set (0.00 sec)
# status_by_user表
admin@localhost : performance_schema 04:08:58> select * from status_by_user where USER is not null limit 5;
+-------+-------------------------+----------------+
| USER | VARIABLE_NAME | VARIABLE_VALUE |
+-------+-------------------------+----------------+
| admin | Bytes_received | 6177 |
| admin | Bytes_sent | 306781 |
| admin | Com_stmt_reprepare | 0 |
| admin | Created_tmp_disk_tables | 7 |
| admin | Created_tmp_tables | 17 |
+-------+-------------------------+----------------+
5 rows in set (0.00 sec)
- 表结构定义
# status_by_account表
CREATE TABLE `status_by_account` (
`USER` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`VARIABLE_NAME` varchar(64) NOT NULL,
`VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
# status_by_host表
CREATE TABLE `status_by_host` (
`HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`VARIABLE_NAME` varchar(64) NOT NULL,
`VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
# status_by_user表
CREATE TABLE `status_by_user` (
`USER` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`VARIABLE_NAME` varchar(64) NOT NULL,
`VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
上一篇： memory_summary_*表 | 下一篇： host_cache表