Dealing with database connection pool starvation on Oracle DB

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

After going through standard troubleshooting based on this article (see: Confluence slows and times out during periods of high load due to database connection pool ), you arrived at the conclusion that you need to increase your database connection pool (so that it equals to 60-80% of your max http threads at peak). However, increasing the pool turns out to make the problem even worse, and it seems that the bottleneck occurs at the DB-side

Cause

The maximum number of sessions and processes set in your Oracle DB may not be enough to satisfy the increased load from the larger Confluence database connection pool. If Oracle hits the maximum number of processes, atlassian-confluence.log will throw an error as below:

Caused by: java.sql.SQLException: ORA-00018: maximum number of sessions exceeded

Resolution

  1. To fix the ORA-00018 and the ORA-00020, we increase the processes parameter, usually doubling the processes value to allow for future database growth. Check your current processes and sessions utilization in Oracle (while Confluence is running at peak), by running the following query as sysdba in Oracle:

    select * from v$resource_limit where resource_name in ('processes','sessions');
  2. Run these queries to set the new limit:

    alter system set processes=<num_processes> scope=spfile
    alter system set sessions=<num_sessions> scope=spfile;

    A basic formula for determining these parameter values is as follows:

    processes = x  
    sessions  = x*1.1+5

    Example:

    alter system set processes=500 scope=spfile;
    alter system set sessions=555 scope=spfile;

This requires an Oracle DB restart

Last modified on Nov 10, 2017

Was this helpful?

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