Bug #89379 summary_by_user tables get extremely slow with many users.
Submitted: 24 Jan 2018 11:41 Modified: 24 Jan 2018 12:26
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S5 (Performance)
Version:5.7.20, 5.7.21 OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[24 Jan 2018 11:41] Daniël van Eeden
Description:
Some P_S queries stared to take multiple minutes on a system with many users.

Setting performance_schema_accounts_size doesn't work for this as that would also influence the accounts table 

mysql> select count(*) from performance_schema.events_statements_summary_by_user_by_event_name;
+----------+
| count(*) |
+----------+
|   297730 |
+----------+
1 row in set (4 min 37.37 sec)

mysql> select count(*) from performance_schema.events_statements_summary_by_program;
+----------+
| count(*) |
+----------+
|       17 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from performance_schema.events_statements_summary_by_thread_by_event_name;
+----------+
| count(*) |
+----------+
|   769120 |
+----------+
1 row in set (0.41 sec)

mysql> select count(*) from performance_schema.accounts;
+----------+
| count(*) |
+----------+
|     5572 |
+----------+
1 row in set (1.83 sec)

Might be related to:
Bug #88834 	Uneven slowdown on systems with many users

'perf top' shows these:
PFS_connection_iterator::visit_user
sanitize_account 

How to repeat:
On a system with many users (e.g. 5k entries in mysql.user) query the P_S tables which summarize by account
[24 Jan 2018 11:53] Daniël van Eeden
Set to "S5 (Performance)"
[24 Jan 2018 12:26] Umesh Shastry
Hello Daniël,

Thank you for the report and feedback.

Thanks,
Umesh
[18 Jun 2018 8:35] Shane Bester
Saw this on 5.7.21 today while trying to dump the P_S tables...

mysql> show processlist;
+-----+------+-----------+--------------------+---------+------+--------------+-------------------------------------------------------------------------------+
| Id  | User | Host      | db                 | Command | Time | State        | Info                                                                          |
+-----+------+-----------+--------------------+---------+------+--------------+-------------------------------------------------------------------------------+
| 151 | root | ::1:61922 | performance_schema | Query   |    0 | starting     | show processlist                                                              |
| 293 | root | ::1:62142 | performance_schema | Query   | 1748 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ * FROM `memory_summary_by_user_by_event_name` |
+-----+------+-----------+--------------------+---------+------+--------------+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
|    31169 |
+----------+
1 row in set (0.00 sec) 

Stacks taken a few times look like:
Call Site
mysqld!PFS_connection_iterator::visit_user
mysqld!table_mems_by_user_by_event_name::make_row
mysqld!table_mems_by_user_by_event_name::rnd_next
mysqld!ha_perfschema::rnd_next
mysqld!handler::ha_rnd_next
mysqld!rr_sequential
mysqld!sub_select
mysqld!do_select
mysqld!JOIN::exec
...