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
• Status: Verified
• Category: MySQL Server: SYS Schema
• Severity: S3 (Non-critical)
• Version: 8.0.28
• OS: CentOS (CentOS Stream release 8)
• CPU Architecture: x86 (Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz)

[8 Apr 2022 9:35] Eduardo Ortega

Description:
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.