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/lib directory in your Data Grid Server installation.

Procedure

  1. Open your Data Grid Server configuration for editing.
  2. Add a new data-source to the data-sources section.
  3. Uniquely identify the datasource with the name attribute or field.

  4. 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.

  5. Set true as the value of the statistics attribute or field to enable statistics for the datasource through the /metrics endpoint.

  6. Provide JDBC driver details that define how to connect to the datasource in the connection-factory section.

    1. Specify the name of the database driver with the driver attribute or field.
    2. Specify the JDBC connection url with the url attribute or field.
    3. Specify credentials with the username and password attributes or fields.
    4. Provide any other configuration as appropriate.
  7. Define how Data Grid Server nodes pool and reuse connections with connection pool tuning properties in the connection-pool section.
  8. Save the changes to your configuration.

Verification

Use the Data Grid Command Line Interface (CLI) to test the datasource connection, as follows:

  1. Start a CLI session. 

    bin/cli.sh

  2. List all datasources and confirm the one you created is available. 

    server datasource ls

  3. 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

  1. Open your cache configuration for editing.
  2. Add the data-source element or field to the JDBC-based cache store configuration.
  3. Specify the JNDI name of the managed datasource as the value of the jndi-url attribute.
  4. Configure the JDBC-based cache stores as appropriate.
  5. 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.

PropertyDescription

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.