def getSprocDataByTags():
sql = """select tm_tag_id , sum("yaxis") AS "yaxis_t" , sum("yaxis2") AS "yaxis_c", "xaxis" from (
select group_date(sp_timestamp,30) as "xaxis",
sp_sproc_id,
max(sp_self_time) - min(sp_self_time) as "yaxis",
max(sp_calls) - min(sp_calls) as "yaxis2"
from monitor_data.sproc_performance_data
where sp_timestamp > 'now'::timestamp - '9 days'::interval
group by sp_sproc_id , group_date(sp_timestamp,30) ) data,
monitor_data.sprocs,
monitor_data.tag_members
where sprocs.sproc_id = sp_sproc_id
and tm_sproc_name = sproc_name
and tm_schema = get_noversion_name(sproc_schema)
group by tm_tag_id , "xaxis" order by 4 asc;"""
conn = DataDB.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(sql)
data = collections.defaultdict(list)
for r in cur:
data[r['tm_tag_id']].append((r['xaxis'], r['yaxis_t'], r['yaxis_c']))
cur.close()
DataDB.closeDataConnection(conn)
return data
def getSprocDataByTags():
sql = """select tm_tag_id , sum("yaxis") AS "yaxis_t" , sum("yaxis2") AS "yaxis_c", "xaxis" from (
select group_date(sp_timestamp,30) as "xaxis",
sp_sproc_id,
max(sp_self_time) - min(sp_self_time) as "yaxis",
max(sp_calls) - min(sp_calls) as "yaxis2"
from monitor_data.sproc_performance_data
where sp_timestamp > 'now'::timestamp - '9 days'::interval
group by sp_sproc_id , group_date(sp_timestamp,30) ) data,
monitor_data.sprocs,
monitor_data.tag_members
where sprocs.sproc_id = sp_sproc_id
and tm_sproc_name = sproc_name
and tm_schema = get_noversion_name(sproc_schema)
group by tm_tag_id , "xaxis" order by 4 asc;"""
conn = DataDB.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(sql)
data = collections.defaultdict(list)
for r in cur:
data[r['tm_tag_id']].append((r['xaxis'], r['yaxis_t'], r['yaxis_c']))
cur.close()
DataDB.closeDataConnection(conn)
return data
def getSingleSprocData(name, hostId=1, interval=None, sprocNr=None):
conn = DataDB.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(getSingleSprocSQL(name, hostId, interval, sprocNr))
data = {
'calls': [],
'self_time': [],
'total_time': [],
'avg_time': [],
'avg_self_time': [],
'name': name
}
for r in cur:
data['calls'].append((r['xaxis'], r['d_calls']))
data['total_time'].append((r['xaxis'], r['d_total_time']))
data['self_time'].append((r['xaxis'], r['d_self_time']))
data['avg_time'].append((r['xaxis'], r['d_avg_time']))
data['avg_self_time'].append((r['xaxis'], r['d_avg_self_time']))
cur.close()
DataDB.closeDataConnection(conn)
return data
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 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 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 getSingleSprocData(name, hostId=1, interval=None, sprocNr = None):
conn = DataDB.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute( getSingleSprocSQL(name, hostId, interval, sprocNr ) )
data = { 'calls' : [], 'self_time': [], 'total_time' : [] , 'avg_time' : [] , 'avg_self_time': [] , 'name' : name }
for r in cur:
data['calls'].append( ( r['xaxis'] , r['d_calls'] ) )
data['total_time'].append ( ( r['xaxis'] , r['d_total_time'] ) )
data['self_time'].append ( ( r['xaxis'] , r['d_self_time'] ) )
data['avg_time'].append ( ( r['xaxis'] , r['d_avg_time'] ) )
data['avg_self_time'].append ( ( r['xaxis'] , r['d_avg_self_time'] ) )
cur.close()
DataDB.closeDataConnection(conn)
return data
def getSprocsOrderedBy( hostId, order = " ORDER BY SUM(delta_total_time) DESC"):
sql = """SELECT sproc_name
FROM ( """ + viewSprocs() + """ ) t JOIN monitor_data.sprocs ON sp_sproc_id = sproc_id
WHERE sproc_host_id = """ + str(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 getSprocsOrderedBy(hostId, order=" ORDER BY SUM(delta_total_time) DESC"):
sql = """SELECT sproc_name
FROM ( """ + viewSprocs(
) + """ ) 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 getDatabaseSizes(host_id=None):
conn = DataDB.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(getSizeTrendSQL(host_id))
size_data = {}
current_host = 0
last_timestamp = None
for record in cur:
if record['t_host_id'] != current_host:
current_host = record['t_host_id']
set_ins = False
set_del = False
set_upd = False
l_ins = None
l_upd = None
l_del = None
last_timestamp = None
if last_timestamp == None:
last_timestamp = int(
time.mktime(record['tsd_timestamp'].timetuple()) * 1000)
if not record['t_host_id'] in size_data:
size_data[record['t_host_id']] = {
'size': [],
'ins': [],
'upd': [],
'del': []
}
""" exclude 0 values, otherwise there is a big peak at start, with wraparound this should be ok"""
if not set_ins and record['s_ins'] != 0:
l_ins = record['s_ins']
set_ins = True
if not set_upd and record['s_upd'] != 0:
l_upd = record['s_upd']
set_upd = True
if not set_del and record['s_del'] != 0:
l_del = record['s_del']
set_del = True
if l_ins == None:
l_ins = record['s_ins']
if l_upd == None:
l_upd = record['s_upd']
if l_del == None:
l_del = record['s_del']
size_data[record['t_host_id']]['size'].append(
(record['tsd_timestamp'], record['size']))
size_data[record['t_host_id']]['ins'].append(
(record['tsd_timestamp'], max(record['s_ins'] - l_ins, 0)))
size_data[record['t_host_id']]['del'].append(
(record['tsd_timestamp'], max(record['s_del'] - l_del, 0)))
size_data[record['t_host_id']]['upd'].append(
(record['tsd_timestamp'], max(record['s_upd'] - l_upd, 0)))
l_ins = record['s_ins']
l_upd = record['s_upd']
l_del = record['s_del']
last_timestamp = int(
time.mktime(record['tsd_timestamp'].timetuple()) * 1000)
cur.close()
DataDB.closeDataConnection(conn)
return size_data
def getDatabaseSizes(host_id = None):
conn = DataDB.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(getSizeTrendSQL(host_id))
size_data = {}
current_host = 0
last_timestamp = None
for record in cur:
if record['t_host_id'] != current_host:
current_host = record['t_host_id']
set_ins = False
set_del = False
set_upd = False
l_ins = None
l_upd = None
l_del = None
last_timestamp = None
if last_timestamp == None:
last_timestamp = int(time.mktime(record['tsd_timestamp'].timetuple()) * 1000)
if not record['t_host_id'] in size_data:
size_data[record['t_host_id']] = { 'size' : [] , 'ins': [], 'upd': [], 'del':[] }
""" exclude 0 values, otherwise there is a big peak at start, with wraparound this should be ok"""
if not set_ins and record['s_ins']!=0:
l_ins = record['s_ins']
set_ins = True
if not set_upd and record['s_upd']!=0:
l_upd = record['s_upd']
set_upd = True
if not set_del and record['s_del']!=0:
l_del = record['s_del']
set_del = True
if l_ins == None:
l_ins = record['s_ins']
if l_upd == None:
l_upd = record['s_upd']
if l_del == None:
l_del = record['s_del']
size_data[record['t_host_id']]['size'].append( ( record['tsd_timestamp'] , record['size'] ) )
size_data[record['t_host_id']]['ins'].append( ( record['tsd_timestamp'] , max( record['s_ins'] - l_ins , 0) ) )
size_data[record['t_host_id']]['del'].append( ( record['tsd_timestamp'] , max( record['s_del'] - l_del , 0) ) )
size_data[record['t_host_id']]['upd'].append( ( record['tsd_timestamp'] , max( record['s_upd'] - l_upd , 0) ) )
l_ins = record['s_ins']
l_upd = record['s_upd']
l_del = record['s_del']
last_timestamp = int(time.mktime(record['tsd_timestamp'].timetuple()) * 1000)
cur.close()
DataDB.closeDataConnection(conn)
return size_data
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
