Skip to content

Commit c324594

Browse files
jbuchbinderjbuchbinder
committed
Merge pull request ganglia#132 from mbroers/master
Initial commit for Python Postgreql module for Ganglia
2 parents 85df066 + 3bb1534 commit c324594

3 files changed

Lines changed: 395 additions & 0 deletions

File tree

‎postgresql/README.md‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
Python module for Ganglia 3.4
2+
Postgresql metrics. Compatible with version 9.2 and above.
3+
4+
Author: MBroers

‎postgresql/conf.d/postgres.pyconf‎

Lines changed: 166 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,166 @@
1+
modules {
2+
module {
3+
name = "postgres"
4+
language = "python"
5+
param host {
6+
value = "hostname_goes_here"
7+
}
8+
param port {
9+
value = "port_goes_here"
10+
}
11+
param dbname {
12+
value = "database_goes_here"
13+
}
14+
param username {
15+
value = "username_goes_here"
16+
}
17+
param password {
18+
value = "password_goes_here"
19+
}
20+
}
21+
}
22+
23+
collection_group {
24+
collect_every = 120
25+
time_threshold = 120
26+
metric {
27+
name = "Pypg_idle_sessions"
28+
title = "Postgres Idle Sessions"
29+
value_threshold = 0
30+
}
31+
metric {
32+
name = "Pypg_idle_in_transaction_sessions"
33+
title = "Postgres Idle In Transaction Sessions"
34+
value_threshold = 0
35+
}
36+
metric {
37+
name = "Pypg_active_sessions"
38+
title = "Postgres Active Sessions"
39+
value_threshold = 0
40+
}
41+
metric {
42+
name = "Pypg_hours_since_last_vacuum"
43+
title = "Postgres Hours Since Last Vacuum"
44+
value_threshold = 0
45+
}
46+
metric {
47+
name = "Pypg_hours_since_last_analyze"
48+
title = "Postgres Hours Since Last Analyze"
49+
value_threshold = 0
50+
}
51+
metric {
52+
name = "Pypg_waiting_sessions"
53+
title = "Postgres Waiting Sessions blocked"
54+
value_threshold = 0
55+
}
56+
metric {
57+
name = "Pypg_locks_accessexclusive"
58+
title = "Postgres AccessExclusive Locks read write blocking"
59+
value_threshold = 0
60+
}
61+
metric {
62+
name = "Pypg_locks_otherexclusive"
63+
title = "Postgres Exclusive Locks write blocking"
64+
value_threshold = 0
65+
}
66+
metric {
67+
name = "Pypg_locks_shared"
68+
title = "Postgres Shared Locks NON blocking"
69+
value_threshold = 0
70+
}
71+
metric {
72+
name = "Pypg_longest_xact"
73+
title = "Postgres Longest Transaction in Minutes"
74+
value_threshold = 0
75+
}
76+
metric {
77+
name = "Pypg_longest_query"
78+
title = "Postgres Longest Active Query in Minutes"
79+
value_threshold = 0
80+
}
81+
metric {
82+
name = "Pypg_transactions"
83+
title = "Postgres Transactions"
84+
value_threshold = 0
85+
}
86+
metric {
87+
name = "Pypg_inserts"
88+
title = "Postgres Inserts"
89+
value_threshold = 0
90+
}
91+
metric {
92+
name = "Pypg_updates"
93+
title = "Postgres Updates"
94+
value_threshold = 0
95+
}
96+
metric {
97+
name = "Pypg_deletes"
98+
title = "Postgres Deletes"
99+
value_threshold = 0
100+
}
101+
metric {
102+
name = "Pypg_reads"
103+
title = "Postgres Reads"
104+
value_threshold = 0
105+
}
106+
metric {
107+
name = "Pypg_blks_diskread"
108+
title = "Postgres Blocks Read From Disk"
109+
value_threshold = 0
110+
}
111+
metric {
112+
name = "Pypg_blks_memread"
113+
title = "Postgres Blocks Read From Memory Buffer Cache"
114+
value_threshold = 0
115+
}
116+
metric {
117+
name = "Pypg_tup_seqscan"
118+
title = "Postgres Tuples Read From Table Sequence Scans"
119+
value_threshold = 0
120+
}
121+
metric {
122+
name = "Pypg_tup_idxfetch"
123+
title = "Postgres Tuples Fetched From Indexes"
124+
value_threshold = 0
125+
}
126+
metric {
127+
name = "Pypg_bgwriter_checkpoints_timed"
128+
title = "Postgres Scheduled Checkpoints"
129+
value_threshold = 0
130+
}
131+
metric {
132+
name = "Pypg_bgwriter_checkpoints_req"
133+
title = "Postgres Unscheduled Checkpoints"
134+
value_threshold = 0
135+
}
136+
metric {
137+
name = "Pypg_bgwriter_checkpoint_write_time"
138+
title = "Postgres Time to Write Checkpoints to Disk"
139+
value_threshold = 0
140+
}
141+
metric {
142+
name = "Pypg_bgwriter_checkpoint_sync_time"
143+
title = "Postgres Time to Sync Checkpoints to Disk"
144+
value_threshold = 0
145+
}
146+
metric {
147+
name = "Pypg_bgwriter_buffers_checkpoint"
148+
title = "Postgres Buffers Written During Checkpoint"
149+
value_threshold = 0
150+
}
151+
metric {
152+
name = "Pypg_bgwriter_buffers_clean"
153+
title = "Postgres Buffers Written By Background Writer"
154+
value_threshold = 0
155+
}
156+
metric {
157+
name = "Pypg_bgwriter_buffers_backend"
158+
title = "Postgres Buffers Written Directly By Backend"
159+
value_threshold = 0
160+
}
161+
metric {
162+
name = "Pypg_bgwriter_buffers_alloc"
163+
title = "Postgres Number Of Buffers Allocated"
164+
value_threshold = 0
165+
}
166+
}

‎postgresql/python_modules/postgres.py‎

Lines changed: 225 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,225 @@
1+
import psycopg2
2+
import psycopg2.extras
3+
import syslog
4+
import functools
5+
import time
6+
7+
# Cache for postgres query values, this prevents opening db connections for each metric_handler callback
8+
class Cache(object):
9+
def __init__(self, expiry):
10+
self.expiry = expiry
11+
self.curr_time = 0
12+
self.last_time = 0
13+
self.last_value = None
14+
15+
def __call__(self, func):
16+
@functools.wraps(func)
17+
def deco(*args, **kwds):
18+
self.curr_time = time.time()
19+
if self.curr_time - self.last_time > self.expiry:
20+
self.last_value = func(*args, **kwds)
21+
self.last_time = self.curr_time
22+
return self.last_value
23+
return deco
24+
25+
# Queries update the pg_metrics dict with their values based on cache interval
26+
@Cache(60)
27+
def pg_metrics_queries():
28+
pg_metrics = {}
29+
db_conn = psycopg2.connect(pgdsn)
30+
db_curs = db_conn.cursor()
31+
32+
# single session state query avoids multiple scans of pg_stat_activity
33+
# state is a different column name in postgres 9.2, previous versions will have to update this query accordingly
34+
db_curs.execute(
35+
'select state, waiting, \
36+
extract(epoch from current_timestamp - xact_start)::int, \
37+
extract(epoch from current_timestamp - query_start)::int from pg_stat_activity;')
38+
results = db_curs.fetchall()
39+
active = 0
40+
idle = 0
41+
idleintxn = 0
42+
waiting = 0
43+
active_results = []
44+
for state, waiting, xact_start_sec, query_start_sec in results:
45+
if state == 'active':
46+
active = int(active + 1)
47+
# build a list of query start times where query is active
48+
active_results.append(query_start_sec)
49+
if state == 'idle':
50+
idle = int(idle + 1)
51+
if state == 'idle in transaction':
52+
idleintxn = int(idleintxn + 1)
53+
if waiting == True:
54+
waitingtrue = int(waitingtrue + 1)
55+
56+
# determine longest transaction in seconds
57+
sorted_by_xact = sorted(results, key=lambda tup: tup[2], reverse=True)
58+
longest_xact_in_sec = (sorted_by_xact[0])[2]
59+
60+
# determine longest active query in seconds
61+
sorted_by_query = sorted(active_results, reverse=True)
62+
longest_query_in_sec = sorted_by_query[0]
63+
64+
pg_metrics.update(
65+
{'Pypg_idle_sessions':idle,
66+
'Pypg_active_sessions':active,
67+
'Pypg_waiting_sessions':waiting,
68+
'Pypg_idle_in_transaction_sessions':idleintxn,
69+
'Pypg_longest_xact':longest_xact_in_sec,
70+
'Pypg_longest_query':longest_query_in_sec})
71+
72+
# locks query
73+
db_curs.execute('select mode, locktype from pg_locks;')
74+
results = db_curs.fetchall()
75+
accessexclusive = 0
76+
otherexclusive = 0
77+
shared = 0
78+
for mode, locktype in results:
79+
if (mode == 'AccessExclusiveLock' and locktype != 'virtualxid'):
80+
accessexclusive = int(accessexclusive + 1)
81+
if (mode != 'AccessExclusiveLock' and locktype != 'virtualxid'):
82+
if 'Exclusive' in mode:
83+
otherexclusive = int(otherexclusive + 1)
84+
if ('Share' in mode and locktype != 'virtualxid'):
85+
shared = int(shared + 1)
86+
pg_metrics.update(
87+
{'Pypg_locks_accessexclusive':accessexclusive,
88+
'Pypg_locks_otherexclusive':otherexclusive,
89+
'Pypg_locks_shared':shared})
90+
91+
# background writer query returns one row that needs to be parsed
92+
db_curs.execute(
93+
'select checkpoints_timed, checkpoints_req, checkpoint_write_time, \
94+
checkpoint_sync_time, buffers_checkpoint, buffers_clean, \
95+
buffers_backend, buffers_alloc from pg_stat_bgwriter;')
96+
results = db_curs.fetchall()
97+
bgwriter_values = results[0]
98+
checkpoints_timed = int(bgwriter_values[0])
99+
checkpoints_req = int(bgwriter_values[1])
100+
checkpoint_write_time = int(bgwriter_values[2])
101+
checkpoint_sync_time = int(bgwriter_values[3])
102+
buffers_checkpoint = int(bgwriter_values[4])
103+
buffers_clean = int(bgwriter_values[5])
104+
buffers_backend = int(bgwriter_values[6])
105+
buffers_alloc = int(bgwriter_values[7])
106+
pg_metrics.update(
107+
{'Pypg_bgwriter_checkpoints_timed':checkpoints_timed,
108+
'Pypg_bgwriter_checkpoints_req':checkpoints_req,
109+
'Pypg_bgwriter_checkpoint_write_time':checkpoint_write_time,
110+
'Pypg_bgwriter_checkpoint_sync_time':checkpoint_sync_time,
111+
'Pypg_bgwriter_buffers_checkpoint':buffers_checkpoint,
112+
'Pypg_bgwriter_buffers_clean':buffers_clean,
113+
'Pypg_bgwriter_buffers_backend':buffers_backend,
114+
'Pypg_bgwriter_buffers_alloc':buffers_alloc})
115+
116+
# database statistics returns one row that needs to be parsed
117+
db_curs.execute(
118+
'select (sum(xact_commit) + sum(xact_rollback)), sum(tup_inserted), \
119+
sum(tup_updated), sum(tup_deleted), (sum(tup_returned) + sum(tup_fetched)), \
120+
sum(blks_read), sum(blks_hit) from pg_stat_database;')
121+
results = db_curs.fetchall()
122+
pg_stat_db_values = results[0]
123+
transactions = int(pg_stat_db_values[0])
124+
inserts = int(pg_stat_db_values[1])
125+
updates = int(pg_stat_db_values[2])
126+
deletes = int(pg_stat_db_values[3])
127+
reads = int(pg_stat_db_values[4])
128+
blksdisk = int(pg_stat_db_values[5])
129+
blksmem = int(pg_stat_db_values[6])
130+
pg_metrics.update(
131+
{'Pypg_transactions':transactions,
132+
'Pypg_inserts':inserts,
133+
'Pypg_updates':updates,
134+
'Pypg_deletes':deletes,
135+
'Pypg_reads':reads,
136+
'Pypg_blks_diskread':blksdisk,
137+
'Pypg_blks_memread':blksmem})
138+
139+
# table statistics returns one row that needs to be parsed
140+
db_curs.execute(
141+
'select sum(seq_tup_read), sum(idx_tup_fetch), \
142+
extract(epoch from now() - min(last_vacuum))::int/60/60, \
143+
extract(epoch from now() - min(last_analyze))::int/60/60 \
144+
from pg_stat_all_tables;')
145+
results = db_curs.fetchall()
146+
pg_stat_table_values = results[0]
147+
seqscan = int(pg_stat_table_values[0])
148+
idxfetch = int(pg_stat_table_values[1])
149+
hours_since_vacuum = int(pg_stat_table_values[2])
150+
hours_since_analyze = int(pg_stat_table_values[3])
151+
pg_metrics.update(
152+
{'Pypg_tup_seqscan':seqscan,
153+
'Pypg_tup_idxfetch':idxfetch,
154+
'Pypg_hours_since_last_vacuum':hours_since_vacuum,
155+
'Pypg_hours_since_last_analyze':hours_since_analyze})
156+
157+
db_curs.close()
158+
return pg_metrics
159+
160+
# Metric handler uses dictionary pg_metrics keys to return values from queries based on metric name
161+
def metric_handler(name):
162+
pg_metrics = pg_metrics_queries()
163+
return int(pg_metrics[name])
164+
165+
# Metric descriptors are initialized here
166+
def metric_init(params):
167+
HOST = str(params.get('host'))
168+
PORT = str(params.get('port'))
169+
DB = str(params.get('dbname'))
170+
USER = str(params.get('username'))
171+
PASSWORD = str(params.get('password'))
172+
173+
global pgdsn
174+
pgdsn = "dbname=" + DB + " host=" + HOST + " user=" + USER + " port=" + PORT + " password=" + PASSWORD
175+
176+
descriptors = [
177+
{'name':'Pypg_idle_sessions','units':'Sessions','slope':'both','description':'PG Idle Sessions'},
178+
{'name':'Pypg_active_sessions','units':'Sessions','slope':'both','description':'PG Active Sessions'},
179+
{'name':'Pypg_idle_in_transaction_sessions','units':'Sessions','slope':'both','description':'PG Idle In Transaction Sessions'},
180+
{'name':'Pypg_waiting_sessions','units':'Sessions','slope':'both','description':'PG Waiting Sessions Blocked'},
181+
{'name':'Pypg_longest_xact','units':'Seconds','slope':'both','description':'PG Longest Transaction in Seconds'},
182+
{'name':'Pypg_longest_query','units':'Seconds','slope':'both','description':'PG Longest Query in Seconds'},
183+
{'name':'Pypg_locks_accessexclusive','units':'Locks','slope':'both','description':'PG AccessExclusive Locks read write blocking'},
184+
{'name':'Pypg_locks_otherexclusive','units':'Locks','slope':'both','description':'PG Exclusive Locks write blocking'},
185+
{'name':'Pypg_locks_shared','units':'Locks','slope':'both','description':'PG Shared Locks NON blocking'},
186+
{'name':'Pypg_bgwriter_checkpoints_timed','units':'checkpoints','slope':'positive','description':'PG scheduled checkpoints'},
187+
{'name':'Pypg_bgwriter_checkpoints_req','units':'checkpoints','slope':'positive','description':'PG unscheduled checkpoints'},
188+
{'name':'Pypg_bgwriter_checkpoint_write_time','units':'ms','slope':'positive','description':'PG time to write checkpoints to disk'},
189+
{'name':'Pypg_bgwriter_checkpoint_sync_time','units':'checkpoints','slope':'positive','description':'PG time to sync checkpoints to disk'},
190+
{'name':'Pypg_bgwriter_buffers_checkpoint','units':'buffers','slope':'positive','description':'PG number of buffers written during checkpoint'},
191+
{'name':'Pypg_bgwriter_buffers_clean','units':'buffers','slope':'positive','description':'PG number of buffers written by the background writer'},
192+
{'name':'Pypg_bgwriter_buffers_backend','units':'buffers','slope':'positive','description':'PG number of buffers written directly by a backend'},
193+
{'name':'Pypg_bgwriter_buffers_alloc','units':'buffers','slope':'positive','description':'PG number of buffers allocated'},
194+
{'name':'Pypg_transactions','units':'xacts','slope':'positive','description':'PG Transactions'},
195+
{'name':'Pypg_inserts','units':'tuples','slope':'positive','description':'PG Inserts'},
196+
{'name':'Pypg_updates','units':'tuples','slope':'positive','description':'PG Updates'},
197+
{'name':'Pypg_deletes','units':'tuples','slope':'positive','description':'PG Deletes'},
198+
{'name':'Pypg_reads','units':'tuples','slope':'positive','description':'PG Reads'},
199+
{'name':'Pypg_blks_diskread','units':'blocks','slope':'positive','description':'PG Blocks Read from Disk'},
200+
{'name':'Pypg_blks_memread','units':'blocks','slope':'positive','description':'PG Blocks Read from Memory'},
201+
{'name':'Pypg_tup_seqscan','units':'tuples','slope':'positive','description':'PG Tuples sequentially scanned'},
202+
{'name':'Pypg_tup_idxfetch','units':'tuples','slope':'positive','description':'PG Tuples fetched from indexes'},
203+
{'name':'Pypg_hours_since_last_vacuum','units':'hours','slope':'both','description':'PG hours since last vacuum'},
204+
{'name':'Pypg_hours_since_last_analyze','units':'hours','slope':'both','description':'PG hours since last analyze'}]
205+
206+
for d in descriptors:
207+
# Add default values to dictionary
208+
d.update({'call_back': metric_handler, 'time_max': 90, 'value_type': 'uint', 'format': '%d', 'groups': 'Postgres'})
209+
210+
return descriptors
211+
212+
# ganglia requires metric cleanup
213+
def metric_cleanup():
214+
'''Clean up the metric module.'''
215+
pass
216+
217+
# this code is for debugging and unit testing
218+
if __name__ == '__main__':
219+
descriptors = metric_init({"host":"hostname_goes_here","port":"port_goes_here","dbname":"database_name_goes_here","username":"username_goes_here","password":"password_goes_here"})
220+
while True:
221+
for d in descriptors:
222+
v = d['call_back'](d['name'])
223+
print 'value for %s is %u' % (d['name'], v)
224+
time.sleep(5)
225+

0 commit comments

Comments
 (0)