Uploaded image for project: 'Jira Software Data Center'
  1. Jira Software Data Center
  2. JSWSERVER-20465

Bulk deletion of projects even with zero issues is very slow and decreases performance

XMLWordPrintable

      Issue Summary

      At times bulk deletion of projects are carried out to clean up the instance and reduce the load on the index. During deletion of the projects, the instance becomes unusable and causes significant performance degrade. Customer analysis shows that this originates from the large number of queries being run in the database individually rather than in batches and very frequently. The time taken for individual query, even though smaller adds up significantly in total, when the database is located remotely.

      Our Analysis shows that this is because to in the delete command, the code around workflows & workflow schemes doesn’t batch the database calls so well. Moreover, we’re often accessing these workflows through caches—yet we clear the caches partway through the process (before dealing with draft workflows) owing to the fix for JRASERVER-8032

      Steps to Reproduce

      1. Create 200 - 300 test projects with one or more test issues using data generator plugin.
      2. Try deletion of the projects in bulk using a plugin such as REST API or BobSwift CLI, the instance experiences large number of calls to the database contending with other normal user operations.

      Expected Results

      The project deletion should not cause significant performance degrade.

      Actual Results

      Project deletion causes the instance to become unusable and causes performance degradation.

      Workaround

      Currently there is no known workaround for this behavior except to perform the deletion during any available maintenance period if possible.

        1. Database_query.png
          Database_query.png
          498 kB

            [JSWSERVER-20465] Bulk deletion of projects even with zero issues is very slow and decreases performance

            Sven.H added a comment - - edited

            We found out that it was the Xray plugin.

            We have already opened a ticket for this at the manufacturer.

             

            Following are my observations:

            • When the deletion of a project is performed, certain events are triggered and published.
            • The event listener is designed to listen for specific events and respond accordingly.
            • In your given scenario, it appears that an Xpandit plugin event listener has been configured and is causing a delay in completing a database operation. This thread remained in the same state through out the captured thread dumps.
            "JiraTaskExecutionThread-64" #52918 daemon prio=5 os_prio=0 cpu=1072.50ms elapsed=43.57s tid=0x00007fec80605800 nid=0x1e72a2 runnable [0x00007fec639fd000]java.lang.Thread.State: RUNNABLEat java.net.SocketInputStream.socketRead0(java.base@11.0.13/Native Method)at java.net.SocketInputStream.socketRead(java.base@11.0.13/Unknown Source)at java.net.SocketInputStream.read(java.base@11.0.13/Unknown Source)at java.net.SocketInputStream.read(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketInputRecord.read(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketInputRecord.readHeader(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketImpl.readApplicationRecord(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketImpl$AppInputStream.read(java.base@11.0.13/Unknown Source)at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128)at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113)at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)at org.postgresql.core.PGStream.receiveChar(PGStream.java:443)at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2069)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)- locked <0x0000000732a34e80> (a org.postgresql.core.v3.QueryExecutorImpl)at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:47)at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeUpdate$7(DiagnosticPreparedStatement.java:69)at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement$$Lambda$3052/0x00000008040ee440.execute(Unknown Source)at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:91)at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:62)at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeUpdate(DiagnosticPreparedStatement.java:69)at net.java.ao.ForwardingPreparedStatement.executeUpdate(ForwardingPreparedStatement.java:45)at net.java.ao.ParameterMetadataCachingPreparedStatement.executeUpdate(ParameterMetadataCachingPreparedStatement.java:10)at com.xpandit.raven.dao.impl.DAOContextImpl.a(Unknown Source)at com.xpandit.raven.repository.testdefinition.impl.GenericDefinitionRepositoryImpl.b(Unknown Source)at com.xpandit.raven.service.impl.IntegrityServiceImpl.C(Unknown Source)at com.xpandit.raven.service.impl.IntegrityServiceImpl.a(Unknown Source)at com.xpandit.raven.RavenIssueEventListener.a(Unknown Source)at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(java.base@11.0.13/Native Method)at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(java.base@11.0.13/Unknown Source)at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(java.base@11.0.13/Unknown Source)at java.lang.reflect.Method.invoke(java.base@11.0.13/Unknown Source)at com.atlassian.event.internal.SingleParameterMethodListenerInvoker.invoke(SingleParameterMethodListenerInvoker.java:42)at com.atlassian.diagnostics.internal.platform.monitor.event.EventSystemMonitor.invokeMonitored(EventSystemMonitor.java:105)at com.atlassian.diagnostics.internal.platform.monitor.event.MonitoredListenerInvoker.invoke(MonitoredListenerInvoker.java:38)at com.atlassian.event.internal.ComparableListenerInvoker.invoke(ComparableListenerInvoker.java:48)at com.atlassian.event.internal.AsynchronousAbleEventDispatcher.lambda$null$0(AsynchronousAbleEventDispatcher.java:37)at com.atlassian.event.internal.AsynchronousAbleEventDispatcher$$Lambda$727/0x0000000800a1b840.run(Unknown Source)at com.atlassian.event.internal.AsynchronousAbleEventDispatcher$$Lambda$231/0x00000008003f0c40.execute(Unknown Source)at com.atlassian.event.internal.AsynchronousAbleEventDispatcher.dispatch(AsynchronousAbleEventDispatcher.java:85)at com.atlassian.diagnostics.internal.platform.monitor.event.MonitoredEventDispatcher.dispatch(MonitoredEventDispatcher.java:36)at com.atlassian.event.internal.EventPublisherImpl.publish(EventPublisherImpl.java:114)at com.atlassian.event.internal.LockFreeEventPublisher.publish(LockFreeEventPublisher.java:40)at com.atlassian.jira.event.project.DefaultProjectEventManager.dispatchProjectDeleted(DefaultProjectEventManager.java:49)at com.atlassian.jira.bc.project.DeleteProjectCommand.call(DeleteProjectCommand.java:167)at com.atlassian.jira.bc.project.DeleteProjectCommand.call(DeleteProjectCommand.java:39)at com.atlassian.jira.task.TaskManagerImpl$TaskCallableDecorator.call(TaskManagerImpl.java:528)at com.atlassian.jira.task.TaskManagerImpl$TaskCallableDecorator.call(TaskManagerImpl.java:486)at java.util.concurrent.FutureTask.run(java.base@11.0.13/Unknown Source)at java.util.concurrent.Executors$RunnableAdapter.call(java.base@11.0.13/Unknown Source)at java.util.concurrent.FutureTask.run(java.base@11.0.13/Unknown Source)at com.atlassian.jira.task.ForkedThreadExecutor$ForkedRunnableDecorator.run(ForkedThreadExecutor.java:216)at java.lang.Thread.run(java.base@11.0.13/Unknown Source)

            Sven.H added a comment - - edited We found out that it was the Xray plugin. We have already opened a ticket for this at the manufacturer.   Following are my observations: When the deletion of a project is performed, certain events are triggered and published. The event listener is designed to listen for specific events and respond accordingly. In your given scenario, it appears that an Xpandit plugin event listener has been configured and is causing a delay in completing a database operation. This thread remained in the same state through out the captured thread dumps. "JiraTaskExecutionThread-64" #52918 daemon prio=5 os_prio=0 cpu=1072.50ms elapsed=43.57s tid=0x00007fec80605800 nid=0x1e72a2 runnable [0x00007fec639fd000]java.lang. Thread .State: RUNNABLEat java.net.SocketInputStream.socketRead0(java.base@11.0.13/Native Method)at java.net.SocketInputStream.socketRead(java.base@11.0.13/Unknown Source)at java.net.SocketInputStream.read(java.base@11.0.13/Unknown Source)at java.net.SocketInputStream.read(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketInputRecord.read(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketInputRecord.readHeader(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketImpl.readApplicationRecord(java.base@11.0.13/Unknown Source)at sun.security.ssl.SSLSocketImpl$AppInputStream.read(java.base@11.0.13/Unknown Source)at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128)at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113)at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)at org.postgresql.core.PGStream.receiveChar(PGStream.java:443)at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2069)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)- locked <0x0000000732a34e80> (a org.postgresql.core.v3.QueryExecutorImpl)at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:47)at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeUpdate$7(DiagnosticPreparedStatement.java:69)at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement$$Lambda$3052/0x00000008040ee440.execute(Unknown Source)at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:91)at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:62)at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeUpdate(DiagnosticPreparedStatement.java:69)at net.java.ao.ForwardingPreparedStatement.executeUpdate(ForwardingPreparedStatement.java:45)at net.java.ao.ParameterMetadataCachingPreparedStatement.executeUpdate(ParameterMetadataCachingPreparedStatement.java:10)at com.xpandit.raven.dao.impl.DAOContextImpl.a(Unknown Source)at com.xpandit.raven.repository.testdefinition.impl.GenericDefinitionRepositoryImpl.b(Unknown Source)at com.xpandit.raven.service.impl.IntegrityServiceImpl.C(Unknown Source)at com.xpandit.raven.service.impl.IntegrityServiceImpl.a(Unknown Source)at com.xpandit.raven.RavenIssueEventListener.a(Unknown Source)at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(java.base@11.0.13/Native Method)at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(java.base@11.0.13/Unknown Source)at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(java.base@11.0.13/Unknown Source)at java.lang.reflect.Method.invoke(java.base@11.0.13/Unknown Source)at com.atlassian.event.internal.SingleParameterMethodListenerInvoker.invoke(SingleParameterMethodListenerInvoker.java:42)at com.atlassian.diagnostics.internal.platform.monitor.event.EventSystemMonitor.invokeMonitored(EventSystemMonitor.java:105)at com.atlassian.diagnostics.internal.platform.monitor.event.MonitoredListenerInvoker.invoke(MonitoredListenerInvoker.java:38)at com.atlassian.event.internal.ComparableListenerInvoker.invoke(ComparableListenerInvoker.java:48)at com.atlassian.event.internal.AsynchronousAbleEventDispatcher.lambda$ null $0(AsynchronousAbleEventDispatcher.java:37)at com.atlassian.event.internal.AsynchronousAbleEventDispatcher$$Lambda$727/0x0000000800a1b840.run(Unknown Source)at com.atlassian.event.internal.AsynchronousAbleEventDispatcher$$Lambda$231/0x00000008003f0c40.execute(Unknown Source)at com.atlassian.event.internal.AsynchronousAbleEventDispatcher.dispatch(AsynchronousAbleEventDispatcher.java:85)at com.atlassian.diagnostics.internal.platform.monitor.event.MonitoredEventDispatcher.dispatch(MonitoredEventDispatcher.java:36)at com.atlassian.event.internal.EventPublisherImpl.publish(EventPublisherImpl.java:114)at com.atlassian.event.internal.LockFreeEventPublisher.publish(LockFreeEventPublisher.java:40)at com.atlassian.jira.event.project.DefaultProjectEventManager.dispatchProjectDeleted(DefaultProjectEventManager.java:49)at com.atlassian.jira.bc.project.DeleteProjectCommand.call(DeleteProjectCommand.java:167)at com.atlassian.jira.bc.project.DeleteProjectCommand.call(DeleteProjectCommand.java:39)at com.atlassian.jira.task.TaskManagerImpl$TaskCallableDecorator.call(TaskManagerImpl.java:528)at com.atlassian.jira.task.TaskManagerImpl$TaskCallableDecorator.call(TaskManagerImpl.java:486)at java.util.concurrent.FutureTask.run(java.base@11.0.13/Unknown Source)at java.util.concurrent.Executors$RunnableAdapter.call(java.base@11.0.13/Unknown Source)at java.util.concurrent.FutureTask.run(java.base@11.0.13/Unknown Source)at com.atlassian.jira.task.ForkedThreadExecutor$ForkedRunnableDecorator.run(ForkedThreadExecutor.java:216)at java.lang. Thread .run(java.base@11.0.13/Unknown Source)

            April added a comment - - edited

            Hi Atlassian,

            I don't agree that this only affects deletion through the REST API.

            I just deleted a project via the GUI with 623 issues and 0 versions from an instance running 8.20.8 with a Postgres DB.

            Task completed in 9 minutes, 12 seconds.

            I can see that for the past 6 hours, writes per second was around 5, until I spiked that number to 70.

            So all activity on a system with 15k users is somehow only 7% of the DB write activity when deleting one small project?

            Hmmnnn.......

            April added a comment - - edited Hi Atlassian, I don't agree that this only affects deletion through the REST API. I just deleted a project via the GUI with 623 issues and 0 versions from an instance running 8.20.8 with a Postgres DB. Task completed in 9 minutes , 12 seconds. I can see that for the past 6 hours, writes per second was around 5, until I spiked that number to 70. So all activity on a system with 15k users is somehow only 7% of the DB write activity when deleting one small project? Hmmnnn.......

            brookula added a comment -

            My team is seeing this in Jira Data Center 8.13.0. Bulk project deletion through the REST API or using profields puts the database CPU at 100%.

            brookula added a comment - My team is seeing this in Jira Data Center 8.13.0. Bulk project deletion through the REST API or using profields puts the database CPU at 100%.

            Gonchik Tsymzhitov added a comment -

            How often did you remove projects?
            I agree with slowness, as I met as well, just curious.

            Gonchik Tsymzhitov added a comment - How often did you remove projects? I agree with slowness, as I met as well, just curious.

              Unassigned Unassigned
              svenkatachari shrivatsaa
              Affected customers:
              28 This affects my team
              Watchers:
              36 Start watching this issue

                Created:
                Updated: