def getTableData(host, name, interval = None): conn = datadb.getDataConnection() cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute(getSingleTableSql(host,name,interval)) d = { 'table_size' : [], 'index_size' : [], 'seq_scans': [], 'index_scans' : [], 'ins':[], 'upd':[], 'del':[], 'hot':[] } last_is = None last_ss = None last_ins = None last_del = None last_upd = None last_hot = None last_timestamp = 0 for r in cur: d['table_size'].append ( ( r['tsd_timestamp'] , r['tsd_table_size'] ) ) d['index_size'].append ( ( r['tsd_timestamp'] , r['tsd_index_size'] ) ) if int(time.mktime(r['tsd_timestamp'].timetuple()) * 1000) - last_timestamp <= ( 15*60*1000 ): if last_ss != None: d['seq_scans'].append ( ( r['tsd_timestamp'] , r['tsd_seq_scans']-last_ss ) ) if last_is != None: d['index_scans'].append( ( r['tsd_timestamp'] , r['tsd_index_scans'] - last_is ) ) if last_ins != None and last_ins != 0: d['ins'].append( ( r['tsd_timestamp'] , r['tsd_tup_ins'] - last_ins ) ) if last_del != None and last_del != 0: d['del'].append( ( r['tsd_timestamp'] , r['tsd_tup_del'] - last_del ) ) if last_upd != None and last_upd != 0: d['upd'].append( ( r['tsd_timestamp'] , r['tsd_tup_upd'] - last_upd ) ) if last_hot != None and last_hot != 0: d['hot'].append( ( r['tsd_timestamp'] , r['tsd_tup_hot_upd'] - last_hot ) ) last_is = r['tsd_index_scans'] last_ss = r['tsd_seq_scans'] last_ins = r['tsd_tup_ins'] last_del = r['tsd_tup_del'] last_upd = r['tsd_tup_upd'] last_hot = r['tsd_tup_hot_upd'] last_timestamp = int(time.mktime(r['tsd_timestamp'].timetuple()) * 1000) cur.close() datadb.closeDataConnection(conn) return d
def getTableData(host, name, interval = None): conn = datadb.getDataConnection() cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute(getSingleTableSql(host,name,interval)) d = { 'table_size' : [], 'index_size' : [], 'seq_scans': [], 'index_scans' : [], 'ins':[], 'upd':[], 'del':[], 'hot':[] } last_is = None last_ss = None last_ins = None last_del = None last_upd = None last_hot = None last_timestamp = 0 for r in cur: d['table_size'].append ( ( r['tsd_timestamp'] , r['tsd_table_size'] ) ) d['index_size'].append ( ( r['tsd_timestamp'] , r['tsd_index_size'] ) ) if last_ss != None: d['seq_scans'].append ( ( r['tsd_timestamp'] , r['tsd_seq_scans']-last_ss ) ) if last_is != None: d['index_scans'].append( ( r['tsd_timestamp'] , r['tsd_index_scans'] - last_is ) ) if last_ins != None and last_ins != 0: d['ins'].append( ( r['tsd_timestamp'] , r['tsd_tup_ins'] - last_ins ) ) if last_del != None and last_del != 0: d['del'].append( ( r['tsd_timestamp'] , r['tsd_tup_del'] - last_del ) ) if last_upd != None and last_upd != 0: d['upd'].append( ( r['tsd_timestamp'] , r['tsd_tup_upd'] - last_upd ) ) if last_hot != None and last_hot != 0: d['hot'].append( ( r['tsd_timestamp'] , r['tsd_tup_hot_upd'] - last_hot ) ) last_is = r['tsd_index_scans'] last_ss = r['tsd_seq_scans'] last_ins = r['tsd_tup_ins'] last_del = r['tsd_tup_del'] last_upd = r['tsd_tup_upd'] last_hot = r['tsd_tup_hot_upd'] last_timestamp = int(time.mktime(r['tsd_timestamp'].timetuple()) * 1000) cur.close() datadb.closeDataConnection(conn) return d
def getIndexesDataForTable(host, full_name, date_from, date_to): conn = datadb.getDataConnection() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute(getSingleTableSql(host, full_name, date_from, date_to)) data = cur.fetchall() cur.close() datadb.closeDataConnection(conn) all_data = [] d = { 'size' : [], 'scan' : [], 'tup_read': [] } #, 'tup_fetch' : [] } last_scan = None last_tup_read = None # last_tup_fetch = None last_name = None last_index_size = None last_total_end_size = None last_pct_of_total_end_size = None for r in data: if last_name != None: if last_name != r['name'] and len(d['size']) > 0: all_data.append({'index_name':last_name, 'data':d, 'last_index_size': round(last_index_size / 1024**2), 'total_end_size': round(last_total_end_size / 1024**2), 'pct_of_total_end_size':last_pct_of_total_end_size}) d = { 'size' : [], 'scan' : [], 'tup_read': [] } # , 'tup_fetch' : [] } d['size'].append( ( r['timestamp'] , r['size'] ) ) d['scan'].append( ( r['timestamp'] , 0 if last_scan > r['scan'] else r['scan'] - last_scan ) ) d['tup_read'].append( ( r['timestamp'] , 0 if last_tup_read > r['tup_read'] else r['tup_read'] - last_tup_read ) ) # d['tup_fetch'].append( ( r['timestamp'] , 0 if last_tup_fetch > r['tup_fetch'] else r['tup_fetch'] - last_tup_fetch ) ) last_scan = r['scan'] last_tup_read = r['tup_read'] # last_tup_fetch = r['tup_fetch'] last_name = r['name'] last_index_size = r['size'] last_total_end_size = r['total_end_size'] last_pct_of_total_end_size = r['pct_of_total_end_size'] if len(d) > 0: all_data.append({'index_name':last_name, 'data':d, 'last_index_size': round(last_index_size / 1024**2), 'total_end_size': round(last_total_end_size / 1024**2), 'pct_of_total_end_size':last_pct_of_total_end_size}) return all_data
def getIndexesDataForTable(host, full_name, date_from, date_to): conn = datadb.getDataConnection() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute(getSingleTableSql(host, full_name, date_from, date_to)) data = cur.fetchall() cur.close() datadb.closeDataConnection(conn) all_data = [] d = { 'size' : [], 'scan' : [], 'tup_read': [] } #, 'tup_fetch' : [] } last_scan = None last_tup_read = None # last_tup_fetch = None last_name = None last_index_size = 0 last_total_end_size = 0 last_pct_of_total_end_size = 0 for r in data: if last_name != None: if last_name != r['name'] and len(d['size']) > 0: all_data.append({'index_name':last_name, 'data':d, 'last_index_size': round(last_index_size / 1024**2), 'total_end_size': round(last_total_end_size / 1024**2), 'pct_of_total_end_size':last_pct_of_total_end_size}) d = { 'size' : [], 'scan' : [], 'tup_read': [] } # , 'tup_fetch' : [] } d['size'].append( ( r['timestamp'] , r['size'] ) ) d['scan'].append( ( r['timestamp'] , 0 if last_scan > r['scan'] else r['scan'] - last_scan ) ) d['tup_read'].append( ( r['timestamp'] , 0 if last_tup_read > r['tup_read'] else r['tup_read'] - last_tup_read ) ) # d['tup_fetch'].append( ( r['timestamp'] , 0 if last_tup_fetch > r['tup_fetch'] else r['tup_fetch'] - last_tup_fetch ) ) last_scan = r['scan'] last_tup_read = r['tup_read'] # last_tup_fetch = r['tup_fetch'] last_name = r['name'] last_index_size = r['size'] last_total_end_size = r['total_end_size'] last_pct_of_total_end_size = r['pct_of_total_end_size'] if len(data) > 0: all_data.append({'index_name':last_name, 'data':d, 'last_index_size': round(last_index_size / 1024**2), 'total_end_size': round(last_total_end_size / 1024**2), 'pct_of_total_end_size':last_pct_of_total_end_size}) return all_data
def getActiveSprocsOrderedBy( hostId, order = " ORDER BY SUM(delta_total_time) DESC"): sql = """SELECT sproc_name FROM ( """ + viewSprocs(hostId) + """ ) t JOIN monitor_data.sprocs ON sp_sproc_id = sproc_id WHERE sproc_host_id = """ + str(adapt(hostId)) + """ GROUP BY sproc_name """ + order + """; """ conn = datadb.getDataConnection() cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) list= [] cur.execute( sql ) for r in cur: list.append ( r['sproc_name'] ) cur.close() datadb.closeDataConnection(conn) return list
def getTableIOData(host, name, interval = None): conn = datadb.getDataConnection() cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute(getSingleTableIOSql(host,name,interval)) d = { 'heap_read' : [], 'heap_hit' : [], 'index_read' : [], 'index_hit': [] } last_hr = None last_hh = None last_ir = None last_ih = None last_timestamp = 0 for r in cur: if int(time.mktime(r['tio_timestamp'].timetuple()) * 1000) - last_timestamp <= ( 15*60*1000 ): if last_hr != None: d['heap_read'].append(( r['tio_timestamp'] , r['tio_heap_read'] - last_hr )) if last_hh != None: d['heap_hit'].append(( r['tio_timestamp'] , r['tio_heap_hit'] - last_hh )) if last_ir != None: d['index_read'].append(( r['tio_timestamp'] , r['tio_idx_read'] - last_ir )) if last_ih != None: d['index_hit'].append(( r['tio_timestamp'] , r['tio_idx_hit'] - last_ih )) last_hr = r['tio_heap_read'] last_hh = r['tio_heap_hit'] last_ir = r['tio_idx_read'] last_ih = r['tio_idx_hit'] last_timestamp = int(time.mktime(r['tio_timestamp'].timetuple()) * 1000) cur.close() datadb.closeDataConnection(conn) return d
def getTableIOData(host, name, interval=None): conn = datadb.getDataConnection() cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute(getSingleTableIOSql(host, name, interval)) d = {'heap_read': [], 'heap_hit': [], 'index_read': [], 'index_hit': []} last_hr = None last_hh = None last_ir = None last_ih = None for r in cur: if last_hr != None: d['heap_read'].append( (r['tio_timestamp'], r['tio_heap_read'] - last_hr)) if last_hh != None: d['heap_hit'].append( (r['tio_timestamp'], r['tio_heap_hit'] - last_hh)) if last_ir != None: d['index_read'].append( (r['tio_timestamp'], r['tio_idx_read'] - last_ir)) if last_ih != None: d['index_hit'].append( (r['tio_timestamp'], r['tio_idx_hit'] - last_ih)) last_hr = r['tio_heap_read'] last_hh = r['tio_heap_hit'] last_ir = r['tio_idx_read'] last_ih = r['tio_idx_hit'] cur.close() datadb.closeDataConnection(conn) return d
def getIndexesDataForTable(host, full_name, date_from, date_to): conn = datadb.getDataConnection() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute(getSingleTableSql(host, full_name, date_from, date_to)) data = cur.fetchall() cur.close() datadb.closeDataConnection(conn) all_data = [] d = {"size": [], "scan": [], "tup_read": []} # , 'tup_fetch' : [] } last_scan = None last_tup_read = None # last_tup_fetch = None last_name = None last_index_size = 0 last_total_end_size = 0 last_pct_of_total_end_size = 0 for r in data: if last_name != None: if last_name != r["name"] and len(d["size"]) > 0: all_data.append( { "index_name": last_name, "data": d, "last_index_size": round(last_index_size / 1024 ** 2), "total_end_size": round(last_total_end_size / 1024 ** 2), "pct_of_total_end_size": last_pct_of_total_end_size, } ) d = {"size": [], "scan": [], "tup_read": []} # , 'tup_fetch' : [] } d["size"].append((r["timestamp"], r["size"])) d["scan"].append((r["timestamp"], 0 if last_scan > r["scan"] else r["scan"] - last_scan)) d["tup_read"].append( (r["timestamp"], 0 if last_tup_read > r["tup_read"] else r["tup_read"] - last_tup_read) ) # d['tup_fetch'].append( ( r['timestamp'] , 0 if last_tup_fetch > r['tup_fetch'] else r['tup_fetch'] - last_tup_fetch ) ) last_scan = r["scan"] last_tup_read = r["tup_read"] # last_tup_fetch = r['tup_fetch'] last_name = r["name"] last_index_size = r["size"] last_total_end_size = r["total_end_size"] last_pct_of_total_end_size = r["pct_of_total_end_size"] if len(data) > 0: all_data.append( { "index_name": last_name, "data": d, "last_index_size": round(last_index_size / 1024 ** 2), "total_end_size": round(last_total_end_size / 1024 ** 2), "pct_of_total_end_size": last_pct_of_total_end_size, } ) return all_data