def topsprocsbyruntime(self):
q = """
select
host_db_export_name as db,
sproc_name,
total_runtime
from (
select
*,
row_number() over(partition by host_db_export_name order by total_runtime desc)
from (
select
host_db_export_name,
substring(sproc_name, 1, position ('(' in sproc_name)-1) as sproc_name,
max(sp_total_time)-min(sp_total_time) as total_runtime
from sprocs
join sproc_performance_data on sp_sproc_id = sproc_id
join hosts on host_id = sproc_host_id
where sp_timestamp > now() - '7days'::interval
and host_db_export_name is not null
group by 1, 2
) a
) b
where row_number <= 10
order by host_db_export_name, total_runtime desc
"""
topbyruntime = DataDB.execute(q)
retdict=defaultdict(list)
for r in topbyruntime:
retdict[r['db']].append(r['sproc_name'])
return json.dumps(retdict)
def getIndexIssues(hostname):
q_invalid = """
SELECT
*,
CASE WHEN indexes_size_bytes = 0 THEN 0 ELSE round((index_size_bytes::numeric / indexes_size_bytes::numeric)*100,1) END AS pct_of_tables_index_space,
pg_size_pretty(total_marked_index_size_bytes::bigint) AS total_marked_index_size
FROM (
SELECT
%s as host_name,
%s as host_id,
schemaname||'.'||relname AS table_full_name,
schemaname||'.'||indexrelname AS index_full_name,
index_size_bytes,
indexes_size_bytes,
pg_size_pretty(index_size_bytes) AS index_size,
pg_size_pretty(indexes_size_bytes) AS indexes_size,
pg_size_pretty(table_size_bytes) AS table_size,
sum(index_size_bytes) over () AS total_marked_index_size_bytes
FROM
(
SELECT quote_ident(schemaname) as schemaname,
quote_ident(relname) as relname,
quote_ident(indexrelname) as indexrelname,
pg_relation_size(i.indexrelid) AS index_size_bytes,
pg_indexes_size(i.relid) AS indexes_size_bytes,
pg_relation_size(i.relid) AS table_size_bytes
FROM pg_stat_user_indexes i
JOIN pg_index USING(indexrelid)
WHERE NOT indisvalid
) a
) b
ORDER BY index_size_bytes DESC, index_full_name
"""
q_unused = """
SELECT
*,
pg_size_pretty(total_marked_index_size_bytes::bigint) AS total_marked_index_size
FROM (
SELECT
*,
pg_size_pretty(index_size_bytes) AS index_size,
pg_size_pretty(indexes_size_bytes) AS indexes_size,
pg_size_pretty(table_size_bytes) AS table_size,
CASE WHEN indexes_size_bytes = 0 THEN 0 ELSE round((index_size_bytes::numeric / indexes_size_bytes::numeric)*100,1) END AS pct_of_tables_index_space,
sum(index_size_bytes) over () AS total_marked_index_size_bytes
FROM (
SELECT %s as host_name,
%s as host_id,
quote_ident(schemaname)||'.'||quote_ident(relname) AS table_full_name,
quote_ident(schemaname)||'.'||quote_ident(indexrelname) AS index_full_name,
pg_relation_size(i.indexrelid) as index_size_bytes,
pg_indexes_size(i.relid) AS indexes_size_bytes,
pg_relation_size(i.relid) AS table_size_bytes,
idx_scan AS scans
FROM pg_stat_user_indexes i
JOIN pg_index USING(indexrelid)
WHERE NOT indisunique
AND NOT schemaname LIKE ANY (ARRAY['tmp%%','temp%%'])
) a
WHERE index_size_bytes > %s
AND scans <= %s
) b
ORDER BY scans, index_size_bytes DESC
"""
q_duplicate = """
SELECT %s AS host_name,
%s as host_id,
n.nspname||'.'||ci.relname AS index_full_name,
n.nspname||'.'||ct.relname AS table_full_name,
pg_size_pretty(pg_total_relation_size(ct.oid)) AS table_size,
pg_total_relation_size(ct.oid) AS table_size_bytes,
n.nspname AS schema_name,
index_names,
def,
count
FROM (
select regexp_replace(replace(pg_get_indexdef(i.indexrelid),c.relname,'X'), '^CREATE UNIQUE','CREATE') as def,
max(indexrelid) as indexrelid,
max(indrelid) as indrelid,
count(1),
array_agg(relname::text) as index_names
from pg_index i
join pg_class c
on c.oid = i.indexrelid
where indisvalid
group
by regexp_replace(replace(pg_get_indexdef(i.indexrelid),c.relname,'X'), '^CREATE UNIQUE','CREATE')
having count(1) > 1
) a
JOIN pg_class ci
ON ci.oid=a.indexrelid
JOIN pg_class ct
ON ct.oid=a.indrelid
JOIN pg_namespace n
ON n.oid=ct.relnamespace
ORDER
BY count DESC, table_size_bytes DESC, schema_name, table_full_name
"""
q_active_hosts="""
select
host_id,
host_name,
host_user,
host_password,
host_db
from monitor_data.hosts
where host_enabled
and (%s = 'all' or host_name=%s)
"""
q_indexing_thresholds="""select * from monitor_data.perf_indexes_thresholds"""
data_invalid = []
data_unused = []
data_duplicate = []
data_noconnect = []
conn=None
hosts = DataDB.execute(q_active_hosts, (hostname, hostname))
indexing_thresholds = DataDB.execute(q_indexing_thresholds)[0]
for h in hosts:
try:
#print ('processing: {}', h)
conn = psycopg2.connect(host=h['host_name'], dbname=h['host_db'], user=h['host_user'], password=h['host_password'],connect_timeout='3')
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(q_invalid, (h['host_name'], h['host_id']))
data_invalid += cur.fetchall()
cur.execute(q_unused, (h['host_name'], h['host_id'], indexing_thresholds['pit_min_size_to_report'], indexing_thresholds['pit_max_scans_to_report']))
data_unused += cur.fetchall()
cur.execute(q_duplicate, (h['host_name'], h['host_id']))
data_duplicate += cur.fetchall()
except Exception, e:
print ('ERROR could not connect to {}:{}'.format(h['host_name'], e))
data_noconnect.append({'host_id':h['host_id'],'host_name': h['host_name']})
finally:
def getIndexIssues(hostname):
q_invalid = """
SELECT
*,
CASE WHEN indexes_size_bytes = 0 THEN 0 ELSE round((index_size_bytes::numeric / indexes_size_bytes::numeric)*100,1) END AS pct_of_tables_index_space,
pg_size_pretty(total_marked_index_size_bytes::bigint) AS total_marked_index_size
FROM (
SELECT
%s as host_name,
%s as host_id,
schemaname||'.'||relname AS table_full_name,
schemaname||'.'||indexrelname AS index_full_name,
index_size_bytes,
indexes_size_bytes,
pg_size_pretty(index_size_bytes) AS index_size,
pg_size_pretty(indexes_size_bytes) AS indexes_size,
pg_size_pretty(table_size_bytes) AS table_size,
sum(index_size_bytes) over () AS total_marked_index_size_bytes
FROM
(
SELECT quote_ident(schemaname) as schemaname,
quote_ident(relname) as relname,
quote_ident(indexrelname) as indexrelname,
pg_relation_size(i.indexrelid) AS index_size_bytes,
pg_indexes_size(i.relid) AS indexes_size_bytes,
pg_relation_size(i.relid) AS table_size_bytes
FROM pg_stat_user_indexes i
JOIN pg_index USING(indexrelid)
WHERE NOT indisvalid
) a
ORDER BY index_size_bytes DESC, relname
) b
"""
q_unused = """
SELECT
*,
pg_size_pretty(total_marked_index_size_bytes::bigint) AS total_marked_index_size
FROM (
SELECT
*,
pg_size_pretty(index_size_bytes) AS index_size,
pg_size_pretty(indexes_size_bytes) AS indexes_size,
pg_size_pretty(table_size_bytes) AS table_size,
CASE WHEN indexes_size_bytes = 0 THEN 0 ELSE round((index_size_bytes::numeric / indexes_size_bytes::numeric)*100,1) END AS pct_of_tables_index_space,
sum(index_size_bytes) over () AS total_marked_index_size_bytes
FROM (
SELECT %s as host_name,
%s as host_id,
quote_ident(schemaname)||'.'||quote_ident(relname) AS table_full_name,
quote_ident(schemaname)||'.'||quote_ident(indexrelname) AS index_full_name,
pg_relation_size(i.indexrelid) as index_size_bytes,
pg_indexes_size(i.relid) AS indexes_size_bytes,
pg_relation_size(i.relid) AS table_size_bytes,
idx_scan AS scans
FROM pg_stat_user_indexes i
JOIN pg_index USING(indexrelid)
WHERE NOT indisunique
AND NOT schemaname LIKE ANY (ARRAY['tmp%%','temp%%'])
) a
WHERE index_size_bytes > %s
AND scans <= %s
ORDER BY scans, index_size_bytes DESC
) b
"""
q_active_hosts = """
select
host_id,
host_name,
host_user,
host_password,
host_db
from monitor_data.hosts
where host_enabled
and (%s = 'all' or host_name=%s)
"""
q_indexing_thresholds = """select * from monitor_data.perf_indexes_thresholds"""
data_invalid = []
data_unused = []
data_noconnect = []
conn = None
hosts = DataDB.execute(q_active_hosts, (hostname, hostname))
indexing_thresholds = DataDB.execute(q_indexing_thresholds)[0]
for h in hosts:
try:
#print ('processing: {}', h)
conn = psycopg2.connect(host=h['host_name'],
dbname=h['host_db'],
user=h['host_user'],
password=h['host_password'],
connect_timeout='3')
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(q_invalid, (h['host_name'], h['host_id']))
data_invalid += cur.fetchall()
cur.execute(q_unused,
(h['host_name'], h['host_id'],
indexing_thresholds['pit_min_size_to_report'],
indexing_thresholds['pit_max_scans_to_report']))
data_unused += cur.fetchall()
except Exception, e:
print('ERROR could not connect to {}:{}'.format(h['host_name'], e))
data_noconnect.append({
'host_id': h['host_id'],
'host_name': h['host_name']
})
finally:
def getIndexIssues(hostname):
q_invalid = """
SELECT
*,
CASE WHEN indexes_size_bytes = 0 THEN 0 ELSE round((index_size_bytes::numeric / indexes_size_bytes::numeric)*100,1) END AS pct_of_tables_index_space,
pg_size_pretty(total_marked_index_size_bytes::bigint) AS total_marked_index_size
FROM (
SELECT
%s as host_name,
%s as host_id,
schemaname||'.'||relname AS table_full_name,
schemaname||'.'||indexrelname AS index_full_name,
index_size_bytes,
indexes_size_bytes,
pg_size_pretty(index_size_bytes) AS index_size,
pg_size_pretty(indexes_size_bytes) AS indexes_size,
pg_size_pretty(table_size_bytes) AS table_size,
sum(index_size_bytes) over () AS total_marked_index_size_bytes
FROM
(
SELECT quote_ident(schemaname) as schemaname,
quote_ident(relname) as relname,
quote_ident(indexrelname) as indexrelname,
pg_relation_size(i.indexrelid) AS index_size_bytes,
pg_indexes_size(i.relid) AS indexes_size_bytes,
pg_relation_size(i.relid) AS table_size_bytes
FROM pg_stat_user_indexes i
JOIN pg_index USING(indexrelid)
WHERE NOT indisvalid
) a
ORDER BY index_size_bytes DESC, relname
) b
"""
q_unused = """
SELECT
*,
pg_size_pretty(total_marked_index_size_bytes::bigint) AS total_marked_index_size
FROM (
SELECT
*,
pg_size_pretty(index_size_bytes) AS index_size,
pg_size_pretty(indexes_size_bytes) AS indexes_size,
pg_size_pretty(table_size_bytes) AS table_size,
CASE WHEN indexes_size_bytes = 0 THEN 0 ELSE round((index_size_bytes::numeric / indexes_size_bytes::numeric)*100,1) END AS pct_of_tables_index_space,
sum(index_size_bytes) over () AS total_marked_index_size_bytes
FROM (
SELECT %s as host_name,
%s as host_id,
quote_ident(schemaname)||'.'||quote_ident(relname) AS table_full_name,
quote_ident(schemaname)||'.'||quote_ident(indexrelname) AS index_full_name,
pg_relation_size(i.indexrelid) as index_size_bytes,
pg_indexes_size(i.relid) AS indexes_size_bytes,
pg_relation_size(i.relid) AS table_size_bytes,
idx_scan AS scans
FROM pg_stat_user_indexes i
JOIN pg_index USING(indexrelid)
WHERE NOT indisunique
AND NOT schemaname LIKE ANY (ARRAY['tmp%%','temp%%'])
) a
WHERE index_size_bytes > %s
AND scans <= %s
ORDER BY scans, index_size_bytes DESC
) b
"""
q_active_hosts="""
select
host_id,
host_name,
host_user,
host_password,
host_db
from monitor_data.hosts
where host_enabled
and (%s = 'all' or host_name=%s)
"""
q_indexing_thresholds="""select * from monitor_data.perf_indexes_thresholds"""
data_invalid = []
data_unused = []
data_noconnect = []
conn=None
hosts = DataDB.execute(q_active_hosts, (hostname, hostname))
indexing_thresholds = DataDB.execute(q_indexing_thresholds)[0]
for h in hosts:
try:
#print ('processing: {}', h)
conn = psycopg2.connect(host=h['host_name'], dbname=h['host_db'], user=h['host_user'], password=h['host_password'],connect_timeout='3')
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute(q_invalid, (h['host_name'], h['host_id']))
data_invalid += cur.fetchall()
cur.execute(q_unused, (h['host_name'], h['host_id'], indexing_thresholds['pit_min_size_to_report'], indexing_thresholds['pit_max_scans_to_report']))
data_unused += cur.fetchall()
except Exception, e:
print ('ERROR could not connect to {}:{}'.format(h['host_name'], e))
data_noconnect.append({'host_id':h['host_id'],'host_name': h['host_name']})
finally:
