search

wso2 / product-is

Welcome to the WSO2 Identity Server source code! For info on working with the WSO2 Identity Server repository and contributing code, click the link below.
http://wso2.github.io/
Apache License 2.0
748 stars 727 forks source link

Performance Test IS 7.0.0 #16517

Closed Sachin-Mamoru closed 3 days ago

Sachin-Mamoru commented 1 year ago

Describe the issue:

Performance analysis on the product-is 6.2.0 pack to identify any performance degradation.

Performance Test - Version 01

Testing the following scenarios with Single Node 4 Core and 2 Core, Two Node 4 Core and 2 Core

Test Scenarios Description
Authenticate Super Tenant User Select random super tenant users and authenticate through the RemoteUserStoreManagerService.
Auth Code Grant Redirect With Consent Obtain an access token using the OAuth 2.0 authorization code grant type.
Implicit Grant Redirect With Consent Obtain an access token using the OAuth 2.0 implicit grant type.
Password Grant Type Obtain an access token using the OAuth 2.0 password grant type.
Client Credentials Grant Type Obtain an access token using the OAuth 2.0 client credential grant type.
OIDC Auth Code Grant Redirect With Consent Obtain an access token and an id token using the OAuth 2.0 authorization code grant type.
OIDC Implicit Grant Redirect With Consent Obtain an access token and an id token using the OAuth 2.0 implicit grant type.
OIDC Password Grant Type Obtain an access token and an id token using the OAuth 2.0 password grant type.
OIDC Auth Code Request Path Authenticator With Consent Obtain an access token and an id token using the request path authenticator.
SAML2 SSO Redirect Binding Obtain a SAML 2 assertion response using redirect binding.

Performance Test - Version 02

Publish performance test results in new representation for the selected flows.

  1. SAML2 SSO Redirect Binding
  2. OIDC Auth Code Grant Redirect With Consent
  3. Client Credentials Grant Type
  4. OIDC Password Grant Type
  5. JWT Bearer Grant Flow

Related Email Threads

  • [subject] Presenting our performance results in an optimal way to do capacity planning for our customers
mpmadhavig commented 11 months ago

<13-12-2023> Correlation log Analysis findings (authorize call with code grant flow)

You can find a sample correlation log of IS 6.1.0 and IS 7.0.0-rc1-SNAPSHOT from the given links.

According to the log files there are 4 sets of call we need to focus on. Please refer the above mentioned files for more information.

  1. Initial authorize call

    • The DB call are almost the same in both versions. Except for the part that the session is being saved in the new IS version in IDN_AUTH_SESSION_STORE instead of IDN_AUTH_TEMP_SESSION_STORE.

  2. Set of network call related to Client App related data loading.

    • The new IS versions uses few new DB queries with join operations. As this is a browser based call, it includes branding related queries as well. 
executeQuery|SELECT ID, NAME, DESCRIPTION FROM IDN_CONFIG_TYPE WHERE NAME = ? |jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT R.ID, R.TENANT_ID, R.NAME, R.CREATED_TIME, R.LAST_MODIFIED, R.HAS_FILE, R.HAS_ATTRIBUTE, T.NAME AS RESOURCE_TYPE, T.DESCRIPTION AS DESCRIPTION, F.ID AS FILE_ID, F.NAME AS FILE_NAME, A.ID AS ATTR_ID, A.ATTR_KEY AS ATTR_KEY, A.ATTR_VALUE AS ATTR_VALUE FROM IDN_CONFIG_RESOURCE AS R INNER JOIN IDN_CONFIG_TYPE AS T ON R.TYPE_ID = T.ID LEFT JOIN IDN_CONFIG_ATTRIBUTE AS A ON ( R.HAS_ATTRIBUTE = TRUE AND A.RESOURCE_ID = R.ID ) LEFT JOIN IDN_CONFIG_FILE AS F ON ( R.HAS_FILE = TRUE AND F.RESOURCE_ID = R.ID )WHERE R.NAME = ? AND R.TENANT_ID = ? AND R.TYPE_ID = ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT ID, NAME, DESCRIPTION FROM IDN_CONFIG_TYPE WHERE NAME = ? |jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT R.ID, R.TENANT_ID, R.NAME, R.CREATED_TIME, R.LAST_MODIFIED, R.HAS_FILE, R.HAS_ATTRIBUTE, T.NAME AS RESOURCE_TYPE, T.DESCRIPTION AS DESCRIPTION, F.ID AS FILE_ID, F.NAME AS FILE_NAME, A.ID AS ATTR_ID, A.ATTR_KEY AS ATTR_KEY, A.ATTR_VALUE AS ATTR_VALUE FROM IDN_CONFIG_RESOURCE AS R INNER JOIN IDN_CONFIG_TYPE AS T ON R.TYPE_ID = T.ID LEFT JOIN IDN_CONFIG_ATTRIBUTE AS A ON ( R.HAS_ATTRIBUTE = TRUE AND A.RESOURCE_ID = R.ID ) LEFT JOIN IDN_CONFIG_FILE AS F ON ( R.HAS_FILE = TRUE AND F.RESOURCE_ID = R.ID )WHERE R.NAME = ? AND R.TENANT_ID = ? AND R.TYPE_ID = ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT ID, NAME, DESCRIPTION FROM IDN_CONFIG_TYPE WHERE NAME = ? |jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT R.ID, R.TENANT_ID, R.NAME, R.CREATED_TIME, R.LAST_MODIFIED, R.HAS_FILE, R.HAS_ATTRIBUTE, T.NAME AS RESOURCE_TYPE, T.DESCRIPTION AS DESCRIPTION, F.ID AS FILE_ID, F.NAME AS FILE_NAME, A.ID AS ATTR_ID, A.ATTR_KEY AS ATTR_KEY, A.ATTR_VALUE AS ATTR_VALUE FROM IDN_CONFIG_RESOURCE AS R INNER JOIN IDN_CONFIG_TYPE AS T ON R.TYPE_ID = T.ID LEFT JOIN IDN_CONFIG_ATTRIBUTE AS A ON ( R.HAS_ATTRIBUTE = TRUE AND A.RESOURCE_ID = R.ID ) LEFT JOIN IDN_CONFIG_FILE AS F ON ( R.HAS_FILE = TRUE AND F.RESOURCE_ID = R.ID ) WHERE R.NAME = ? AND R.TENANT_ID = ? AND R.TYPE_ID = ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT COUNT(SP_APP.APP_NAME) FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?)|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT SP_APP.ID, SP_APP.APP_NAME, SP_APP.DESCRIPTION, SP_APP.UUID, SP_APP.IMAGE_URL, SP_APP.ACCESS_URL, SP_INBOUND_AUTH.INBOUND_AUTH_KEY, SP_INBOUND_AUTH.INBOUND_AUTH_TYPE, SP_APP.USERNAME, SP_APP.USER_STORE, SP_APP.TENANT_ID FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?) ORDER BY SP_APP.ID DESC LIMIT ?, ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT ID, NAME, DESCRIPTION FROM IDN_CONFIG_TYPE WHERE NAME = ? |jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT R.ID, R.TENANT_ID, R.NAME, R.CREATED_TIME, R.LAST_MODIFIED, R.HAS_FILE, R.HAS_ATTRIBUTE, T.NAME AS RESOURCE_TYPE, T.DESCRIPTION AS DESCRIPTION, F.ID AS FILE_ID, F.NAME AS FILE_NAME, A.ID AS ATTR_ID, A.ATTR_KEY AS ATTR_KEY, A.ATTR_VALUE AS ATTR_VALUE FROM IDN_CONFIG_RESOURCE AS R INNER JOIN IDN_CONFIG_TYPE AS T ON R.TYPE_ID = T.ID LEFT JOIN IDN_CONFIG_ATTRIBUTE AS A ON ( R.HAS_ATTRIBUTE = TRUE AND A.RESOURCE_ID = R.ID ) LEFT JOIN IDN_CONFIG_FILE AS F ON ( R.HAS_FILE = TRUE AND F.RESOURCE_ID = R.ID ) WHERE R.NAME = ? AND R.TENANT_ID = ? AND R.TYPE_ID = ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT COUNT(SP_APP.APP_NAME) FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?)|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT SP_APP.ID, SP_APP.APP_NAME, SP_APP.DESCRIPTION, SP_APP.UUID, SP_APP.IMAGE_URL, SP_APP.ACCESS_URL, SP_INBOUND_AUTH.INBOUND_AUTH_KEY, SP_INBOUND_AUTH.INBOUND_AUTH_TYPE, SP_APP.USERNAME, SP_APP.USER_STORE, SP_APP.TENANT_ID FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?) ORDER BY SP_APP.ID DESC LIMIT ?, ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT COUNT(SP_APP.APP_NAME) FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?)|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT SP_APP.ID, SP_APP.APP_NAME, SP_APP.DESCRIPTION, SP_APP.UUID, SP_APP.IMAGE_URL, SP_APP.ACCESS_URL, SP_INBOUND_AUTH.INBOUND_AUTH_KEY, SP_INBOUND_AUTH.INBOUND_AUTH_TYPE, SP_APP.USERNAME, SP_APP.USER_STORE, SP_APP.TENANT_ID FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?) ORDER BY SP_APP.ID DESC LIMIT ?, ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT ID, NAME, DESCRIPTION FROM IDN_CONFIG_TYPE WHERE NAME = ? |jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT R.ID, R.TENANT_ID, R.NAME, R.CREATED_TIME, R.LAST_MODIFIED, R.HAS_FILE, R.HAS_ATTRIBUTE, T.NAME AS RESOURCE_TYPE, T.DESCRIPTION AS DESCRIPTION, F.ID AS FILE_ID, F.NAME AS FILE_NAME, A.ID AS ATTR_ID, A.ATTR_KEY AS ATTR_KEY, A.ATTR_VALUE AS ATTR_VALUE FROM IDN_CONFIG_RESOURCE AS R INNER JOIN IDN_CONFIG_TYPE AS T ON R.TYPE_ID = T.ID LEFT JOIN IDN_CONFIG_ATTRIBUTE AS A ON ( R.HAS_ATTRIBUTE = TRUE AND A.RESOURCE_ID = R.ID ) LEFT JOIN IDN_CONFIG_FILE AS F ON ( R.HAS_FILE = TRUE AND F.RESOURCE_ID = R.ID ) WHERE R.NAME = ? AND R.TENANT_ID = ? AND R.TYPE_ID = ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT ID, NAME, DESCRIPTION FROM IDN_CONFIG_TYPE WHERE NAME = ? |jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT R.ID, R.TENANT_ID, R.NAME, R.CREATED_TIME, R.LAST_MODIFIED, R.HAS_FILE, R.HAS_ATTRIBUTE, T.NAME AS RESOURCE_TYPE, T.DESCRIPTION AS DESCRIPTION, F.ID AS FILE_ID, F.NAME AS FILE_NAME, A.ID AS ATTR_ID, A.ATTR_KEY AS ATTR_KEY, A.ATTR_VALUE AS ATTR_VALUE FROM IDN_CONFIG_RESOURCE AS R INNER JOIN IDN_CONFIG_TYPE AS T ON R.TYPE_ID = T.ID LEFT JOIN IDN_CONFIG_ATTRIBUTE AS A ON ( R.HAS_ATTRIBUTE = TRUE AND A.RESOURCE_ID = R.ID ) LEFT JOIN IDN_CONFIG_FILE AS F ON ( R.HAS_FILE = TRUE AND F.RESOURCE_ID = R.ID ) WHERE R.NAME = ? AND R.TENANT_ID = ? AND R.TYPE_ID = ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB

Below are the queries used by IS 6.1.0

executeQuery|SELECT COUNT(SP_APP.APP_NAME) FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?)|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT SP_APP.ID, SP_APP.APP_NAME, SP_APP.DESCRIPTION, SP_APP.UUID, SP_APP.IMAGE_URL, SP_APP.ACCESS_URL, SP_INBOUND_AUTH.INBOUND_AUTH_KEY, SP_INBOUND_AUTH.INBOUND_AUTH_TYPE, SP_APP.USERNAME, SP_APP.USER_STORE, SP_APP.TENANT_ID FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?) ORDER BY SP_APP.ID DESC LIMIT ?, ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT COUNT(SP_APP.APP_NAME) FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?)|jdbc:h2:./repository/database/WSO2IDENTITY_DB
executeQuery|SELECT SP_APP.ID, SP_APP.APP_NAME, SP_APP.DESCRIPTION, SP_APP.UUID, SP_APP.IMAGE_URL, SP_APP.ACCESS_URL, SP_INBOUND_AUTH.INBOUND_AUTH_KEY, SP_INBOUND_AUTH.INBOUND_AUTH_TYPE, SP_APP.USERNAME, SP_APP.USER_STORE, SP_APP.TENANT_ID FROM SP_APP LEFT JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE SP_APP.TENANT_ID = ? AND SP_APP.APP_NAME != ? AND (SP_APP.APP_NAME = ?) ORDER BY SP_APP.ID DESC LIMIT ?, ?|jdbc:h2:./repository/database/WSO2IDENTITY_DB

  1. commonauth call

    • The main different spotted was additional DB call to retrieve the tenant domain using the tenant id. Need to evaluate whether the caching layer got inactivated.

  2. Authorize redirect call

    • Below DB calls were introduced in IS 7.0.0 version. The time consumed by the extra DB calls are ~1 millisecond. 
      
executeQuery|SELECT OPERATION, SESSION_OBJECT, TIME_CREATED FROM IDN_AUTH_SESSION_STORE WHERE SESSION_ID =? AND SESSION_TYPE=? ORDER BY TIME_CREATED DESC LIMIT 1|jdbc:mysql://localhost:3306/reg_db_5?useSSL=false
executeUpdate|INSERT INTO IDN_AUTH_SESSION_STORE(SESSION_ID, SESSION_TYPE,OPERATION, TIME_CREATED, EXPIRY_TIME) VALUES (?,?,?,?,?)|jdbc:mysql://localhost:3306/reg_db_5?useSSL=false
mpmadhavig commented 11 months ago

Identified flows to be fixed:

mpmadhavig commented 11 months ago

<15-12-2023> Correlation log Analysis findings (authorize call with code grant flow)

  1. Initial authorize call

  2. Set of network call related to Client App related data loading. (UI related)

  3. commonauth call

  4. Authorize redirect call

mpmadhavig commented 10 months ago

Performance Test results IS 7.0.0 with the latest changes added

The following changes were done to the IS 7.0.0 alpha-SNAPSHOT before the test.

Result Summary

mpmadhavig commented 10 months ago

Performance Test Results for session and temp data separation enabled in legacy mode

Added the below config in the deployment.toml file to enable the config.

[session_data.session_data_persist.session_and_temp_data_separation_enabled]
enable=true

Result Summary

mpmadhavig commented 10 months ago

Please find the comparison between the different config modes

mpmadhavig commented 10 months ago

Performance Test Results Analysis based on concurrency

The below analysis done to the code grant flow authorize call.

To verify this, increased the maximum db pool count by 10 in IS 7.0.0, performance increased.

mpmadhavig commented 10 months ago

IS Performance Results - 2 Node 4 Core

  1. Increased pool size

    • Increased pool size to 500 as the maximum allowed concurrency is 500.
    • For concurrency below 300, there are slight improvements, for 300 and above signinficant improvements can be seen as the Thread queuing is now reduced.

  2. Scope validation removed and event listers removed cases showed the so far best results. Hence ran a test on the both cases combined.

    • Combined results are lower than the independent results.
mpmadhavig commented 10 months ago

Slow query analysis

mpmadhavig commented 10 months ago

Thread dump Analysis

  1. Threads on awaiting notification and timed awaiting notification states has increased in is7. On average, 176 -> 212 25 -> 42

  2. awaiting notification is7

    • Too many https-jsse-nio-9443-exec-xxx type of threads.
    • Threads blocked at tomcat level.

    is6

    • Observed awaiting pool-xx-thread-xx
    • Threads blocked at database level.

    Threads are mostly blocked at tomcat level in is7.

  3. timed awaiting notification

    • Too many SQL statement cancellations. On average 32s waiting time is taken by these type of statements.

Conclusion

Todo:

mpmadhavig commented 10 months ago

Slow query analysis - II

Concurrency - 300 Average response time of the fastest API call - ~340ms Slow query threshold - 100ms

IS 7.0.0 - Query Average Duration (s) Frequency
INSERT INTO IDN_AUTH_SESSION_META_DATA... 0.12 35
INSERT INTO IDN_AUTH_SESSION_APP_INFO... 0.28 32
INSERT INTO IDN_OAUTH2_TOKEN_BINDING... 0.13 31
INSERT INTO IDN_OAUTH2_ACCESS_TOKEN_SCOPE... 0.13 11
SELECT TOKEN_ID FROM IDN_OAUTH2_ACCESS_TOKEN... 0.12 6
SELECT OPERATION, SESSION_OBJECT, TIME_CREATED FROM IDN_AUTH_TEMP_SESSION_STORE... 0.23 3
SELECT UM_ROLE_NAME FROM UM_USER_ROLE, UM_ROLE, UM_USER... 0.16 5
IS 6.1.0 - Query Average Duration (s) Frequency
INSERT INTO IDN_AUTH_SESSION_META_DATA... 0.12 25

With the increase of the concurrency, the heavy queries such as listed above are getting slow.

mpmadhavig commented 10 months ago

Schema Analysis of the slowed tables

Query Any changes
IDN_AUTH_SESSION_META_DATA None
IDN_AUTH_SESSION_APP_INFO None
IDN_OAUTH2_TOKEN_BINDING None
IDN_OAUTH2_ACCESS_TOKEN_SCOPE TOKEN_SCOPE field size increased from 60 -> 255
IDN_OAUTH2_ACCESS_TOKEN New field AUTHORIZED_ORGANIZATION VARCHAR(36) DEFAULT 'NONE' NOT NULL , Con app key constraint updated with the new field, new index added CREATE INDEX IDX_TBR_TS ON IDN_OAUTH2_ACCESS_TOKEN(TOKEN_BINDING_REF, TOKEN_STATE)
IDN_AUTH_TEMP_SESSION_STORE None
mpmadhavig commented 10 months ago

Correlation log analysis of Send Consent Approve Request

When doing the performance degradation analysis we used oauth code grant flow. The flow has the below 5 API calls.

  1. Send request to authorize end point
  2. Common Auth Login HTTP Request
  3. Post Authentication Authorize call
  4. Send Consent Approve Request
  5. Get tokens

Correlation log analysis of 4. Send Consent Approve Request

Sample Correlation log DB calls of IS 7.0.0-beta4:

|executeQuery|SELECT OPERATION, SESSION_OBJECT, TIME_CREATED FROM IDN_AUTH_TEMP_SESSION_STORE WHERE SESSION_ID =? AND SESSION_TYPE=? ORDER BY TIME_CREATED DESC LIMIT 1|jdbc:mysql://localhost:3306/reg_db_t_2?useSSL=false
|executeUpdate|INSERT INTO IDN_AUTH_TEMP_SESSION_STORE(SESSION_ID, SESSION_TYPE,OPERATION, TIME_CREATED, EXPIRY_TIME) VALUES (?,?,?,?,?)|jdbc:mysql://localhost:3306/reg_db_t_2?useSSL=false
|execute|INSERT INTO  IDN_OAUTH2_AUTHORIZATION_CODE (CODE_ID, AUTHORIZATION_CODE, CONSUMER_KEY_ID, CALLBACK_URL, SCOPE, AUTHZ_USER, USER_DOMAIN, TENANT_ID, TIME_CREATED, VALIDITY_PERIOD, SUBJECT_IDENTIFIER, PKCE_CODE_CHALLENGE, PKCE_CODE_CHALLENGE_METHOD, AUTHORIZATION_CODE_HASH, IDP_ID) SELECT ?,?,IDN_OAUTH_CONSUMER_APPS.ID,?,?,?,?,?,?,?,?,?,?,?,IDP.ID FROM IDN_OAUTH_CONSUMER_APPS, IDP WHERE CONSUMER_KEY=? AND IDP.NAME=? AND IDP.TENANT_ID=? AND IDN_OAUTH_CONSUMER_APPS.TENANT_ID=?|jdbc:mysql://localhost:3306/reg_db_t_2?useSSL=false
|executeBatch|INSERT INTO IDN_OAUTH2_AUTHZ_CODE_SCOPE (CODE_ID, SCOPE, TENANT_ID) VALUES (?,?,?)|jdbc:mysql://localhost:3306/reg_db_t_2?useSSL=false
|executeUpdate|INSERT INTO IDN_AUTH_SESSION_STORE(SESSION_ID, SESSION_TYPE, OPERATION, SESSION_OBJECT, TIME_CREATED, EXPIRY_TIME, TENANT_ID) VALUES (?,?,?,?,?,?,?)|jdbc:mysql://localhost:3306/reg_db_t_2?useSSL=false
|executeUpdate|INSERT INTO IDN_AUTH_SESSION_STORE(SESSION_ID, SESSION_TYPE, OPERATION, SESSION_OBJECT, TIME_CREATED, EXPIRY_TIME, TENANT_ID) VALUES (?,?,?,?,?,?,?)|jdbc:mysql://localhost:3306/reg_db_t_2?useSSL=false

Sample Correlation log DB calls of IS 6.1.0:

|executeUpdate|DELETE FROM IDN_AUTH_TEMP_SESSION_STORE WHERE SESSION_ID = ? AND  SESSION_TYPE = ?|jdbc:mysql://localhost:3306/reg_db_2?useSSL=false
|execute|INSERT INTO  IDN_OAUTH2_AUTHORIZATION_CODE (CODE_ID, AUTHORIZATION_CODE, CONSUMER_KEY_ID, CALLBACK_URL, SCOPE, AUTHZ_USER, USER_DOMAIN, TENANT_ID, TIME_CREATED, VALIDITY_PERIOD, SUBJECT_IDENTIFIER, PKCE_CODE_CHALLENGE, PKCE_CODE_CHALLENGE_METHOD, AUTHORIZATION_CODE_HASH, IDP_ID) SELECT ?,?,IDN_OAUTH_CONSUMER_APPS.ID,?,?,?,?,?,?,?,?,?,?,?,IDP.ID FROM IDN_OAUTH_CONSUMER_APPS, IDP WHERE CONSUMER_KEY=? AND IDP.NAME=? AND IDP.TENANT_ID=?|jdbc:mysql://localhost:3306/reg_db_2?useSSL=false
|executeBatch|INSERT INTO IDN_OAUTH2_AUTHZ_CODE_SCOPE (CODE_ID, SCOPE, TENANT_ID) VALUES (?,?,?)|jdbc:mysql://localhost:3306/reg_db_2?useSSL=false
|executeUpdate|INSERT INTO IDN_AUTH_SESSION_STORE(SESSION_ID, SESSION_TYPE, OPERATION, SESSION_OBJECT, TIME_CREATED, EXPIRY_TIME, TENANT_ID) VALUES (?,?,?,?,?,?,?)|jdbc:mysql://localhost:3306/reg_db_2?useSSL=false
|executeUpdate|INSERT INTO IDN_AUTH_SESSION_STORE(SESSION_ID, SESSION_TYPE, OPERATION, SESSION_OBJECT, TIME_CREATED, EXPIRY_TIME, TENANT_ID) VALUES (?,?,?,?,?,?,?)|jdbc:mysql://localhost:3306/reg_db_2?useSSL=false

Observations

  1. Below new queries are getting executed:

The query 

|executeUpdate|DELETE FROM IDN_AUTH_TEMP_SESSION_STORE WHERE SESSION_ID = ? AND  SESSION_TYPE = ?|jdbc:mysql://localhost:3306/reg_db_2?useSSL=false

has been replaced with 

|executeQuery|SELECT OPERATION, SESSION_OBJECT, TIME_CREATED FROM IDN_AUTH_TEMP_SESSION_STORE WHERE SESSION_ID =? AND SESSION_TYPE=? ORDER BY TIME_CREATED DESC LIMIT 1|jdbc:mysql://localhost:3306/reg_db_t_2?useSSL=false
|executeUpdate|INSERT INTO IDN_AUTH_TEMP_SESSION_STORE(SESSION_ID, SESSION_TYPE,OPERATION, TIME_CREATED, EXPIRY_TIME) VALUES (?,?,?,?,?)|jdbc:mysql://localhost:3306/reg_db_t_2?useSSL=false

Explanation:

Instead of deleting the temp session data record, the record has been marked as deleted and in a separate session data cleaner, these records are being removed. By the time of perf test execution this clean up task is not happening causing the DB to contain huge number of temp session data records.

mpmadhavig commented 10 months ago

Performance test results - 2 Node 4 Core - All improvements added + Internal session data clean up enabled by default

https://github.com/wso2/carbon-identity-framework/pull/5362

[session_data.cleanup]
enable_expired_data_cleanup = true
clean_logged_out_sessions_at_immediate_cycle = true
enable_pre_session_data_cleanup = true

Results Summary

mpmadhavig commented 10 months ago

Correlation log analysis of Post Authentication Authorize call

6.1.0 extra queries

|executeQuery|SELECT CONSENTED_SCOPES.SCOPE, CONSENTED_SCOPES.CONSENT FROM IDN_OAUTH2_USER_CONSENT USER_CONSENT, IDN_OAUTH2_USER_CONSENTED_SCOPES CONSENTED_SCOPES WHERE USER_CONSENT.USER_ID = ? AND USER_CONSENT.APP_ID = ? AND USER_CONSENT.TENANT_ID = ? AND USER_CONSENT.CONSENT_ID = CONSENTED_SCOPES.CONSENT_ID
|executeUpdate|INSERT INTO IDN_AUTH_TEMP_SESSION_STORE(SESSION_ID, SESSION_TYPE, OPERATION, SESSION_OBJECT, TIME_CREATED, EXPIRY_TIME, TENANT_ID) VALUES (?,?,?,?,?,?,?)

7.0.0 extra queries

|executeQuery|SELECT UUID FROM SP_APP INNER JOIN SP_INBOUND_AUTH ON SP_APP.ID = SP_INBOUND_AUTH.APP_ID WHERE INBOUND_AUTH_KEY = ? AND INBOUND_AUTH_TYPE = ? AND SP_APP.TENANT_ID = ? AND SP_INBOUND_AUTH.TENANT_ID=?|jdbc:mysql://localhost:3306/reg_db_1?useSSL=false
|executeQuery|SELECT FILTEREDSCOPES.NAME, FILTEREDSCOPES.DISPLAY_NAME, FILTEREDSCOPES.DESCRIPTION, IDN_CLAIM.CLAIM_URI FROM (SELECT * FROM IDN_OAUTH2_SCOPE WHERE IDN_OAUTH2_SCOPE.TENANT_ID=? AND IDN_OAUTH2_SCOPE.SCOPE_TYPE=?) FILTEREDSCOPES LEFT JOIN IDN_OIDC_SCOPE_CLAIM_MAPPING ON IDN_OIDC_SCOPE_CLAIM_MAPPING.SCOPE_ID = FILTEREDSCOPES.SCOPE_ID LEFT JOIN IDN_CLAIM ON IDN_CLAIM.ID = IDN_OIDC_SCOPE_CLAIM_MAPPING.EXTERNAL_CLAIM_ID LEFT JOIN IDN_CLAIM_DIALECT ON IDN_CLAIM_DIALECT.ID = IDN_CLAIM.DIALECT_ID WHERE FILTEREDSCOPES.TENANT_ID = ? AND (IDN_CLAIM_DIALECT.TENANT_ID = ? AND IDN_CLAIM_DIALECT.DIALECT_URI = ?) OR (IDN_CLAIM_DIALECT.DIALECT_URI IS NULL AND IDN_CLAIM_DIALECT.TENANT_ID IS NULL)|jdbc:mysql://localhost:3306/reg_db_1?useSSL=false
|executeQuery|SELECT MAIN_APP_ID FROM SP_SHARED_APP WHERE SHARED_APP_ID = ?|jdbc:mysql://localhost:3306/reg_db_1?useSSL=false
|executeQuery|SELECT POLICY_ID, SCOPE_NAME FROM AUTHORIZED_API JOIN AUTHORIZED_SCOPE ON AUTHORIZED_API.APP_ID = AUTHORIZED_SCOPE.APP_ID AND AUTHORIZED_API.API_ID = AUTHORIZED_SCOPE.API_ID WHERE AUTHORIZED_API.APP_ID = ?|jdbc:mysql://localhost:3306/reg_db_1?useSSL=false
|executeQuery|SELECT ID, NAME, DISPLAY_NAME, DESCRIPTION, API_ID, TENANT_ID FROM SCOPE WHERE  TENANT_ID = ?|jdbc:mysql://localhost:3306/reg_db_1?useSSL=false
|executeQuery|SELECT CONSENTED_SCOPES.SCOPE, CONSENTED_SCOPES.CONSENT FROM IDN_OAUTH2_USER_CONSENT USER_CONSENT, IDN_OAUTH2_USER_CONSENTED_SCOPES CONSENTED_SCOPES WHERE USER_CONSENT.USER_ID = ? AND USER_CONSENT.APP_ID = ? AND USER_CONSENT.TENANT_ID = ? AND USER_CONSENT.CONSENT_ID = CONSENTED_SCOPES.CONSENT_ID|jdbc:mysql://localhost:3306/reg_db_1?useSSL=false
|executeUpdate|INSERT INTO IDN_AUTH_TEMP_SESSION_STORE(SESSION_ID, SESSION_TYPE, OPERATION, SESSION_OBJECT, TIME_CREATED, EXPIRY_TIME, TENANT_ID) VALUES (?,?,?,?,?,?,?)|jdbc:mysql://localhost:3306/reg_db_1?useSSL=false

Observations

Conclusion

mpmadhavig commented 10 months ago

Perf analysis for https://github.com/wso2-extensions/identity-inbound-auth-oauth/pull/2312

mpmadhavig commented 10 months ago

Next Task plan

mpmadhavig commented 9 months ago

Perf analysis for https://github.com/wso2-extensions/identity-inbound-auth-oauth/pull/2324

mpmadhavig commented 9 months ago

Perf analysis for https://github.com/wso2/carbon-identity-framework/pull/5404

mpmadhavig commented 8 months ago

Action Items

isharak commented 3 days ago

This issue is being closed due to extended inactivity. Please feel free to reopen it if further attention is needed. Thank you for helping us keep the issue list relevant and focused!