Run one partition sync per database at a time

What does this MR do and why?

Only one partition sync changes the partitions of a database at a time.

Ten of the 11 tables in the CI partition sync have a foreign key to p_ci_builds. Every ATTACH PARTITION on them needs the same lock on p_ci_builds. The per-table lease does not stop syncs of different tables from running together. During the incident, three syncs ran at once and queued CI writes behind that lock. The rails_primary_sql Apdex fell to 98.80%.

The sync now holds the async DDL lease of the database while it creates and detaches partitions. The key is gitlab/database/asyncddl/actions/<database>. Reindexing and async indexes use the same key. A sync that finds the lease taken skips the database and logs a warning. ANALYZE runs after the lease is released.

sync_partitions_ignore_db_error also ignores Redis errors, so jobs that boot Rails without Redis do not fail. PartitionManagementWorker still raises on a Redis failure, so the job retries.

The second loop in sync_partitions (owner_db_only false) is off in production and does not take the lease.

There is no feature flag. The ops flag partition_manager_sync_partitions still disables the whole sync. To roll back, revert this MR.

References

• Incident: gitlab-com/gl-infra/production#23392
• Timeline and cause: gitlab-com/gl-infra/production#23392 (comment 4151144888)
• Lock on p_ci_builds: gitlab-com/gl-infra/production#23392 (comment 4151210407)

How to set up and validate locally

1. In a Rails console, take the lease of the main database:

   key = 'gitlab/database/asyncddl/actions/main'
   uuid = Gitlab::ExclusiveLease.new(key, timeout: 10.minutes).try_obtain

2. Run Gitlab::Database::Partitioning.sync_partitions(only_on: 'main'). log/application_json.log shows Skipping partition sync: another DDL action holds the lease of the main database. No partitions change.
3. Release the lease with Gitlab::ExclusiveLease.cancel(key, uuid) and run the sync again. It now runs.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.