Shared buffers increased but cache hit ratio is still 85%
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)
Am 18.07.2018 um 10:26 schrieb Hans Schou:
Am I doing something wrong or should some history be cleared?
Reset the stats for that database. You can check the date of last reset
with:
select stats_reset from pg_stat_database where datname = 'database_name';
and reset it with:
||pg_stat_reset()
||
||Reset all statistics counters for the current database to zero
(requires superuser privileges by default, but EXECUTE for this function
can be granted to others.)||
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
On Wed, Jul 18, 2018 at 10:44 AM Andreas Kretschmer <andreas@a-kretschmer.de>
wrote:
||pg_stat_reset()
Thanks, I guess we can see the result in a few days.
BTW, strang command: it only reset current database and it can't take db as
parameter.
On 07/18/2018 10:43 AM, Andreas Kretschmer wrote:
Am 18.07.2018 um 10:26 schrieb Hans Schou:
Am I doing something wrong or should some history be cleared?
Reset the stats for that database. You can check the date of last reset
with:select stats_reset from pg_stat_database where datname = 'database_name';
and reset it with:
||pg_stat_reset()
||
||Reset all statistics counters for the current database to zero
(requires superuser privileges by default, but EXECUTE for this function
can be granted to others.)||
It might be better to note current values of the counters somewhere, and
compute a delta later (and use that to compute the cache hit ratio). The
issue is that pg_stat_reset() throws away all sorts of interesting and
important stats, including those driving autovacuum/autoanalyze.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 07/18/2018 10:26 AM, Hans Schou wrote:
Am I doing something wrong or should some history be cleared?
Hi,
FIY, check_pgactivity save the diff between each call to compute the
real hit ratio : https://github.com/OPMDG/check_pgactivity
Regards,