Обсуждение: 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)
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)
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)
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;
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()
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,