[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:
Dependency
is a precondition for RTFACT-17108 prop_value field in PostgreSQL schema... Resolved
Trigger
triggered RTFACT-17784 New node_props index fails on creatio... Resolved
Assigned QA: Alex Dvorkin

 Description   

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. 



 Comments   
Comment by Alex Dvorkin [ 10/Oct/18 ]

Testing methodology

Setups

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

Push

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

Pull

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 Sun Oct 20 11:09:27 UTC 2019 using JIRA 7.6.16#76018-sha1:9ed376192612a49536ac834c64177a0fed6290f5.