Bug #88834 Uneven slowdown on systems with many users
Submitted: 8 Dec 2017 11:41 Modified: 24 Jan 11:43
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S5 (Performance)
Version:5.7.20, 8.0.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: grants, performance, users

[8 Dec 2017 11:41] Daniël van Eeden
Description:
On a system with 216088 records in mysql.user:

grant select on test.* to 'test_4'@'127.0.0.1';    Takes 0.01s (tried 3 times)
grant select on test.* to 'test_9994'@'127.0.0.1'; Takes 0.05s (tried 3 times)

time mysql -h 127.0.0.1 -u test_4 -pfoobar -e QUIT Takes 0.015s
time mysql -h 127.0.0.1 -u test_4 -pfoobar -e QUIT Takes 0.019s

How to repeat:
Create a system with 216088 user accounts.
Test grant and connection performance with a user which is in the beginning of a alphabetically ordered user list.
Test grant and connection performance with a user which is in the end of a alphabetically ordered user list.

Suggested fix:
Currently find_acl_user() etc. just loops through the list of users to find the correct one. Maybe use a data structure which allows faster access?
[8 Dec 2017 13:19] Daniël van Eeden
This:
time mysql -h 127.0.0.1 -u test_4 -pfoobar -e QUIT Takes 0.019s

Should have been:
time mysql -h 127.0.0.1 -u test_9994 -pfoobar -e QUIT Takes 0.019s
[11 Dec 2017 12:22] Umesh Shastry
Hello Daniël,

Thank you for the report and feedback!

Thanks,
Umesh
[12 Jan 8:39] Simon Mudd
Also visible in 5.7.20
[24 Jan 11:43] Daniël van Eeden
Adding 5.7.20 to versions. Note that 5.7 is worse than 8.0.

Might be related:
Bug #89379 	summary_by_user tables get extremely slow with many users.
[30 Mar 9:43] Eric Herman
addressed with pull request:

 http://github.com/mysql/mysql-server/pull/201

 as tracked by https://bugs.mysql.com/bug.php?id=90244
[25 Sep 7:40] Georgi Kodinov
Posted by developer:
 
It will be fixed by the contribution by Eric in 27772506. Hence closing as duplicate.