From: | <soumik(dot)bhattacharjee(at)kpn(dot)com> |
---|---|
To: | <f(dot)pardi(at)portavita(dot)eu>, <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | RE: Cache Hit Ratio% |
Date: | 2020-07-28 12:49:39 |
Message-ID: | 2A91BEF8171A5349931391E0C721CC539696226A@CPEMS-KPN301.KPNCNL.LOCAL |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Thanks Fabio for your help.
From: Fabio Pardi <f(dot)pardi(at)portavita(dot)eu>
Sent: dinsdag 28 juli 2020 11:46
To: Bhattacharjee, Soumik <soumik(dot)bhattacharjee(at)kpn(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: 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<mailto: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><mailto:f(dot)pardi(at)portavita(dot)eu>; pgsql-admin(at)lists(dot)postgresql(dot)org<mailto: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
[cid:image001(dot)jpg(at)01D664EE(dot)513F1110]
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
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Fiske | 2020-07-28 13:55:39 | Re: How can i be certain autovacuum is causing reuse if table still grows |
Previous Message | Sidney Aloisio Ferreira Pryor | 2020-07-28 10:17:57 | Re: How can i be certain autovacuum is causing reuse if table still grows |