Problem Summary

The DVCS plug-in uses the entity_property table in JIRA to cache development-related information of issues in JSON format. This is a problem for instances with a large number of issues, as it will severely impact querying performance on this table due to too many records.

Environment

• Instances with a large amount of issues (1 million+) and linked to DVCS accounts.

Steps to Reproduce

1. Create instances with a large amount of issues
2. link to DVCS accounts with valid content (Bamboo, Fe/Cru)
3. Load all issues in JIRA

Expected Behavior

Purge caches on a timely basis - e.g. automatically clear caches not updated for more than 30 days.

Actual Behavior

The table will grow in size indefinitely, affecting the querying performance and also impacting resource usage on the database server.

According to support cases, the number of rows with development caches will be the product of 'number of issues' * 'number of Dev Application Links'.

Other symptoms:

• High CPU utilizations at Database
• slow issue load for projects with DevIntegration panel enabled.
• That leads to high number of connection in the DB pool
• and later other part of JIRA code not using this table, also become slow as it needs to fight for DB pool connection.

Notes

Number of fusion.caches.issue rows in entity_property table:

• Client1 - 300k

  SELECT entity_name,count(id) from entity_property group by entity_name;
       entity_name     | count
  ---------------------+--------
   fusion.caches.issue | 332790
   IssueProperty       |   6298
   ProjectProperty     |      3
  

• Client2 - 3.7M

  SELECT count(id) from entity_property where ENTITY_NAME = 'fusion.caches.issue';
  3720708
  

Workaround

Manually purge older records from the table:

DELETE FROM entity_property WHERE ENTITY_NAME = 'fusion.caches.issue' AND UPDATED < '<date-30d>';

You can use DBMS' specific functions for date intervals or simply replace <date-30d with a date 30 days before now.

// MS SQL server
DELETE FROM entity_property WHERE ENTITY_NAME = 'fusion.caches.issue' AND UPDATED < dateadd(day,-30,getdate());