Posts made by david.billings

david.billings

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

david.billings

Perfect Joshua.

This gives me what I need!

Thank you!

david.billings

Is there a query I can run on the user search which will show me users with 2FA enabled?

david.billings

@joshua said in Performance issues when retrieving user:

Per _robotdan, it might be related to this issue?

https://github.com/FusionAuth/fusionauth-issues/issues/480

Yes I think it's definitely related to that

david.billings

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.

david.billings

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