How to calculate hitrate of shared buffer in postgresql, using TPC-C workload?
0
votes
0
answers
213
views
I use TPC-C(benchmarksql 4.0) to test postgres. I set up
=1000
, the whole database is about
, and I set =10GB
in .conf
. I also set =false
to change the warehouse, and I run the benchmark for
.
But when I calculate the hitrate with the sql below, I got a very high hitrate, that was strange. I also print the workload of benchmarksql, it did choose warehouse, district, customer at random. And I modified postgres to output the access times of blocks of the table file, though it is skewed, but it can't cause that high hit rate in shared buffer.
with
all_tables as
(
SELECT *
FROM (
SELECT 'all'::text as table_name,
sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
sum( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) + coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache
FROM pg_statio_USER_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
) a
WHERE (from_disk + from_cache) > 0 -- discard tables without hits
),
tables as
(
SELECT *
FROM (
SELECT relname as table_name,
( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) + coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache
FROM pg_statio_USER_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
) a
WHERE (from_disk + from_cache) > 0 -- discard tables without hits
)
SELECT table_name as "table name",
from_disk as "disk hits",
round((from_disk::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% disk hits",
round((from_cache::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% cache hits",
(from_disk + from_cache) as "total hits"
FROM (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a
ORDER BY (case when table_name = 'all' then 0 else 1 end), from_disk desc;

Asked by Hongtao Wang
(1 rep)
Jul 4, 2023, 07:30 AM