Uploaded image for project: 'Dev - Nexus Repo'
  1. Dev - Nexus Repo
  2. NEXUS-31672

Potentially long running query deleting components with repository.cleanup task due to non-batched DELETE statement

    Details

    • Story Points:
      5
    • Sprint:
      NXRM Immortals Sprint 31, NXRM Immortals Sprint 45, NXRM Optimus Sprint 46
    • Notability:
      1

      Description

      It was noticed a customer created a cleanup policy to delete components with updated date 180 days or older and then applied this to a RAW format hosted repository with a PostgreSQL backend.

      Thread dump showed the task thread still running:

      "quartz-11-thread-1" id=590 state=RUNNABLE (running in native)
          at java.net.SocketInputStream.socketRead0(Native Method)
          at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
          at java.net.SocketInputStream.read(SocketInputStream.java:171)
          at java.net.SocketInputStream.read(SocketInputStream.java:141)
          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)
          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.execute(PgPreparedStatement.java:153)
          at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
          at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
          at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
          at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
          at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
          at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
          at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
          at org.sonatype.nexus.datastore.mybatis.EntityExecutor.update(EntityExecutor.java:81)
          at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194)
          at org.sonatype.nexus.datastore.mybatis.DataAccessSqlSession.update(DataAccessSqlSession.java:83)
          at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:209)
          at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:72)
          at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
          at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
          at com.sun.proxy.$Proxy179.purgeSelectedComponents(Unknown Source)
          at org.sonatype.nexus.repository.content.store.ComponentStore.purge(ComponentStore.java:336)
          at org.sonatype.nexus.repository.content.store.ComponentStore$$EnhancerByGuice$$830437529.GUICE$TRAMPOLINE(<generated>)
          at org.sonatype.nexus.repository.content.store.ComponentStore$$EnhancerByGuice$$830437529$$Lambda$673/458163476.apply(Unknown Source)
          at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:74)
          at org.sonatype.nexus.transaction.TransactionalWrapper.proceedWithTransaction(TransactionalWrapper.java:58)
          at org.sonatype.nexus.transaction.TransactionInterceptor.proceedWithTransaction(TransactionInterceptor.java:66)
          at org.sonatype.nexus.transaction.TransactionInterceptor.invoke(TransactionInterceptor.java:55)
          at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)
          at com.google.inject.internal.InterceptorStackCallback.invoke(InterceptorStackCallback.java:55)
          at org.sonatype.nexus.repository.content.store.ComponentStore$$EnhancerByGuice$$830437529.purge(<generated>)
          at org.sonatype.nexus.repository.content.maintenance.DefaultMaintenanceFacet.deleteComponents(DefaultMaintenanceFacet.java:79)
          at org.sonatype.nexus.cleanup.internal.content.method.DeleteCleanupMethod.run(DeleteCleanupMethod.java:51)
          at org.sonatype.nexus.cleanup.internal.content.service.CleanupServiceImpl.deleteByPolicy(CleanupServiceImpl.java:120)
          at org.sonatype.nexus.cleanup.internal.content.service.CleanupServiceImpl.lambda$1(CleanupServiceImpl.java:102)
          at org.sonatype.nexus.cleanup.internal.content.service.CleanupServiceImpl$$Lambda$1542/1103876918.accept(Unknown Source)
          at java.util.ArrayList.forEach(ArrayList.java:1259)
          at org.sonatype.nexus.cleanup.internal.content.service.CleanupServiceImpl.cleanup(CleanupServiceImpl.java:101)
          at org.sonatype.nexus.cleanup.internal.content.service.CleanupServiceImpl.lambda$0(CleanupServiceImpl.java:93)
          at org.sonatype.nexus.cleanup.internal.content.service.CleanupServiceImpl$$Lambda$1537/355794324.accept(Unknown Source)
          at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:406)
          at org.sonatype.nexus.cleanup.internal.content.service.CleanupServiceImpl.cleanup(CleanupServiceImpl.java:91)
          at org.sonatype.nexus.cleanup.internal.task.CleanupTask.execute(CleanupTask.java:46)
          at org.sonatype.nexus.scheduling.TaskSupport.call(TaskSupport.java:100)
          at org.sonatype.nexus.quartz.internal.task.QuartzTaskJob.doExecute(QuartzTaskJob.java:143)
          at org.sonatype.nexus.quartz.internal.task.QuartzTaskJob.execute(QuartzTaskJob.java:106)
          at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
          at org.sonatype.nexus.quartz.internal.QuartzThreadPool.lambda$0(QuartzThreadPool.java:145)
          at org.sonatype.nexus.quartz.internal.QuartzThreadPool$$Lambda$1528/2011942940.run(Unknown Source)
          at org.sonatype.nexus.thread.internal.MDCAwareRunnable.run(MDCAwareRunnable.java:40)
          at org.apache.shiro.subject.support.SubjectRunnable.doRun(SubjectRunnable.java:120)
          at org.apache.shiro.subject.support.SubjectRunnable.run(SubjectRunnable.java:108)
          at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
          at java.util.concurrent.FutureTask.run(FutureTask.java:266)
          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
          at java.lang.Thread.run(Thread.java:748)
      

      The following query selected from the pg_stat_activity table using select * from pg_stat_activity where state <> 'idle'; was still active after 5 days:

      DELETE FROM raw_component WHERE component_id = ANY($1::int[]) 
      

      The Repository cleanup task cannot be manually cancelled. The only way to end the task is to restart Nexus Repository. There is also no indication this task and the query it is using will ever complete.

      Expected

      Execute cleanup policy queries in a way that

      • timeout after an unexpectedly long time
      • perform DB queries in an efficient manner. For example, ComponentStore#purge could delete with "LIMIT n" and loop.

      Please review if the following index could affect to the performance ("ON DELETE" part):

          ADD CONSTRAINT fk_raw_browse_node_component FOREIGN KEY (component_id) REFERENCES public.raw_component(component_id) ON DELETE SET NULL;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              vgrab Vladimir Grab
              Reporter:
              plynch Peter Lynch
              Last Updated By:
              Peter Lynch Peter Lynch
              Team:
              NXRM - Optimus
              Owner:
              Vladimir Grab Vladimir Grab
              Votes:
              1 Vote for this issue
              Watchers:
              14 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Date of First Response:

                  tigCommentSecurity.panel-title