python.d/postgres.chart.py

   1 # -*- coding: utf-8 -*-
   2
   3 import psycopg2
   4 from base import SimpleService
   5 from psycopg2.extras import DictCursor
   6
   7 # default module values
   8 update_every = 1
   9 priority = 90000
  10 retries = 60
  11
  12 # Default Config options.
  13 # {
  14 #    'database': None,
  15 #    'user': 'postgres',
  16 #    'password': None,
  17 #    'host': 'localhost',
  18 #    'port': 5432
  19 # }
  20
  21 ORDER = ["Tuples", "Scans", "BGWriter", "BufferCache"]
  22 CHARTS = {
  23     "Tuples": {
  24         'options': ["tuples", "PostgreSQL tuple access", "Tuples / sec", "tuples", "postgres.tuples", "line"],
  25         'lines': [
  26             ["inserted", "inserted", "incremental", 1, 1],
  27             ["seqread", "seqread", "incremental", 1, 1],
  28             ["hotupdated", "hotupdated", "incremental", 1, 1],
  29             ["deleted", "deleted", "incremental", 1, 1],
  30             ["updated", "updated", "incremental", 1, 1],
  31             ["idxfetch", "idxfetch", "incremental", 1, 1],
  32         ]},
  33     "Scans": {
  34         'options': ["scans", "PostgreSQL scan types", "Scans / sec", "scans", "postgres.scans", "line"],
  35         'lines': [
  36             ["sequential", "sequential", "incremental", 1, 1],
  37             ["index", "index", "incremental", 1, 1],
  38         ]},
  39     "BGWriter": {
  40         'options': ["bgwriter", "BG Writer Activity", "Buffers / sec", "bgwriter", "postgres.bgwriter", "line"],
  41         'lines': [
  42             ["buffers_alloc", "buffers_alloc", "incremental", 1, 1],
  43             ["buffers_clean", "buffers_clean", "incremental", 1, 1],
  44             ["buffers_checkpoint", "buffers_checkpoint", "incremental", 1, 1],
  45             ["buffers_backend", "buffers_backend", "incremental", 1, 1],
  46         ]},
  47     "BufferCache": {
  48         'options': ["buffer_cache", "Buffer Cache", "Buffers / sec", "buffer_cache", "postgres.buffer_cache", "line"],
  49         'lines': [
  50             ["blks_read", "blks_read", "incremental", 1, 1],
  51             ["blks_hit", "blks_hit", "incremental", 1, 1],
  52         ]}
  53 }
  54
  55
  56 class Service(SimpleService):
  57     def __init__(self, configuration=None, name=None):
  58         super(self.__class__, self).__init__(configuration=configuration, name=name)
  59         self.order = ORDER
  60         self.definitions = CHARTS
  61         self.configuration = configuration
  62         self.connection = None
  63
  64     def connect(self):
  65         params = dict(user='postgres',
  66                       database=None,
  67                       password=None,
  68                       host='localhost',
  69                       port=5432)
  70         params.update(self.configuration)
  71         if self.connection is None:
  72             self.connection = psycopg2.connect(**params)
  73
  74     def check(self):
  75         try:
  76             self.connect()
  77             return True
  78         except Exception as e:
  79             self.error(e)
  80             return False
  81
  82     def _get_data(self):
  83         cursor = self.connection.cursor(cursor_factory=DictCursor)
  84         cursor.execute("""
  85             SELECT
  86                     -- Tuples
  87                     COALESCE(sum(seq_tup_read),0) AS seqread,
  88                     COALESCE(sum(idx_tup_fetch),0) AS idxfetch,
  89                     COALESCE(sum(n_tup_ins),0) AS inserted,
  90                     COALESCE(sum(n_tup_upd),0) AS updated,
  91                     COALESCE(sum(n_tup_del),0) AS deleted,
  92                     COALESCE(sum(n_tup_hot_upd),0) AS hotupdated,
  93
  94                     -- Scans
  95                     COALESCE(sum(seq_scan),0) AS sequential,
  96                     COALESCE(sum(idx_scan),0) AS index
  97             FROM pg_stat_user_tables
  98         """)
  99         graph_data = {k: float(v) for k, v in cursor.fetchone().items()}
 100
 101         # Pull in BGWriter info
 102         cursor.execute("""
 103             SELECT
 104               buffers_checkpoint,
 105               buffers_clean,
 106               buffers_backend,
 107               buffers_alloc
 108             FROM
 109               pg_stat_bgwriter
 110         """)
 111         graph_data.update(dict(cursor.fetchone()))
 112
 113         cursor.execute("""
 114             SELECT
 115               sum(blks_read) AS blks_read,
 116               sum(blks_hit) AS blks_hit
 117             FROM
 118               pg_stat_database
 119             WHERE
 120               datname = %(database)s
 121         """, self.configuration)
 122         graph_data.update({k: float(v) for k, v in cursor.fetchone().items()})
 123
 124         self.connection.commit()
 125         cursor.close()
 126         return graph_data