A.20 Implementation of Connection Pooling in PGX Realm

PGX Server creates a connection and is used to load data. Implementing a connection pool for performance improvement is recommended to save time when creating and closing connections.

To implement the connection pool, follow these steps:
  1. Login to the server as a non-root user.
  2. Navigate to the <COMPLIANCE_STUDIO_INSTALLATION_PATH>/bin directory.
  3. Configure following attributes in the config.sh file as shown in the following table.

    Table A-12 Config.sh File

    Parameter Significance Default Value / Example
    PGX_ENABLE_CP It is used to enable or disable connection pooling for sub graph loading. The value for 'PGX_ENABLE_CP' is "true" or "false". For example, PGX_ENABLE_CP=true
    PGX_CP_INITIAL_SIZE Indicates the initial number of connections that are created when the pool is started. For example, 5
    PGX_CP_MAX_TOTAL Indicates the maximum number of active connections that can be allocated from this pool at the same time or negative for no limit. For example, 25
    PGX_CP_MAX_IDLE Indicates the maximum number of connections that can remain idle in the pool, without extra ones being released or negative for no limit. For example, 10
    PGX_CP_MIN_IDLE Indicates the minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none. For example, 5
    PGX_CP_MAX_WAIT_ MILLIS Indicates the maximum number of milliseconds that the pool will wait (when there are no available connections)for a connection to be returned before throwing an exception or -1 to wait indefinitely. Forexample, 3000
    PGX_CP_MIN_EVICTABLE_ID LE Indicates the minimum amount of timea connection may sit idle in the pool before it is closed and a new connection is created if count of connections is less than PGX_CP_MIN_IDLE. Forexample, PT30M
    PGX_CP_SOFT_MIN_EVICTA BLE

    Indicates the minimum amount of timea connection may sit idle in the pool before it is closed and a new connection is created.

    NOTE:Thevalue is lesser than PGX_CP_MIN_EVICTABLE_IDL

    E_TIME will close all the idle connectionand create connection to match PGX_CP_MIN_IDLE.

    		 For example, PT8H. PT30M= 30 minutes PT55S = 55 seconds PT2H = 2 hours

While executing the Refresh Graph task, the connection pooling parameters can be overridden by the run time parameters.

To configure run time parameters, follow these steps:
  1. On the Orchestration menu, click Schedule Batch.
  2. Select the Out-of-the-box (BD/ECM) graph and click Edit Dynamic Params.
  3. On the Refresh Graph, provide the following value.

    initialSize=5, maxTotal=15, maxIdle=10, minIdle=5, maxWaitMillis=3000, minEvictableIdleTime=PT30M, softMinEvictableIdleTime=PT8H

  4. Run the Refresh Graph task.

    Note:

    If you have more than one PGX server for load balancer, restart PGX servers.
  5. To update connection pooling details in the existing graph, execute the following script in the <COMPLIANCE_STUDIO_INSTALLATION_PATH>/deployed/mmg-home/mmg-load-tograph/ graph-service/utility/bin directory.
    ./SetConnectionPoolConfig.sh --username <#username#> --graph-id <#graphid
#> --initial-size <#initial-size#> --max-total <#max-total#> --maxidle
<#max-idle#> --min-idle<# min-idle#> --max-wait-millis <#max-waitmillis#>
--min-evict-idle-time <#min-evict-idle-time#> --soft-min-evictidle-
time <#soft-min-evict-idle-time#>

    Note:

    This step is applicable only for the existing graph pipeline.
    For example,
    ./SetConnectionPoolConfig.sh --username fccuser --graph-id --
initial-size 25 --max-total 50 --max-idle 35 --min-idle 10 --max-waitmillis
3000 --min-evict-idle-time PT30M --soft-min-evict-idle-time PT8H
You can refer place holder details in the following table.

Table A-13 Description for Connection Pool Parameter

#Place Holder# Description
[-u|--username] Compliance Studio User
[-g|--graph-id] The graph id for which pool-able connection details are to be set.
[-i|--initial-size] The initial number of connections created when the pool is started.
[-t|--max-total] The maximum number of active connections that can be allocated from this pool at the same time.
[-mi|--max-idle] The maximum number of connections that can remain idle in the pool without extra ones being released.
[-li|--min-idle] The minimum number of connections that can remain idle in the pool without extra ones being created.
[-w|--max-wait-millis] The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception.
[-et|--min-evict-idle-time]

The minimum amount of time a connection may sit idle in the pool before it is closed and a new connection is created if the count of connections is less than the min-idle value.

NOTE:

The value should be in ISO-8601 format. Refer to examples on ISO-8601 format values.
[-st|--soft-min-evict-idle-time]

The minimum amount of time a connection may sit idle in the pool before it is closed and a new connection is created.

NOTE:

The value should be in ISO-8601 format. Refer to examples on ISO-8601 format values.
[-h|--help] For any help required to execute this script.