Home > mailing lists

Re: BUG #16314: Database Cache Hit Ratio (Warning) - Mailing list pgsql-bugs

From	Rajiv Ranjan
Subject	Re: BUG #16314: Database Cache Hit Ratio (Warning)
Date	March 26, 2020 16:51:42
Msg-id	CAKeFoKuuwHATE0vQ0EJ-tB6w4iw0AOd5O0HHcDz1GYTqoHXdHw@mail.gmail.com Whole thread Raw
In response to	Re: BUG #16314: Database Cache Hit Ratio (Warning) (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
List	pgsql-bugs

Tree view

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

pgsql-bugs by date:

From: Fujii Masao
Date: 26 March 2020, 15:41:34
Subject: Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECTstatements

From: Tomas Vondra
Date: 26 March 2020, 18:40:39
Subject: Re: BUG #16314: Database Cache Hit Ratio (Warning)

Re: BUG #16314: Database Cache Hit Ratio (Warning) - Mailing list pgsql-bugs

Previous

Next