pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16314: Database Cache Hit Ratio (Warning)
6+ messages / 4 participants
[nested] [flat]

* BUG #16314: Database Cache Hit Ratio (Warning)
@ 2020-03-24 12:06 PG Bug reporting form <noreply@postgresql.org>
  2020-03-24 15:44 ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Jeff Janes <jeff.janes@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: PG Bug reporting form @ 2020-03-24 12:06 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: rajiv.mca08@gmail.com

The following bug has been logged on the website:

Bug reference:      16314
Logged by:          Rajiv Ranjan
Email address:      rajiv.mca08@gmail.com
PostgreSQL version: 9.6.0
Operating system:   Red Hat Enterprise Linux Server release 7.6 (Maip)
Description:        

Hi,

Currently, we are receiving a warning "Database Cache Hit Ratio
(%)(Warning)" from one of the monitoring tools.

We use Postgres database for Jira, confluence, and bitbucket application and
hosted on a different machine and only 35% disc space is used.

Database warning “Database Cache Hit Ratio” issue has been fixed by
increasing the below values however after a few days of stability the
warning is back hence not sure increasing the below parameters are correct
or not.

shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB

Could you please suggest how much we can increase the parameters and how
does it impact the database. 

Thanks,
Rajiv Ranjan



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16314: Database Cache Hit Ratio (Warning)
  2020-03-24 12:06 BUG #16314: Database Cache Hit Ratio (Warning) PG Bug reporting form <noreply@postgresql.org>
@ 2020-03-24 15:44 ` Jeff Janes <jeff.janes@gmail.com>
  2020-03-25 19:21   ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Tomas Vondra <tomas.vondra@2ndquadrant.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Jeff Janes @ 2020-03-24 15:44 UTC (permalink / raw)
  To: rajiv.mca08@gmail.com, pgsql-bugs <pgsql-bugs@lists.postgresql.org>

On Tue, Mar 24, 2020 at 8:06 AM PG Bug reporting form <
noreply@postgresql.org> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      16314
> Logged by:          Rajiv Ranjan
> Email address:      rajiv.mca08@gmail.com
> PostgreSQL version: 9.6.0
> Operating system:   Red Hat Enterprise Linux Server release 7.6 (Maip)
> Description:
>
> Hi,
>
> Currently, we are receiving a warning "Database Cache Hit Ratio
> (%)(Warning)" from one of the monitoring tools.
>

This is not a bug in PostgreSQL.  It is arguably a bug in your unnamed
monitoring tool.

It is a dumb warning. The disk is there to be read from--doing so is not
inherently a problem. Ignore the warning or disable it.

Are the queries you care about too slow because data is occasionally read
from disk?

Cheers,

Jeff

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16314: Database Cache Hit Ratio (Warning)
  2020-03-24 12:06 BUG #16314: Database Cache Hit Ratio (Warning) PG Bug reporting form <noreply@postgresql.org>
  2020-03-24 15:44 ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Jeff Janes <jeff.janes@gmail.com>
@ 2020-03-25 19:21   ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
  2020-03-26 13:51     ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Rajiv Ranjan <rajiv.mca08@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tomas Vondra @ 2020-03-25 19:21 UTC (permalink / raw)
  To: Jeff Janes <jeff.janes@gmail.com>; +Cc: rajiv.mca08@gmail.com, pgsql-bugs <pgsql-bugs@lists.postgresql.org>

On Tue, Mar 24, 2020 at 11:44:10AM -0400, Jeff Janes wrote:
>On Tue, Mar 24, 2020 at 8:06 AM PG Bug reporting form <
>noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      16314
>> Logged by:          Rajiv Ranjan
>> Email address:      rajiv.mca08@gmail.com
>> PostgreSQL version: 9.6.0
>> Operating system:   Red Hat Enterprise Linux Server release 7.6 (Maip)
>> Description:
>>
>> Hi,
>>
>> Currently, we are receiving a warning "Database Cache Hit Ratio
>> (%)(Warning)" from one of the monitoring tools.
>>
>
>This is not a bug in PostgreSQL.  It is arguably a bug in your unnamed
>monitoring tool.
>

Yeah, not a PostgreSQL bug.

>It is a dumb warning. The disk is there to be read from--doing so is not
>inherently a problem. Ignore the warning or disable it.
>

I wouldn't say it's entirely dumb, improving cache hit ratio may make 
huge difference, but it requires a lot more information than was
provided.

Rajiv, please send your question to pgsql-general or pgsql-performance
list, and be sure to include information about the system (amount of RAM
for example), resource utilization, workload, threshold currently used
by the alert, actual cache hit ratio, etc.

>Are the queries you care about too slow because data is occasionally read
>from disk?
>

Yeah, it makes little sense to deal with this unless you can demonstrate
some practical impact. In my experience low cache hit ratio values are
an issue with write-intensive workloads, in which case it may cause
significant write amplification.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16314: Database Cache Hit Ratio (Warning)
  2020-03-24 12:06 BUG #16314: Database Cache Hit Ratio (Warning) PG Bug reporting form <noreply@postgresql.org>
  2020-03-24 15:44 ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Jeff Janes <jeff.janes@gmail.com>
  2020-03-25 19:21   ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2020-03-26 13:51     ` Rajiv Ranjan <rajiv.mca08@gmail.com>
  2020-03-26 15:40       ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Tomas Vondra <tomas.vondra@2ndquadrant.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Rajiv Ranjan @ 2020-03-26 13:51 UTC (permalink / raw)
  To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Hi Tomas,

I can't find pgsql-general or pgsql-performance where I post my query hence
posting all details here.

Below are hardware, CPU, disk and memory utilization of the system. Kindly
suggest the most suitable parameters for a cache hit ratio or we can simply
ignore it.

The threshold for the “database cache hit ratio %” is 90% for a High and
95% for Critical.

Hardware:
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                8
On-line CPU(s) list:   0-7
Thread(s) per core:    1
Core(s) per socket:    4
Socket(s):             2
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 45
Model name:            Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz
Stepping:              2
CPU MHz:               2494.224
BogoMIPS:              4988.44
Hypervisor vendor:     VMware
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              30720K
NUMA node0 CPU(s):     0-7


Resource Utilization:

MemTotal:        8008640 kB
MemFree:          145332 kB
MemAvailable:    4710460 kB
Buffers:               0 kB
Cached:          6877160 kB
SwapCached:        19168 kB
Active:          4853536 kB
Inactive:        2575608 kB
Active(anon):    2023944 kB
Inactive(anon):   694232 kB
Active(file):    2829592 kB
Inactive(file):  1881376 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       8388604 kB
SwapFree:        7596796 kB
Dirty:              1660 kB
Writeback:            12 kB
AnonPages:        540584 kB
Mapped:          2189176 kB
Shmem:           2165612 kB
Slab:             212860 kB
SReclaimable:     151188 kB
SUnreclaim:        61672 kB
KernelStack:        4976 kB
PageTables:        90028 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    12392924 kB
Committed_AS:    4439168 kB
VmallocTotal:   34359738367 kB
VmallocUsed:      169396 kB
VmallocChunk:   34359341052 kB
HardwareCorrupted:     0 kB
AnonHugePages:     77824 kB
CmaTotal:              0 kB
CmaFree:               0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:      174016 kB
DirectMap2M:     8214528 kB

procs -----------memory---------- ---swap-- -----io---- -system--
------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id
wa st
 3  0 797184 200984      0 7021436    0    0   414   241    0    0  8  1 88
 3  0


 PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 3809 postgres  20   0 2439200   2.1g   2.1g S  37.2 27.2 387:11.59
postmaster
 7998 postgres  20   0 2395212 219828 217564 S  11.3  2.7   0:02.53
postmaster
 7999 postgres  20   0 2395208  58384  56200 S  11.3  0.7   0:02.07
postmaster
 8000 postgres  20   0 2395208  59456  57216 S  11.3  0.7   0:02.08
postmaster
 7214 postgres  20   0 2397520   1.9g   1.9g D  11.0 24.4   1:35.25
postmaster
 8003 postgres  20   0 2395208  57848  55656 S  10.3  0.7   0:02.08
postmaster
 8001 postgres  20   0 2399704   1.3g   1.3g D   3.3 17.1   0:15.29
postmaster
21979 postgres  20   0 2412120   2.1g   2.1g S   2.7 27.1 296:22.44
postmaster

Disk Space:
Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/localvg-rootlv  242G   68G  175G  28% /
devtmpfs                    3.9G     0  3.9G   0% /dev
tmpfs                       3.9G  4.0K  3.9G   1% /dev/shm
tmpfs                       3.9G  386M  3.5G  10% /run
tmpfs                       3.9G     0  3.9G   0% /sys/fs/cgroup
/dev/sda1                   497M  186M  312M  38% /boot
tmpfs                       783M     0  783M   0% /run/user/1000




On Thu, 26 Mar 2020 at 00:51, Tomas Vondra <tomas.vondra@2ndquadrant.com>
wrote:

> On Tue, Mar 24, 2020 at 11:44:10AM -0400, Jeff Janes wrote:
> >On Tue, Mar 24, 2020 at 8:06 AM PG Bug reporting form <
> >noreply@postgresql.org> wrote:
> >
> >> The following bug has been logged on the website:
> >>
> >> Bug reference:      16314
> >> Logged by:          Rajiv Ranjan
> >> Email address:      rajiv.mca08@gmail.com
> >> PostgreSQL version: 9.6.0
> >> Operating system:   Red Hat Enterprise Linux Server release 7.6 (Maip)
> >> Description:
> >>
> >> Hi,
> >>
> >> Currently, we are receiving a warning "Database Cache Hit Ratio
> >> (%)(Warning)" from one of the monitoring tools.
> >>
> >
> >This is not a bug in PostgreSQL.  It is arguably a bug in your unnamed
> >monitoring tool.
> >
>
> Yeah, not a PostgreSQL bug.
>
> >It is a dumb warning. The disk is there to be read from--doing so is not
> >inherently a problem. Ignore the warning or disable it.
> >
>
> I wouldn't say it's entirely dumb, improving cache hit ratio may make
> huge difference, but it requires a lot more information than was
> provided.
>
> Rajiv, please send your question to pgsql-general or pgsql-performance
> list, and be sure to include information about the system (amount of RAM
> for example), resource utilization, workload, threshold currently used
> by the alert, actual cache hit ratio, etc.
>
> >Are the queries you care about too slow because data is occasionally read
> >from disk?
> >
>
> Yeah, it makes little sense to deal with this unless you can demonstrate
> some practical impact. In my experience low cache hit ratio values are
> an issue with write-intensive workloads, in which case it may cause
> significant write amplification.
>
>
> regards
>
> --
> Tomas Vondra                  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>


-- 
*Thanks,*
*Rajiv Ranjan *

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16314: Database Cache Hit Ratio (Warning)
  2020-03-24 12:06 BUG #16314: Database Cache Hit Ratio (Warning) PG Bug reporting form <noreply@postgresql.org>
  2020-03-24 15:44 ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Jeff Janes <jeff.janes@gmail.com>
  2020-03-25 19:21   ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Tomas Vondra <tomas.vondra@2ndquadrant.com>
  2020-03-26 13:51     ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Rajiv Ranjan <rajiv.mca08@gmail.com>
@ 2020-03-26 15:40       ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
  2020-03-27 14:50         ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Tomas Vondra <tomas.vondra@2ndquadrant.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tomas Vondra @ 2020-03-26 15:40 UTC (permalink / raw)
  To: Rajiv Ranjan <rajiv.mca08@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

On Thu, Mar 26, 2020 at 07:21:42PM +0530, Rajiv Ranjan wrote:
>Hi Tomas,
>
>I can't find pgsql-general or pgsql-performance where I post my query hence
>posting all details here.
>

See the mailing lists at https://www.postgresql.org/list/

I'm not going to comment here, because this clearly is not a bug.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16314: Database Cache Hit Ratio (Warning)
  2020-03-24 12:06 BUG #16314: Database Cache Hit Ratio (Warning) PG Bug reporting form <noreply@postgresql.org>
  2020-03-24 15:44 ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Jeff Janes <jeff.janes@gmail.com>
  2020-03-25 19:21   ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Tomas Vondra <tomas.vondra@2ndquadrant.com>
  2020-03-26 13:51     ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Rajiv Ranjan <rajiv.mca08@gmail.com>
  2020-03-26 15:40       ` Re: BUG #16314: Database Cache Hit Ratio (Warning) Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2020-03-27 14:50         ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Tomas Vondra @ 2020-03-27 14:50 UTC (permalink / raw)
  To: Rajiv Ranjan <rajiv.mca08@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

On Fri, Mar 27, 2020 at 12:19:02PM +0530, Rajiv Ranjan wrote:
>I saw this but how could I submit my case
>https://www.postgresql.org/list/pgsql-general/
>

You need to subscribe to the mailing list, the instructions are here:

   https://www.postgresql.org/list/

and the URL for managing subscriptions is here:

   https://lists.postgresql.org/

You already have the community account I believe (you had to create one
before submitting the bug).

FWIW it's customary here not to top post, because it makes it harder to
follow the discussion. Please respond in-line in the future.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2020-03-27 14:50 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-24 12:06 BUG #16314: Database Cache Hit Ratio (Warning) PG Bug reporting form <noreply@postgresql.org>
2020-03-24 15:44 ` Jeff Janes <jeff.janes@gmail.com>
2020-03-25 19:21   ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2020-03-26 13:51     ` Rajiv Ranjan <rajiv.mca08@gmail.com>
2020-03-26 15:40       ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2020-03-27 14:50         ` Tomas Vondra <tomas.vondra@2ndquadrant.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox