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
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

#blocked_queries ⇒ Object 



71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/postgres/vacuum/monitor/query.rb', line 71

def blocked_queries
  # The query was taken from https://wiki.postgresql.org/wiki/Lock_Monitoring
  "    SELECT blocked_locks.pid AS blocked_pid,\n      blocked_activity.usename AS blocked_user,\n      blocking_locks.pid AS blocking_pid,\n      blocking_activity.usename AS blocking_user,\n      blocked_activity.query AS blocked_statement,\n      blocking_activity.query AS current_statement_in_blocking_process,\n      blocked_activity.application_name AS blocked_application,\n      blocking_activity.application_name AS blocking_application\n    FROM pg_catalog.pg_locks blocked_locks\n    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid\n    JOIN pg_catalog.pg_locks blocking_locks \n      ON blocking_locks.locktype = blocked_locks.locktype\n      AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE\n      AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation\n      AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page\n      AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple\n      AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid\n      AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid\n      AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid\n      AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid\n      AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid\n      AND blocking_locks.pid != blocked_locks.pid\n    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid\n    WHERE NOT blocked_locks.GRANTED;\n  SQL\nend\n"

#connection_idle_time ⇒ Object 



111
112
113
114
115
116
117
118
119
120
121
 
# File 'lib/postgres/vacuum/monitor/query.rb', line 111

def connection_idle_time
  "    SELECT\n        max(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change))) as max,\n        percentile_cont(0.5) within GROUP (ORDER BY EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change)) DESC) AS median,\n        percentile_cont(0.9) within GROUP (ORDER BY EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change)) DESC) AS percentile_90\n    FROM pg_stat_activity\n    WHERE state = 'idle'\n    LIMIT 1000;\n  SQL\nend\n"

#connection_state ⇒ Object 



101
102
103
104
105
106
107
108
109
 
# File 'lib/postgres/vacuum/monitor/query.rb', line 101

def connection_state
  "    SELECT \n      state, count(*) as connection_count \n    FROM pg_stat_activity \n    GROUP BY state \n    ORDER BY connection_count DESC;\n  SQL\nend\n"

#long_running_transactions ⇒ Object 



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

def long_running_transactions
  "    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        state,\n        backend_xid,\n        backend_xmin,\n        wait_event_type\n      FROM pg_stat_activity\n      WHERE state IN (\#{STATES.join(', ')})\n      ORDER BY seconds DESC\n    ) AS long_queries\n    WHERE seconds > \#{Postgres::Vacuum::Monitor.configuration.long_running_transaction_threshold_seconds};\n  SQL\nend\n"

#tables_eligible_vacuuming ⇒ Object 



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
67
68
69
 
# File 'lib/postgres/vacuum/monitor/query.rb', line 35

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"