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

Rebuilding maven-metadata.xml on slower PostgreSQL could take extremely long time

    Details

    • Story Points:
      2
    • Notability:
      2

      Description

      SYMPTOM:

      Since migrated to PostgreSQL, accessing /repository/maven-releases/com/example/my-app/maven-metadata.xml after the scheduled Cleanup task takes longer time.

      REPRODUCE STEPS:

      1. Install 3.35 and migrate to PostgreSQL
      2. Enable DEBUG logging for "org.sonatype.nexus.content.maven.store.Maven2ComponentDAO"
      3. Deploy a maven artifact with 2 versions into the maven-releases repository
      4. Delete one version from Browse page
      5. With curl command, request maven-metadata.xml (should be quick), for example:
        time curl -sf -D- -u admin:admin123 -O http://localhost:8081/repository/maven-releases/com/example/my-app/maven-metadata.xml
        
      1. Deploy a maven artifact with 101 versions
      2. Delete one version from Browse page
      3. With curl command, request maven-metadata.xml again

      EXPECTED BEHAVIOUR:

      The number of SQL SELECT statements should be proportional to the number of components or assets (blobs)

      ACTUAL BEHAVIOUR:

      One particular query looks like executed close to the number of comps multiplied by the number of comps, which is from org.sonatype.nexus.repository.content.fluent.internal.FluentComponentBuilderImpl#findComponent:

      grep -E '==>  Preparing:.+' -o extract_threadId_from_nexus.log | sort | uniq -c
      

      When 100 components (versions):

      ... (snip) ...
      10500 ==>  Preparing: SELECT * FROM maven2_component WHERE repository_id = ? AND namespace = ? AND name = ? AND version = ? ;
      

      With 200 components:

      40600 ==>  Preparing: SELECT * FROM maven2_component WHERE repository_id = ? AND namespace = ? AND name = ? AND version = ? ;
      

       

      Even though above query uses the correct index and each query should be very fast (expecting a few ms), if PostgreSQL side had some slight slowness (including the connection between Nexus and PG), above can potentially cause significant slowness.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              mkearns Michael Kearns
              Reporter:
              hosako Hajime Osako
              Last Updated By:
              Peter Lynch Peter Lynch
              Team:
              NXRM - IMMORTALS
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Date of First Response:

                  tigCommentSecurity.panel-title