Publish scrubber metrics and create grafana dashboard

number of checked_ranges (and ratio that was checked).

oldest checked_range

number of missing_object

---

Migrated from T4684 (view on Phabricator)

added Datastore Scrubber priority:High labels

assigned to @vlorentz

changed title from Create grafana dashboard for scrubber metrics to Create scrubber metrics and grafana dashboard

changed title from Create scrubber metrics and grafana dashboard to Publish scrubber metrics and create grafana dashboard

changed milestone to %Regularly scrub journal, storage, and objstorage [Roadmap - Preserve]

added activity::Epic label

mentioned in issue #4685 (closed)

note to self: use prometheus-sql-exporter, probably? we already use it for the scheduler and the old archive counters.

https://gitlab.softwareheritage.org/swh/infra/puppet/puppet-swh-site/-/blob/production/site-modules/profile/files/prometheus/sql/config/swh-scheduler.yml

untested config:

- name: scrubber_checked
  scope: database
  database: ^(swh|softwareheritage)-scrubber$
  interval: '1h'
  help: "Software Heritage Scheduler scrubber coverage"
  query: |
    WITH bucket AS (
        SELECT distinct datastore, object_type, nb_partitions FROM checked_partition
    )
    SELECT
        datastore.package AS datastore_package,
        datastore.class AS datastore_class,
        datastore.instance AS datastore_instance,
        bucket.object_type AS object_type,
        bucket.nb_partitions AS nb_partitions,
        COUNT(*) AS checked_partitions_total,
        MIN(last_date) AS oldest_check_date,
        NOW() - MIN(last_date) AS oldest_check_age_seconds
    FROM checked_partition
    INNER JOIN datastore ON (datastore.id=bucket.datastore)
    WHERE (
        checked_partition.datastore=bucket.datastore
        AND checked_partition.object_type=bucket.object_type
        AND checked_partition.nb_partitions=bucket.nb_partitions
    )
    GROUP BY bucket.datastore, bucket.object_type, bucket.nb_partitions
  labels:
    - datastore_package
    - datastore_class
    - datastore_instance
    - object_type
    - nb_partitions
  values:
    - checked_partitions_total
    - oldest_check_date
    - oldest_check_age_seconds

I'll try it when the new schema is applied

Updated (tested) query:

SELECT
        datastore.package AS datastore_package,
        datastore.class AS datastore_class,
        datastore.instance AS datastore_instance,
        checked_partition.object_type AS object_type,
        checked_partition.nb_partitions AS nb_partitions,
        COUNT(*) AS checked_partitions_total,
        EXTRACT (EPOCH FROM MIN(last_date)) AS oldest_check_epoch,
        EXTRACT (EPOCH FROM MAX(last_date)) AS newest_check_epoch
    FROM checked_partition
    INNER JOIN datastore ON (datastore.id=checked_partition.datastore)
    GROUP BY 1,2,3,4,5;

Diff: drop the CTE, use proper fields for group by, extracting epochs instead of returning timestamptzs

The oldest_check_age_seconds computation can be done by prometheus

mentioned in commit swh/infra/puppet/puppet-swh-site@ded2e4e9

mentioned in merge request swh/infra/puppet/puppet-swh-site!614 (merged)

Metrics added to prometheus:

(I know, ghastly URL)

https://grafana.softwareheritage.org/explore?orgId=1&left=%7B%22datasource%22:%22RVaGqq67z%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22editorMode%22:%22code%22,%22expr%22:%22swh_scrubber_coverage_partitions_checked_total%22,%22legendFormat%22:%22__auto%22,%22range%22:true,%22instant%22:true%7D,%7B%22refId%22:%22B%22,%22editorMode%22:%22code%22,%22expr%22:%22time%28%29%20-%20swh_scrubber_coverage_oldest_partition_check_timestamp_seconds%22,%22legendFormat%22:%22__auto%22,%22range%22:true,%22instant%22:true%7D,%7B%22refId%22:%22C%22,%22editorMode%22:%22code%22,%22expr%22:%22time%28%29%20-%20swh_scrubber_coverage_newest_partition_check_timestamp_seconds%22,%22legendFormat%22:%22__auto%22,%22range%22:true,%22instant%22:true%7D%5D,%22range%22:%7B%22from%22:%22now-6h%22,%22to%22:%22now%22%7D%7D

added to a nice dashboard: https://grafana.softwareheritage.org/goto/hTa7hwBVk?orgId=1

closed