class PostgreStats

Attributes

aggregate_counters[RW]
aggregate_gauges[RW]
config[RW]
connection[RW]
db[RW]
log[RW]
table_counters[RW]
table_gauges[RW]

Public Class Methods

new(config, logger = nil) click to toggle source
# File lib/postgrestats.rb, line 9
def initialize(config, logger = nil)
  @config = config
  
  initialize_log unless logger
  @log = logger if logger
  @log.error("Logging started")
  
  initialize_metrics
end

Public Instance Methods

connect(db) click to toggle source
# File lib/postgrestats.rb, line 182
def connect(db)
  return if connection and (@db == db)
  
  connection.close if @db == db
  
  tmp_config ={
    'host' => @config['host'],
    'user' => @config['user'],
    'password' => @config['password'],
    'dbname' => db
  }
  
  @connection = PG.connect(tmp_config)
  @db = db
end
get_aggregate_ganglia_double() click to toggle source
# File lib/postgrestats.rb, line 229
def get_aggregate_ganglia_double()
  return [
    aggregate_gauges.select{ |k, v| v.has_key?('ganglia_double') }.keys,
    aggregate_counters.select{ |k, v| v.has_key?('ganglia_double') }.keys
  ].flatten
end
get_aggregate_metrics() click to toggle source
# File lib/postgrestats.rb, line 218
def get_aggregate_metrics()
  return [aggregate_gauges.keys, aggregate_counters.keys].flatten
end
get_blocks_fetched_per_database(db) click to toggle source
  • number of blocks fetched per database

# File lib/postgrestats.rb, line 392
   def get_blocks_fetched_per_database(db)
     query = <<-END_GET_BLOCKS_FETCHED_PER_DATABASE_QUERY
       SELECT pg_stat_get_db_blocks_fetched(oid)
       FROM pg_database
       WHERE datname = '#{db}'
     END_GET_BLOCKS_FETCHED_PER_DATABASE_QUERY
     
     ret = run_query(db, query)
     
     return ret.first['pg_stat_get_db_blocks_fetched'].to_i
   end
get_blocks_hit_per_database(db) click to toggle source
  • number of blocks hit per database

# File lib/postgrestats.rb, line 406
   def get_blocks_hit_per_database(db)
     query = <<-END_GET_BLOCK_HITS_PER_DATABASE_QUERY
       SELECT pg_stat_get_db_blocks_hit(oid)
       FROM pg_database
       WHERE datname = '#{db}'
     END_GET_BLOCK_HITS_PER_DATABASE_QUERY
     
     ret = run_query(db, query)
     
     return ret.first['pg_stat_get_db_blocks_hit'].to_i
   end
get_commits_per_database(db) click to toggle source
  • number of commits per database

# File lib/postgrestats.rb, line 420
   def get_commits_per_database(db)
     query = <<-END_GET_COMMITS_PER_DATABASE_QUERY
       SELECT pg_stat_get_db_xact_commit(oid)
       FROM pg_database
       WHERE datname = '#{db}'
     END_GET_COMMITS_PER_DATABASE_QUERY
     
     ret = run_query(db, query)
     
     return ret.first['pg_stat_get_db_xact_commit'].to_i
   end
get_connections_per_database(db = nil) click to toggle source
  • number of connections

# File lib/postgrestats.rb, line 346
   def get_connections_per_database(db = nil)
     query = <<-END_GET_CONNECTIONS_PER_DATABASE_QUERY
       SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;
     END_GET_CONNECTIONS_PER_DATABASE_QUERY
     
     ret = run_query('postgres', query)
     
     ret.select!{ |v| not @config['exclude_dbs'].include?(v['datname']) }.map!{ |v| [v['datname'], v['count'].to_i] }
     
     if db
       res = Hash[*ret.flatten][db].to_i || 0
     else
       res = Hash[*ret.flatten]
     end
     
     return res
   end
get_databases() click to toggle source
# File lib/postgrestats.rb, line 236
  def get_databases()
    query = <<-END_GET_DATABASES_QUERY
      SELECT pg_database.datname AS "Database", pg_user.usename AS "Owner"
      FROM pg_database, pg_user
      WHERE pg_database.datdba = pg_user.usesysid
      UNION
      SELECT pg_database.datname AS "Database", NULL AS "Owner"
      FROM pg_database
      WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
      ORDER BY "Database"
    END_GET_DATABASES_QUERY
    
    ret = run_query('postgres', query)
    
    ret.select!{ |v| not @config['exclude_dbs'].include?(v['Database']) }.map!{ |v| v['Database'] }
    
    return ret
  end
get_dead_per_table(db) click to toggle source
  • number of dead rows per table

# File lib/postgrestats.rb, line 620
   def get_dead_per_table(db)
     query = <<-END_GET_DEAD_PER_TABLE_QUERY
       SELECT c.relname, pg_stat_get_dead_tuples(c.oid) FROM pg_catalog.pg_class c
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
       AND pg_catalog.pg_table_is_visible(c.oid)
     END_GET_DEAD_PER_TABLE_QUERY

     ret = run_query(db, query)

     ret.map!{ |v| [v['relname'], v['pg_stat_get_dead_tuples'].to_i] }

     return Hash[*ret.flatten]
   end
get_deletes_per_database(db) click to toggle source
  • number of rows deleted per database

# File lib/postgrestats.rb, line 479
   def get_deletes_per_database(db)
     query = <<-END_GET_DELETES_PER_DATABASE_QUERY
       SELECT pg_stat_get_db_tuples_deleted(oid)
       FROM pg_database
       WHERE datname = '#{db}'
     END_GET_DELETES_PER_DATABASE_QUERY
     
     ret = run_query(db, query)
     
     return ret.first['pg_stat_get_db_tuples_deleted'].to_i
   end
get_deletes_per_table(db) click to toggle source
  • number of deleted rows per table

# File lib/postgrestats.rb, line 603
   def get_deletes_per_table(db)
      query = <<-END_GET_DELETES_PER_TABLE_QUERY
        SELECT c.relname, pg_stat_get_tuples_deleted(c.oid) FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
        AND pg_catalog.pg_table_is_visible(c.oid)
      END_GET_DELETES_PER_TABLE_QUERY

      ret = run_query(db, query)

      ret.map!{ |v| [v['relname'], v['pg_stat_get_tuples_deleted'].to_i] }

      return Hash[*ret.flatten]
   end
get_fetched_per_table(db) click to toggle source
  • number of fetched rows per table

# File lib/postgrestats.rb, line 537
   def get_fetched_per_table(db)
      query = <<-END_GET_FETCHED_PER_TABLE_QUERY
        SELECT c.relname, pg_stat_get_tuples_fetched(c.oid) FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
        AND pg_catalog.pg_table_is_visible(c.oid)
      END_GET_FETCHED_PER_TABLE_QUERY

      ret = run_query(db, query)

      ret.map!{ |v| [v['relname'], v['pg_stat_get_tuples_fetched'].to_i] }

      return Hash[*ret.flatten]
   end
get_heap_blocks_hit_per_table(db) click to toggle source
# File lib/postgrestats.rb, line 705
   def get_heap_blocks_hit_per_table(db)
     query = <<-END_GET_HEAP_BLOCKS_HIT_PER_TABLE_QUERY
       SELECT stat.relname AS relname, heap_blks_hit
       FROM pg_stat_user_tables stat
       RIGHT JOIN pg_statio_user_tables statio ON stat.relid=statio.relid;
     END_GET_HEAP_BLOCKS_HIT_PER_TABLE_QUERY

     ret = run_query(db, query)

     ret.map!{ |v| [v['relname'], v['heap_blks_hit'].to_i] }

     return Hash[*ret.flatten]
   end
get_heap_blocks_read_per_table(db) click to toggle source
# File lib/postgrestats.rb, line 691
   def get_heap_blocks_read_per_table(db)
     query = <<-END_GET_HEAP_BLOCKS_READ_PER_TABLE_QUERY
       SELECT stat.relname AS relname, heap_blks_read
       FROM pg_stat_user_tables stat
       RIGHT JOIN pg_statio_user_tables statio ON stat.relid=statio.relid;
     END_GET_HEAP_BLOCKS_READ_PER_TABLE_QUERY

     ret = run_query(db, query)

     ret.map!{ |v| [v['relname'], v['heap_blks_read'].to_i] }

     return Hash[*ret.flatten]
   end
get_hot_updates_per_table(db) click to toggle source
# File lib/postgrestats.rb, line 586
   def get_hot_updates_per_table(db)
      query = <<-END_GET_HOT_UPDATES_PER_TABLE_QUERY
        SELECT c.relname, pg_stat_get_tuples_hot_updated(c.oid) FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
        AND pg_catalog.pg_table_is_visible(c.oid)
      END_GET_HOT_UPDATES_PER_TABLE_QUERY

      ret = run_query(db, query)

      ret.map!{ |v| [v['relname'], v['pg_stat_get_tuples_hot_updated'].to_i] }

      return Hash[*ret.flatten]
   end
get_index_blocks_hit_per_table(db) click to toggle source
# File lib/postgrestats.rb, line 733
   def get_index_blocks_hit_per_table(db)
     query = <<-END_GET_INDEX_BLOCKS_HIT_PER_TABLE_QUERY
       SELECT stat.relname AS relname, idx_blks_hit
       FROM pg_stat_user_tables stat
       RIGHT JOIN pg_statio_user_tables statio ON stat.relid=statio.relid;
     END_GET_INDEX_BLOCKS_HIT_PER_TABLE_QUERY

     ret = run_query(db, query)

     ret.map!{ |v| [v['relname'], v['idx_blks_hit'].to_i] }

     return Hash[*ret.flatten]
   end
get_index_blocks_read_per_table(db) click to toggle source
# File lib/postgrestats.rb, line 719
   def get_index_blocks_read_per_table(db)
     query = <<-END_GET_INDEX_BLOCKS_READ_PER_TABLE_QUERY
       SELECT stat.relname AS relname, idx_blks_read
       FROM pg_stat_user_tables stat
       RIGHT JOIN pg_statio_user_tables statio ON stat.relid=statio.relid;
     END_GET_INDEX_BLOCKS_READ_PER_TABLE_QUERY

     ret = run_query(db, query)

     ret.map!{ |v| [v['relname'], v['idx_blks_read'].to_i] }

     return Hash[*ret.flatten]
   end
get_index_rows_fetched_per_table(db) click to toggle source
# File lib/postgrestats.rb, line 677
   def get_index_rows_fetched_per_table(db)
     query = <<-END_GET_INDEX_ROWS_FETCHED_PER_TABLE_QUERY
       SELECT stat.relname AS relname, idx_tup_fetch
       FROM pg_stat_user_tables stat
       RIGHT JOIN pg_statio_user_tables statio ON stat.relid=statio.relid;
     END_GET_INDEX_ROWS_FETCHED_PER_TABLE_QUERY

     ret = run_query(db, query)

     ret.map!{ |v| [v['relname'], v['idx_tup_fetch'].to_i] }

     return Hash[*ret.flatten]
   end
get_index_scans_per_table(db) click to toggle source
# File lib/postgrestats.rb, line 663
   def get_index_scans_per_table(db)
     query = <<-END_GET_INDEX_SCANS_PER_TABLE_QUERY
       SELECT stat.relname AS relname, idx_scan
       FROM pg_stat_user_tables stat
       RIGHT JOIN pg_statio_user_tables statio ON stat.relid=statio.relid;
     END_GET_INDEX_SCANS_PER_TABLE_QUERY

     ret = run_query(db, query)

     ret.map!{ |v| [v['relname'], v['idx_scan'].to_i] }

     return Hash[*ret.flatten]
   end
get_index_sizes(db) click to toggle source
  • size of indexes

# File lib/postgrestats.rb, line 286
   def get_index_sizes(db)
     query = <<-END_GET_INDEX_SIZES_QUERY
       SELECT table_name,(pg_total_relation_size(table_name) - pg_relation_size(table_name)) AS size
       FROM information_schema.tables
       WHERE table_schema NOT IN ('information_schema', 'pg_catalog') AND table_type = 'BASE TABLE'
     END_GET_INDEX_SIZES_QUERY
     
     ret = run_query(db, query)
     
     ret.map!{ |v| [v['table_name'], v['size'].to_i] }
     
     return Hash[*ret.flatten]
   end
get_inserts_per_database(db) click to toggle source
  • number of disk block reads per database

  • number of buffer hits per database

  • number of rows returned per database

  • number of rows inserted per database

# File lib/postgrestats.rb, line 451
   def get_inserts_per_database(db)
     query = <<-END_GET_INSERTS_PER_DATABASE_QUERY
       SELECT pg_stat_get_db_tuples_inserted(oid)
       FROM pg_database
       WHERE datname = '#{db}'
     END_GET_INSERTS_PER_DATABASE_QUERY
     
     ret = run_query(db, query)
     
     return ret.first['pg_stat_get_db_tuples_inserted'].to_i
   end
get_inserts_per_table(db) click to toggle source
  • number of inserted rows per table

# File lib/postgrestats.rb, line 554
   def get_inserts_per_table(db)
      query = <<-END_GET_INSERTS_PER_TABLE_QUERY
        SELECT c.relname, pg_stat_get_tuples_inserted(c.oid) FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
        AND pg_catalog.pg_table_is_visible(c.oid)
      END_GET_INSERTS_PER_TABLE_QUERY

      ret = run_query(db, query)

      ret.map!{ |v| [v['relname'], v['pg_stat_get_tuples_inserted'].to_i] }

      return Hash[*ret.flatten]
   end
get_last_autovacuum_per_table(db) click to toggle source
  • dead unused space taken up in a table or index

  • number of seconds since the last checkpoint per database

  • number of seconds since the last autovacuum per database/table

# File lib/postgrestats.rb, line 368
   def get_last_autovacuum_per_table(db)
     query = <<-END_GET_LAST_AUTOVACUUM_PER_DATABASE_QUERY
       SELECT current_database() AS datname, nspname AS sname, relname AS tname,
         CASE WHEN v IS NULL THEN -1 ELSE round(extract(epoch FROM now()-v)) END AS ltime
       FROM (
         SELECT nspname, relname, pg_stat_get_last_autovacuum_time(c.oid) AS v
         FROM pg_class c, pg_namespace n
         WHERE relkind = 'r'
         AND n.oid = c.relnamespace
         AND n.nspname <> 'information_schema'
         AND n.nspname = 'public'
         ORDER BY 3
        ) AS foo
     END_GET_LAST_AUTOVACUUM_PER_DATABASE_QUERY
     
     ret = run_query(db, query)
     
     ret.map!{ |v| [v['tname'], v['ltime']] }
     
     return Hash[*ret.flatten]
   end
get_locks_per_database(db) click to toggle source
  • list of locks

# File lib/postgrestats.rb, line 496
   def get_locks_per_database(db)
     query = <<-END_GET_LOCKS_PER_DATABASE_QUERY
       SELECT pid, virtualxid, datname, relname, locktype, mode
       FROM pg_locks l 
       LEFT JOIN pg_database d ON (d.oid=l.database)
       LEFT JOIN pg_class c on (c.oid=l.relation)
       WHERE datname = '#{db}' AND NOT relname ~ 'pg_'
     END_GET_LOCKS_PER_DATABASE_QUERY
     
     ret = run_query(db, query)
     
     return ret
   end
get_number_of_locks_per_database(db) click to toggle source
  • number of locks

# File lib/postgrestats.rb, line 511
def get_number_of_locks_per_database(db)
  return get_locks_per_database(db).size
end
get_returned_per_table(db) click to toggle source
  • number of returned rows per table

# File lib/postgrestats.rb, line 520
   def get_returned_per_table(db)
      query = <<-END_GET_RETURNED_PER_TABLE_QUERY
        SELECT c.relname, pg_stat_get_tuples_returned(c.oid) FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
        AND pg_catalog.pg_table_is_visible(c.oid)
      END_GET_RETURNED_PER_TABLE_QUERY

      ret = run_query(db, query)

      ret.map!{ |v| [v['relname'], v['pg_stat_get_tuples_returned'].to_i] }

      return Hash[*ret.flatten]
   end
get_rollbacks_per_database(db) click to toggle source
  • number of rollbacks per database

# File lib/postgrestats.rb, line 434
   def get_rollbacks_per_database(db)
     query = <<-END_GET_ROLLBACKS_PER_DATABASE_QUERY
       SELECT pg_stat_get_db_xact_rollback(oid)
       FROM pg_database
       WHERE datname = '#{db}'
     END_GET_ROLLBACKS_PER_DATABASE_QUERY
     
     ret = run_query(db, query)
     
     return ret.first['pg_stat_get_db_xact_rollback'].to_i
   end
get_sequential_rows_read_per_table(db) click to toggle source
# File lib/postgrestats.rb, line 649
   def get_sequential_rows_read_per_table(db)
     query = <<-END_GET_SEQ_ROWS_READ_PER_TABLE_QUERY
       SELECT stat.relname AS relname, seq_tup_read
       FROM pg_stat_user_tables stat
       RIGHT JOIN pg_statio_user_tables statio ON stat.relid=statio.relid;
     END_GET_SEQ_ROWS_READ_PER_TABLE_QUERY

     ret = run_query(db, query)

     ret.map!{ |v| [v['relname'], v['seq_tup_read'].to_i] }

     return Hash[*ret.flatten]
   end
get_sequential_scans_per_table(db) click to toggle source
# File lib/postgrestats.rb, line 635
   def get_sequential_scans_per_table(db)
     query = <<-END_GET_SEQ_SCANS_PER_TABLE_QUERY
       SELECT stat.relname AS relname, seq_scan
       FROM pg_stat_user_tables stat
       RIGHT JOIN pg_statio_user_tables statio ON stat.relid=statio.relid;
     END_GET_SEQ_SCANS_PER_TABLE_QUERY

     ret = run_query(db, query)

     ret.map!{ |v| [v['relname'], v['seq_scan'].to_i] }

     return Hash[*ret.flatten]
   end
get_table_estimated_rows(db, table) click to toggle source
  • estimated rows per database/table

# File lib/postgrestats.rb, line 317
   def get_table_estimated_rows(db, table)
     query = <<-END_GET_TABLE_ESTIMATED_ROWS_QUERY
       SELECT (
         CASE 
         WHEN reltuples > 0 THEN pg_relation_size('#{table}')/(8192*relpages::bigint/reltuples)
         ELSE 0
         END
       )::bigint AS estimated_row_count
       FROM pg_class
       WHERE oid = '#{table}'::regclass;
     END_GET_TABLE_ESTIMATED_ROWS_QUERY
     
     ret = run_query(db, query)
     
     return ret.first['estimated_row_count'].to_i
   end
get_table_ganglia_double() click to toggle source
# File lib/postgrestats.rb, line 222
def get_table_ganglia_double()
  return [
    table_gauges.select{ |k, v| v.has_key?('ganglia_double') }.keys,
    table_counters.select{ |k, v| v.has_key?('ganglia_double') }.keys
  ].flatten
end
get_table_metrics() click to toggle source
# File lib/postgrestats.rb, line 214
def get_table_metrics()
  return [table_gauges.keys, table_counters.keys].flatten
end
get_table_sizes(db) click to toggle source
  • size per database/table

# File lib/postgrestats.rb, line 271
   def get_table_sizes(db)
     query = <<-END_GET_TABLE_SIZES_QUERY
       SELECT table_name,pg_relation_size(table_name) AS size
       FROM information_schema.tables
       WHERE table_schema NOT IN ('information_schema', 'pg_catalog') AND table_type = 'BASE TABLE'
     END_GET_TABLE_SIZES_QUERY
     
     ret = run_query(db, query)
     
     ret.map!{ |v| [v['table_name'], v['size'].to_i] }
     
     return Hash[*ret.flatten]
   end
get_tables(db) click to toggle source
# File lib/postgrestats.rb, line 255
  def get_tables(db)
    query = <<-END_GET_TABLES_QUERY
      SELECT c.relname FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
    END_GET_TABLES_QUERY
    
    ret = run_query(db, query)
    
    ret.map!{ |v| v['relname'] }
    
    return ret
  end
get_tables_estimated_rows(db) click to toggle source
  • estimated rows for every table in the database

# File lib/postgrestats.rb, line 335
def get_tables_estimated_rows(db)
  ret = {}
  
  get_tables(db).each do |table|
     ret[table] = get_table_estimated_rows(db, table)
  end
  
  return ret
end
get_updates_per_database(db) click to toggle source
  • number of rows updates per database

# File lib/postgrestats.rb, line 465
   def get_updates_per_database(db)
     query = <<-END_GET_UPDATES_PER_DATABASE_QUERY
       SELECT pg_stat_get_db_tuples_updated(oid)
       FROM pg_database
       WHERE datname = '#{db}'
     END_GET_UPDATES_PER_DATABASE_QUERY
     
     ret = run_query(db, query)
     
     return ret.first['pg_stat_get_db_tuples_updated'].to_i
   end
get_updates_per_table(db) click to toggle source
  • number of updates rows per table

# File lib/postgrestats.rb, line 571
   def get_updates_per_table(db)
      query = <<-END_GET_UPDATES_PER_TABLE_QUERY
        SELECT c.relname, pg_stat_get_tuples_updated(c.oid) FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
        AND pg_catalog.pg_table_is_visible(c.oid)
      END_GET_UPDATES_PER_TABLE_QUERY

      ret = run_query(db, query)

      ret.map!{ |v| [v['relname'], v['pg_stat_get_tuples_updated'].to_i] }

      return Hash[*ret.flatten]
   end
initialize_log() click to toggle source
# File lib/postgrestats.rb, line 19
def initialize_log
  @config['log'] = {
    'file' => STDOUT,
    'level' => 'INFO'
  }.merge(@config['log'] || {})
  
  log_initialize = [@config['log']['file']]
  log_initialize << @config['log']['shift_age'] if @config['log']['shift_age']
  log_initialize << @config['log']['shift_size'] if @config['log']['shift_size']
  
  begin
    @log = Logger.new(*log_initialize)
    @log.level = Logger.const_get(@config['log']['level'])
  rescue Exception => e
    @config['log'] = {
      'file' => STDOUT,
      'level' => 'INFO'
    }
    @log = Logger.new(@config['log']['file'])
    @log.level = Logger.const_get(@config['log']['level'])
    @log.error("Caught a problem with log settings")
    @log.error("#{e.message}")
    @log.error("Setting log settings to defaults")
  end
end
initialize_metrics() click to toggle source
# File lib/postgrestats.rb, line 45
def initialize_metrics
  @table_gauges = {
    'table_size' => {
      'units' => 'bytes',
      'handler' => :get_table_sizes
    },
    'index_size' => {
      'units' => 'bytes',
      'handler' => :get_index_sizes
    },
    'estimated_rows' => {
      'units' => 'rows',
      'handler' => :get_tables_estimated_rows
    },
    'dead' => {
      'units' => 'rows',
      'handler' => :get_dead_per_table
    }, 
    'last_vacuum' => {
      'units' => 'seconds since',
      'handler' => :get_last_autovacuum_per_table
    }
  }
  
  @table_counters = {
    'inserts' => {
      'units' => 'inserts/s',
      'ganglia_double' => true,
      'handler' => :get_inserts_per_table
    }, 
    'updates' => {
      'units' => 'updates/s',
      'ganglia_double' => true,
      'handler' => :get_updates_per_table
    },
    'deletes' => {
      'units' => 'deletes/s',
      'ganglia_double' => true,
      'handler' => :get_deletes_per_table
    },
    'hot_updates' => {
      'units' => 'updates/s',
      'ganglia_double' => true,
      'handler' => :get_hot_updates_per_table
    },
    'seq_scan' => {
      'units' => 'scans/s',
      'ganglia_double' => true,
      'handler' => :get_sequential_scans_per_table
    },
    'idx_scan' => {
      'units' => 'scans/s',
      'ganglia_double' => true,
      'handler' => :get_index_scans_per_table
    },
    'seq_tup_read' => {
      'units' => 'rows/s',
      'ganglia_double' => true,
      'handler' => :get_sequential_rows_read_per_table
    },
    'idx_tup_fetch' => {
      'units' => 'rows/s',
      'ganglia_double' => true,
      'handler' => :get_index_rows_fetched_per_table
    },
    'heap_blks_read' => {
      'units' => 'blocks/s',
      'ganglia_double' => true,
      'handler' => :get_heap_blocks_read_per_table
    },
    'heap_blks_hit' => {
      'units' => 'blocks/s',
      'ganglia_double' => true,
      'handler' => :get_heap_blocks_hit_per_table
    },
    'idx_blks_read' => {
      'units' => 'blocks/s',
      'ganglia_double' => true,
      'handler' => :get_index_blocks_read_per_table
    },
    'idx_blks_hit' => {
      'units' => 'blocks/s',
      'ganglia_double' => true,
      'handler' => :get_index_blocks_hit_per_table
    }
  }
  
  @aggregate_gauges = {
    'locks' => {
      'units' => 'locks',
      'handler' => :get_number_of_locks_per_database
    },
    'connections' => {
      'units' => 'connections',
      'handler' => :get_connections_per_database
    }
  }
  
  @aggregate_counters = {
    'blocks_fetched' => {
      'units' => 'blocks/s',
      'ganglia_double' => true,
      'handler' => :get_blocks_fetched_per_database
    },
    'blocks_hit' => {
      'units' => 'blocks/s',
      'ganglia_double' => true,
      'handler' => :get_blocks_hit_per_database
    },
    'commits' => {
      'units' => 'commits/s',
      'ganglia_double' => true,
      'handler' => :get_commits_per_database
    },
    'rollbacks' => {
      'units' => 'rollbacks/s',
      'ganglia_double' => true,
      'handler' => :get_rollbacks_per_database
    },
    'inserts' => {
      'units' => 'inserts/s',
      'ganglia_double' => true,
      'handler' => :get_inserts_per_database
    },
    'updates' => {
      'units' => 'updates/s',
      'ganglia_double' => true,
      'handler' => :get_updates_per_database
    },
    'deletes' => {
      'units' => 'deletes/s',
      'ganglia_double' => true,
      'handler' => :get_deletes_per_database
    }
  }
end
run_query(db, query) click to toggle source
# File lib/postgrestats.rb, line 198
def run_query(db, query)
  connect(db)
  
  result = @connection.exec(query)
  fields = result.fields
  ret = []
  
  result.each do |row|
    item = {}
    fields.each { |field| item[field] = row[field] }
    ret << item
  end
  
  return ret
end