Hi Guys,
We're looking at using LDAP to connect to an external customer system. Does FA remove users once they've been removed from LDAP? Is there anyway to sync this and sync the users without each user having to login?
Regards
David
Hi Guys,
We're looking at using LDAP to connect to an external customer system. Does FA remove users once they've been removed from LDAP? Is there anyway to sync this and sync the users without each user having to login?
Regards
David
Perfect Joshua.
This gives me what I need!
Thank you!
Is there a query I can run on the user search which will show me users with 2FA enabled?
@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
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,
The slow query log also notes that 900,000 rows are scanned during the query.
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_id
If 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