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'])