Introduction
Artifactory v7.98 release includes internal database indexing enhancements that improve the performance and efficiency of cleanup policy processes.
During the upgrade, Artifactory will create new indexes for the Nodes table.
If you upgrade to this version with a database containing over 50 million artifacts, JFrog recommends manually creating the index before upgrading to 7.98. This will reduce the risk of performance degradation during the upgrade process, which might take several hours, depending on the DB size and available resources.
Instructions
First, removing an index that will be redundant is recommended, and then creating a new one.
Remove Existing Index
PostgreSQL
DROP INDEX CONCURRENTLY nodes_repo_path_checksum;
Normally, this should take just a few seconds. If it takes more than 1 minute, it is recommended to abort/kill the command, wait for a quiet period, and run again when no vacuum or auto-vacuum is running.
MySQL/MSSQL/Oracle/Derby
DROP INDEX nodes_repo_path_checksum;
Create New Index
PostgreSQL
set maintenance_work_mem = '1GB'; set max_parallel_maintenance_workers = 8; CREATE INDEX CONCURRENTLY nodes_repo_created_idx ON nodes (repo, created);
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_repo_created_idx'; The query response should be: index_name | index_is_valid ---------------------+---------------- nodes_repo_created_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, in that case, remove the index and recreate it.
MySQL/MSSQL/Oracle/Derby
CREATE INDEX nodes_repo_created_idx ON nodes (repo, created);
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.
Note: The old version can run safely with the new and dropped index.
Important Note
There is a known issue when upgrading to Artifactory version 7.98.x (RTDEV-50553).
Artifactory attempts to recreate the nodes_repo_path_checksum index, which can cause transaction blocks on the nodes table.
Workaround
To avoid this issue, disable the RepoPathChecksumMigration job by adding this line to your artifactory.system.properties file before the upgrade/start of Artifactory:
artifactory.pathChecksum.migration.job.forceRunOnNodeId=DoNotRun
This will help prevent transaction conflicts until a permanent solution is available in the next minor version of Artifactory.