Chapter 12. Configuring Data Grid Server Datasources
Create managed datasources to optimize connection pooling and performance for database connections.
You can specify database connection properties as part of a JDBC cache store configuration. However, you must do this for each cache definition, which duplicates configuration and wastes resources by creating multiple distinct connection pools.
By using shared, managed datasources, you centralize connection configuration and pooling for more efficient usage.
12.1. Datasource Configuration for JDBC Cache Stores
Data Grid server configuration for datasources is composed of two sections:
-
A
connection factorythat defines how to connect to the database. -
A
connection poolthat defines how to pool and reuse connections.
<data-sources>
<!-- Defines a unique name for the datasource, JNDI name, and enables statistics. -->
<data-source name="ds" jndi-name="jdbc/datasource" statistics="true">
<!-- Specifies the JDBC driver that creates connections. -->
<connection-factory driver="org.database.Driver"
username="db_user"
password="secret"
url="jdbc:db://database-host:10000/dbname"
new-connection-sql="SELECT 1"
transaction-isolation="READ_COMMITTED">
<!-- Sets optional JDBC driver-specific connection properties. -->
<connection-property name="name">value</connection-property>
</connection-factory>
<!-- Defines connection pool 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>Connection pools can be tuned using the following parameters:
-
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.
12.2. Using Datasources in JDBC Cache Stores
Use a shared, managed datasource in your JDBC cache store configuration instead of specifying individual connection properties for each cache definition.
Prerequisites
Create a managed datasource for JDBC cache stores in your Data Grid server configuration.
Procedure
- Reference the JNDI name of the datasource in the JDBC cache store configuration of your cache configuration, as in the following example:
<distributed-cache-configuration name="persistent-cache" xmlns:jdbc="urn:infinispan:config:store:jdbc:12.1">
<persistence>
<jdbc:string-keyed-jdbc-store>
<!-- Specifies the JNDI name that you provided for the datasource connection in the server configuration. -->
<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-configuration>12.3. Testing Data Sources
Verify that connections to data sources are functioning correctly with the CLI.
Procedure
Start the CLI.
$ bin/cli.sh [disconnected]>
List all data sources:
[//containers/default]> server datasource ls
Test a data source connection.
[//containers/default]> server datasource test my-datasource
