[RTFACT-17337] Performance improvement: Create a more efficient index on prop_value in PostgreSQL and limit the field length in the code Created: 16/Aug/18  Updated: 03/Apr/19  Resolved: 10/Oct/18

Status: Resolved
Project: Artifactory Binary Repository
Component/s: Database, Performance, Properties
Affects Version/s: 5.7.0, 6.1.0
Fix Version/s: 6.5.0

Type: Improvement Priority: Critical
Reporter: Yossi Shaul Assignee: Shay Bagants
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
is a precondition for RTFACT-17108 prop_value field in PostgreSQL schema... Resolved
triggered RTFACT-17784 New node_props index fails on creatio... Resolved


In some scenarios (i.e. Artifactory instances that have not migrated to SHA2 support), Artifactory might dynamically generate SQL queries with join between the 'nodes' and 'node_props' tables. Due to inefficient index in the 'node_props' table on PostgreSQL DB, the response time of these queries might be slow. This mainly affects Docker repositories. 

Comment by Alex Dvorkin [ 10/Oct/18 ]

Testing methodology


We took customer's simulated DB (3M nodes), duplicated it, upgraded it from 5.4.11
DB #1 was upgraded to 6.4.1 and connected to Artifactory #1
DB #2 was upgraded to 6.5.0 (with the fix) and connected to Artifactory #2

DB specification: db.m4.4xlarge (CPU: 16, Memory: 64GB)

Old Docker client performance

We installed Docker client 1.7.1 (protocol v2, manifest v1) and counted time it took for PUSH calls [single client]. Comparing 6.4.1 to 6.5.0, HEAD request time dropped from 3000ms 20ms and PUT request dropped to 800ms

Concurrent stress


We used 10 concurrent scripts that performed docker push

DB CPU 60% ->  0.5%
HEAD requests 2525ms -> 406ms
PUT requests 1266ms -> 208ms
Throughput: 0.5 -> 2.5 uploads/per second


We used 10 concurrent curl scripts which sent GET requests to Docker repository (using conversion from V1 to V2)

DB CPU 60% ->  0.5%
Throughput: 2.2 -> 19 GET requests/per second
Generated at Mon Jun 01 18:42:52 UTC 2020 using Jira 8.5.3#805003-sha1:b4933e02eaff29a49114274fe59e1f99d9d963d7.