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

Details

    Description

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

      Attachments

        Issue Links

          is cloned from

          Bug - A problem which impairs or prevents the functions of the product. JRASERVER-64928 Slow performance due to high number of rows in the entity_property table

          • Highest - Our top priority issues
          • Closed

          Activity

            People

              Unassigned Unassigned
              6d6f6a289aac Konde
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated: