Chapter 11. Adding managed datasources to Data Grid Server Optimize connection pooling and performance for JDBC database connections by adding managed datasources to your Data Grid Server configuration. 11.1. Configuring managed datasources Create managed datasources as part of your Data Grid Server configuration to optimize connection pooling and performance for JDBC database connections. You can then specify the JDNI name of the managed datasources in your caches, which centralizes JDBC connection configuration for your deployment. Prerequisites Copy database drivers to the server/lib directory in your Data Grid Server installation. Tip Use the install command with the Data Grid Command Line Interface (CLI) to download the required drivers to the server/lib directory, for example: Procedure Open your Data Grid Server configuration for editing. Add a new data-source to the data-sources section. Uniquely identify the datasource with the name attribute or field. Specify a JNDI name for the datasource with the jndi-name attribute or field. Tip You use the JNDI name to specify the datasource in your JDBC cache store configuration. Set true as the value of the statistics attribute or field to enable statistics for the datasource through the /metrics endpoint. Provide JDBC driver details that define how to connect to the datasource in the connection-factory section. Specify the name of the database driver with the driver attribute or field. Specify the JDBC connection url with the url attribute or field. Specify credentials with the username and password attributes or fields. Provide any other configuration as appropriate. Define how Data Grid Server nodes pool and reuse connections with connection pool tuning properties in the connection-pool section. Save the changes to your configuration. Verification Use the Data Grid Command Line Interface (CLI) to test the datasource connection, as follows: Start a CLI session. List all datasources and confirm the one you created is available. Test a datasource connection. Managed datasource configuration XML <server xmlns="urn:infinispan:server:14.0"> <data-sources> <!-- Defines a unique name for the datasource and JNDI name that you reference in JDBC cache store configuration. Enables statistics for the datasource, if required. --> <data-source name="ds" jndi-name="jdbc/postgres" statistics="true"> <!-- Specifies the JDBC driver that creates connections. --> <connection-factory driver="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/postgres" username="postgres" password="changeme"> <!-- Sets optional JDBC driver-specific connection properties. --> <connection-property name="name">value</connection-property> </connection-factory> <!-- Defines connection pool tuning properties. --> <connection-pool initial-size="1" max-size="10" min-size="3" background-validation="1000" idle-removal="1" blocking-timeout="1000" leak-detection="10000"/> </data-source> </data-sources> </server> JSON { "server": { "data-sources": [{ "name": "ds", "jndi-name": "jdbc/postgres", "statistics": true, "connection-factory": { "driver": "org.postgresql.Driver", "url": "jdbc:postgresql://localhost:5432/postgres", "username": "postgres", "password": "changeme", "connection-properties": { "name": "value" } }, "connection-pool": { "initial-size": 1, "max-size": 10, "min-size": 3, "background-validation": 1000, "idle-removal": 1, "blocking-timeout": 1000, "leak-detection": 10000 } }] } } YAML server: dataSources: - name: ds jndiName: 'jdbc/postgres' statistics: true connectionFactory: driver: "org.postgresql.Driver" url: "jdbc:postgresql://localhost:5432/postgres" username: "postgres" password: "changeme" connectionProperties: name: value connectionPool: initialSize: 1 maxSize: 10 minSize: 3 backgroundValidation: 1000 idleRemoval: 1 blockingTimeout: 1000 leakDetection: 10000 11.2. Configuring caches with JNDI names When you add a managed datasource to Data Grid Server you can add the JNDI name to a JDBC-based cache store configuration. Prerequisites Configure Data Grid Server with a managed datasource. Procedure Open your cache configuration for editing. Add the data-source element or field to the JDBC-based cache store configuration. Specify the JNDI name of the managed datasource as the value of the jndi-url attribute. Configure the JDBC-based cache stores as appropriate. Save the changes to your configuration. JNDI name in cache configuration XML <distributed-cache> <persistence> <jdbc:string-keyed-jdbc-store> <!-- Specifies the JNDI name of a managed datasource on Data Grid Server. --> <jdbc:data-source jndi-url="jdbc/postgres"/> <jdbc:string-keyed-table drop-on-exit="true" create-on-start="true" prefix="TBL"> <jdbc:id-column name="ID" type="VARCHAR(255)"/> <jdbc:data-column name="DATA" type="BYTEA"/> <jdbc:timestamp-column name="TS" type="BIGINT"/> <jdbc:segment-column name="S" type="INT"/> </jdbc:string-keyed-table> </jdbc:string-keyed-jdbc-store> </persistence> </distributed-cache> JSON { "distributed-cache": { "persistence": { "string-keyed-jdbc-store": { "data-source": { "jndi-url": "jdbc/postgres" }, "string-keyed-table": { "prefix": "TBL", "drop-on-exit": true, "create-on-start": true, "id-column": { "name": "ID", "type": "VARCHAR(255)" }, "data-column": { "name": "DATA", "type": "BYTEA" }, "timestamp-column": { "name": "TS", "type": "BIGINT" }, "segment-column": { "name": "S", "type": "INT" } } } } } } YAML distributedCache: persistence: stringKeyedJdbcStore: dataSource: jndi-url: "jdbc/postgres" stringKeyedTable: prefix: "TBL" dropOnExit: true createOnStart: true idColumn: name: "ID" type: "VARCHAR(255)" dataColumn: name: "DATA" type: "BYTEA" timestampColumn: name: "TS" type: "BIGINT" segmentColumn: name: "S" type: "INT" 11.3. Connection pool tuning properties You can tune JDBC connection pools for managed datasources in your Data Grid Server configuration. Property Description initial-size Initial number of connections the pool should hold. max-size Maximum number of connections in the pool. min-size Minimum number of connections the pool should hold. blocking-timeout Maximum time in milliseconds to block while waiting for a connection before throwing an exception. This will never throw an exception if creating a new connection takes an inordinately long period of time. Default is 0 meaning that a call will wait indefinitely. background-validation Time in milliseconds between background validation runs. A duration of 0 means that this feature is disabled. validate-on-acquisition Connections idle for longer than this time, specified in milliseconds, are validated before being acquired (foreground validation). A duration of 0 means that this feature is disabled. idle-removal Time in minutes a connection has to be idle before it can be removed. leak-detection Time in milliseconds a connection has to be held before a leak warning.

Commands:
install org.postgresql:postgresql:42.4.3
bin/cli.sh
server datasource ls
server datasource test my-datasource
<server xmlns="urn:infinispan:server:14.0"> <data-sources> <!-- Defines a unique name for the datasource and JNDI name that you reference in JDBC cache store configuration. Enables statistics for the datasource, if required. --> <data-source name="ds" jndi-name="jdbc/postgres" statistics="true"> <!-- Specifies the JDBC driver that creates connections. --> <connection-factory driver="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/postgres" username="postgres" password="changeme"> <!-- Sets optional JDBC driver-specific connection properties. --> <connection-property name="name">value</connection-property> </connection-factory> <!-- Defines connection pool tuning properties. --> <connection-pool initial-size="1" max-size="10" min-size="3" background-validation="1000" idle-removal="1" blocking-timeout="1000" leak-detection="10000"/> </data-source> </data-sources> </server>
{ "server": { "data-sources": [{ "name": "ds", "jndi-name": "jdbc/postgres", "statistics": true, "connection-factory": { "driver": "org.postgresql.Driver", "url": "jdbc:postgresql://localhost:5432/postgres", "username": "postgres", "password": "changeme", "connection-properties": { "name": "value" } }, "connection-pool": { "initial-size": 1, "max-size": 10, "min-size": 3, "background-validation": 1000, "idle-removal": 1, "blocking-timeout": 1000, "leak-detection": 10000 } }] } }
server: dataSources: - name: ds jndiName: 'jdbc/postgres' statistics: true connectionFactory: driver: "org.postgresql.Driver" url: "jdbc:postgresql://localhost:5432/postgres" username: "postgres" password: "changeme" connectionProperties: name: value connectionPool: initialSize: 1 maxSize: 10 minSize: 3 backgroundValidation: 1000 idleRemoval: 1 blockingTimeout: 1000 leakDetection: 10000
<distributed-cache> <persistence> <jdbc:string-keyed-jdbc-store> <!-- Specifies the JNDI name of a managed datasource on Data Grid Server. --> <jdbc:data-source jndi-url="jdbc/postgres"/> <jdbc:string-keyed-table drop-on-exit="true" create-on-start="true" prefix="TBL"> <jdbc:id-column name="ID" type="VARCHAR(255)"/> <jdbc:data-column name="DATA" type="BYTEA"/> <jdbc:timestamp-column name="TS" type="BIGINT"/> <jdbc:segment-column name="S" type="INT"/> </jdbc:string-keyed-table> </jdbc:string-keyed-jdbc-store> </persistence> </distributed-cache>
{ "distributed-cache": { "persistence": { "string-keyed-jdbc-store": { "data-source": { "jndi-url": "jdbc/postgres" }, "string-keyed-table": { "prefix": "TBL", "drop-on-exit": true, "create-on-start": true, "id-column": { "name": "ID", "type": "VARCHAR(255)" }, "data-column": { "name": "DATA", "type": "BYTEA" }, "timestamp-column": { "name": "TS", "type": "BIGINT" }, "segment-column": { "name": "S", "type": "INT" } } } } } }
distributedCache: persistence: stringKeyedJdbcStore: dataSource: jndi-url: "jdbc/postgres" stringKeyedTable: prefix: "TBL" dropOnExit: true createOnStart: true idColumn: name: "ID" type: "VARCHAR(255)" dataColumn: name: "DATA" type: "BYTEA" timestampColumn: name: "TS" type: "BIGINT" segmentColumn: name: "S" type: "INT"