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.

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 requests that are coming 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, then this resource will become the bottleneck.

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

Also, if there is a rogue job or greedy thread which 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 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 that are 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>
      
    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"
      />


      If 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, then 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

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


Last modified on Nov 2, 2018

Was this helpful?

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