Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-64928

Slow performance due to high number of rows in the entity_property table

XMLWordPrintable

    • 6.04
    • 123
    • Severity 2 - Major
    • 1,698
    • Hide
      Atlassian Update – 01 February 2019

      Hello everyone,

      We introduced indexes to entity_property table.
      The new indexes are named entityproperty_key_name and entityproperty_id_name_key. If you modified your indexes please adjust names/remove them. Otherwise they will get duplicated.

      As a friendly reminder - remember to update your database statistics frequently. Otherwise your queries will be vulnerable to parameter sniffing.

      Sincerely,
      Jira Bugfix Team.

      Show
      Atlassian Update – 01 February 2019 Hello everyone, We introduced indexes to entity_property table. The new indexes are named entityproperty_key_name and entityproperty_id_name_key. If you modified your indexes please adjust names/remove them. Otherwise they will get duplicated. As a friendly reminder - remember to update your database statistics frequently. Otherwise your queries will be vulnerable to parameter sniffing. Sincerely, Jira Bugfix Team.

      NOTE: This suggestion is for JIRA Server. Using JIRA Cloud? See the corresponding suggestion.

      Problem Summary

      The entity_property table was designed to be generic, and we provide API and encourage vendors to use it to store any kind of data. Given there is no policy for expiring data in there, and with many datasources it can reach millions of rows easily, we need to look for solutions to optimize it.

      Expected Behavior

      Have minimal impact in performance regardless of amount of data stored in the table.

      Actual Behavior

      When this table reaches millions of rows, and many classes use it, querying it will be slow and can impact performance of the whole database.

      Steps to Reproduce

      Use DVCS integration in an instance with more than 1 million issues - see JSW-15748.

      Notes

      From existing cases we identified following expensive SQLs for entity_property:

      • SELECT ID, UPDATED FROM entity_property WHERE ENTITY_NAME=:1 AND ENTITY_ID=:2 AND PROPERTY_KEY=:3
        • Problem - no cover index
      • SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM entity_property WHERE ENTITY_NAME=:1 AND ENTITY_ID=:2 AND PROPERTY_KEY=:3
        • Problem - no cover index
      • SELECT COUNT(*) FROM entity_property WHERE ENTITY_NAME=:1 AND PROPERTY_KEY=:2
        • Problem - partial index:
        • Unnecessary COUNT 
      • SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM dbo.entity_property WHERE (ENTITY_NAME='IssueProperty' AND ENTITY_ID='680519') AND (PROPERTY_KEY IN ('development') ) ORDER BY UPDATED, ID"
        • Problem - no cover index, ORDER without index

      Workaround

      Delete stale cache data from entity_property

      Identify table entity_property data distribution and delete some data if possible:

      1. Look for count of entities on the table, grouped by entity_name: 
        SELECT entity_name,count(id) from entity_property group by entity_name;
      1. If fusion.caches.issue is one of the entity_name with higher counts, purge entries not updated for more than 30 days (see JSW-15748): 
        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.

      Replace an index on entity_property

      For MS SQL Server:

      DROP INDEX ENTITYPROPERTY_ENTITY ON dbo.ENTITY_PROPERTY; 
CREATE INDEX ENTITYPROPERTY_ENTITY ON dbo.ENTITY_PROPERTY(entity_id, entity_name) INCLUDE(property_key);

      Other databases + MS SQL Server 2016+:

      DROP INDEX ENTITYPROPERTY_ENTITY; 
CREATE INDEX ENTITYPROPERTY_ENTITY ON ENTITY_PROPERTY(entity_id, entity_name, property_key);

              Unassigned Unassigned
              jpalharini Joao Palharini (Inactive)
              Votes:
              36 Vote for this issue
              Watchers:
              81 Start watching this issue

                Created:
                Updated:
                Resolved: