pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #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