WIPReports » History » Revision 5
Revision 4 (Darcy Buskermolen, 03/15/2007 01:02 PM) → Revision 5/7 (Darcy Buskermolen, 03/15/2007 01:03 PM)
these are work in prgress reports:
Example of IO to locate the busy write tables, in order for this one to be really effective we need to collect information from the pg_statistic table.
select ss.relid , s.relname, ((sum(sn_tup_ins)*1.5) + (sum(sn_tup_upd)*1.73) + sum(sn_tup_del)) AS weighted_io, date_trunc('day',insert_time) as "date", sum(sn_tup_ins) AS "insert",sum(sn_tup_upd) as "update", sum(sn_tup_del) as "delete" FROM (SELECT date_trunc('hour',(insert_time + '3 hours'::interval)) AS insert_time, relid, (max(n_tup_ins) - min(n_tup_ins)) AS sn_tup_ins, (max(n_tup_upd) - min(n_tup_upd)) AS sn_tup_upd, (max(n_tup_del) - min(n_tup_del)) AS sn_tup_del from public.myyearbook_pg_stat_user_tables where datname='myyearbook' and (insert_time + '3 hours'::interval)::date >= '2007-03-03' and (insert_time + '3 hours'::interval)::date < '2007-03-13' and date_part('hour', insert_time + '3 hours'::interval) > 13 GROUP BY 1,2) AS ss, (SELECT distinct relname, relid FROM public.myyearbook_pg_stat_user_tables) AS s WHERE ss.relid = s.relid GROUP BY 4, 1, 2 ORDER by ((sum(sn_tup_ins) + sum(sn_tup_upd)) > sum(sn_tup_del))<>'t', 3 DESC;
overview of tuples read:
SELECT insert_time::date, SUM(seq_scan) as seq_scan, sum(seq_tup_read) as seq_tup_read, sum(idx_scan) as idx_scan, sum(idx_tup_fetch) as idx_tup_fetch FROM (SELECT insert_time::date, relid,(max(seq_scan) - min(seq_scan)) AS seq_scan , (max(seq_tup_read) - min(seq_tup_read)) AS seq_tup_read, (max(idx_scan) - min(idx_scan)) AS idx_scan, (max(idx_tup_fetch) - min(idx_tup_fetch)) AS idx_tup_fetch from public.myyearbook_pg_stat_user_tables where datname='myyearbook' and insert_time::date >= '2007-03-03' and insert_time::date < '2007-03-13' group by 1,2) AS ss GROUP BY 1 ORDER BY 1 DESC;
overview of bytes read:
SELECT insert_time::date, pg_size_pretty((max(blks_read-blks_hit) - min(blks_read-blks_hit))*8192) AS Bytes_read from public.myyearbook_pg_stat_database where datname='myyearbook' and insert_time::date >= '2007-03-03' and insert_time::date < '2007-03-13' group by 1 order by 1 desc;