Re: BUG #16314: Database Cache Hit Ratio (Warning)

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(dot)vondra(at)2ndquadrant(dot)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(at)postgresql(dot)org> wrote:
> >
> >> The following bug has been logged on the website:
> >>
> >> Bug reference: 16314
> >> Logged by: Rajiv Ranjan
> >> Email address: rajiv(dot)mca08(at)gmail(dot)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 *