DVCS connector causes long running database queries on AO_E8B6CC_REPO_TO_CHANGESET and makes the instance non-responsive

Summary

DVCS connector (for GitHub) causes a large number of stuck threads, with the threads mostly waiting on the database for quite a long time. This can be confirmed from the long running queries in the database, resulting in a performance impact on the Jira system. The large number of connections build up results in dvcs sync not progressing and also exhausts the dbcp pool in the node, resulting in the instance performing poorly.

Environment

Jira 8.20.x

Jira 9.4.x

MySQL

Diagnosis

Generating a thread dump at the time of the occurrence of the problem shows that most of the threads waiting on the database to complete running a specific query. A large number of threads can be seen with a stack trace similar to below with the queries pointing to the changeset mapping table AO_E8B6CC_REPO_TO_CHANGESET.

Cause

From the database it can be noticed that there are long running queries looking like the one below. This query is executed by the DVCS plugin when it receives a change and is trying to determine if it needs to adapt an already existing changeset in the 'AO_E8B6CC_CHANGESET_MAPPING' table or if it should create a new one. They get accumulated over time and consume a DBCP pool connection and gets stuck on the database.

We understand from JSWSERVER-20724 - As an JIRA Administrator I want to be able to delete old DVCS data that AO_E8B6CC_CHANGESET_MAPPING can grow quite large for some enterprise instance who migrated the data from the fusion caches from older instances. Such instances will have an impact for queries like above, since there are no composite indexes for RAW_NODE and NODE column together.

Solution

1. Run the explain plan on the above select query to understand what indexes are being used and how many rows are being scanned.

2. You would see a result similar to below. As evidenced below the query was not utilizing any of the indexes from the possible keys and also has to filter 50% of the rows on the entire table. The following is applicable to MySQL.

   1 2 3 | id | select_type | table                       | partitions | type | possible_keys                                              | key  | key_len | ref  | rows    | filtered | Extra       | +----+-------------+-----------------------------+------------+------+------------------------------------------------------------+------+---------+------+---------+----------+-------------+ |  1 | SIMPLE      | AO_E8B6CC_CHANGESET_MAPPING | NULL       | ALL  | index_ao_e8b6cc_cha509722037,index_ao_e8b6cc_cha1483243924 | NULL | NULL    | NULL | 2462186 |    50.00 | Using where |

3. Add the following composite index for the database to re-index with the new additional indexes.

   Composite Index

   1 CREATE INDEX index_ao_JiraSupport_Rawnode_Node ON AO_E8B6CC_CHANGESET_MAPPING(RAW_NODE,NODE);

4. After the index is added and incorporated in the database, we would notice that the explain plan gives a different result. As evidenced below, the query started using the newly added index 'index_ao_JiraSupport_Rawnode_Node' for the key and the number of rows it examined was brought down from 2462186 to 3.

   1 2 3 | id | select_type | table                       | partitions | type | possible_keys                                             | key   | key_len | ref  | rows | filtered | Extra       | +----+-------------+-----------------------------+------------+------+----------------------------------------------------------------------------------------------+-----------------------------------+---------+------+------+----------+-------------+ |  1 | SIMPLE      | AO_E8B6CC_CHANGESET_MAPPING | NULL       | range| index_ao_e8b6cc_cha509722037,index_ao_e8b6cc_cha1483243924,index_ao_JiraSupport_Rawnode_Node | index_ao_JiraSupport_Rawnode_Node | 2046    | NULL |    3 |   100.00 | Using index condition |

ℹ️ Other databases like Postgres may not offer the same benefit from the same indexes on a similar performance problem. In such cases alternative scenarios in consultation with the data base administrators should be explored.