BUG #16314: Database Cache Hit Ratio (Warning)

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

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

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 *

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

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