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

BUG #16314: Database Cache Hit Ratio (Warning)

From
PG Bug reporting form
Date:
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


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

From
Jeff Janes
Date:
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

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

From
Tomas Vondra
Date:
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



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

From
Rajiv Ranjan
Date:
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 

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

From
Tomas Vondra
Date:
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



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

From
Tomas Vondra
Date:
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