Last active
November 27, 2019 16:41
-
-
Save nseinlet/10b0604cd50c1fa3252cf51f43ec3e60 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env bash | |
| #%# family=manual | |
| case $1 in | |
| config) | |
| echo graph_category postgres | |
| echo graph_title PostgreSQL Vacuum jobs waiting | |
| echo vacuum_job_waiting.label Vacuum jobs waiting | |
| echo analyse_job_waiting.label Analyse jobs waiting | |
| exit 0 | |
| ;; | |
| esac | |
| echo -n "vacuum_job_waiting.value " | |
| psql postgres -X -A -t -c "WITH autovacuum_vacuum_scale_factor as (select setting | |
| from pg_settings | |
| where name like 'autovacuum_vacuum_scale_factor'), | |
| autovacuum_vacuum_threshold as (select setting | |
| from pg_settings | |
| where name like 'autovacuum_vacuum_threshold'), | |
| dt as (SELECT | |
| relname AS TableName | |
| ,n_live_tup AS LiveTuples | |
| ,n_dead_tup AS DeadTuples | |
| FROM pg_stat_user_tables) | |
| SELECT count(*) | |
| FROM dt,autovacuum_vacuum_scale_factor,autovacuum_vacuum_threshold | |
| WHERE DeadTuples>autovacuum_vacuum_threshold.setting::int4+(autovacuum_vacuum_scale_factor.setting::float*(LiveTuples+DeadTuples));" | |
| echo -n "analyse_job_waiting.value " | |
| psql postgres -X -A -t -c "WITH autovacuum_analyze_scale_factor as (select setting | |
| from pg_settings | |
| where name like 'autovacuum_analyze_scale_factor'), | |
| autovacuum_analyze_threshold as (select setting | |
| from pg_settings | |
| where name like 'autovacuum_analyze_threshold'), | |
| dt as (SELECT | |
| relname AS TableName, | |
| n_mod_since_analyze, | |
| n_live_tup AS LiveTuples | |
| FROM pg_stat_user_tables) | |
| SELECT count(*) | |
| FROM dt,autovacuum_analyze_scale_factor,autovacuum_analyze_threshold | |
| WHERE n_mod_since_analyze>autovacuum_analyze_threshold.setting::int4+(autovacuum_analyze_scale_factor.setting::float*LiveTuples);" | |
| exit 0 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment