Re: Buffer Analysis

Tim,
This view is great. I've always wondered if my db was mostly in memory as
I thought or not.
I've added a UNION SELECT that returns a row for all tables aggregated
together. Selecting avg(heap) etc. can give very skewed information, as
you're averaging averages.
Thanks for sharing this. A version with the added row follows:

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 ;

Thanks,
Peter Darley

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tim Knowles
Sent: Monday, September 16, 2002 5:52 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Buffer Analysis

Hi All,

Thought I'd share with the list a small VIEW I've been using to monitor
buffer usage on my systems. It calculates as a percentage the number of
times the buffer is used instead of the disk. It relies on the
stats_collector being active at the block level (edit postgresql.conf to set
these values). The CASE statement is there to avoid divide by zero errors.
The WHERE clause removes any tables for which no access has yet taken place.
These statistics are only worthwhile after a period of normal database use.

Regards,

Tim Knowles

-- SQL

CREATE VIEW buffer_percentageuse 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 ORDER BY relname;

-- You can select * from the entire view to see percentages on a table
basis, or more interestingly calculate averages as follows:

SELECT AVG(heap) FROM buffersize;

SELECT AVG(index) FROM buffersize;

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html