Skip to content

Sync some useful postgres-exporter queries from GitLab.com

Stan Hu requested to merge sh-sync-postgres-exporter-queries into master

What does this MR do?

This adds a number of useful metrics:

  • pg_oldest_blocked
  • pg_long_running_transactions
  • pg_stuck_idle_in_transaction
  • pg_stat_activity_marginalia_sampler
  • pg_stat_activity_autovacuum
  • pg_stat_activity_autovacuum_active
  • pg_long_running_transactions_marginalia

The Marginalia sampler has proved quite useful on GitLab.com for tracking which endpoints are causing the most database activity.

Local testing

$ curl -s http://localhost:9187/metrics | egrep "pg_oldest_bocked|pg_long_running_transactions|pg_stuck_idle_in_transaction|pg_stat_activity_marginalia_sampler|pg_stat_activity_autovacuum|pg_stat_activity_autovacuum_active" | egrep -v "^#"
pg_long_running_transactions_age_in_seconds{server="/var/opt/gitlab/postgresql:5432"} NaN
pg_long_running_transactions_transactions{server="/var/opt/gitlab/postgresql:5432"} 0
pg_stat_activity_marginalia_sampler_active_count{application="",command="SELECT",endpoint="",server="/var/opt/gitlab/postgresql:5432",state="active",usename="gitlab-psql",wait_event="",wait_event_type=""} 1
pg_stat_activity_marginalia_sampler_active_count{application="",command="SELECT",endpoint="",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} 1
pg_stat_activity_marginalia_sampler_active_count{application="sidekiq",command="SELECT",endpoint="",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} 1
pg_stat_activity_marginalia_sampler_active_count{application="sidekiq",command="SELECT",endpoint="Cronjob",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} 1
pg_stat_activity_marginalia_sampler_active_count{application="sidekiq",command="SELECT",endpoint="Database::BatchedBackgroundMigrationWorker",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} 1
pg_stat_activity_marginalia_sampler_active_count{application="sidekiq",command="SELECT",endpoint="ElasticIndexInitialBulkCronWorker",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} 1
pg_stat_activity_marginalia_sampler_active_count{application="sidekiq",command="SELECT",endpoint="UpdateAllMirrorsWorker",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} 1
pg_stat_activity_marginalia_sampler_active_count{application="sidekiq",command="SELECT",endpoint="UserStatusCleanup::BatchWorker",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} 1
pg_stat_activity_marginalia_sampler_max_tx_age_in_seconds{application="",command="SELECT",endpoint="",server="/var/opt/gitlab/postgresql:5432",state="active",usename="gitlab-psql",wait_event="",wait_event_type=""} 0.000623
pg_stat_activity_marginalia_sampler_max_tx_age_in_seconds{application="",command="SELECT",endpoint="",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} NaN
pg_stat_activity_marginalia_sampler_max_tx_age_in_seconds{application="sidekiq",command="SELECT",endpoint="",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} NaN
pg_stat_activity_marginalia_sampler_max_tx_age_in_seconds{application="sidekiq",command="SELECT",endpoint="Cronjob",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} NaN
pg_stat_activity_marginalia_sampler_max_tx_age_in_seconds{application="sidekiq",command="SELECT",endpoint="Database::BatchedBackgroundMigrationWorker",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} NaN
pg_stat_activity_marginalia_sampler_max_tx_age_in_seconds{application="sidekiq",command="SELECT",endpoint="ElasticIndexInitialBulkCronWorker",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} NaN
pg_stat_activity_marginalia_sampler_max_tx_age_in_seconds{application="sidekiq",command="SELECT",endpoint="UpdateAllMirrorsWorker",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} NaN
pg_stat_activity_marginalia_sampler_max_tx_age_in_seconds{application="sidekiq",command="SELECT",endpoint="UserStatusCleanup::BatchWorker",server="/var/opt/gitlab/postgresql:5432",state="idle",usename="gitlab",wait_event="ClientRead",wait_event_type="Client"} NaN
pg_stuck_idle_in_transaction_queries{server="/var/opt/gitlab/postgresql:5432"} 0

Related issues

Checklist

See Definition of done.

For anything in this list which will not be completed, please provide a reason in the MR discussion

Required

  • Merge Request Title, and Description are up to date, accurate, and descriptive
  • MR targeting the appropriate branch
  • MR has a green pipeline on GitLab.com
  • Pipeline is green on dev.gitlab.org if the change is touching anything besides documentation or internal cookbooks
  • trigger-package has a green pipeline running against latest commit

Expected (please provide an explanation if not completing)

  • Test plan indicating conditions for success has been posted and passes
  • Documentation created/updated: do we document the exporter stats anywhere?
  • Tests added: we have some tests in gitlab-cookbooks/gitlab-exporters!242 (merged) that I added; do we want those?
  • Integration tests added to GitLab QA: not relevant
  • Equivalent MR/issue for the GitLab Chart opened: we don't ship postgres-exporter?
Edited by Stan Hu

Merge request reports