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

NuGet queries against asset attributes can be slow due to non-optimized indexes

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 3.1.0
    • Fix Version/s: 3.3.0
    • Component/s: NuGet

      Description

      Certain NuGet query/download/update requests from visual studio 2015 are reported to be slow in Nexus 3.1

      Nexus 2.14 does not exhibit the slowness.

      The following NuGet related log message from a customer indicates slow query performance:

      2017-02-07 11:03:13,165-0600 INFO [qtp1490888539-26206] *UNKNOWN com.orientechnologies.common.profiler.OProfilerStub - $ANSI{green {db=component}} [TIP] Query 'SELECT FROM asset WHERE (LOWER(attributes.nuget.id) = "runtime.native.system" ) AND (bucket = #11:82 OR bucket = #11:83 OR bucket = #11:84 OR bucket = #11:86 OR bucket = #11:87 OR bucket = #11:88 OR bucket = #11:89 OR bucket = #11:90 ) ORDER BY attributes.nuget.download_count DESC, id ASC, version ASC LIMIT 40' fetched more than 50000 records: to speed up the execution, create an index or change the query to use an existent index
      

      asset.attributes is a distinct data structure from other properties like asset.name, asset.format etc. [ code reference|https://github.com/sonatype/nexus-internal/blob/e28b4254688e26ea6daae74b67ad85c3936db85e/components/nexus-repository/src/main/java/org/sonatype/nexus/repository/storage/MetadataNodeEntityAdapter.java#L53] - therefore this excludes this issue being a duplicate of NEXUS-12310 where a missing index on component name was the problem.

      'attributes.nuget.id' is not handled with an index

      Also there are no indices around attributes.nuget.id in AssetEntityAdapter, so these kind of queries are non-performant because they do table scans.

      For NuGet, users can essentially write their own queries where they can do pretty much anything non-performant, but we should ensure the sunny day uses cases by common clients are supported in a performant manner.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              JoeryChe Joery Che
              Last Updated By:
              Peter Lynch
              Votes:
              2 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Date of First Response:

                  tigCommentSecurity.panel-title