CLI Command to Query Online GC Stats

Context & Motivation

Administrators currently need direct database access to monitor online garbage collection (GC) health via SQL queries on gc_blob_review_queue and gc_manifest_review_queue tables, as documented in online garbage collection monitoring.

This is especially important post-import when queues can be large and administrators need to verify GC is processing tasks effectively. The focus should be on actionable metrics (tasks pending removal, long overdue tasks requiring attention, and high retry counts) rather than raw queue sizes, which aren't reliable health indicators.

Proposed Solution

A new CLI command registry database gc-stats that provides a snapshot of GC queue health with:

For both blob and manifest queues:

  1. Tasks Pending Removal:
    • Count of tasks where review_after < NOW()
    • Sample of up to 10 entries (digest, repository path, review_after timestamp)
  2. Long Overdue Tasks:
    • Count of tasks where review_after < NOW() - configured_delay
    • Sample of up to 10 entries (digest, repository path, review_after timestamp, time overdue)
  3. High Retry Tasks:
    • Count of tasks with >10 retries
    • Sample of up to 10 entries (digest, repository path, retry count, review_after timestamp)

Output Formats:

  • Human-readable table format (default) with interpretation guidance using tablewriter library
  • JSON format (via --format json flag) for programmatic consumption
  • Optional --limit flag to control sample size (default: 10)
  • Each section shows count + sample entries with basic guidance

Implementation Tasks

  • CLI command structure with mock data
    • Create registry database gc-stats command structure using existing command framework
    • Implement hardcoded sample data demonstrating all output sections (counts + sample entries)
    • Implement both JSON and human-readable output formats with mock data
    • Add --format flag
    • Add --limit flag for sample size (default: 10)
    • Use tablewriter library for human-readable table formatting
    • Include basic guidance/interpretation text in each output section
    • This allows omnibus integration work to begin in parallel
  • Database queries - Tasks pending removal
    • Add count query for blobs pending removal (where review_after < NOW())
    • Add count query for manifests pending removal (where review_after < NOW())
    • Add sample query for blobs pending removal (limit N, ordered by review_after)
    • Add sample query for manifests pending removal (limit N, ordered by review_after)
    • Include unit tests for all queries
  • Database queries - Long overdue tasks
    • Add count query for blobs overdue by configured delay (where review_after < NOW() - configured_delay)
    • Add count query for manifests overdue by configured delay (where review_after < NOW() - configured_delay)
    • Add sample query for blobs overdue by configured delay (limit N, ordered by review_after)
    • Add sample query for manifests overdue by configured delay (limit N, ordered by review_after)
    • Include unit tests for all queries
  • Database queries - High retry statistics
    • Add count query for blobs with >10 retries
    • Add count query for manifests with >10 retries
    • Add sample query for blobs with >10 retries (limit N, ordered by retry count DESC)
    • Add sample query for manifests with >10 retries (limit N, ordered by retry count DESC)
    • Return detailed information: digest, repository path, retry count, review_after timestamp
    • Include unit tests for all queries
  • Wire real data into CLI command
    • Replace mock data with actual database queries
    • Wire up the stats service to the command
    • Ensure both output formats work with real data
    • Refine guidance/interpretation text based on real data patterns
  • Documentation
    • Update CLI documentation with command usage and examples
    • Update admin documentation to reference the new command as replacement to SQL queries (aligning with MR !217417 approach)

Related Work

Edited by Hayley Swimelar