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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
