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)