Pggraph

WIPReports » History » Revision 6

Revision 5 (Darcy Buskermolen, 03/15/2007 01:03 PM) → Revision 6/7 (bford -, 03/22/2007 08:50 AM)

these are work in progress prgress reports: 

 '''Example 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 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 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; 

 '''Indexes that are not used, that are not unique and not primary keys:''' 

 select indexrelname FROM pg_stat_user_indexes JOIN pg_index ON (pg_index.indexrelid = pg_stat_user_indexes.indexrelid) where idx_scan = 0 and indisunique = 'f' and indisprimary = 'f' and idx_tup_read = 0 and idx_tup_fetch = 0; 

 ''before we implement this we may want to rewrite the query to use pg_class instead as this is calling on a couple of views''