Performance Degradation During Authenticator Update with a Large Number of Associated Applications

[aaujayasena]

*Describe the Issue: A significant delay has been observed in the execution of authenticator update requests (PUT requests) when a large number of applications are associated with the authenticator. Although no errors were logged, a considerable number of repeated SELECT queries were observed under the same correlation ID, leading to extended execution times

Detailed Observations:

• The delay occurs during the IDP update [1] process, where two listeners, doPreUpdateIdP [2] and doPostUpdateIdP, are invoked.
• doPreUpdateIdP is responsible for validating and updating applications associated with the authenticator before the authenticator itself is updated.
• The repeated database calls to retrieve basic application information are triggered during this validation process. [3]
• A large number of SELECT queries are executed repeatedly, indicating a potential inefficiency in the data retrieval mechanism, especially in scenarios with many associated applications.
• Additionally, the absence of database indexing may contribute to the slow execution times.

To mitigate this performance issue, it is recommended to optimize the data retrieval process during the IDP update. This could involve:

• Reducing the number of repetitive SELECT queries.
• Implementing database indexing where appropriate.
• Enhancing the logic in doPreUpdateIdP to minimize redundant operations, especially when dealing with large numbers of - associated applications.

[1] https://github.com/wso2/carbon-identity-framework/blob/v5.17.5/components/idp-mgt/org.wso2.carbon.idp.mgt/src/main/java/org/wso2/carbon/idp/mgt/IdentityProviderManager.java#L2217

[2] https://github.com/wso2/carbon-identity-framework/blob/v5.17.5/components/application-mgt/org.wso2.carbon.identity.application.mgt/src/main/java/org/wso2/carbon/identity/application/mgt/listener/ApplicationIdentityProviderMgtListener.java#L51

[3]

SELECT ID, TENANT_ID, APP_NAME, USER_STORE, USERNAME, DESCRIPTION, ROLE_CLAIM, AUTH_TYPE, PROVISIONING_USERSTORE_DOMAIN, IS_LOCAL_CLAIM_DIALECT,IS_SEND_LOCAL_SUBJECT_ID, IS_SEN
D_AUTH_LIST_OF_IDPS, IS_USE_TENANT_DOMAIN_SUBJECT, IS_USE_USER_DOMAIN_SUBJECT, ENABLE_AUTHORIZATION, SUBJECT_CLAIM_URI, IS_SAAS_APP, UUID, IMAGE_URL, ACCESS_URL, IS_DISCOVERABLE FROM SP_APP WHERE ID = ?|

SELECT ID, NAME, VALUE, DISPLAY_NAME FROM SP_METADATA WHERE SP_ID = ?

SELECT INBOUND_AUTH_KEY, INBOUND_AUTH_TYPE, PROP_NAME, PROP_VALUE,INBOUND_CONFIG_TYPE FROM  SP_INBOUND_AUTH WHERE APP_ID = ? AND TENANT_ID = ?

SELECT STEP_ORDER, AUTHENTICATOR_ID, IS_SUBJECT_STEP, IS_ATTRIBUTE_STEP FROM SP_AUTH_STEP INNER JOIN SP_FEDERATED_IDP ON SP_AUTH_STEP.ID=SP_FEDERATED_IDP.ID WHERE APP_ID = ?

SELECT AUTH_TYPE FROM SP_APP WHERE ID = ? AND TENANT_ID = ?

SELECT CONTENT, IS_ENABLED FROM SP_AUTH_SCRIPT WHERE APP_ID = ?

SELECT IS_USE_TENANT_DOMAIN_SUBJECT, IS_USE_USER_DOMAIN_SUBJECT, ENABLE_AUTHORIZATION, IS_SEND_AUTH_LIST_OF_IDPS, SUBJECT_CLAIM_URI FROM SP_APP WHERE TENANT_ID= ? AND ID = ?

SELECT PROVISIONING_USERSTORE_DOMAIN, IS_DUMB_MODE FROM SP_APP WHERE TENANT_ID= ? AND ID = ?

SELECT IDP_NAME, CONNECTOR_NAME, IS_JIT_ENABLED, BLOCKING, RULE_ENABLED FROM SP_PROVISIONING_CONNECTOR WHERE APP_ID = ? AND TENANT_ID = ?

SELECT IDP_CLAIM, SP_CLAIM, IS_REQUESTED, IS_MANDATORY, DEFAULT_VALUE FROM SP_CLAIM_MAPPING WHERE APP_ID = ? AND TENANT_ID = ?

SELECT ROLE_CLAIM, IS_LOCAL_CLAIM_DIALECT, IS_SEND_LOCAL_SUBJECT_ID FROM SP_APP WHERE TENANT_ID= ? AND ID = ?

SELECT SP_DIALECT FROM SP_CLAIM_DIALECT WHERE TENANT_ID= ? AND APP_ID = ?

SELECT IDP_ROLE, SP_ROLE FROM SP_ROLE_MAPPING WHERE APP_ID = ? AND TENANT_ID = ?

SELECT AUTHENTICATOR_NAME FROM SP_REQ_PATH_AUTHENTICATOR WHERE APP_ID = ? AND TENANT_ID = ?

SELECT REG_PATH_ID FROM REG_PATH WHERE REG_PATH_VALUE=? AND REG_TENANT_ID=?

SELECT REG_PATH_ID FROM REG_PATH WHERE REG_PATH_VALUE=? AND REG_TENANT_ID=?

SELECT REG_VERSION FROM REG_RESOURCE WHERE REG_PATH_ID=? AND REG_NAME=? AND REG_TENANT_ID=?

How To Reproduce:

1. Log in to the management console.
2. Add an email authenticator as a federated authenticator. https://is.docs.wso2.com/en/5.9.0/learn/configuring-email-otp/
3. Create an application.
4. Assign the email authenticator as the federated authenticator for the application.
5. Enable correlation logs. https://is.docs.wso2.com/en/5.11.0/setup/working-with-product-observability/#configuring-product-observability
6. Execute an API call to update the authenticator. ex:

curl --location --request PUT 'https://localhost:9443/t/carbon.super/api/server/v1/identity-providers/<IDP_ID>/federated-authenticators/RW1haWxPVFA' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic YWRtaW46YWRtaW4=' \
--data-raw '{
    "authenticatorId": "RW1haWxPVFA",
    "name": "EmailOTP",
    "isEnabled": true,
    "isDefault": true,
    "properties": [
        {
            "key": "EmailOtpLength",
            "value": "6"
        },
        {
            "key": "EmailOtpExpiryTime",
            "value": "6"
        },
        {
            "key": "Email",
            "value": "aaaaaa@gmail.com"
        },
        {
            "key": "EmailAPI",
            "value": ""
        },
        {
            "key": "OnlyNumerjjjicCharactersForOtp",
            "value": "true"
        }
    ]
}'

8. Increase the number of applications using the email OTP as a federated authenticator.
9. Validate that the number of DB calls increases proportionally, leading to delayed response times.

Ex:

Expected behavior:

The authenticator update request should execute efficiently, regardless of the number of associated applications. Database calls should be optimized to avoid unnecessary repetition, and appropriate indexing should be applied to improve query performance. The update process should be completed within a reasonable timeframe without performance degradation.

Environment information (Please complete the following information; remove any unnecessary fields) :

• Product Version: IS 5.10.0
• OS: [e.g., Windows, Linux, Mac]
• Database: [e.g., MySQL, H2]
• Userstore: [e.g., LDAP, JDBC]

---

Optional Fields

Related issues:

Suggested labels:

[aaujayasena]

@DilshanSenarath Can you share the current update on this please.