Issue

The following error may occur when trying to establish a connection to MariaDB in the 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

Background

As an example, let’s take the multi-server environment that includes three servers. In this case, the number of connections that can be established with the default server.xml configuration will be as follows:

• Minimum DB connections that will always be kept open in connection pool: 45 = 3 (servers) * 15 (5 for Ephesoft application, 5 for Reports, 5 for Report_Archive)
• Maximum DB connections Ephesoft Transact is allowed to open: 900 = 3 (servers) * 300 (100 for Ephesoft application, 100 for Reports, 100 for Report_Archive)

By default, MariaDB supports up to 150 connections at a time. That means this error can occur if Ephesoft Transact tries to open more connections than the limit set by the database.

Solution

There are two approaches to handle this problem: 

1. Increase the number of connections on the database server. You will need to calculate your environment’s connection requirement and increase the connection number on the database server accordingly.
2. Restrict Ephesoft Transact from opening more connections than the database server capacity. This can be done by adjusting the connection setting in the server.xml file, located at [Ephesoft_Directory]\JavaAppServer\conf. In the server.xml, the maximumPoolSize attribute of the Resource tag can be configured as needed to restrict Ephesoft Transact from opening a higher number of connections.

Figure 1. server.xml File

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

1. The required number of connections increases as the number of batch instances being processed in parallel on a server increases. On average, Ephesoft Transact uses two connections per batch being processed on a server. So, 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.
2. Reporting requires a large number of database connections to process a large amount of reporting data in a shorter period of time. 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.
3. Connection consumption on the reporting server can shoot up to maximumPoolSize when clean-up and advanced reports jobs are executed.
4. Reduce the minimumIdle attribute value for the reports_archive database to “1”, as this database is rarely used.
5. On a non-reporting server, maximumPoolSize can be set to lower values, as the connection requirement will never reach the default maximum value.
6. You can configure connections in the server.xml so that the max connection capacity of the database server is never exceeded (by ensuring the sum of maximumPoolSize doesn’t exceed the connection limit on the database server).

Note: This type of defensive setting can decrease application performance.