proxysql-admin syncusers hangs at 1000 DB user or above

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

  1. Add 2,000 new users and remove 10,000 users

  1. 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

  1. List the application users in the PXC cluster

  1. Create 10,000 users

  1. Verify that users are created

  1. Enable proxysql-admin (--enable) and get the list of users

  1. PXC cluster should now have two additional users (monitor and proxysql_user100)

  1. Run --syncusers

  1. 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.

Done

Assignee

Reporter

Needs Review

Sprint

Fix versions

Affects versions

Priority

Created February 23, 2024 at 8:49 AM
Updated April 30, 2024 at 2:25 PM
Resolved April 11, 2024 at 7:30 AM