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

Slow Performance due to MSSQL entity_property index after an upgrade

XMLWordPrintable

    • 8.07
    • 19
    • Severity 2 - Major
    • 4
    • Hide
      Atlassian Update – 26 October 2024

      Hi everyone,

      This issue has been reviewed by the Jira Data Center Development team. As a result of our investigation, we've identified that the performance issues described are caused by outdated index statistics on the entity_property table, which can occur after upgrading Jira. This can lead to inefficient query execution plans and overall system slowdown.

      We have prepared a Knowledge Base article that provides detailed steps on how to update the index statistics to resolve this issue. The article includes guidance on executing the necessary SQL queries. We recommend that a Database Administrator (DBA) or team member with database expertise perform these actions to ensure they are carried out safely and correctly.

      We will continue to monitor this issue for further updates, so please feel free to share any thoughts or experiences in the comments.

      Best regards,
      Natalia Wróblewska
      Senior Software Engineer

      Show
      Atlassian Update – 26 October 2024 Hi everyone, This issue has been reviewed by the Jira Data Center Development team. As a result of our investigation, we've identified that the performance issues described are caused by outdated index statistics on the entity_property table, which can occur after upgrading Jira. This can lead to inefficient query execution plans and overall system slowdown. We have prepared a Knowledge Base article  that provides detailed steps on how to update the index statistics to resolve this issue. The article includes guidance on executing the necessary SQL queries. We recommend that a Database Administrator (DBA) or team member with database expertise perform these actions to ensure they are carried out safely and correctly. We will continue to monitor this issue for further updates, so please feel free to share any thoughts or experiences in the comments. Best regards, Natalia Wróblewska Senior Software Engineer

      Problem Summary

      After an upgrade, Jira's operations on issues or anything that concerns making a query to the database performance is very slow. Upon checking long running threads, we observe that they are on the database.

      		at java.base@11.0.13/java.net.SocketInputStream.socketRead0(Native Method)
		at java.base@11.0.13/java.net.SocketInputStream.socketRead(Unknown Source)
		at java.base@11.0.13/java.net.SocketInputStream.read(Unknown Source)
		at java.base@11.0.13/java.net.SocketInputStream.read(Unknown Source)
		at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2058)
		at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6617)
		at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7803)
		at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600)
		at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
		at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
		at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)
		at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
		at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
		at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446)
		at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
		at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)

      Database latency is observed to be high, and the expensive queries are related to entity_property:

      SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM dbo.entity_property WHERE (ENTITY_NAME=@P0 AND ENTITY_ID=@P1) AND (PROPERTY_KEY IN (@P2) ) ORDER BY UPDATED, ID

      Expected Behavior

      Performance is the same or similar to before the upgrade without any interference in the database.

      Actual Behavior

      Carrying out the MSSQL workaround steps from JRASERVER-64928: Slow performance due to high number of rows in the entity_property table improves the performance.

      Steps to Reproduce

      Upgrading Jira with an MSSQL database.

      Workaround

      From JRASERVER-64928: Slow performance due to high number of rows in the entity_property table:

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

      If the index re-create doesn't work, please consider running an update statistics on the entity_property table. Replace dbo with your database schema if you are using a different schema (dbo is the default).

      UPDATE STATISTICS [dbo].[entity_property] [entityproperty_id_name_key] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[entity_property] [entityproperty_key_name] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[entity_property] [PK_entity_property] WITH FULLSCAN;

              nwroblewska Natalia Wroblewska
              6d6f6a289aac Konde
              Votes:
              3 Vote for this issue
              Watchers:
              6 Start watching this issue

                Created:
                Updated:
                Resolved: