Chapter 10. 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.
10.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/libdirectory in your Data Grid Server installation.
Procedure
- Open your Data Grid Server configuration for editing.
-
Add a new
data-sourceto thedata-sourcessection. -
Uniquely identify the datasource with the
nameattribute or field. Specify a JNDI name for the datasource with the
jndi-nameattribute or field.TipYou use the JNDI name to specify the datasource in your JDBC cache store configuration.
-
Set
trueas the value of thestatisticsattribute or field to enable statistics for the datasource through the/metricsendpoint. Provide JDBC driver details that define how to connect to the datasource in the
connection-factorysection.-
Specify the name of the database driver with the
driverattribute or field. -
Specify the JDBC connection url with the
urlattribute or field. -
Specify credentials with the
usernameandpasswordattributes or fields. - Provide any other configuration as appropriate.
-
Specify the name of the database driver with the
-
Define how Data Grid Server nodes pool and reuse connections with connection pool tuning properties in the
connection-poolsection. - 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.
bin/cli.sh
List all datasources and confirm the one you created is available.
server datasource ls
Test a datasource connection.
server datasource test my-datasource
Managed datasource configuration
XML
<server xmlns="urn:infinispan:server:13.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
10.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-sourceelement or field to the JDBC-based cache store configuration. -
Specify the JNDI name of the managed datasource as the value of the
jndi-urlattribute. - 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"
10.3. Connection pool tuning properties
You can tune JDBC connection pools for managed datasources in your Data Grid Server configuration.
| Property | Description |
|---|---|
|
| Initial number of connections the pool should hold. |
|
| Maximum number of connections in the pool. |
|
| Minimum number of connections the pool should hold. |
|
|
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 |
|
|
Time in milliseconds between background validation runs. A duration of |
|
|
Connections idle for longer than this time, specified in milliseconds, are validated before being acquired (foreground validation). A duration of |
|
| Time in minutes a connection has to be idle before it can be removed. |
|
| Time in milliseconds a connection has to be held before a leak warning. |
