search cancel

Symantec Identity Suite - Performance tuning for SQL pool connection sizes

book

Article ID: 240023

calendar_today

Updated On:

Products

CA Identity Manager CA Identity Portal CA Identity Suite

Issue/Introduction

This guide will provide foundation SQL settings for Identity Manager / Identity Portal. The below recommendations are starting points and do not represent where you may end up.

Environment

Release : 14.3, 14.4

Component : IdentitySuite(Identity Suite)

Component : IdentityManager(Identity Manager)

Resolution

 

For Identity Manager, it is recommended to increase at a minimum the imobjectstoredb and imtaskpersistencedb tables but depending on your usage and architecture you may want to increase the connection pool sizes for other tables and if you configure SQL to be your userstore.

If you are using Standalone you can search for imobjectstoredb or imtaskpersistencedb and update the connection pool settings to be the below value:

<min-pool-size>350</min-pool-size>
<initial-pool-size>550</initial-pool-size>
<max-pool-size>1200</max-pool-size>
<prefill>true</prefill> 

For VAPP you will need to run Jboss CLI commands. 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 : YourJbossAdmin
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 'YourJbossAdmin' for realm 'ManagementRealm'
Is this correct yes/no? yes
Added user 'YourJbossAdmin' to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-users.properties'
Added user 'YourJbossAdmin' to file '/opt/CA/wildfly-idm/domain/configuration/mgmt-users.properties'
Added user 'YourJbossAdmin' with groups  to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-groups.properties'
Added user 'YourJbossAdmin' 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=350)
/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-prefill,value=true)

imtaskpersistencedb Pool Size Settings:
/subsystem=datasources/data-source=iam_im-imtaskpersistencedb-ds/:write-attribute(name=min-pool-size,value=350)
/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-prefill,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=350)
/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-prefill,value=true)

 

For Identity Portal, it is recommended to increase the SQL pool to the below values for the sigma-ds datasource:

<min-pool-size>35</min-pool-size>
<initial-pool-size>55</initial-pool-size>
<max-pool-size>120</max-pool-size>
<prefill>true</prefill> 

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 : YourJbossAdmin
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 'YourJbossAdmin' for realm 'ManagementRealm'
Is this correct yes/no? yes
Added user 'YourJbossAdmin' to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-users.properties'
Added user 'YourJbossAdmin' to file '/opt/CA/wildfly-idm/domain/configuration/mgmt-users.properties'
Added user 'YourJbossAdmin' with groups  to file '/opt/CA/wildfly-idm/standalone/configuration/mgmt-groups.properties'
Added user 'YourJbossAdmin' 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=35)
/subsystem=datasources/data-source=sigma-ds/:write-attribute(name=initial-pool-size,value=55)
/subsystem=datasources/data-source=sigma-ds/:write-attribute(name=max-pool-size,value=120)
/subsystem=datasources/data-source=sigma-ds/:write-attribute(name=pool-prefill,value=true)

Additional Information

Please be sure to cycle your services after updating the connection pool sizes as these values only get challenged during start-up.

Additionally, 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.