user_summary,x$user_summary

• 查看活跃连接中按用户分组的总执行时间、平均执行时间、总的IOS、总的内存使用量、表扫描数量等摘要信息，默认按照总延迟时间(执行时间)降序排序。数据来源：accounts、sys.x$user_summary_by_statement_latency、sys.x$user_summary_by_file_io、sys.x$memory_by_user_by_current_bytes

• user_summary和x$user_summary视图字段含义如下：

  • user：客户端用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为'background',如果为前台线程，则该字段对应具体的用户名
  • statements：对应用户的语句总数量
  • statement_latency：对应用户的语句执行的总等待时间(执行时间)
  • statement_avg_latency：对应用户的语句中，平均每个语句的等待时间(执行时间)(SUM(stmt.total_latency/SUM(stmt.total))
  • table_scans：对应用户的语句发生表扫描总次数
  • file_ios：对应用户的语句的文件I/O事件总次数
  • file_io_latency：对应用户的语句的文件I/O事件的总等待时间(执行时间)
  • current_connections：对应用户的当前连接数
  • total_connections：对应用户的历史总连接数
  • unique_hosts：对应用户的来自不同主机(针对主机名去重)连接的数量
  • current_memory：对应用户的连接当前已使用的内存分配量
  • total_memory_allocated：对应用户的连接的历史内存分配量

• 视图定义语句

# user_summary
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW user_summary (
  user,  statements,  statement_latency,  statement_avg_latency,  table_scans,  file_ios,  file_io_latency,  current_connections,  total_connections,  unique_hosts,  current_memory,  total_memory_allocated
) AS
SELECT IF(accounts.user IS NULL, 'background', accounts.user) AS user,
      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 host) AS unique_hosts,
      sys.format_bytes(SUM(mem.current_allocated)) AS current_memory,
      sys.format_bytes(SUM(mem.total_allocated)) AS total_memory_allocated
  FROM performance_schema.accounts
  LEFT JOIN sys.x$user_summary_by_statement_latency AS stmt ON IF(accounts.user IS NULL, 'background', accounts.user) = stmt.user
  LEFT JOIN sys.x$user_summary_by_file_io AS io ON IF(accounts.user IS NULL, 'background', accounts.user) = io.user
  LEFT JOIN sys.x$memory_by_user_by_current_bytes mem ON IF(accounts.user IS NULL, 'background', accounts.user) = mem.user
GROUP BY IF(accounts.user IS NULL, 'background', accounts.user)
ORDER BY SUM(stmt.total_latency) DESC;

# x$user_summary
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$user_summary (
  user,  statements,  statement_latency,  statement_avg_latency,  table_scans,  file_ios,  file_io_latency,  current_connections,  total_connections,  unique_hosts,  current_memory,  total_memory_allocated
) AS
SELECT IF(accounts.user IS NULL, 'background', accounts.user) AS user,
      SUM(stmt.total) AS statements,
      SUM(stmt.total_latency) AS statement_latency,
      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,
      SUM(io.io_latency) AS file_io_latency,
      SUM(accounts.current_connections) AS current_connections,
      SUM(accounts.total_connections) AS total_connections,
      COUNT(DISTINCT host) AS unique_hosts,
      SUM(mem.current_allocated) AS current_memory,
      SUM(mem.total_allocated) AS total_memory_allocated
  FROM performance_schema.accounts
  LEFT JOIN sys.x$user_summary_by_statement_latency AS stmt ON IF(accounts.user IS NULL, 'background', accounts.user) = stmt.user
  LEFT JOIN sys.x$user_summary_by_file_io AS io ON IF(accounts.user IS NULL, 'background', accounts.user) = io.user
  LEFT JOIN sys.x$memory_by_user_by_current_bytes mem ON IF(accounts.user IS NULL, 'background', accounts.user) = mem.user
GROUP BY IF(accounts.user IS NULL, 'background', accounts.user)
ORDER BY SUM(stmt.total_latency) DESC;

• 视图查询信息示例

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)

ERROR: 
No query specified

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)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary.html

---

上一篇： statements_with_temp_tables,x$statements_with_temp_tables视图 |

下一篇： user_summary_by_file_io,x$user_summary_by_file_io视图