Confluence is displaying pages slowly or showing 503 errors with PostgreSQL

Summary

Problem

Confluence pages are displaying very slowly or users are seeing 503 errors in the browser.

Diagnosis

Environment

• PostgreSQL is used with c3p0 pooling. c3p0 is used in Confluence 7.13 and earlier.

Diagnostic Steps

• Check <confluence-home>/confluence.cfg.xml for the value <property name="hibernate.c3p0.max_size">. The default is usually 30.

• When the system is running slow, or showing 503 errors, run the following SQL command where USER is the username defined in <confluence-home>/confluence.cfg.xml for the database connection in <property name="hibernate.connection.username">:

  from Linux shell

  1 while true; do sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity where usename = 'USER';"; sleep 5; done

  in psql

  1  SELECT count(*) FROM pg_stat_activity where usename = 'USER';

• If the count is equal to or close to hibernate.c3p0.max_size, this may indicate there are insufficient database connections.

• Check the value of max_connections in postgresql.conf and ensure that it is greater than the hibernate.c3p0.max_size. See also the PostgeSQL documentation on tuning. The default is usually 100.

  postgresql.conf

  1  max_connections = 100

Cause

The c3p0 database connection pool is running out of connections and causing timeouts when accessing the database.

Solution

Resolution

• Increase the value of hibernate.c3p0.max_size in <confluence-home>/confluence.cfg.xml and restart Confluence.

  Monitor the connections during peak time using the above script. The value cannot go higher than max_connections in postgresql.conf.