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

Separate COUNT and SUM queries where the UI want to show a count and a size



    • Type: Improvement
    • Status: Open
    • Priority: Normal
    • Resolution: Unresolved
    • Affects Version/s: 6.4.0
    • Fix Version/s: None
    • Component/s: Performance, Web UI
    • Labels:
    • Environment:

      Artifactory 6.4.0 (dockerized) with >50M artifacts


      When clicking to show the "Artifact Count / Size" item for a repo item and also for the admin Storage page the web ui asks only one query to the database which might sound like a good idea until you have an installation of Artifactory with >50M artifacts.

      The query will always wait for the SUM to complete even if the COUNT was a O(1) lookup in a cache.

      I would suggest to make the COUNT query together with the inital page request since it should be constant with a correctly indexed table.

      Let SUM queries be user initiated for both repo sub-items and the storage summary page. Or for that matter, keep a separate field in the DB for repo sizes.


      The query for the storage page and takes a long time to return for large repositories.

      2018-10-19 14:17:11.491 UTC SELECT LOG:  duration: 178828.718 ms  execute <unnamed>: SELECT repo, SUM(CASE WHEN node_type = 0 THEN 1 ELSE 0 END) as folders, SUM(CASE WHEN node_type = 1 THEN 1 ELSE 0 END) as files, SUM(bin_length) FROM nodes GROUP BY repo


      And sometimes the index isn't properly used as shown below for a count/sum query of an empty repo.

      2018-10-19 14:29:22.307 UTC SELECT LOG:  duration: 14541.695 ms  execute <unnamed>: SELECT COUNT(*) AS file_count, SUM(bin_length) AS total_size FROM nodes WHERE node_type=1 and repo = $1
      2018-10-19 14:29:22.307 UTC SELECT DETAIL:  parameters: $1 = 'dbdiffer-logs-stage'

      After this query returned, the next lookup was immediate and hit the index. But sometimes it will execute a sequential read.





            stefanga Stefan Gangefors
            1 Vote for this issue
            3 Start watching this issue