Thread: BUG #16314: Database Cache Hit Ratio (Warning)
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
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
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