Performance issues when retrieving user
-
Hi,
We're getting poor performance when a user tries to login but only certain users. We're running a good enough MYSQL DB 2CPU 7.5GB.
It appears that this query takes over a second to run
SELECT u.id AS u_id, u.active AS u_active, i.breached_password_last_checked_instant AS i_breached_password_last_checked_instant, i.breached_password_status AS i_breached_password_status, u.birth_date AS u_birth_date, u.clean_speak_id AS u_clean_speak_id, i.connectors_id AS i_connectors_id, u.data AS u_data, i.email AS i_email, i.encryption_scheme AS i_encryption_scheme, u.expiry AS u_expiry, i.factor AS i_factor, u.first_name AS u_first_name, u.full_name AS u_full_name, u.image_url AS u_image_url, u.insert_instant AS u_insert_instant, i.last_login_instant AS i_last_login_instant, i.last_update_instant AS i_last_update_instant, u.last_name AS u_last_name, u.middle_name AS u_middle_name, u.mobile_phone AS u_mobile_phone, u.parent_email AS u_parent_email, i.password AS i_password, i.password_change_reason AS i_password_change_reason, i.password_change_required AS i_password_change_required, i.password_last_update_instant AS i_password_last_update_instant, i.salt AS i_salt, u.tenants_id AS u_tenants_id, u.timezone AS u_timezone, -- Note, username_index is always the upper case version of username. To derive the unique username -- value, replace the upcase version with the user version in username preserving the suffix which -- consists of a separator and digits which will not be affected by the case. REPLACE(username_index, UPPER(i.username), i.username) AS i_unique_username, i.username AS i_username, i.username_status AS i_username_status, -- Use COALESCE to make this work with MySQL. -- MySQL uses a BIT(1) column instead of Boolean. CASE WHEN i.email IS NULL THEN true ELSE COALESCE (i.verified, false) END AS i_verified, ur.id AS ur_id, ur.applications_id AS ur_applications_id, ur.authentication_token AS ur_authentication_token, ur.clean_speak_id AS ur_cleanspeak_id, ur.data AS ur_data, ur.insert_instant AS ur_insert_instant, ur.last_login_instant AS ur_last_login_instant, ur.last_update_instant AS ur_last_update_instant, ur.timezone AS ur_timezone, ur.username AS ur_username, ur.username_status AS ur_username_status, ur.verified AS ur_verified, ar.name AS ar_name, gm.id AS gm_id, gm.groups_id AS gm_groups_id, gm.data AS gm_data, gm.insert_instant AS gm_insert_instant FROM users AS u LEFT OUTER JOIN identities AS i ON u.id = i.users_id LEFT OUTER JOIN user_registrations AS ur ON u.id = ur.users_id LEFT OUTER JOIN group_members AS gm ON u.id = gm.users_id LEFT OUTER JOIN group_application_roles AS gar ON gar.groups_id = gm.groups_id LEFT OUTER JOIN user_registrations_application_roles AS urar ON urar.user_registrations_id = ur.id LEFT OUTER JOIN application_roles AS ar ON (ar.id = urar.application_roles_id OR gar.application_roles_id = ar.id) AND ar.applications_id = ur.applications_id WHERE i.email = lower('blah') AND u.tenants_id = x'ourtenantid'When I run this query separately it matches 16,443 rows for the user!
These users are logged in regularly and also have up to 90 roles in the application they are assigned to.
It's slow when getting a user, logging in or even viewing that user in the FA admin application.
Any ideas?
Thanks
David
Edit:
Ok the performance issue def appears with this join:
LEFT OUTER JOIN user_registrations_application_roles AS urar ON urar.user_registrations_id = ur.id LEFT OUTER JOIN application_roles AS ar ON (ar.id = urar.application_roles_id OR gar.application_roles_id = ar.id) AND ar.applications_id = ur.applications_idIf I take this out the query returns instantly. We currently have 11,432 rows in user_registrations_application_roles but only 253 roles in the application_roles table and 497 roles in the user_registrations table
-
Thanks for the detailed analysis!
Interesting. I will check with the team and see if they have any thoughts.
Thanks,
Josh -
Three things to check:
- Can you confirm the version of FusionAuth that you are running?
- Can you run an
explainon some of the queries you were testing. Doing so will offer useful output. - Also, do you happen to have a lot of groups in your setup?
Thanks,
Josh -
Per _robotdan, it might be related to this issue?
-
Hi,
-
We're using FA 1.28.0
-
Please find results of the explain, sorry the sql tool I'm using only allowed me to export as CSV
id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra, 1,SIMPLE,i,,ref,"identities_uk_1,identities_i_1",identities_uk_1,767,const,1,100.00,Using where, 1,SIMPLE,u,,eq_ref,"PRIMARY,users_fk_1",PRIMARY,16,fusionauth.i.users_id,1,36.62,Using where, 1,SIMPLE,ur,,ref,user_registrations_i_2,user_registrations_i_2,16,fusionauth.i.users_id,1,100.00,, 1,SIMPLE,gm,,ref,group_members_i_1,group_members_i_1,16,fusionauth.i.users_id,1,100.00,, 1,SIMPLE,gar,,ref,group_application_roles_uk_1,group_application_roles_uk_1,16,fusionauth.gm.groups_id,50,100.00,Using index, 1,SIMPLE,urar,,ref,user_registrations_application_roles_uk_1,user_registrations_application_roles_uk_1,16,fusionauth.ur.id,28,100.00,Using index, 1,SIMPLE,ar,,ref,"PRIMARY,application_roles_fk_1",application_roles_fk_1,16,fusionauth.ur.applications_id,36,100.00,Using where,- We have a total of 8 groups in that tenant, only 1 or 2 are in use though.
The slow query log also notes that 900,000 rows are scanned during the query.
-
-
This post is deleted! -
@joshua said in Performance issues when retrieving user:
Per _robotdan, it might be related to this issue?
Yes I think it's definitely related to that
-
Thanks for the additional information @david-billings. We will have to see if we can do some additional testing to recreate.
Josh