Sync some useful postgres-exporter queries from GitLab.com
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