def __init__(self,in_db_conn,in_prod_conn,in_id=None):
super(TableName,self).__init__(in_db_conn,in_id)
self.table='table_name'
self.toast_id=None
self.sub_table='index_name'
self.sub_fk='tn_id'
self.prod_conn=in_prod_conn
if in_id:
self._populate()
self.stat_obj=table.genericStat(self.db_conn,'table_stat','tn_id',in_id)
self.va_stat_obj=table.genericStat(self.db_conn,'table_va_stat','tn_id',in_id)
self.stat_query="""SELECT
pg_relation_size(oid) AS tbl_size,
reltuples::bigint AS tbl_tuples,
pg_stat_get_numscans(oid) AS seq_scan,
pg_stat_get_tuples_returned(oid) AS seq_tup_read,
pg_stat_get_tuples_fetched(oid) AS seq_tup_fetch,
pg_stat_get_tuples_inserted(oid) AS n_tup_ins,
pg_stat_get_tuples_updated(oid) AS n_tup_upd,
pg_stat_get_tuples_deleted(oid) AS n_tup_del,
pg_stat_get_tuples_hot_updated(oid) AS n_tup_hot_upd,
pg_stat_get_live_tuples(oid) AS n_live_tup,
pg_stat_get_dead_tuples(oid) AS n_dead_tup,
pg_stat_get_blocks_fetched(oid) AS heap_blks_fetch,
pg_stat_get_blocks_hit(oid) AS heap_blks_hit
FROM pg_class
WHERE oid={0}""".format(self.db_fields['obj_oid'])
self.va_stat_query="""SELECT
pg_stat_get_last_vacuum_time(oid) AS last_vacuum,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum,
pg_stat_get_last_analyze_time(oid) AS last_analyze,
pg_stat_get_last_autoanalyze_time(oid) AS last_autoanalyze
FROM pg_class WHERE oid={0}""".format(self.db_fields['obj_oid'])
def __init__(self,in_db_conn,in_prod_dsn,in_id=None):
super(DatabaseName,self).__init__(in_db_conn,in_id)
self.table='database_name'
self.set_prod_dsn(in_prod_dsn)
self.sub_table='schema_name'
self.sub_fk='dn_id'
if in_id:
self._populate()
self.stat_obj=table.genericStat(self.db_conn,'database_stat','dn_id',in_id)
self.runtime_stat_obj=table.genericStat(self.db_conn,'db_runtime_stat','dn_id',in_id)
self.stat_query="""SELECT
pg_database_size(oid) AS db_size,
pg_stat_get_db_xact_commit(oid) AS xact_commit,
pg_stat_get_db_xact_rollback(oid) AS xact_rollback,
pg_stat_get_db_blocks_fetched(oid) AS blks_fetch,
pg_stat_get_db_blocks_hit(oid) AS blks_hit,
pg_stat_get_db_tuples_returned(oid) AS tup_returned,
pg_stat_get_db_tuples_fetched(oid) AS tup_fetched,
pg_stat_get_db_tuples_inserted(oid) AS tup_inserted,
pg_stat_get_db_tuples_updated(oid) AS tup_updated,
pg_stat_get_db_tuples_deleted(oid) AS tup_deleted
FROM pg_database
WHERE oid ={0}""".format(self.db_fields['obj_oid'])
self.runtime_stat_query="""SELECT
COALESCE(MAX(current_timestamp-query_start),now()-now()) AS max_interval_query_dur,
COALESCE(AVG(current_timestamp-query_start),now()-now()) AS avg_interval_query_dur,
CAST(COALESCE(EXTRACT(epoch FROM MAX(current_timestamp-query_start)),0) AS INTEGER) AS max_sec_query_dur,
CAST(COALESCE(EXTRACT(epoch FROM AVG(current_timestamp-query_start)),0) AS INTEGER) AS avg_sec_query_dur,
MAX(age(datfrozenxid)) AS tx_max_age
FROM pg_stat_activity psa
JOIN pg_database pd ON psa.datid=pd.oid
WHERE pd.oid='{0}'""".format(self.db_fields['obj_oid'])
def _initialyze(self, in_id):
self._populate()
self.prod_dsn = self.get_conn_string()
self.stat_obj = table.genericStat(self.db_conn, "bgwriter_stat", "hc_id", in_id)
self.runtime_stat_obj = table.genericStat(self.db_conn, "cluster_runtime_stat", "hc_id", in_id)
self.sub_table = "database_name"
self.sub_fk = "hc_id"
self.stat_query = """SELECT
pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_written_backend() AS buffers_backend,
pg_stat_get_buf_alloc() AS buffers_alloc"""
self.runtime_stat_query = """SELECT current_setting('max_connections') AS conn_total,COUNT(*) AS conn_used
def __init__(self,in_db_conn,in_prod_conn,in_id=None):
super(FunctionName,self).__init__(in_db_conn,in_id)
self.table='function_name'
self.prod_conn=in_prod_conn
if in_id:
self._populate()
self.stat_obj=table.genericStat(self.db_conn,'function_stat','fn_id',in_id)
self.stat_query="""SELECT
pg_stat_get_function_calls(oid) AS func_calls,
pg_stat_get_function_total_time(oid) AS total_time,
pg_stat_get_function_self_time(oid) AS self_time
FROM pg_proc
WHERE oid={0}""".format(self.db_fields['pro_oid'])
def __init__(self,in_db_conn,in_prod_conn,in_id=None):
super(IndexName,self).__init__(in_db_conn,in_id)
self.table='index_name'
self.prod_conn=in_prod_conn
if in_id:
self._populate()
self.stat_obj=table.genericStat(self.db_conn,'index_stat','in_id',in_id)
self.stat_query="""SELECT
pg_relation_size(oid) AS idx_size,
pg_stat_get_numscans(oid) AS idx_scan,
pg_stat_get_tuples_returned(oid) AS idx_tup_read,
pg_stat_get_tuples_fetched(oid) AS idx_tup_fetch,
pg_stat_get_blocks_fetched(oid) AS idx_blks_fetch,
pg_stat_get_blocks_hit(oid) AS idx_blks_hit
FROM pg_class WHERE oid={0}""".format(self.db_fields['obj_oid'])
def va_stat(self,time_id):
va_stat=genericStat()
va_stat.set_fk_field('tn_id')
va_stat.set_table_name('table_va_stat')
va_stat.set_fk_value(self.id)
va_stat.set_time_id(time_id)
va_stat_q="""SELECT
pg_stat_get_last_vacuum_time(oid) AS last_vacuum,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum,
pg_stat_get_last_analyze_time(oid) AS last_analyze,
pg_stat_get_last_autoanalyze_time(oid) AS last_autoanalyze
FROM pg_class WHERE oid={0}""".format(self.db_fields['obj_oid'])
try:
self.prod_cursor.execute(va_stat_q)
except Exception, e:
logger.warning("Details: {0}".format(e.pgerror))
return
def __init__(self,in_db_conn,in_prod_conn,in_id=None):
super(TableToastName,self).__init__(in_db_conn,in_id)
self.table='table_toast_name'
self.toast_idx_id=None
self.prod_conn=in_prod_conn
if in_id:
self._populate()
self.stat_obj=table.genericStat(self.db_conn,'table_toast_stat','ttn_id',in_id)
self.stat_query="""SELECT
pg_relation_size(oid) AS ttbl_size,
pg_stat_get_numscans(oid) AS seq_scan,
pg_stat_get_tuples_returned(oid) AS seq_tup_read,
pg_stat_get_tuples_fetched(oid) AS seq_tup_fetch,
pg_stat_get_tuples_inserted(oid) AS n_tup_ins,
pg_stat_get_tuples_updated(oid) AS n_tup_upd,
pg_stat_get_tuples_deleted(oid) AS n_tup_del,
pg_stat_get_tuples_hot_updated(oid) AS n_tup_hot_upd,
pg_stat_get_live_tuples(oid) AS n_live_tup,
pg_stat_get_dead_tuples(oid) AS n_dead_tup,
pg_stat_get_blocks_fetched(oid) AS heap_blks_fetch,
pg_stat_get_blocks_hit(oid) AS heap_blks_hit
FROM pg_class
WHERE oid={0}""".format(self.db_fields['obj_oid'])
