Artifactory Advisory Locks Configuration for PostgreSQL
JFrog Artifactory 7.77.3 and later supports PostgreSQL advisory locks as an alternative to distributed table locks, reducing contention in high-throughput environments.
Starting with Artifactory 7.77.3, self-hosted users with PostgreSQL can use a database locking mechanism based on advisory locks instead of the previous mechanism based on distributed table locks.
What Are Advisory Locks
Advisory locks (also referred to as native locks) are a PostgreSQL feature that enables applications such as Artifactory to create locks enforced by the application, not by PostgreSQL.
Advisory locks help reduce database table bloating, which increases storage consumption and can, in some cases, overload the database server CPU and increase the duration of lock/unlock operations.
Prerequisites
To implement advisory locks for PostgreSQL:
-
All Artifactory servers must be upgraded to version 7.77.3 or later.
-
Advisory locks require a continuous connection between Artifactory and PostgreSQL.
Note
The native-locks recovery daemon logs the following INFO-level message when it detects a PostgreSQL connection drop and re-acquires previously held locks on the new connection:
Detected native locks DB connection dropped! Attempting to re-acquire all previous locks with new connection...A single occurrence, or infrequent occurrences, of this message is expected after a transient disconnect and does not require consulting JFrog before activation.
If this message floods the logs (many occurrences in a short window, or continuous recovery), consult JFrog before you activate this feature. If the feature is already active, investigate database connectivity or connection pooling.
- Advisory locks cannot be enabled when Artifactory is run with PgBouncer in transaction mode.
Activation
Use the following cURL command to activate advisory locks:
curl -u admin:{password} -H "Content-Type: application/yaml" -d 'forceNativeDbLocksMechanismInAutoMode: true' -XPATCH http://{IP_address}/artifactory/api/system/configurationNo downtime is required to transition to advisory locks. We recommend performing the transition during off-peak times, and performing a rolling restart after activation to prevent excess overhead.
When advisory locks are activated, the following message appears in artifactory-service.log:
"Criteria for native DB locks met"To view the current status of the advisory locks, enter the following:
select * FROM pg_locks;Note
The new advisory locks mechanism does not include Artifactory’s background jobs synchronization. These operations will still use the old distributed_locks table even after the advisory locks mechanism is activated. All other relevant system operations (upload/download, etc.) will be synchronized using the advisory locks mechanism.
select * FROM pg_locks;Improving Performance in Self-Hosted Environments
Users in large, self-hosted environments containing millions of artifacts can improve Artifactory performance by setting the system property artifactory.db.lock.native.pool.size to a value higher than 5. Starting with Artifactory 7.122, the default value of this property is 5. In earlier versions, the default value is 3. Increasing the pool size above the default value helps prevent system slowdown during periods of intense activity.
Related Topics
Updated 16 days ago