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)
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" |