Обсуждение: Shared buffers increased but cache hit ratio is still 85%

Поиск
Список
Период
Сортировка

Shared buffers increased but cache hit ratio is still 85%

От
Hans Schou
Дата:
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)

Re: Shared buffers increased but cache hit ratio is still 85%

От
Andreas Kretschmer
Дата:

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

Re: Shared buffers increased but cache hit ratio is still 85%

От
Hans Schou
Дата:
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.

Re: Shared buffers increased but cache hit ratio is still 85%

От
Tomas Vondra
Дата:
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

Re: Shared buffers increased but cache hit ratio is still 85%

От
Adrien NAYRAT
Дата:
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,