Re: Cache Hit Ratio%

Depending on your PostgreSQL RAM budget, you might want to increase the shared_buffers in order to be able to fit more in it.

If I were you, I would investigate the matter more in depth, and educate yourself on the subject.

You can get insights using pg_buffercache:

https://www.postgresql.org/docs/current/pgbuffercache.html

When in need, I also use the following query to understand how the buffers are rotating.

select usagecount,count(*),isdirty
from pg_buffercache
group by isdirty,usagecount
order by isdirty,usagecount ;

hope it helps.

regards,

fabio pardi

On 28/07/2020 11:18, soumik(dot)bhattacharjee(at)kpn(dot)com wrote:
>
> Hi,
>
>  
>
> This are the values for
>
>  
>
> * effective_cache_size = 6GB
> * OS RAM = 25GB
>
> Thanks..
>
>  
>
> *From:*Bhattacharjee, Soumik
> *Sent:* dinsdag 28 juli 2020 10:36
> *To:* Fabio Pardi <f(dot)pardi(at)portavita(dot)eu>; pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* RE: Cache Hit Ratio%
>
>  
>
> Hi Fabio,
>
>  
>
> Its same across  all env's
>
>  
>
> * Shared_buffers is 2GB
> * DB size is same 12GB
>
>  
>
> *_Query Plan in PROD Env_*
>
>  
>
> *From:*Fabio Pardi <f(dot)pardi(at)portavita(dot)eu <mailto:f(dot)pardi(at)portavita(dot)eu>>
> *Sent:* dinsdag 28 juli 2020 10:13
> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org <mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>
> *Subject:* Re: Cache Hit Ratio%
>
>  
>
> Hi,
>
> I would start checking (and comparing with pre-prod) the db size, the shared_buffers size, how much data end up in cache and how are the cache layer's used.
>
> regards,
>
> fabio pardi
>
> On 28/07/2020 10:04, soumik(dot)bhattacharjee(at)kpn(dot)com <mailto:soumik(dot)bhattacharjee(at)kpn(dot)com> wrote:
>
> Dear Experts,
>
>  
>
> We have done production migration from Oracle to PostgreSQL.
>
>  
>
> Could you please help me to know where to look out for - the Cache Hit Ratio is always at *65.72%* , but in *lower Pre-Prod env* it’s kind of 95%.
>
>  
>
> As such no major slowness is reported by the customer so far.
>
>  
>
>  
>
> Thanks
>
>  
>
>  
>
>  
>
>  
>
>  
>
>  
>