Module: Postgres::Vacuum::Monitor::Query

Extended by:
Query
Included in:
Query
Defined in:
lib/postgres/vacuum/monitor/query.rb

Constant Summary collapse

STATES = 
["'idle in transaction'", "'active'"].freeze
TIME_LIMIT = 
3600
THRESHOLD_SETTING = 
"'autovacuum_vacuum_threshold'".freeze
SCALE_FACTOR_SETTING = 
"'autovacuum_vacuum_scale_factor'".freeze
MAX_AGE_SETTING = 
"'autovacuum_freeze_max_age'".freeze
PG_CATALOG = 
"'pg_catalog'".freeze

Instance Method Summary collapse

Instance Method Details

#long_running_queries ⇒ Object 



14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
# File 'lib/postgres/vacuum/monitor/query.rb', line 14

def long_running_queries
  "    SELECT *\n    FROM (\n      SELECT\n        pid,\n        xact_start,\n        EXTRACT(EPOCH FROM (now() - xact_start)) AS seconds,\n        application_name,\n        query\n      FROM pg_stat_activity\n      WHERE state IN (\#{STATES.join(', ')})\n      ORDER BY seconds DESC\n    ) AS long_queries\n    WHERE seconds > \#{TIME_LIMIT};\n  SQL\nend\n"

#tables_eligible_vacuuming ⇒ Object 



32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
# File 'lib/postgres/vacuum/monitor/query.rb', line 32

def tables_eligible_vacuuming
  # The query was taken from http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html
  "    WITH vbt AS (\n    SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = \#{THRESHOLD_SETTING}) , vsf AS (\n      SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = \#{SCALE_FACTOR_SETTING}) , fma AS (\n        SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = \#{MAX_AGE_SETTING}) , sto AS (\n          SELECT opt_oid, split_part(setting, '=', 1) AS param, split_part(setting, '=', 2) AS value FROM (\n            SELECT oid opt_oid, unnest(reloptions) setting FROM pg_class\n          ) opt\n        )\n          SELECT '\"'||ns.nspname||'\".\"'||c.relname||'\"' AS relation,\n                  pg_size_pretty(pg_table_size(c.oid)) AS table_size,\n                  age(relfrozenxid) AS xid_age,\n                  coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age,\n                  (\n                    coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +\n                    coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * pg_table_size(c.oid)\n                  ) AS autovacuum_vacuum_tuples,\n                  n_dead_tup AS dead_tuples\n          FROM pg_class c\n          JOIN pg_namespace ns ON ns.oid = c.relnamespace\n          JOIN pg_stat_all_tables stat ON stat.relid = c.oid\n          JOIN vbt ON (1=1) JOIN vsf ON (1=1)\n          JOIN fma ON (1=1)\n          LEFT JOIN sto cvbt ON cvbt.param = \#{THRESHOLD_SETTING} AND c.oid = cvbt.opt_oid\n          LEFT JOIN sto cvsf ON cvsf.param = \#{SCALE_FACTOR_SETTING} AND c.oid = cvsf.opt_oid\n          LEFT JOIN sto cfma ON cfma.param = \#{MAX_AGE_SETTING} AND c.oid = cfma.opt_oid\n          WHERE c.relkind = 'r'\n            AND nspname <> \#{PG_CATALOG}\n            AND ( age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)\n            OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * pg_table_size(c.oid) <= n_dead_tup)\n    ORDER BY  age(relfrozenxid) DESC LIMIT 50;\n  SQL\nend\n"