Bug #106958 Querying the user_summary views in sys schema is extremely slow
Submitted: 8 Apr 2022 9:35 Modified: 11 Apr 2022 14:30
Reporter: Eduardo Ortega Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: SYS Schema Severity:S3 (Non-critical)
Version:8.0.28 OS:CentOS (CentOS Stream release 8)
Assigned to: CPU Architecture:x86 (Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz)

[8 Apr 2022 9:35] Eduardo Ortega
The user_summary views in sys schema provide very handy information for determining per user workload characteristics. However, on a somewhat but not terribly busy host, querying them is extremely slow:

root@hostdb-1016 [sys]> SELECT                                                                                                                                                                                          ->                 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                                                                                                                                                                         ->         FROM                                                                                                                                                                                               
->                 sys.x$user_summary;
234 rows in set (1 min 19.62 sec)
This host is an active primary replication source. It is somewhat busy, but definitely not overwhelmed. CPU utilization is hovering at 10% to 25%.

We collect metrics minutely, which clearly does not work if running a query to obtain those metrics takes over a minute. It will be worse for folks who collect metrics more often.

How to repeat:
Get a somewhat busy hosts with many users
Issue query. 
See it be slow.
[8 Apr 2022 9:47] Simon Mudd
Related to bug#89379.