Confluence slows and times out during periods of high load due to database connection pool

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Problem

Confluence slows during a period of high load.

Note that this issue may be a symptom rather than a cause. Other threads may be consuming the resources.

Diagnosis

During the slow down take 10 Thread Dumps spaced out by 20 - 30 seconds and analyze the thread dumps.

You will find that a number of HTTP threads waiting to acquire a connection to the DB such as the one below.

"http-8080-Processor150" daemon prio=1 tid=0x08543368 nid=0x11aa in Object.wait() [0x665a4000..0x665a51b0]
at java.lang.Object.wait(Native Method)
- waiting on <0x83140488> (a com.mchange.v2.resourcepool.BasicResourcePool)
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAcquire(BasicResourcePool.java:968)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:208)
- locked <0x83140488> (a com.mchange.v2.resourcepool.BasicResourcePool)

or

"TP-Processor3" daemon prio=1 tid=0x1fc5bd30 nid=0x279c in Object.wait() [0x1e47d000..0x1e47dda0]
	at java.lang.Object.wait(Native Method)
	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1315)
	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
	- locked <0x32cf7538> (a com.mchange.v2.resourcepool.BasicResourcePool)
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)

You will also see that a number of HTTP threads are actually running as normal.

"http-8080-Processor47" daemon prio=1 tid=0x098d5d40 nid=0xe2a runnable [0x757fc000..0x757ff1b0]

Count the number of HTTP threads that are running as normal.

Cause

The number of connections configured for the database connection pool is too small to handle the number of incoming requests during peak load time.

For example, after doing the Diagnostics/Test, a large majority of HTTP connections are waiting to acquire a connection to the database. As in the above example, HTTP-thread 150 is waiting for a connection to the database. This indicates that Confluence has almost 150 concurrent things it needs to do.

Almost every HTTP thread requires a connection to the database to perform some sort of work. However, if the number of connections to the DB is too small, this resource will become the bottleneck.

In the above example, we can count that there are usually 30 threads running and actually doing work. Thirty is the default number of database connections in the database pool.

Also, if a rogue or greedy thread consumes many resources, it will force the installation into garbage collection mode. When the JRE does this, it takes all application threads off the CPU. The end result being that requests continue to accrue from users however Confluence is not in a position to complete these requests, and eventually burns out all of its available database connections from the pool.

Resolution

  1. Optimize the database connection pool. This defines the maximum number of connections in the database pool. This number should be at least 10 counts higher, or 25% higher (whichever value is greater) than the number of HTTP threads that will be used in peak time (see below for setting HTTP threads.) This is to account for connections used for background jobs on top of active HTTP threads. (Take a Thread Dump Externally during peak load times and count the number of HTTP-threads in use).
    1. If you set up a direct JDBC connection to the database:
      Check your confluence.cfg.xml file, found in your <confluence-home> directory. You will find the following line, which you need to update to your own need:

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

      If you're running Confluence 7.14 or later, update both of these properties to the same value

      <property name="hibernate.c3p0.max_size">60</property>
      <property name="hibernate.hikari.maximumPoolSize">60</property>
    2. If you are using a data source in conf/server.xml:
      If using Confluence 5.8.x or higher (which bundles Tomcat 8): configure the maxTotal parameter. Also note that t he  maxWait  configuration option has been renamed to  maxWaitMillis in Tomcat8.  See Configuring a MySQL Datasource in Apache Tomcat for an example. 

      <Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"
      ...
      maxTotal="60"
      />


      If using Confluence 5.7.x or below (which bundles Tomcat 7 or below): configure the maxActive parameter instead (this attribute is renamed to maxTotal in Tomcat 8).

      <Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"
      ...
      maxActive="60"
      />
    3. Suppose the number defined is approximately equal to the number of HTTP-threads that are running when you take a thread dump, and the rest of the threads are waiting on a connection to the database. In that case, this indicates that you have an insufficient number of connections to your database.
  2. Limit the number of HTTP threads available to be closer to the number of database connections available. If you are going to have a bottleneck, then the HTTP thread pool is better than the DB connection pool since fewer resources are consumed while waiting for an HTTP connection.
    How to do this depends on how your application server is configured.
    1. For a standalone configuration using the default connector, change (or add if it doesn't exist) the maxThreads attribute of the Connector defined in <conf-install>\conf\server.xml. If this parameter isn't specified, it will default to 200.

          maxThreads="48"
  3. Assess Database performance.
  4. Establish a timeout. See Configuring a database query timeout for more information.

Notes

Synchrony

Synchrony process used for Collaborative Editing feature has its own Database Connection Pool and will use up to 15 DB connections. This pool size cannot be modified, but Synchrony is very efficient at recycling it's connections. Keep this pool in mind when configuring the number of connection in your database.

Database connections

It may be necessary to also configure the number of connections in your database. This is usually relevant for PostgreSQL and MySQL as SQL Server and Oracle have sufficiently high limits.

MySQL

MySQL has a default of 151 maximum connections. You can check the maximum configured with:

MySQL
mysql -u confluence -h localhost -p -e "SHOW VARIABLES LIKE '%max_connections%';"

Please review the MySQL documentation on how to increase the limit

PostgreSQL

PostgreSQL has a maximum of 100 connections. You can check the maximum configured with:

PostgreSQL
psql -U postgres -c 'show max_connections;'

Please review the PostgreSQL documentation on how to increase the limit

SQL Server

Sql Server by default allows 32767 concurrent connections but If you need to adjust it see following community discussion:

https://docs.microsoft.com/en-us/answers/questions/116462/sql-server-maximum-number-of-connections-limit-byp.html

Last modified on Jan 14, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.