Re: blks_read/blks_hit stats

On 11-06-15 12:38 PM, Nigel Heron wrote:
> 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>

I understand now.. querying pg_statio_all_tables includes stats from the
cluster wide objects (pg_database, etc.), those stats increase from
queries on other databases as well, hence the gap between the sum and
blks_hit from pg_stat_database.

I think changing the 1st query to this should do the trick..

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
WHERE relid NOT IN (
   SELECT rel.oid
   FROM pg_class rel INNER JOIN pg_tablespace spc ON
(rel.reltablespace=spc.oid)
   WHERE spc.spcname='pg_global'
)

i'll put the cluster wide object stats on another graph

-nigel.