Symantec Identity Suite - Performance tuning for SQL pool connection sizes
Article ID: 240023
Updated On:
Products
Issue/Introduction
This is a Tuning guide, which will provide details on adjusting the SQL database connection settings for Identity Manager / Identity Portal to optimize your environment.
If you are seeing messages about "unable to fill pool" in the server.log, for example:
From JBoss / Wildfly:
2022-11-14 19:25:00,418 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubject] (JCA PoolFiller) IJ000610: Unable to fill pool: java:/iam/im/jdbc/jdbc/idm: javax.resource.ResourceException: IJ031084: Unable to create connection
from Weblogic:
08:50:38,639 ERROR [im.provisioning.inbound] weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool iam_im DataSource-tp to allocate to applications, please increase the size of the pool and retry..
Have overall slowness in the GUI, during Task execution, or would simply like to ensure plenty of headroom as your environment grows, please review the following details.
Please note, as this is a tuning exercise there is no one size fits all answer for all clients.
The below recommendations are starting points and do not represent where you may end up.
Environment
Release : 14.3, 14.4, 14.5
Component : IdentitySuite(Identity Suite)
Component : IdentityManager(Identity Manager)
Resolution
The following information discusses adjusting the database connection pools. Identity Manager makes heavy usage of the backend database to perform a task. A single task can go through hundreds, and potentially thousands of independent connections into the database to create, update or simply retrieve data during the course of the Task Run. For a discussion of the Task Persistence runtime database and its usage please see KB 10220
The below information discusses tuning the JDBC Connection Pools, is based on a Wildfly / JBOSS application server deployment, with Oracle as the backend database. These values are intended as initial reference points as you tune your environment for optimal performance, and would be applicable, though implemented in different ways, for all supported application servers and backend databases. For details on changing the connection pools in other application servers, please discuss with your application server admin.
The default settings out of the box configure the minimum pool to 5, and the max to 200. This is sufficient for many of our clients, but as overall Task volume increases these defaults are not enough for optimal performance and in severe cases can lead to in-progress Tasks and potential outages as there are not enough connections available to reach into the database to perform work.
Both the Application Server Administrator and your DBA should be engaged in this tuning process as there are potential impacts on both the Application Server and Database, where adjusting the number of possible pooled connections the application server can establish will require adjustments at the database layer to accommodate the increase.
Broadcom has identified through working with several of our largest volume clients several connection settings around the pooling that can be tuned to optimize overall performance. Again your Application server and database Admin should be consulted to fully understand the impacts of these changes.
The settings and a very brief description of what each does:
<min-pool-size> - this is the minimum number of connections the application server will attempt to maintain. If the load increases beyond this pool additional pools will be requested from the database up to the max pool setting, as the load decreases the number of pools will be closed down to this minimum value.
<max-pool-size> - this is the maximum number of connections the application server will establish into the database. Not having a sufficient number of available connections will, in minor cases, result in updates having to wait for a connection to be freed up before its work can be completed; in severe cases, these updates will be abandoned and the actions never committed into the database leaving tasks in an in-progress state.
<initial-pool-size> - this tells the application server how many connections to immediately establish upon startup. This bypasses the negotiation between the application server and the database when an update needs to be made by opening the specified connections and holding them open. Instead of having to request a connection and then send the request through that connection to the database, the application server can utilize one of these already established connections immediately.
The recommendation from our engineering team is that we define at least some connections for the initial pool to lower the overhead associated with establishing a new connection.
<pool-prefill> - This is handled differently between JBOSS and Wildfly. In JBOSS environments we have found that setting prefill to true is beneficial.
<use-strict-min> - The pool-use-strict-min attribute specifies whether JBoss EAP allows the number of connections in the pool to fall below the specified minimum. If pool-use-strict-min is set to true, JBoss EAP will not allow the number of connections to temporarily fall below the specified minimum. The default value is false.
<validate-on-match>
<background-validation>
These two settings impact the validation of available connections. With validate-on-match, set to TRUE, the database connection is validated every time it is checked out from the connection pool, with background-validation set to TRUE this is done in the background based on the <background-validation-millis> setting. We have found that some clients see benefits from using back background validation whereas in other cases validate-on-match is a better configuration.
It is recommended to increase at a minimum the imobjectstoredb and imtaskpersistencedb datasource configuration, but depending on your usage and architecture you may want or need to increase the connection pool sizes for other datasources.
As with all Tuning, there is no one right answer for all clients and the following information should be used as a starting point and reviewed as IDM usage patterns change.
For non-VAPP based Identity Manager deployments,
If you are using an on-premises non-vapp based deployment you can search for imobjectstoredb or imtaskpersistencedb datasources and update the connection pool settings directly within the /standalone/configuration/ standalone-full.xml (for single node deployments) or standalone-full-ha.xml (for clustered deployments).
The following examples show the configurations we arrived at while working directly with our engineering team to optimize the performance of internal test environments depending on different usage patterns and have been implemented in different client environments to increase overall performance.
These configurations required the Oracle database to have enough overall horsepower and be tuned to support these changes. In the following examples, the oracle "PROCESSES" needed to be increased to accommodate the number of connections specified and increase of server resources as each open connection requires some CPU and Memory in the database to hold open which may increase the CPU / Memory requirements of the Database server.
Very Large load environments:
<min-pool-size>550</min-pool-size>
<initial-pool-size>550</initial-pool-size>
<max-pool-size>1200</max-pool-size>
<use-strict-min>true</use-strict-min> (JBoss/Wildfly)
<prefill>true</prefill> (Jboss Only/Not needed on Wildfly)
Medium load environments:
For most of our clients, the above settings are excessive. The following example shows a healthy pool configuration that should be sufficient for much the Identity Manager client base:
<min-pool-size>350</min-pool-size>
<initial-pool-size>350</initial-pool-size>
<max-pool-size>700</max-pool-size>
<use-strict-min>true</use-strict-min> (JBoss/Wildfly)
<prefill>true</prefill> (Jboss Only/Not needed on Wildfly)
Small load environments:
We also have clients that have only had to make minor adjustments to these configurations to reach optimal performance for their usage patterns:
<min-pool-size>150</min-pool-size>
<initial-pool-size>150</initial-pool-size>
<max-pool-size>300</max-pool-size>
<use-strict-min>true</use-strict-min> (JBoss/Wildfly)
<prefill>true</prefill> (Jboss Only/Not needed on Wildfly)
Regarding the connection validation; validate-on-match versus background-validation.
When the <validate-on-match> option is set to true, the database connection will be validated every time from the connection pool. When <background-validation> is true the connection validation is a background process that checks the connections on a regular schedule. These settings conflict with each other, if one is set to true the other needs to be set to false.
Adjusting these can have performance benefits and you will need to test to determine which configuration works best for each environment.
The out-of-the-box configuration is set to do background validation every 12 seconds:
<validate-on-match>false</validate-on-match>
<background-validation>true</background-validation>
<background-validation-millis>120000</background-validation-millis>
We have seen performance benefits in some environments by changing this to use the validate-on-match:
<validate-on-match>true</validate-on-match>
<background-validation>false</background-validation>
For VAPP based deployments,
Starting in Version 14.4 we have provided an alias to update the connection string settings. The alias is "setOOTBDatasourceConfig" and allows simplified adjustments of the database connection string without the need to use the CLI commands.
Please review the documentation on this alias for more details.
For VAPP version 14.3 deployments we cannot directly update the standalone files and you will need to run CLI commands to effect the changes.
To do this on VAPP first add a new user to JBoss:
sudo /opt/CA/wildfly-idm/bin/add-user.sh
What type of user do you wish to add?
a) Management User (mgmt-users.properties)
b) Application User (application-users.properties)
(a): a
Username : <JbossAdminName>
What groups do you want this user to belong to? (Please enter a comma separated list, or leave blank for none)[ ]:
Press Enter to leave blank
About to add user '<JbossAdminName>' for realm 'ManagementRealm'
Is this correct yes/no? yes
Added user '<JbossAdminName>' to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-users.properties'
Added user '<JbossAdminName>' to file '/opt/CA/wildfly-idm/domain/configuration/mgmt-users.properties'
Added user '<JbossAdminName>' with groups to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-groups.properties'
Added user '<JbossAdminName>' with groups to file '/opt/CA/wildfly-idm/domain/configuration/mgmt-groups.properties'
Is this new user going to be used for one AS process to connect to another AS process?
e.g. for a slave host controller connecting to the master or for a Remoting connection for server to server EJB calls.
yes/no? no
Next, navigate to /opt/CA/wildfly-idm/bin/
execute ./jboss-cli.sh
type: connect
Enter credentials
Once connected run the below CLI commands:
Objectstoredb Pool Size Settings:
/subsystem=datasources/data-source=iam_im-imobjectstoredb-ds/:write-attribute(name=min-pool-size,value=550)
/subsystem=datasources/data-source=iam_im-imobjectstoredb-ds/:write-attribute(name=initial-pool-size,value=550)
/subsystem=datasources/data-source=iam_im-imobjectstoredb-ds/:write-attribute(name=max-pool-size,value=1200)
/subsystem=datasources/data-source=iam_im-imobjectstoredb-ds/:write-attribute(name=pool-use-strict-min,value=true)
imtaskpersistencedb Pool Size Settings:
/subsystem=datasources/data-source=iam_im-imtaskpersistencedb-ds/:write-attribute(name=min-pool-size,value=550)
/subsystem=datasources/data-source=iam_im-imtaskpersistencedb-ds/:write-attribute(name=initial-pool-size,value=550)
/subsystem=datasources/data-source=iam_im-imtaskpersistencedb-ds/:write-attribute(name=max-pool-size,value=1200)
/subsystem=datasources/data-source=iam_im-imtaskpersistencedb-ds/:write-attribute(name=pool-use-strict-min,value=true)
To build this for other tables, simply update the 'data-source' to represent the appropriate name.
For example if you additionally wanted to update the imworkflowdb it would look like the below sample:
imworkflowdb Pool Size Settings:
/subsystem=datasources/data-source=iam_im-imworkflowdb-ds/:write-attribute(name=min-pool-size,value=550)
/subsystem=datasources/data-source=iam_im-imworkflowdb-ds/:write-attribute(name=initial-pool-size,value=550)
/subsystem=datasources/data-source=iam_im-imworkflowdb-ds/:write-attribute(name=max-pool-size,value=1200)
/subsystem=datasources/data-source=iam_im-imworkflowdb-ds/:write-attribute(name=pool-use-strict-min,value=true)
To change the validation, for example to change from the default background-validation to validate-on-match = true:
/subsystem=datasources/data-source=iam_im-imobjectstoredb-ds/:write-attribute(name=background-validation,value=false)
/subsystem=datasources/data-source=iam_im-imobjectstoredb-ds/:write-attribute(name=validate-on-match,value=true)
/subsystem=datasources/data-source=iam_im-imtaskpersistencedb-ds/:write-attribute(name=background-validation,value=false)
/subsystem=datasources/data-source=iam_im-imtaskpersistencedb-ds/:write-attribute(name=validate-on-match,value=true)
/subsystem=datasources/data-source=iam_im-imarchivedb-ds/:write-attribute(name=background-validation,value=false)
/subsystem=datasources/data-source=iam_im-imarchivedb-ds/:write-attribute(name=validate-on-match,value=true)
/subsystem=datasources/data-source=iam_im-imauditdb-ds/:write-attribute(name=background-validation,value=false)
/subsystem=datasources/data-source=iam_im-imauditdb-ds/:write-attribute(name=validate-on-match,value=true)
/subsystem=datasources/data-source=iam_im-imreportsnapshotdb-ds/:write-attribute(name=background-validation,value=false)
/subsystem=datasources/data-source=iam_im-imreportsnapshotdb-ds/:write-attribute(name=validate-on-match,value=true)
/subsystem=datasources/data-source=iam_im-imworkflowdb-ds/:write-attribute(name=background-validation,value=false)
/subsystem=datasources/data-source=iam_im-imworkflowdb-ds/:write-attribute(name=validate-on-match,value=true)
For Identity Portal,
it is recommended to increase the SQL pool to the below values for the sigma-ds datasource and should be sufficient for almost all usage patterns as most of the workload seen in the Portal is handled within IDM, or Governance and only displayed through the Portal
<min-pool-size>100</min-pool-size>
<initial-pool-size>100</initial-pool-size>
<max-pool-size>250</max-pool-size>
<use-strict-min>true</use-strict-min> (JBoss/Wildfly)
<prefill>true</prefill> (Jboss Only/Not needed on Wildfly)
To do this on VAPP first add a new user to JBoss:
sudo /opt/CA/wildfly-portal/bin/add-user.sh
What type of user do you wish to add?
a) Management User (mgmt-users.properties)
b) Application User (application-users.properties)
(a): a
Username : <JbossAdminName>
What groups do you want this user to belong to? (Please enter a comma separated list, or leave blank for none)[ ]:
Press Enter to leave blank
About to add user '<JbossAdminName>' for realm 'ManagementRealm'
Is this correct yes/no? yes
Added user '<JbossAdminName>' to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-users.properties'
Added user '<JbossAdminName>' to file '/opt/CA/wildfly-idm/domain/configuration/mgmt-users.properties'
Added user '<JbossAdminName>' with groups to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-groups.properties'
Added user '<JbossAdminName>' with groups to file '/opt/CA/wildfly-idm/domain/configuration/mgmt-groups.properties'
Is this new user going to be used for one AS process to connect to another AS process?
e.g. for a slave host controller connecting to the master or for a Remoting connection for server to server EJB calls.
yes/no? no
Next, navigate to /opt/CA/wildfly-portal/bin/
execute ./jboss-cli.sh
type: connect
Enter credentials
Once connected run the below CLI commands:
sigma-ds Pool Size Settings:
/subsystem=datasources/data-source=sigma-ds/:write-attribute(name=min-pool-size,value=100)
/subsystem=datasources/data-source=sigma-ds/:write-attribute(name=initial-pool-size,value=100)
/subsystem=datasources/data-source=sigma-ds/:write-attribute(name=max-pool-size,value=250)
/subsystem=datasources/data-source=sigma-ds/:write-attribute(name=pool-use-strict-min,value=true)
Additional Information
IMPORTANT:
In a previous version of this article, we had recommended prefill = true. This is no longer required with the new tuning suggestions.
To remove the setting you can run the below command through JBossCLI. You will need to also the datasource object for all locations.
/subsystem=datasources/data-source=iam_im-imobjectstoredb-ds/:undefine-attribute(name=pool-prefill)
Please be sure to cycle your services after updating the connection pool sizes as these values only get challenged during start-up.
It is important to determine the max amount of possible connections going to your SQL database. To do this, navigate into the standalone XML add all the max-pool-size connections together and then multiply it by the number of nodes you have in the IDM cluster and that is the max amount of connections you could use.
This is an important thing to note as if the total max pool size exceeds the total amount of SQL connections allowed by the database, you could cause the application to cap the maximum amount of connections.
Feedback
