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
