Shared buffers increased but cache hit ratio is still 85%

From: Hans Schou <hans(dot)schou(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Shared buffers increased but cache hit ratio is still 85%
Date: 2018-07-18 08:26:21
Message-ID: CAApBw37fKf42KjQbt3cQfj7V4gx7LPAdLOMsLCRP__2TRRhvVg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I have this system with some databases and I have run the
cache_hit_ratio.sql script on it. It showed that the db acme777booking had
a ratio on 85%. I then changed shared_buffer size from 0.5GB to 4GB as the
server has 16GB of physical RAM. After 6 days of running I checked the
ratio again and it is still 85%.
Am I doing something wrong or should some history be cleared?

cache_hit_ratio.sql
datname | blks_read | blks_hit | cachehitratio
----------------+------------+--------------+---------------
acme777web | 50225009 | 3157586919 | 98.43
acmelog | 462198 | 14332508 | 96.88
acme777domain | 7540616252 | 119574349075 | 94.07
acme777booking | 337915568 | 1902310783 | 84.92
(4 rows)

pg_runtime.sql
pg_start | runtime
-------------------------------+------------------------
2018-07-12 06:49:48.654252+02 | 6 days 02:44:09.488978
(1 row)

get_version_num.sql
Version text | Num
--------------+-------
9.1.9 | 90109
(1 row)

SELECT pg_stat_database.datname,
pg_stat_database.blks_read,
pg_stat_database.blks_hit,
round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+1)::double precision * 100::double precision)::numeric,
2) AS cachehitratio
FROM pg_stat_database
WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
ORDER BY round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+ 1)::double precision * 100::double
precision)::numeric, 2) DESC;

OS: Red Hat Enterprise Linux Server release 6.4 (Santiago)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2018-07-18 08:43:30 Re: Shared buffers increased but cache hit ratio is still 85%
Previous Message Thomas Kellerer 2018-07-18 07:53:30 A bit confused about "pgsql_tmp" vs "temp tablespace"