Repository /metrics/ endpoint is slow (3x COUNT(DISTINCT) + per-row REGEXP_REPLACE), times out under concurrent polling

Open
  • Is something wrong?
#539 2 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
25/100
Issue type
Refactor
Clarity
Mostly clear
Activity status
Active
Tech stack
python, sql
Domain
databases, performance

Research direction

Start at MavenRepositoryViewSet.metrics and repository_metrics in pulp_maven/app/catalog.py, then read base_version_annotation and maven_packages_in_version to see how the queryset is built. Work out how the three .distinct().count() calls can share one aggregation while returning the same package_count, version_count and build_count. Done when the endpoint returns identical numbers from a single query and the existing metrics tests still pass.

Written by the indexing model from the issue text.

Description

Summary

The repository metrics endpoint (GET /pulp/api/v3/repositories/maven/maven/<pk>/metrics/) is expensive on large repositories. In production it takes ~15s for a large repo on a single call and spikes to ~45s when polled concurrently, exceeding the 30s CDN gateway timeout and surfacing as 504 Gateway Timeout to clients (the origin still returns 200 ~15s later).

Root cause

MavenRepositoryViewSet.metrics calls repository_metrics(maven_packages_in_version(repo_version)) in pulp_maven/app/catalog.py, which runs three separate COUNT(DISTINCT …) aggregations over the repository's MavenPackage set:

def repository_metrics(content_qs):
    content_qs = content_qs.order_by()
    return {
        "package_count": content_qs.values("group_id", "artifact_id").distinct().count(),
        "version_count": (
            content_qs.annotate(_base_version=base_version_annotation())
            .values("group_id", "artifact_id", "_base_version")
            .distinct()
            .count()
        ),
        "build_count": content_qs.values("group_id", "artifact_id", "version").distinct().count(),
    }

Issues:

  1. Three full passes. Each .distinct().count() is a separate scan + hash/sort-distinct over the whole content set.
  2. Per-row REGEXP_REPLACE. version_count applies base_version_annotation() (a REGEXP_REPLACE on version) to every row before the distinct — non-sargable, so no index can help it.
  3. pk__in membership subquery. content_qs comes from maven_packages_in_version() which is MavenPackage.objects.filter(pk__in=<memberships subquery>), producing a large intermediate set for big repositories.

The three aggregations are independent, so concurrent pollers multiply DB CPU/IO pressure, which is what pushes the latency from ~15s to ~45s.

Proposed fix

Collapse the three counts into a single pass, e.g. one aggregation over (group_id, artifact_id, version) that also carries a precomputed base_version, using conditional/Count(..., distinct=True) expressions, so the content set is scanned once and REGEXP_REPLACE is evaluated once per row instead of across a separate full pass. This removes two of the three scans and the duplicated regex work.

Impact

  • /metrics/ is polled by automated clients (observed: an OpenAPI-Generator Go client polling every ~5 min with 3 concurrent requests), so the slow path recurs continuously and generates steady 504s at the edge even though there is no outage.
  • Affects any sufficiently large Maven repository.

Notes

  • Code is unchanged since repository_metrics was introduced; current main still has this implementation.
Dominant language
Python
Stars
5
Forks
30
Avg merge
2d 7h
Merged PRs (30d)
36

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from pulp/pulp_maven

All issues in pulp/pulp_maven

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.