Symptoms: Regular REST API requests take a long time to complete, the UI hangs, there is high database activity, and the problem goes away after a build completes.
In Artifactory, the database has a "nodes" table which tracks some of the artifact metadata information. This table contains information on each file in Artifactory, including the folder path. Some SQL queries look to be inefficient when it comes to deploying artifacts to folders which already contain a huge number of items.
This is a problem because at a very large scale, Artifactory performs poorly.
Steps to reproduce:
- Deploy at least 7,000,000 files to a local Docker repository
It doesn't have to be actual Docker images, plaintext files will work
- Deploy around 800,000 - 1,000,000 files to a single folder in this large repository
- Rapidly deploy Docker Images to the single large folder - Simulating a build
- A good number to try for would be at least 100 docker images, pushed rapidly in succession
- Observe that Artifactory stops responding during the rapid deployment
The logs indicate that this performance problem is coming from the database:
20191119002934| 30068 [30 seconds] |REQUEST|RESTRICTED_IP|admin|PUT|/api/docker/docker-local/v2/big-folder/centos/manifests/39|HTTP/1.1|201|529
2019-11-19 00:29:34,011 [http-nio-8081-exec-57] [DEBUG] (o.j.s.JdbcHelper :191) - Query returned in 28.02 secs : 'select distinct n.repo as itemRepo,n.node_id as itemId,n.node_path as itemPath,n.node_name as itemName,n.node_type as itemType from nodes n where ( n.repo = 'docker-local' and n.depth >= 4 and( n.node_path like 'big-folder/centos/88/%' or n.node_path = 'big-folder/centos/88') and n.node_type = 1) '
I believe the query took 4 seconds because the SELECT statement is done along a path containing a wildcard (centos/latest/%), and the SQL table is very large along that path.