waits_by_user_by_latency,x$waits_by_user_by_latency

• 按照用户和事件名称分组的等待事件摘要信息，默认情况下按照用户名和总的等待事件延迟事件降序排序，数据来源：events_waits_summary_by_user_by_event_name

  • 该视图忽略空闲等待事件信息

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

  • user：与该连接关联的用户名
  • 其他字段与2.3.50. waits_by_host_by_latency,x$waits_by_host_by_latency 视图字段含义相同，不同的是waits_by_user_by_latency,x$waits_by_user_by_latency视图是按照用户名和事件名称分组

• 视图定义语句

# waits_by_user_by_latency
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW waits_by_user_by_latency (
  user,  event,  total,  total_latency,  avg_latency,  max_latency
) AS
SELECT IF(user IS NULL, 'background', user) AS user,
      event_name AS event,
      count_star AS total,
      sys.format_time(sum_timer_wait) AS total_latency,
      sys.format_time(avg_timer_wait) AS avg_latency,
      sys.format_time(max_timer_wait) AS max_latency
  FROM performance_schema.events_waits_summary_by_user_by_event_name
WHERE event_name != 'idle'
  AND user IS NOT NULL
  AND sum_timer_wait > 0
ORDER BY user, sum_timer_wait DESC;

# x$waits_by_user_by_latency
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$waits_by_user_by_latency (
  user,  event,  total,  total_latency,  avg_latency,  max_latency
) AS
SELECT IF(user IS NULL, 'background', user) AS user,
      event_name AS event,
      count_star AS total,
      sum_timer_wait AS total_latency,
      avg_timer_wait AS avg_latency,
      max_timer_wait AS max_latency
  FROM performance_schema.events_waits_summary_by_user_by_event_name
WHERE event_name != 'idle'
  AND user IS NOT NULL
  AND sum_timer_wait > 0
ORDER BY user, sum_timer_wait DESC;

• 视图查询信息示例

admin@localhost : sys 12:59:07> select * from waits_by_user_by_latency limit 3;
+-------+---------------------------------------------------+----------+---------------+-------------+-------------+
| user  | event                                            | total    | total_latency | avg_latency | max_latency |
+-------+---------------------------------------------------+----------+---------------+-------------+-------------+
| admin | wait/lock/metadata/sql/mdl                        |        2 | 56.57 m      | 28.28 m    | 43.63 m    |
| admin | wait/synch/cond/sql/MDL_context::COND_wait_status |    3395 | 56.56 m      | 999.66 ms  | 1.00 s      |
| admin | wait/io/table/sql/handler                        | 16096791 | 4.59 m        | 17.11 us    | 2.02 m      |
+-------+---------------------------------------------------+----------+---------------+-------------+-------------+
3 rows in set (0.01 sec)

admin@localhost : sys 12:59:22> select * from x$waits_by_user_by_latency limit 3;
+-------+---------------------------------------------------+----------+------------------+------------------+------------------+
| user  | event                                            | total    | total_latency    | avg_latency      | max_latency      |
+-------+---------------------------------------------------+----------+------------------+------------------+------------------+
| admin | wait/lock/metadata/sql/mdl                        |        2 | 3393932470401750 | 1696966235200875 | 2617554075360375 |
| admin | wait/synch/cond/sql/MDL_context::COND_wait_status |    3395 | 3393839154564375 |    999658071750 |    1004173431750 |
| admin | wait/io/table/sql/handler                        | 16096791 |  275441586767625 |        17111250 |  121243803313125 |
+-------+---------------------------------------------------+----------+------------------+------------------+------------------+
3 rows in set (0.01 sec)

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

---

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

下一篇： waits_global_by_latency,x$waits_global_by_latency视图