The following error occurs when trying to establish 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
Reason / Explanation
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 opened 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. Thus, the above-mentioned error can occur any time if Ephesoft Transact tries to open more connections than the limit set by the database.
There are two approaches to handle this problem.
1. Increase the number of connections on the database server. The user needs to calculate their 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 connection setting in the server.xml file. In server.xml (<Ephesoft Installation Directory>\JavaAppServer\conf), the maximumPoolSize attribute of the Resource tag can be configured as needed to restrict Ephesoft Transact from opening higher number of connections.
When calculating connection requirement and adjusting connections in server.xml and on database server, please consider the following:
- The required number of connections increases as the number of batch instances being processed in parallel on a server increases. On an average, Ephesoft Transact uses 2 connections per batch instance being processed on a server. So, if maximum process capacity of a server is set to 12, a minimum of 24 connection to Transact database will be required for batch processing.
- Reporting requires a large number of database connections to process 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 requirement, set the reporting cron jobs to execute more frequently.
- Connection consumption on the reporting server can shoot up to maximumPoolSize when clean-up and advanced reports jobs are executed.
- Reduce the minimumIdle attribute value for reports archive database to 1, as this database is used very rarely.
- On a non-reporting server, maximumPoolSize can be set to lower values as connection requirement will never reach default maximum value.
- The user can configure connections in server.xml in such a manner that the max connection capacity of the database server is never exceeded (by ensuring sum of maximumPoolSize doesn’t exceeds the connection limit on the database server). But this type of defensive setting can decrease applications performance.