proxysql-admin syncusers hangs at 1000 DB user or above
General
Escalation
General
Escalation
Description
Description
500 DB users proxysql-admin syncusers done in 6 minutes
1000 DB users hangs on the last “Adding user to ProxySQL”. I waited, it runned for total of 51 minutes and I can see in strace that it is continuously moving with attaching processes. I have cancelled it since it did not finish and runtime_mysql_users is still same when it starts.
I tried to manually do it via “INSERT INTO mysql_users …” and “LOAD MYSQL USERS TO RUNTIME” and it finish in a few seconds.
Check with strace. It continuously attaching processes.
How repeat
Environment
Create DB Users
ProxySQL server
Reproduce hanging
Verify that it is continuously moving(attaching process) in the background via strace.
Workaround
Manually insert to DB users to ProxySQL.
Example
Outputs
Environment
None
AFFECTED CS IDs
CS0044135
Activity
Show:
Venkatesh Prasad April 8, 2024 at 10:38 AM
Test 2
Add 2,000 new users and remove 10,000 users
Run --syncusers again
It took 4 mins 13 seconds to add 2000 new users and to remove 10000 old users.
Venkatesh Prasad April 8, 2024 at 9:18 AM
Edited
With the new implementation
List the application users in the PXC cluster
Create 10,000 users
Verify that users are created
Enable proxysql-admin (--enable) and get the list of users
PXC cluster should now have two additional users (monitor and proxysql_user100)
Run --syncusers
Check users in ProxySQL
It took 5 mins 33 seconds to complete synchronisation of 10,000 MySQL users to ProxySQL.
Venkatesh Prasad March 1, 2024 at 8:49 AM
Rootcause: Logic used by --syncusers is of O(n^2). When it tries to create a new user in proxysql, it compares the new user to be added against all the existing users thereby resulting in a huge delay.
Here is the debug output generated using --debug option, and we can see that when the script started processing user0259, it compares against all users from user0001 upto user0259 and so on.
Description
500 DB users proxysql-admin syncusers done in 6 minutes
1000 DB users hangs on the last “Adding user to ProxySQL”. I waited, it runned for total of 51 minutes and I can see in strace that it is continuously moving with attaching processes. I have cancelled it since it did not finish and runtime_mysql_users is still same when it starts.
I tried to manually do it via “INSERT INTO mysql_users …” and “LOAD MYSQL USERS TO RUNTIME” and it finish in a few seconds.
Check with strace. It continuously attaching processes.
How repeat
Environment
Create DB Users
ProxySQL server
Reproduce hanging
Verify that it is continuously moving(attaching process) in the background via strace.
Workaround
Manually insert to DB users to ProxySQL.
Example
Outputs