Uploaded image for project: 'Artifactory Binary Repository'
  1. Artifactory Binary Repository
  2. RTFACT-23875

Artifact search performance issue due to case-insensitivity

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Resolution: Unresolved
    • Affects Version/s: 7.0.0, 7.10.5
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Severity:
      Critical
    • Regression:
      Yes

      Description

      Starting Artifactory 7.0 the "artifact search" via UI/API is case-insensitive by default.

      The case-insensitive artifact search translates to an SQL query such as below:

      2020-11-04T16:55:52.193Z [jfrt ] [DEBUG] [25be0654c345f34 ] [o.j.s.JdbcHelper:325          ] [http-nio-8081-exec-2] - Query returned in 1.21 millis: "SELECT nodes.node_id, nodes.node_type, nodes.repo, nodes.node_path, nodes.node_name FROM nodes WHERE nodes.node_type=1 AND  nodes.repo  != 'auto-trashcan' AND  nodes.repo  != 'jfrog-support-bundle' AND lower(nodes.node_name) LIKE 'npm-local%'  LIMIT 1000"
      

      The usage of lower() function results in a full scan of the "nodes" table which can be very large.
      We have witnessed scenarios where lots of such queries were running in parallel (and didn't complete for a long while), causing high DB CPU usage and impacting the entire system.

      Steps to reproduce
      1. Install Artifactory 7.10.x.
      2. Apply the below to $JFROG_HOME/artifactory/var/etc/artifactory/logback.xml so we can log the queries:

      <appender name="jdbc" class="ch.qos.logback.core.rolling.RollingFileAppender">
      <File>${log.dir}/artifactory-jdbc.log</File>
      <rollingPolicy class="org.jfrog.common.logging.logback.rolling.FixedWindowWithDateRollingPolicy">
      <FileNamePattern>${log.dir.archived}/artifactory-jdbc.%i.log.gz</FileNamePattern>
      <maxIndex>10</maxIndex>
      </rollingPolicy>
      <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
      <MaxFileSize>100MB</MaxFileSize>
      </triggeringPolicy>
      <encoder class="ch.qos.logback.core.encoder.LayoutWrappingEncoder">
      <layout class="org.jfrog.common.logging.logback.layout.BackTracePatternLayout">
      <pattern>%date{yyyy-MM-dd'T'HH:mm:ss.SSS, UTC}Z [jfrt ] [%-5p] [%-16X{uber-trace-id}] [%-30.30(%c{3}:%L)] [%-20.20thread] - %m%n</pattern>
      </layout>
      </encoder>
      </appender>
      
      <logger name="org.jfrog.storage.JdbcHelper" additivity="false">
      <level value="DEBUG"/>
      <appender-ref ref="jdbc"/>
      </logger>
      

      3. Make an artifact search:

      curl -uadmin:Password1 "http://mill.jfrog.info:12552/artifactory/api/search/artifact?name=lib&repos=libs-release-local"
      

      Alternatively, make an artifact search via the UI.

      4. In "artifactory-jdbc.log" you should see the query sent with lower() function:

      2020-11-05T13:19:34.170Z [jfrt ] [DEBUG] [21f3662c6fa1e0c0] [o.j.s.JdbcHelper:325          ] [http-nio-8081-exec-4] - Query returned in 1.31 millis: "SELECT nodes.node_id, nodes.node_type, nodes.repo, nodes.node_path, nodes.node_name FROM nodes WHERE nodes.node_type=1 AND  nodes.repo  = 'libs-release-local' AND lower(nodes.node_name) LIKE '%lib%'  LIMIT 9223372036854775807"
      

      Workaround
      We can disable the case-insensitive search by applying this system property:

      artifactory.ui.search.artifacts.caseInsensitive=false
      

        Attachments

          Issue Links

            Activity

                People

                Assignee:
                nadavy Nadav Yogev
                Reporter:
                arielk Ariel Kabov
                Votes:
                5 Vote for this issue
                Watchers:
                8 Start watching this issue

                  Dates

                  Created:
                  Updated:

                    Sync Status

                    Connection: RTFACT Sync
                    RTMID-23875 -
                    SYNCHRONIZED
                    • Last Sync Date: