def getTop10Interval(order=avgRuntimeOrder,interval=None,hostId = 1, limit = 10):
sql = """select regexp_replace("name", E'(\\\\(.*\\\\))','()') AS "name",
round( sum(d_calls) , 0 ) AS "calls",
round( sum(d_total_time) , 0 ) AS "totalTime",
round( sum(d_total_time) / sum(d_calls) , 0 ) AS "avgTime"
from ( """ + getSQL(interval, hostId) + """) tt
where d_calls > 0
group by "name"
order by """+order+""" limit """ + str(adapt(limit))
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(sql)
sprocs = []
for record in cur:
record['avgTime'] = makeTimeIntervalReadable(record['avgTime'])
record['totalTime'] = makeTimeIntervalReadable(record['totalTime'])
sprocs.append(record)
conn.close()
return sprocs
def getTablePerformanceIssues(hostname, date_from, date_to):
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(
"""select * from monitor_data.get_table_threshold_sinners_for_period(%s,%s,%s)""",
(hostname, date_from, date_to))
data = [] # cur.fetchall()
for r in cur:
row = {
'host_name': r['host_name'],
'host_id': r['host_id'],
'schema_name': r['schema_name'],
'table_name': r['table_name'],
'day': r['day'],
'scan_change_pct': r['scan_change_pct'],
'scans1': r['scans1'],
'scans2': r['scans2'],
'size1': r['size1'],
'size2': r['size2'],
'size_change_pct': r['size_change_pct'],
'allowed_seq_scan_pct': r['allowed_seq_scan_pct'],
}
data.append(row)
cur.close()
conn.close()
return data
def getTop10Interval(order=avgRuntimeOrder, interval=None, hostId=1, limit=10):
sql = """select regexp_replace("name", E'(\\\\(.*\\\\))','()') AS "name",
round( sum(d_calls) , 0 ) AS "calls",
round( sum(d_total_time) , 0 ) AS "totalTime",
round( sum(d_total_time) / sum(d_calls)::numeric, 1) AS "avgTime"
from ( """ + getSQL(interval, hostId) + """) tt
where d_calls > 0
group by "name"
order by """ + order + """ limit """ + str(adapt(limit))
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(sql)
sprocs = []
for record in cur:
record['avgTime'] = makeTimeIntervalReadable(record['avgTime'])
record['totalTime'] = makeTimeIntervalReadable(record['totalTime'])
sprocs.append(record)
conn.close()
return sprocs
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 getGroupsData():
conn = datadb.getDataConnection()
groups = {}
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("SELECT * FROM monitor_data.host_groups;")
for g in cur:
groups [ g['group_id'] ] = g['group_name']
cur.close()
conn.close()
return groups
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 getAllHostsData():
conn = datadb.getDataConnection()
hosts = {}
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute("SELECT host_id, host_name, host_port, host_db, host_settings, host_group_id, host_enabled,"
" host_ui_shortname, host_ui_longname"
" FROM monitor_data.hosts ORDER BY host_id ASC;")
for r in cur:
r['uishortname'] = r['host_ui_shortname'].lower().replace('-','')
r['uilongname'] = r['host_ui_longname']
hosts[r['host_id']] = r
cur.close()
conn.close()
return hosts
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 getApiPerformanceIssues(hostname, api_from, api_to):
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(
"""select * from monitor_data.get_sproc_threshold_sinners_for_release(%s,%s,%s)""",
(hostname, api_from, api_to))
data = [] # cur.fetchall()
for r in cur:
row = {
'host_name':
r['host_name'],
'host_id':
r['host_id'],
'sproc_schema':
r['sproc_schema'],
'sproc_name':
r['sproc_name'],
'calltime_change_pct':
r['calltime_change_pct'],
'share_on_total_runtime':
r['share_on_total_runtime'],
'execution_avg1':
r['execution_avg1'],
'execution_avg2':
r['execution_avg2'],
'calls1':
r['calls1'],
'calls2':
r['calls2'],
'callscount_change_pct':
r['callscount_change_pct'],
'allowed_runtime_growth_pct':
r['allowed_runtime_growth_pct'],
'allowed_share_on_total_runtime_pct':
r['allowed_share_on_total_runtime_pct'],
}
data.append(row)
cur.close()
conn.close()
return data
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 getTablePerformanceIssues(hostname, date_from, date_to):
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("""select * from monitor_data.get_table_threshold_sinners_for_period(%s,%s,%s)""", (hostname, date_from, date_to))
data = [] # cur.fetchall()
for r in cur:
row = {'host_name' : r['host_name'],
'host_id' : r['host_id'],
'schema_name' : r['schema_name'],
'table_name' : r['table_name'],
'day' : r['day'],
'scan_change_pct' : r['scan_change_pct'],
'scans1': r['scans1'],
'scans2': r['scans2'],
'size1': r['size1'],
'size2': r['size2'],
'size_change_pct': r['size_change_pct'],
'allowed_seq_scan_pct': r['allowed_seq_scan_pct'],
}
data.append(row)
cur.close()
conn.close()
return data
def getApiPerformanceIssues(hostname, api_from, api_to):
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("""select * from monitor_data.get_sproc_threshold_sinners_for_release(%s,%s,%s)""", (hostname, api_from, api_to))
data = [] # cur.fetchall()
for r in cur:
row = {'host_name' : r['host_name'],
'host_id' : r['host_id'],
'sproc_schema' : r['sproc_schema'],
'sproc_name' : r['sproc_name'],
'calltime_change_pct' : r['calltime_change_pct'],
'share_on_total_runtime' : r['share_on_total_runtime'],
'execution_avg1': r['execution_avg1'],
'execution_avg2': r['execution_avg2'],
'calls1': r['calls1'],
'calls2': r['calls2'],
'callscount_change_pct': r['callscount_change_pct'],
'allowed_runtime_growth_pct': r['allowed_runtime_growth_pct'],
'allowed_share_on_total_runtime_pct': r['allowed_share_on_total_runtime_pct'],
}
data.append(row)
cur.close()
conn.close()
return data
def getLoadReportData(hostId=None, weeks=10):
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = """
with q as (
select
tsd_host_id as host_id
, week
, (select round((sum(tsd_table_size) + sum(tsd_index_size))/(1024*1024*1024)::numeric,1)::text --in GB
from monitor_data.table_size_data
where tsd_timestamp = max) as db_size
from (
select
tsd_host_id
, extract(week from tsd_timestamp) as week
, max(tsd_timestamp)
from monitor_data.table_size_data
where tsd_timestamp >= date_trunc('week', now()) - '""" + str(weeks) + """ weeks'::interval
and (%s is null or tsd_host_id = %s)
group by tsd_host_id, extract(week from tsd_timestamp)
) a
)
select
load_host_id AS id,
extract(week from load_timestamp)::text AS kw,
round(avg(load_15min_value)/100,2) AS avg,
round(max(load_15min_value)/100,2) AS max,
to_char(min(load_timestamp::date),'dd.mm.YYYY') AS min_date,
to_char(max(load_timestamp::date),'dd.mm.YYYY') AS max_date,
min(load_timestamp::date) AS sort_date,
max(q.db_size) as db_size,
round((max(xlog_location_mb) - min(xlog_location_mb)) / 1024.0, 1) as wal_written
from monitor_data.host_load hl
, monitor_data.hosts h
, q
where h.host_id = hl.load_host_id
and host_enabled
and load_timestamp >= date_trunc('week', now()) - '""" + str(weeks) + """ weeks'::interval
and extract(dow from load_timestamp) IN(1,2,3,4,5)
and q.host_id = load_host_id
and q.week = extract(week from load_timestamp)
and (%s is null or hl.load_host_id = %s)
group by load_host_id, extract(week from load_timestamp)
order by 1 ASC,7 DESC
"""
cur.execute(query, (hostId,hostId,hostId,hostId))
data = defaultdict(list)
lastRR = None
for r in cur:
rr = {'id' : r['id'],
'avg' : r['avg'],
'max' : r['max'],
'min_date' : r['min_date'],
'max_date' : r['max_date'],
'db_size' : r['db_size'],
'wal_written' : r['wal_written'],
'trendAvg': 0,
'trendMax': 0,
'kw' : r['kw']
}
if lastRR != None and lastRR['id']==rr['id']:
if lastRR['max'] < r['max']:
lastRR['trendMax'] = -1
elif lastRR['max'] > r['max']:
lastRR['trendMax'] = 1
if lastRR['avg'] < r['avg']:
lastRR['trendAvg'] = -1
elif lastRR['avg'] > r['avg']:
lastRR['trendAvg'] = 1
if lastRR['db_size'] < r['db_size']:
lastRR['trendSize'] = -1
elif lastRR['db_size'] > r['db_size']:
lastRR['trendSize'] = 1
if lastRR['wal_written'] < r['wal_written']:
lastRR['trendWal'] = -1
elif lastRR['wal_written'] > r['wal_written']:
lastRR['trendWal'] = 1
data[int(r['id'])].append(rr);
lastRR = rr
cur.close()
conn.close()
return sorted(data.values(), key = lambda x : hosts.hosts[x[0]['id']]['uishortname'])
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 getLoad(hostId, days='8'):
days += 'days'
sql = """
SELECT
xaxis,
MAX(load_15min) as load_15min -- needed for 15min overlap
FROM (
SELECT
sla_timestamp as xaxis,
sla_load_15min as load_15min
FROM
monitor_data.sproc_load_agg
WHERE
sla_host_id = """ + str(adapt(hostId)) + """
AND sla_timestamp > now() - """ + str(
adapt(days)) + """::interval
AND sla_timestamp < now() - '2 hours'::interval
UNION ALL
SELECT
xaxis,
(sum(d_self_time) OVER (ORDER BY xaxis ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) / (1*15*60*1000))::numeric(8,2) AS load_15min
FROM
( SELECT
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval AS xaxis,
sum(t.delta_self_time) AS d_self_time
FROM ( SELECT
spd.sp_timestamp,
COALESCE(spd.sp_self_time - lag(spd.sp_self_time) OVER w, 0::bigint) AS delta_self_time
FROM
monitor_data.sproc_performance_data spd
WHERE
spd.sp_host_id = """ + str(adapt(hostId)) + """
AND sp_timestamp >= now() - '2 hours 15 minutes'::interval --15 minutes overlap due to window
WINDOW w AS
( PARTITION BY spd.sp_sproc_id ORDER BY spd.sp_timestamp )
) t
GROUP BY
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval
ORDER BY
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval
) loadTable
) a
GROUP BY
xaxis
ORDER BY
xaxis
"""
if not tplE._settings.get('run_aggregations'):
sql = """
SELECT
xaxis,
load_15min
FROM (
SELECT
xaxis,
(sum(d_self_time) OVER (ORDER BY xaxis ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) / (1*15*60*1000))::numeric(8,2) AS load_15min
FROM
( SELECT
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval AS xaxis,
sum(t.delta_self_time) AS d_self_time
FROM ( SELECT
spd.sp_timestamp,
COALESCE(spd.sp_self_time - lag(spd.sp_self_time) OVER w, 0::bigint) AS delta_self_time
FROM
monitor_data.sproc_performance_data spd
WHERE
spd.sp_host_id = """ + str(adapt(hostId)) + """
AND sp_timestamp > now() - """ + str(
adapt(days)) + """::interval
WINDOW w AS
( PARTITION BY spd.sp_sproc_id ORDER BY spd.sp_timestamp )
) t
GROUP BY
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval
ORDER BY
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval
) loadTable
) a
ORDER BY
xaxis
"""
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
load = {'load_15min': []}
cur.execute(sql)
lastTime = None
skip15min = 0
for record in cur:
currentTime = int(time.mktime(record['xaxis'].timetuple()) * 1000)
if lastTime != None:
if currentTime - lastTime > (15 * 60 * 1000):
skip15min = 2
if skip15min > 0:
skip15min -= 1
else:
load['load_15min'].append(
(record['xaxis'], round(record['load_15min'], 2)))
lastTime = int(time.mktime(record['xaxis'].timetuple()) * 1000)
cur.close()
conn.close()
return load
def getLoad(hostId, days='8'):
days += 'days'
sql = """
SELECT
xaxis,
MAX(load_15min) as load_15min -- needed for 15min overlap
FROM (
SELECT
sla_timestamp as xaxis,
sla_load_15min as load_15min
FROM
monitor_data.sproc_load_agg
WHERE
sla_host_id = """ +str(adapt(hostId)) + """
AND sla_timestamp > now() - """ + str(adapt(days)) + """::interval
AND sla_timestamp < now() - '2 hours'::interval
UNION ALL
SELECT
xaxis,
(sum(d_self_time) OVER (ORDER BY xaxis ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) / (1*15*60*1000))::numeric(8,2) AS load_15min
FROM
( SELECT
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval AS xaxis,
sum(t.delta_self_time) AS d_self_time
FROM ( SELECT
spd.sp_timestamp,
COALESCE(spd.sp_self_time - lag(spd.sp_self_time) OVER w, 0::bigint) AS delta_self_time
FROM
monitor_data.sproc_performance_data spd
WHERE
spd.sp_host_id = """ + str(adapt(hostId)) + """
AND sp_timestamp >= now() - '2 hours 15 minutes'::interval --15 minutes overlap due to window
WINDOW w AS
( PARTITION BY spd.sp_sproc_id ORDER BY spd.sp_timestamp )
) t
GROUP BY
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval
ORDER BY
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval
) loadTable
) a
GROUP BY
xaxis
ORDER BY
xaxis
"""
if not tplE._settings['run_aggregations']:
sql = """
SELECT
xaxis,
load_15min
FROM (
SELECT
xaxis,
(sum(d_self_time) OVER (ORDER BY xaxis ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) / (1*15*60*1000))::numeric(8,2) AS load_15min
FROM
( SELECT
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval AS xaxis,
sum(t.delta_self_time) AS d_self_time
FROM ( SELECT
spd.sp_timestamp,
COALESCE(spd.sp_self_time - lag(spd.sp_self_time) OVER w, 0::bigint) AS delta_self_time
FROM
monitor_data.sproc_performance_data spd
WHERE
spd.sp_host_id = """ + str(adapt(hostId)) + """
AND sp_timestamp > now() - """ + str(adapt(days)) + """::interval
WINDOW w AS
( PARTITION BY spd.sp_sproc_id ORDER BY spd.sp_timestamp )
) t
GROUP BY
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval
ORDER BY
date_trunc('hour'::text, t.sp_timestamp) + floor(date_part('minute'::text, t.sp_timestamp) / 15::double precision) * '00:15:00'::interval
) loadTable
) a
ORDER BY
xaxis
"""
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
load = { 'load_15min' : [] }
cur.execute(sql)
lastTime = None
skip15min=0
for record in cur:
currentTime = int(time.mktime(record['xaxis'].timetuple()) * 1000)
if lastTime != None:
if currentTime - lastTime > ( 15 * 60 * 1000):
skip15min = 2
if skip15min>0:
skip15min -= 1
else:
load['load_15min'].append((record['xaxis'], round ( record['load_15min'], 2 ) ) )
lastTime = int(time.mktime(record['xaxis'].timetuple()) * 1000)
cur.close()
conn.close()
return load
def getLoadReportData(hostId=None, weeks=10):
conn = datadb.getDataConnection()
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = """
with q as (
select
tsd_host_id as host_id
, week
, (select round((sum(tsd_table_size) + sum(tsd_index_size))/(1024*1024*1024)::numeric,1)::text --in GB
from monitor_data.table_size_data
where tsd_timestamp = max) as db_size
from (
select
tsd_host_id
, extract(week from tsd_timestamp) as week
, max(tsd_timestamp)
from monitor_data.table_size_data
where tsd_timestamp >= date_trunc('week', now()) - '""" + str(
weeks) + """ weeks'::interval
and (%s is null or tsd_host_id = %s)
group by tsd_host_id, extract(week from tsd_timestamp)
) a
)
select
load_host_id AS id,
extract(week from load_timestamp)::text AS kw,
round(avg(load_15min_value)/100,2) AS avg,
round(max(load_15min_value)/100,2) AS max,
to_char(min(load_timestamp::date),'dd.mm.YYYY') AS min_date,
to_char(max(load_timestamp::date),'dd.mm.YYYY') AS max_date,
min(load_timestamp::date) AS sort_date,
max(q.db_size) as db_size,
round((max(xlog_location_mb) - min(xlog_location_mb)) / 1024.0, 1) as wal_written
from monitor_data.host_load hl
, monitor_data.hosts h
, q
where h.host_id = hl.load_host_id
and host_enabled
and load_timestamp >= date_trunc('week', now()) - '""" + str(
weeks) + """ weeks'::interval
and extract(dow from load_timestamp) IN(1,2,3,4,5)
and q.host_id = load_host_id
and q.week = extract(week from load_timestamp)
and (%s is null or hl.load_host_id = %s)
group by load_host_id, extract(week from load_timestamp)
order by 1 ASC,7 DESC
"""
cur.execute(query, (hostId, hostId, hostId, hostId))
data = defaultdict(list)
lastRR = None
for r in cur:
rr = {
'id': r['id'],
'avg': r['avg'],
'max': r['max'],
'min_date': r['min_date'],
'max_date': r['max_date'],
'db_size': r['db_size'],
'wal_written': r['wal_written'],
'trendAvg': 0,
'trendMax': 0,
'kw': r['kw']
}
if lastRR != None and lastRR['id'] == rr['id']:
if lastRR['max'] < r['max']:
lastRR['trendMax'] = -1
elif lastRR['max'] > r['max']:
lastRR['trendMax'] = 1
if lastRR['avg'] < r['avg']:
lastRR['trendAvg'] = -1
elif lastRR['avg'] > r['avg']:
lastRR['trendAvg'] = 1
if lastRR['db_size'] < r['db_size']:
lastRR['trendSize'] = -1
elif lastRR['db_size'] > r['db_size']:
lastRR['trendSize'] = 1
if lastRR['wal_written'] < r['wal_written']:
lastRR['trendWal'] = -1
elif lastRR['wal_written'] > r['wal_written']:
lastRR['trendWal'] = 1
data[int(r['id'])].append(rr)
lastRR = rr
cur.close()
conn.close()
return sorted(data.values(),
key=lambda x: hosts.hosts[x[0]['id']]['uishortname'])
