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);

            [JRASERVER-64928] Slow performance due to high number of rows in the entity_property table

            Windcave added a comment -

            We've just upgraded to v9.5.0 and the "bug" is most certainly still there. The workaround described above (replacing the index on entity_property) still works.
            Can Atlassian please fix this properly?

            Windcave added a comment - We've just upgraded to v9.5.0 and the "bug" is most certainly still there. The workaround described above (replacing the index on entity_property) still works. Can Atlassian please fix this properly?

            Ignat (Inactive) added a comment -

            itinfrastructure3 hello!

            It is unfortunate that you were affected by the issue. Thank you for sharing your experience.
            To better understand the issue you were facing - may you please tell which workaround you had to apply to address the problem?

            Thank you!
            Ignat Alexeyenko
            Atlassian.

            Ignat (Inactive) added a comment - itinfrastructure3  hello! It is unfortunate that you were affected by the issue. Thank you for sharing your experience. To better understand the issue you were facing - may you please tell which workaround you had to apply to address the problem? Thank you! Ignat Alexeyenko Atlassian.

            Windcave added a comment -

            We also experienced the same issue. Running Jira DC 8.7.1 proving that this issue is still not fixed in 8.0.0.

            Atlassian support were taking hours to respond and even then no solution or workarounds were suggested - we did our own research and stumbled upon this ticket.

            This performance degrade bug also caused issues to mail fetching where all our service desk projects weren't receiving emails. After applying the workaround suggested in this bug ticket, we immediately noticed Jira running faster and emails coming through.

             

            Windcave added a comment - We also experienced the same issue. Running Jira DC 8.7.1 proving that this issue is still not fixed in 8.0.0. Atlassian support were taking hours to respond and even then no solution or workarounds were suggested - we did our own research and stumbled upon this ticket. This performance degrade bug also caused issues to mail fetching where all our service desk projects weren't receiving emails. After applying the workaround suggested in this bug ticket, we immediately noticed Jira running faster and emails coming through.  

            Dom Baldin [Adobe] added a comment -

            We just got hit by this after upgrading this weekend. Had we not found this ticket and workaround we would have had an outage. Please add this to all Upgrade docs. Users need to be aware of this and add it to their procedures. 

            Dom Baldin [Adobe] added a comment - We just got hit by this after upgrading this weekend. Had we not found this ticket and workaround we would have had an outage. Please add this to all Upgrade docs. Users need to be aware of this and add it to their procedures. 

            Knut Arne Ristebråten added a comment -

            In our case, this workaround worked great. Running on Jira DataCenter 7.12.3.

            Went from a 38 second operation time for adding a comment, down to 2-3 seconds immediately after workaround was implemented.

            Knut Arne Ristebråten added a comment - In our case, this workaround worked great. Running on Jira DataCenter 7.12.3. Went from a 38 second operation time for adding a comment, down to 2-3 seconds immediately after workaround was implemented.

            ΞΔ (Inactive) added a comment -

            gonchik
            Unfortunately we are not going to backport it.
            However using workaround you can achieve same results.
            Just name indexes "entityproperty_id_name_key" and "entityproperty_key_name" so the indexes won't get duplicated on upgrade

            ΞΔ (Inactive) added a comment - gonchik Unfortunately we are not going to backport it. However using workaround you can achieve same results. Just name indexes "entityproperty_id_name_key" and "entityproperty_key_name" so the indexes won't get duplicated on upgrade

            Gonchik Tsymzhitov added a comment - - edited

            Thanks for deliver that.
            Any plans to backport on enterprise release ?

            Gonchik Tsymzhitov added a comment - - edited Thanks for deliver that. Any plans to backport on enterprise release ?

            Andreas Johansson added a comment - - edited

            We had the same issue as Tommy Bollhofer under MSSQL 12.0.5556.0 and Jira 7.12.3 where that query was utilizing 100% of the CPU until a recompilation was forced by UPDATE STATISTICS entity_property.

            For us the solution was to apply a Plan Guide with the option OPTIMIZE FOR UNKNOWN to make sure that a bad plan doesn't get cached.

            EXEC sp_create_plan_guide 
@name = N'[Entity_Property (with 24 spaces after stmt)]', 
@stmt = N'SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM jirascheme.entity_property WHERE ENTITY_NAME=@P0 AND ENTITY_ID=@P1 AND PROPERTY_KEY=@P2                        ', 
@type = N'SQL', 
@params = N'@P0 nvarchar(4000),@P1 bigint,@P2 nvarchar(4000)', 
@hints = N'OPTION (OPTIMIZE FOR UNKNOWN)'

            Note the 24 trailing spaces in @stmt. They are required as the plan needs an exact match and that's how the query with the bad plan looked in our instance.

            Andreas Johansson added a comment - - edited We had the same issue as Tommy Bollhofer under MSSQL 12.0.5556.0 and Jira 7.12.3 where that query was utilizing 100% of the CPU until a recompilation was forced by UPDATE STATISTICS entity_property . For us the solution was to apply a Plan Guide with the option OPTIMIZE FOR UNKNOWN to make sure that a bad plan doesn't get cached. EXEC sp_create_plan_guide @ name = N '[Entity_Property ( with 24 spaces after stmt)]' , @stmt = N ' SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM jirascheme.entity_property WHERE ENTITY_NAME=@P0 AND ENTITY_ID=@P1 AND PROPERTY_KEY=@P2 ' , @ type = N ' SQL ' , @params = N '@P0 nvarchar(4000),@P1 bigint ,@P2 nvarchar(4000)' , @hints = N ' OPTION ( OPTIMIZE FOR UNKNOWN )' Note the 24 trailing spaces in @stmt . They are required as the plan needs an exact match and that's how the query with the bad plan looked in our instance.

            Rajeev Kapur added a comment -

            We have the same issue and have re-opned our support case, before the changes made 7.9 we had stability using the workaround.

            Rajeev Kapur added a comment - We have the same issue and have re-opned our support case, before the changes made 7.9 we had stability using the workaround.

            Gonchik Tsymzhitov added a comment -

            Hm, it is also on on 7.6.10

            Gonchik Tsymzhitov added a comment - Hm, it is also on on 7.6.10

              Unassigned Unassigned
              jpalharini Joao Palharini (Inactive)
              Affected customers:
              36 This affects my team
              Watchers:
              81 Start watching this issue

                Created:
                Updated:
                Resolved: