Maximum MariaDB connections

This topic explains how to resolve the error related to maximum connections in MariaDB when using Tungsten Transact in a multi-server environment.

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection : Too many Connections open
            

1. Understand the background of the issue.

   In a multi-server environment with three servers, the number of connections established with the default server.xml configuration is calculated as follows:

   • Minimum DB connections that are always kept open in the connection pool: 45 = 3 (servers) × 15

     • 5 for Transact application

     • 5 for Reports

     • 5 for Report_Archive

   • Maximum DB connections Tungsten Transact is allowed to open: 900 = 3 (servers) × 300

     • 100 for Transact application

     • 100 for Reports

     • 100 for Report_Archive

   By default, MariaDB supports up to 150 connections at a time. This error occurs if Tungsten Transact tries to open more connections than the limit set by the database.

2. Resolve the maximum connections issue.

   There are two approaches to handle this problem:

   • Option 1: Increase the number of connections on the database server.

     Calculate your environment's connection requirements and increase the connection limit on the database server accordingly.

   • Option 2: Restrict Tungsten Transact from opening more connections than the database server capacity.

     Adjust the connection settings in the server.xml file, located at [Transact Installation Directory]\JavaAppServer\conf. In the server.xml, configure the maximumPoolSize attribute of the Resource tag to restrict Tungsten Transact from opening a higher number of connections.

3. Consider connection requirements and adjustments.

   When calculating connection requirements and adjusting connections in the server.xml and database server, consider the following:

   • The required number of connections increases as the number of batch instances being processed in parallel on a server increases. On average, Tungsten Transact uses two connections per batch being processed on a server. For example, if the maximum process capacity of a server is set to 12, a minimum of 24 connections to the Transact database is required for batch processing.

   • Reporting requires a large number of database connections to process a large amount of reporting data in a shorter period. If the number of batches processed between two reporting clean-up cycles is very large, reporting will require a large number of connections to process and clean the processed records. To reduce reporting connection requirements, set the reporting cron jobs to execute more frequently.

   • Connection consumption on the reporting server can reach maximumPoolSize when clean-up and advanced reports jobs are executed.

   • Reduce the minimumIdle attribute value for the reports_archive database to "1", as this database is rarely used.

   • On a non-reporting server, maximumPoolSize can be set to lower values, as the connection requirement will never reach the default maximum value.

   • You can configure connections in the server.xml so that the maximum connection capacity of the database server is never exceeded by ensuring the sum of maximumPoolSize does not exceed the connection limit on the database server.

     This type of defensive setting can decrease application performance.