Skip to content

5. Review of SILVA Backend Database Connections

Jump to bottom
carolinemwood edited this page May 6, 2024 · 1 revision

This page aims to provide you with more information regarding SILVA backend database connection configurations and settings.

Databases

First, let’s discuss that SILVA utilizes two different databases:

  1. Oracle: This database connects to the THE schema, using DBQ01 for the TEST environment and DPB01 for the PROD environment.
  2. Postgres: The Postgres database resides in the SILVA schema. It runs on OpenShift and establishes local connections. Since the SILVA application operates within the same container group on OpenShift, this local connection suffices.

Both connections are stablished through JDBC, where Oracle goes over an encrypted listener signed with certificate, and Postgres goes over local connection, since the SILVA application runs on the same container group, on OpenShift.

Connection Pool settings

Each of the two databases has its own connection pool settings, managed separately by the application and the database itself. Here are the current values in use for both connection pools:

  • connectionTimeout: 90 seconds (90000 milliseconds)
  • idleTimeout: 60 seconds (60000 milliseconds)
  • maxLifetime: 120 seconds (120000 milliseconds)
  • keepaliveTime: 30 seconds (30000 milliseconds)
  • minimumIdle: 1
  • maximumPoolSize: 1

✅ These values were determined after extensive collaboration with the DBAs, particularly with John. Kudos to John for his hard work and patience in querying data and monitoring connection sessions on Oracle!

If you’d like to explore additional options, please visit the Hikari page: https://github.com/brettwooldridge/HikariCP

Recommendations

We strongly recommend referring to the bcgov/nr-oracle-service repository if you’ll be working with Oracle, JDBC, and encrypted listeners on your end. This repository provides a sample service written in Java with Quarkus and GraalVM.

Additionally, if you already have a service in place using another framework (such as Spring Boot), you can leverage the same configuration properties for the connection pool. Both services utilize Hikari for connection management.

Lessons learned

The SILVA Oracle user operates withing a limit of 20 open sessions at a time. But we didn't realize the amount of containers running on DEV, TEST and PROD plus the pool settings.

Before the change, we use to work with a maximumPoolSize of 3 per container/instance. And Both TEST and PROD required a minimum of 3 container/instances. That means 9 sessions were being taken by TEST and more 9 by PROD. Having only 2 available for DEV. And making impossible to deploy new instances without having to kill existing ones.

After changing maximumPoolSize to 1, we got our problems solved.

That's is. We hope you can find some light in here.

Clone this wiki locally