Count Geo secondary registries in a single pass

What does this MR do and why?

On a secondary, each status update counts every registry table six times, with one batch_count loop per metric. On sites with 300M+ job artifacts, this can push the collection past its 50-minute limit. No status is saved, and the site eventually shows as unhealthy.

This MR adds Gitlab::Geo::Replicator.registry_counts. It gets all six counts from one batch_count over the registry table, grouped by state and verification_state. The relation is unfiltered, so the batch counter's end bound is a plain primary-key MAX(id) instead of six filtered ones.

  • GeoNodeStatus and the geo:status Rake task use it when the geo_single_pass_registry_counts ops flag is enabled. The flag is disabled by default. With the flag off, the counts run as before.
  • Geo::ContainerRepositoryRegistry overrides the new raw_state_values, because its state column still holds both integer and string values (#371667).
  • With the flag on, the six counts succeed or fail together. If the batch counter gives up, all six report -1. If the query is canceled, all six stay unset. Today, each count can fail on its own.

Database review

With geo_single_pass_registry_counts enabled, Gitlab::Geo::Replicator.registry_counts replaces the six count loops for each replicator with one loop. It runs on the Geo tracking database, on each secondary status update and in the geo:status Rake task. The queries are the same for every registry table. The plans below use job_artifact_registry, the largest one, seeded to 311,707,772 rows.

1. End bound, once per replicator

SELECT MAX("job_artifact_registry"."id") FROM "job_artifact_registry"

Plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/86649/commands/193375 (Index Only Scan Backward on job_artifact_registry_pkey, 1 row, 3.0 ms cold, buffers: 2 hit / 3 read)

Before, each of the six counts ran its own end bound on a filtered relation. The slowest one, for failed_count, took 4.4 s at 311M rows (plan).

2. One batch, repeated for each 100,000 IDs up to the end bound

SELECT COUNT("job_artifact_registry"."id") AS "count_job_artifact_registry_id", "job_artifact_registry"."state" AS "job_artifact_registry_state", "job_artifact_registry"."verification_state" AS "job_artifact_registry_verification_state" FROM "job_artifact_registry" WHERE "job_artifact_registry"."id" >= <batch_start> AND "job_artifact_registry"."id" < <batch_end> GROUP BY "job_artifact_registry"."state", "job_artifact_registry"."verification_state"

Plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/86649/commands/193373 (Index Scan on job_artifact_registry_pkey, 85,131 rows into 6 groups, 83 ms cold, buffers: 2 hit / 1,627 read)

This reads the same pages as one batch of one of today's counts. For example, a verified_count batch read 1,547 buffers at 311M rows (plan). The difference is that the pages are read once instead of six times.

Full loop

Run Batches Time With the 10 ms sleeps
1 3,667 4 min 55 s ~5 min 31 s
2 3,667 4 min 58 s ~5 min 35 s

Before, the six loops took about 22 minutes cold and 13 minutes warm at 311M rows. Those numbers come from #637128, which used a different clone.

How we measured
  • Geo is not enabled on GitLab.com, so the Database Lab snapshot has no registry tables. On gitlab-production-main, we created job_artifact_registry from ee/db/geo/structure.sql, seeded it, added the primary key, and ran VACUUM (ANALYZE). We left out the other indexes, because these queries filter on id only and can't use them.
  • Seed: IDs 1-366,700,000 with about 15% random gaps. 99.4% of rows are synced and 0.003% failed to sync; the rest are pending or started. Of the synced rows, 99.8% are verified and 0.02% failed verification. The newest 0.5% of IDs are mostly pending.
  • The loop runs used a DO block that reproduces Gitlab::Database::BatchCounter: start at 0, 100,000-ID batches, end bound MAX(id) + 1. Each batch ran through EXECUTE format(...), so it got a plan for its literal values. The block leaves out the 10 ms sleep between batches, and the last column adds it back. The block reports through RAISE EXCEPTION, so Database Lab didn't save the runs and they have no console links.
  • Both runs gave the same six totals, which match the seed.
  • Database Lab hardware and cache differ from a customer secondary. Compare the versions with each other, not with production times.

References

Part of #637128

Screenshots or screen recordings

Not applicable: this MR has no UI changes.

How to set up and validate locally

  1. On a GDK Geo secondary, compare the counts in a Rails console:
    r = Geo::JobArtifactReplicator
    r.registry_counts
    Gitlab::Geo::Replicator::REGISTRY_COUNT_NAMES.index_with { |name| r.public_send(name) }
  2. On the primary, enable the flag:
    Feature.enable(:geo_single_pass_registry_counts)
  3. On the secondary, run bin/rake geo:status. The replication and verification counts should match the output with the flag disabled.

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.

Edited by Chloe Fons

Merge request reports

Loading
Loading