pg_reset_stats + cache I/O %

From: "mcelroy, tim" <tim(dot)mcelroy(at)bostonstock(dot)com>
To: "'pgsql-performance(at)postgresql(dot)org'" <pgsql-performance(at)postgresql(dot)org>
Subject: pg_reset_stats + cache I/O %
Date: 2006-03-07 19:07:24
Message-ID: 0C4841B42F87D51195BD00B0D020F5CB044B240E@morpheus.bostonstock.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Good afternoon,

Relatively new to PostgreSQL and have been assigned the task of capturing
cache I/O % hits. I figured out (thanks to other posts) how to turn on the
capture and what stats to (kind of) capture. I did find a view in the
archives as shown below but it does not execute, error follows. I'm using
8.0.1 so that shouldn't be the issue. Any help will be appreciated.

Also, I also found pg_reset_stats.tar.gz in the archives with a lot of talk
regarding its addition as a patch, did it ever make it in? If not, can I
get a copy of it somewhere? The tar.gz gets corrupted when I download it.

Thank you,
Tim

CREATE VIEW cache_hits AS SELECT relname, ROUND(CASE WHEN heap_blks_hit = 0
THEN 0 ELSE ((heap_blks_hit::float /(heap_blks_read::float +
heap_blks_hit::float)) * 100) END ,2) as heap, ROUND(CASE WHEN idx_blks_hit
= 0 THEN 0 ELSE ((idx_blks_hit::float /(idx_blks_read::float +
idx_blks_hit::float)) * 100) END,2) as index,ROUND(CASE WHEN toast_blks_hit
= 0 THEN 0 ELSE ((toast_blks_hit::float /(toast_blks_read::float +
toast_blks_hit::float)) * 100) END,2) as toast FROM pg_statio_user_tables
WHERE heap_blks_read <> 0 or idx_blks_read <> 0 OR toast_blks_read <> 0

union select 'ALL TABLES', ROUND(CASE WHEN sum(heap_blks_hit) = 0 THEN 0
ELSE ((sum(heap_blks_hit::float) /(sum(heap_blks_read::float) +
sum(heap_blks_hit::float))) * 100) END ,2) as heap, ROUND(CASE WHEN
sum(idx_blks_hit) = 0 THEN 0 ELSE ((sum(idx_blks_hit::float)
/(sum(idx_blks_read::float) + sum(idx_blks_hit::float))) * 100) END,2) as
index,ROUND(CASE WHEN sum(toast_blks_hit) = 0 THEN 0 ELSE
((sum(toast_blks_hit::float) /(sum(toast_blks_read::float) +
sum(toast_blks_hit::float))) * 100) END,2) as toast FROM
pg_statio_user_tables HAVING sum(heap_blks_read) <> 0 or sum(idx_blks_read)
<> 0 OR sum(toast_blks_read) <> 0 ;

ERROR: function round(double precision, integer) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jim C. Nasby 2006-03-07 19:09:34 Re: Can anyone explain this pgbench results?
Previous Message Jim C. Nasby 2006-03-07 19:00:19 Re: Postgres and Ingres R3 / SAN