ARTIFACTORY: Internal Database Indexing Enhancements for Nodes Table

ARTIFACTORY: Internal Database Indexing Enhancements for Nodes Table
AuthorFullName__c
JFrog Support
articleNumber
000005867
ft:sourceType
Salesforce
FirstPublishedDate
2023-09-11T09:41:27Z
lastModifiedDate
2023-09-10
VersionNumber
5

Artifactory v7.68 release includes internal database indexing enhancements that improve the performance of virtual repositories. During the upgrade, Artifactory will create a new index for the Nodes table.

If you are upgrading to this version from a version below Artifactory 7.59.X with a database containing over 50 million artifacts, JFrog recommends creating the index manually before upgrading to 7.68. This will reduce the risk of performance degradation during the index creation process, which might take several hours, depending on the DB size and available resources. 

This index was implemented to provide a solution for a performance issue described in the Known Issues table for 7.59. In case you disabled the artifactory system property 'artifactory.file.service.cache.enabled=false', JFrog recommends enabling it following the index creation to benefit from the latest improvements in the JFrog Platform.

To create the index, run the following command, according to your database type:
 

  • PostgreSQL
CREATE INDEX concurrently nodes_name_path_idx ON nodes (node_name varchar_pattern_ops, node_path varchar_pattern_ops);

To validate the creation of the index, run the following command: 

SELECT relname index_name, indisvalid index_is_valid FROM pg_class, pg_index WHERE  pg_index.indexrelid = pg_class.oid and relname = 'nodes_name_path_idx';

The query response should be: 

index_name | index_is_valid
---------------------+----------------
nodes_name_path_idx | true

If there are no rows or the value of index_is_valid is not 'true' then the index was either not created or was created as INVALID.  
 

  • MySQL
CREATE INDEX nodes_name_path_idx ON nodes (node_name, node_path(255));
  • Oracle/MSSQL
CREATE INDEX nodes_name_path_idx ON nodes (node_name, node_path);
  • Derby - Index will be created following the upgrade of Artifactory

 


After the successful creation of the index it is recommended to remove an index that is made redundant:  

  • Postgres: 
DROP INDEX CONCURRENTLY nodes_node_name_idx;

Normally this should take just a few seconds. If it takes more than 1 minute than it is recommended to abort/kill the command and wait for a quiet period and run again when no vacuum or auto-vacuum is running.  
 

  • All other databases:
DROP INDEX nodes_node_name_idx;

After creating the index, upgrade Artifactory to the latest version: Artifactory will recognize that the index has been created and will not initiate the action again during the upgrade.