Tuning database connections
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:
- When Jira starts up, a minimum number of database connections are established in the pool between Jira and its database.
- When Jira needs to access its database, it:
- requests a database connection from the pool
- uses this database connection to read from and/or write to its database
- 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
- Shut down your Jira installation.
- Proceed with one of the following options:
- Use the Jira configuration tool to tune Jira 's database connections.
- Edit the
dbconfig.xmlfile at the root of your Jira home directory .
Use Jira configuration tool to start Jira 's database connections
- Start the Jira configuration tool:
- Windows: Open a command prompt and run
config.batin thebinsub-directory of the Jira installation directory. Linux/Unix: Open a console and execute
config.shin thebinsub-directory of the Jira installation directory.This command might fail with the error as described in Unable to Start Jira applications Config Tool due to No X11 DISPLAY variable was set error. If it happens, refer to this article for the workaround.
You may need to set theJAVA_HOMEenvironment variable to run the Jira configuration tool. See Installing Java for details. - Windows: Open a command prompt and run
- Once the Jira configuration tool is running, select the Advanced tab.
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".
- Save your changes. They will be stored as elements in your
dbconfig.xmlfile.
Editing the dbconfig.xml file to start Jira 's database connections
Edit the dbconfig.xml file at the root of your Jira home directory:
- Refer to the Connection pool settings for more information about the elements you can add to your
dbconfig.xmlfile to fine-tune Jira 's database connection. - Save your edited
dbconfig.xmlfile. - Restart your Jira installation.
DBCP settings
- The default values of the settings will be written to the
dbconfig.xmlfile 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.xmlfile after you’ve run the Jira setup wizard . - The related element was written to the
dbconfig.xmlfile 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 | Default value | Description |
| Maximum Size |
| 20 | The maximum number of database connections that can be open at any time. |
| Maximum Idle |
| Value of Maximum Size | The maximum number of database connections that are allowed to remain idle in the pool. |
| Minimum Size |
( | Value of Maximum Size | The minimum number of idle database connections that can be open at any time. |
| Minimum Idle |
| Value of Minimum Size | The minimum number of database connections that are allowed to remain idle in the pool |
| Initial Size |
| 0 | 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 |
| 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. |
Advanced settingsGenerally, changing the following settings isn't required. Refer to the Apache DBCP documentation if necessary. | |||
| Pool Statements |
|
| 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 |
|
| 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 |
|
(otherwise, not specified in | 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. |
| Validation Query Timeout |
|
(otherwise, not specified in | 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. |
| Test On Borrow |
|
| Tests if a database connection is valid when it's borrowed from the database connection pool by Jira. |
| Test On Return |
|
| Tests if a database connection is valid when it's returned to the database connection pool by Jira. |
| Test While Idle |
|
| Periodically tests if a database connection is valid when it's idle. |
| Time Between Eviction Runs |
|
(otherwise, not specified in | 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. |
| Minimum Evictable Idle Time |
|
(otherwise, not specified in | The minimum amount of time an object can sit idle in a database connection pool before it's eligible for eviction. |
| Remove Abandoned |
|
| 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. |
| Remove Abandoned Timeout |
|
| The length of time in seconds when a database connection can be idle before it's considered abandoned. |
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.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.