Cloud
Data Center 10.2
Versions

Tuning database connections

Connecting Jira applications to a database

On this page

In this section

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

Jira uses the database connection pool (DBCP) based on Apache Commons DBCP to manage Jira's access to its underlying database.

In earlier Jira versions, the database connection pool was handled purely through the Apache Tomcat application server running Jira.

From Jira version 4.4, Jira's dbconfig.xml file provides a set of database connection pool settings to Tomcat, which in turn are used by Tomcat to manage Jira's database connection pool.

From JIRA version 5.1, the number of database connection pool settings defined in Jira's dbconfig.xml file has substantially increased.

The information on this page can help you tweak Jira's database connection pool settings. You can do this by using the Jira configuration tool or by directly editing Jira's dbconfig.xml file, as described in the following sections.

The  Advanced tab of the Jira  Configuration Tool makes it easier to both configure and control Jira's database connection pool. The Database monitoring  page (accessible to Jira  system administrators) provides a visual tool for monitoring Jira 's database connection usage.

On this page:

Connection pool architecture

Whenever Jira needs to access (read from or write to) its database, a database connection is required.

A database connection is a large and complex object that handles all communication between Jira and its database. As such, database connections are time-consuming to establish and consume a significant amount of memory on both the client (the Jira application) and the database server.

To avoid the impact of creating a new database connection for each Jira's database access request, a pool of pre-established database connections is maintained. Each new database access request made by Jira uses a connection from this pool of pre-established connections, as required. This results in the following:

  1. When Jira starts up, a minimum number of database connections are established in the pool between Jira and its database.
  2. When Jira needs to access its database, it:
    1. requests a database connection from the pool
    2. uses this database connection to read from and/or write to its database
    3. returns the database connection to the pool when finished

If the frequency of Jira's database access requests begins to exceed the number of available database connections in the pool, extra connections are automatically created to handle the load.

Conversely, if the frequency of Jira's database access requests begins to drop below the number of available database connections in the pool, connections can be automatically closed to release resources back to the system.

Modern databases can handle hundreds of connections relatively easily and with sufficient memory. On the client side, however, these connections can consume a significant amount of memory. Hence, it is generally best to limit the number of connections to a much smaller number while having a sufficient number for the application so that it doesn't wait for a connection when it needs one.

Tuning Jira's database connections

  1. Shut down your Jira installation.
  2. Proceed with one of the following options:

Use Jira configuration tool to start Jira 's database connections

  1. Start the Jira  configuration tool:

    You may need to set the JAVA_HOME environment variable to run the Jira  configuration tool. See Installing Java  for details.
  2. Once the Jira configuration tool is running, select the Advanced tab.
    Jira configuration tool.

  3. Refer to the Connection pool settings for more information about the options on this tab. To specify a value for one of these options, ensure that its leftmost checkbox has been selected first.

    Some options on the preceding screenshot are simple checkboxes. Selecting these checkboxes sets the values of their associated options to "true". Conversely, clearing these checkboxes sets the values of their associated options to "false".

  4. Save your changes. They will be stored as elements in your dbconfig.xml file.

Editing the dbconfig.xml file to start Jira 's database connections

Edit the dbconfig.xml file at the root of your Jira home directory:

  1. Refer to the Connection pool settings  for more information about the elements you can add to your  dbconfig.xml file to fine-tune Jira 's database connection.
  2. Save your edited dbconfig.xml file.
  3. Restart your Jira installation.

DBCP settings

  • The default values of the settings will be written to the dbconfig.xml file after one of the following takes place:
    • You’ve run the Jira setup wizard.
    • You’ve used the  Advanced  tab of the Jira configuration tool to configure your database connection, even if you haven’t selected any leftmost checkbox for any option.
  • If you see the note “when not specified in dbconfig.xml” for the default value of the setting, it means one of the following: 
    • The related element wasn’t written to the dbconfig.xml file after you’ve run the  Jira setup wizard .
    • The related element was written to the dbconfig.xml file in one of the following ways:
      • Manually by the accountable user
      • According to the options on the Advanced  tab specified by selecting their leftmost checkboxes and setting values for these options.
  • If you see the note “when not specified in dbconfig.xml” for the default value of the setting, the system will consider this value even though it may be not present in your dbconfig.xml file.

The following table features all connection pool settings and their configuration.

Jira configuration tool Advanced tab option

Element in dbconfig.xml

Default value

Description

Maximum Size

pool-max-size

20

The maximum number of database connections that can be open at any time.

Tips

This value should be large enough so that Jira rarely needs to wait for a database connection to become available when Jira requires one.

See the Monitoring the connection pool section for suggestions on how to set this parameter.

Maximum Idle

pool-max-idle

Value of Maximum Size

The maximum number of database connections that are allowed to remain idle in the pool.

Tips
  • Specifying a negative number sets no limit on the number of database connections that can remain idle.
  • By default, the value of the Minimum Idle and Minimum Size settings is the same as the value of Maximum Size. As a result, Maximum Idle has no effect.
Minimum Size

pool-min-size

(min-idle)

Value of Maximum Size

The minimum number of idle database connections that can be open at any time.

Why are the default values of Minimum Size and Maximum Size the same?

The default value of Minimum Size is the same as of Maximum Size. This means that the pool will always have a fixed number of connections, and that idle connections will never be closed.

If your Jira installation is large, setting a smaller value for Minimum Size will help you conserve resources.

Minimum Idle

pool-min-idle

Value of Minimum SizeThe minimum number of database connections that are allowed to remain idle in the pool
Initial Size

pool-initial-size

0
(when not specified in dbconfig.xml)

The initial number of database connections opened in the pool.

This setting is not usually configured to other values than the default one, because database connections are created fast when Jira starts up.

Maximum Wait Time

pool-max-wait

30000

The length of time in milliseconds when Jira is allowed to wait for a database connection to become available before returning an error, while there are no free ones in the pool.

Tips
  • Specifying the value -1 makes Tomcat wait indefinitely.
  • You should set the time that is long enough to allow any contention spikes and short enough so that users will receive a meaningful error rather than just getting no response or a browser timeout.

Advanced settings

Generally, changing the following settings isn't required. Refer to the Apache DBCP documentation if necessary.

Pool Statements

pool-prepared-statements

false
(when not specified in dbconfig.xml)

Enable the pooling of prepared statements for the database connection pool.

Do not change the default value as it will cause exceptions. For more information, see JRA-44908 - Getting issue details... STATUS

Maximum Open Statements

max-open-prepared-statements

0
(when not specified in dbconfig.xml)

The maximum number of open statements that can be allocated from the statement pool at the same time.

Do not change the default value as it will cause exceptions.

Validation Query

validation-query

select 1
(for MySQL)

(otherwise, not specified in dbconfig.xml)

The SQL query that will be used to validate connections from this pool. If specified, this query MUST be an SQL SELECT statement that returns at least one row.

What validation query is recommended for what database?

MySQL – select 1

Microsoft SQL Server – select 1

Oracle – select 1 from dual

PostgreSQL – select version();

See Surviving connection closures for more information.

Validation Query Timeout

validation-query-timeout

3
(for MySQL)

(otherwise, not specified in dbconfig.xml)

Set it only for MySQL. Using the Validation Query Timeout setting on any other database will negatively impact the performance of your Jira instance.

The length of time must be quite short because the Validation Query should be designed to do a minimum amount of work.

Tips

If you specify the Validation Query, you must specify a value for the Validation Query Timeout too.

If not, the value of -1 is assumed by default. This results in the system waiting indefinitely until a validation query succeeds against a broken database connection.

Test On Borrow

pool-test-on-borrow

true (when not specified in  dbconfig.xml )

(info) This doesn't take effect unless a Validation Query has been explicitly specified. The exception is MySQL that has a default Validation Query, and it will therefore have an effect.

Tests if a database connection is valid when it's borrowed from the database connection pool by Jira.

Tips
  • If the database connection is broken, it's removed from the pool.
  • For Jira to borrow a connection for each database operation, set the value to false.
  • If you have issues with closing database connections, try setting this option to true. Note that this should only be used as the last resort and only in the case where decreasing the value of Time Between Eviction Runs hasn't reduced or prevented issues with closing database connections.
Test On Return

pool-test-on-return

false
(when not specified in dbconfig.xml)

Tests if a database connection is valid when it's returned to the database connection pool by Jira.

Tips
  • If the database connection is broken, it's removed from the pool.
  • For Jira to borrow a connection for each database operation, set the value to false.
Test While Idle

pool-test-while-idle

  • true for MySQL
  • false when not specified in dbconfig.xml

Periodically tests if a database connection is valid when it's idle.

Tips
  • Set Test While Idle only if you have set a Validation Query.
  • If the database connection is broken, it's removed from the pool.
A note on MySQL

By default, MySQL database servers close database connections if they aren't used for an extended period of time.

This causes problems with Jira installations that use MySQL databases and are largely inactive for long periods, for example overnight. Setting Test While Idle to true is a workaround for this behavior.

Time Between Eviction Runs

time-between-eviction-runs-millis

  • 300000 for MySQL
  • 5000 for HSQLDB

(otherwise, not specified in dbconfig.xml)

The number of milliseconds to sleep between runs of an idle object eviction thread. When non-positive, no idle object eviction thread will be run.

The eviction thread will remove idle database connections when the number of idle connections exceeds Minimum Idle or Maximum Size.

Tips
  • The value should be set to a positive but largish number for MySQL so the evictor runs and tests connections. A reasonable value should be 300000 (5 minutes).
  • If you continue having issues with closing database connections, try setting a lower value.
Minimum Evictable Idle Time

min-evictable-idle-time-millis

  • 60000 for MySQL
  • 4000 for HSQLDB

(otherwise, not specified in dbconfig.xml)

The minimum amount of time an object can sit idle in a database connection pool before it's eligible for eviction.
Remove Abandoned

pool-remove-abandoned

true

The flag to remove abandoned database connections if they exceed Removed Abandoned Timeout.

Do not change the default value. So, the pool will be able to recover any abandoned connections and prevent impact on the system performance.

Tips

If an internal failure occurs, Jira can borrow a connection and never return it. If this happens too often, the pool will run short of database connections, causing Jira performance to degrade or Jira to fail.

Remove Abandoned Timeout

pool-remove-abandoned-timeout

300

The length of time in seconds when a database connection can be idle before it's considered abandoned.

Monitoring the connection pool

Jira provides a view of its database connection usage via the Database Monitoring page. See Monitoring database connection usage for more information.

Last modified on Mar 14, 2023

Was this helpful?

Yes
No
Provide feedback about this article

In this section

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.