Issue Summary

Confluence uses C3P0 as its database pooling layer, which provides tons of options for administrators who wish to tune the way their applications connect with their database through a JDBC Driver.

Among these options, there's one called hibernate.c3p0.min_size, which sets the minimum size that the database pool must have to operate throughout its lifecycle. Due to the way that Confluence handles its database pools, if this property is set to a non-zeroed value, the application may end up doubling the amount of database connections that the database will need to handle, which can cause performance issues and jeopardise resource planning.

Issue Details

Confluence uses Hibernate as its ORM framework. Recently, Confluence has been bumped to Hibernate version 5, which is good, since new capabilities are added, but is also bad, given that the issue mentioned above happens.

The problem happens because during startup Hibernate is going to open parallel Database Connection Pool(s) to perform metadata queries and fulfil its own needs. This/these parallel Database Connection Pool(s) will use C3P0 as well to be configured. That being said, let's imagine the following setup:

<property name="hibernate.c3p0.max_size">60</property>
<property name="hibernate.c3p0.min_size">30</property>

This implies that Confluence will raise a Database Connection Pool that will have 30 connections opened with the database since startup and that can grow up to 60 connections depending on need. In a nutshell, this means that, at anytime, we'll have at least 30 connections established with the database.

That being said, an administrator can then plan database capacity accordingly, given that the administrator knows that the maximum amount of connections that the database will need to handle is 60 connections. Right? Wrong!

From Hibernate 5 onwards (which is our current version), Hibernate will also open database pool(s) as mentioned above. This means that during startup, we can double the amount of connections being sent to the database. As two pools are opened, we can send max_size times two to the database, meaning that 120 connections can be established. If an administrator planned to have his database being able to hold up to 60 connections maximum, a bottleneck is established as the pool can grow bigger than what the database can handle.

Aside from that, Confluence will never close the parallel pool(s) opened by Hibernate, which means that if hibernate.c3p0.min_size has been set to a non-zeroed value, once Hibernate is done using its pool, 30 connections are going to remain established with the database but are never going to be used, given that it is used only during startup.

How to Validate Impact

Here's a step by step guide to check the bug behaviour on PostgreSQL:

1. Stop Confluence.
2. Edit <Confluence-Home-Directory>/confluence.cfg.xml and set the following properties as below:

   <property name="hibernate.c3p0.max_size">60</property>
   <property name="hibernate.c3p0.min_size">60</property>
   

3. This is supposed to mean that the maximum number of connections that your pool can establish with the database is 60 connections.
4. Start Confluence.
5. Go to your database and run the following query once Confluence is up and running - Don't forget to replace your database name and database username in the query:

   select count (datid)
   from pg_stat_activity
   where datname = 'confluence_6.6.3' and usename = 'confluenceuser';
   

6. This query should not return values bigger than 60, as it brings the amount of JDBC connections established by C3P0 with the database. Once you run it, you are going to see more than 60 due to the bug mentioned herein. On a testing environment (clean instance), the count reached to 81.

Workaround

The workaround is to go ahead and set hibernate.c3p0.min_size as 0. This way, once Hibernate is done using its database pool, unused connections will be removed from the pool based on timeout until it has zeroed out. In a matter of a few minutes, pooled but unused connections should no longer exist and the database won't have connections without usage tied to it.