blks_read/blks_hit stats

From: Nigel Heron <nigel(at)psycode(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: blks_read/blks_hit stats
Date: 2011-06-15 16:38:15
Message-ID: 4DF8DFF7.10708@psycode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi everyone,

I'm playing with the stats views and functions to graph them in cacti..
Adding up *_blks_hit (heap, idx, toast and tidx) from pg_statio doesn't
match blks_hit in pg_stat_database.
Sometimes the sum is higher, sometimes lower. Do they have similar names
but represent different metrics?
Same issue with blks_read.

eg.
SELECT
SUM(pg_statio_all_tables.heap_blks_hit)::bigint +
SUM(pg_statio_all_tables.idx_blks_hit)::bigint +
SUM(pg_statio_all_tables.toast_blks_hit)::bigint +
SUM(pg_statio_all_tables.tidx_blks_hit)::bigint AS blks_hit
FROM pg_statio_all_tables;
blks_hit
------------
1275299563
(1 row)

SELECT blks_hit
FROM pg_stat_database
where datname='mydb';
blks_hit
-----------
674295210
(1 row)

here's 2 graphs from different databases on the same cluster (8.4.2).
first 4 stacked graph items are from pg_statio_all_tables and the red
line is from pg_stat_database.

blks_hit is way under the sum:
<http://www.psycode.com/gallery/d/88438-1/blks_read1.png>
blks_hit seems pretty close to the sum of table+idx (but no toast):
<http://www.psycode.com/gallery/d/88440-1/blks_read2.png>

thanks,
-nigel.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Anibal David Acosta 2011-06-15 20:01:36 pgadmin "Running VACUUM recommended"
Previous Message John R Pierce 2011-06-15 16:29:07 Re: Setting up tablepace