From: luis.roberto@siscobra.com.br
To: Krzysztof Olszewski <kolszew73@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql server gets stuck at low load
Date: Fri, 5 Jun 2020 08:16:42 -0300 (BRT)
Message-ID: <1732071732.32630732.1591355802776.JavaMail.zimbra@siscobra.com.br> (raw)
In-Reply-To: <CAHihO3wAmb_b=uMT86PhJYk3-C-4_pNBqJ-TUehZVk82ifFFDw@mail.gmail.com>
References: <CAHihO3wAmb_b=uMT86PhJYk3-C-4_pNBqJ-TUehZVk82ifFFDw@mail.gmail.com>
De: "Krzysztof Olszewski" <kolszew73@gmail.com>
Para: pgsql-performance@lists.postgresql.org
Enviadas: Sexta-feira, 5 de junho de 2020 7:07:02
Assunto: Postgresql server gets stuck at low load
BQ_BEGIN
I have problem with one of my Postgres production server. Server works fine almost always, but sometimes without any increase of transactions or statements amount, machine gets stuck. Cores goes up to 100%, load up to 160%. When it happens then there are problems with connect to database and even it will succeed, simple queries works several seconds instead of milliseconds.Problem sometimes stops after a period a time (e.g. 35 min), sometimes we must restart Postgres, Linux, or even KVM (which exists as virtualization host).
My hardware56 cores (Intel Core Processor (Skylake, IBRS))400 GB RAMRAID10 with about 40k IOPS
Os
CentOS Linux release 7.7.1908
kernel 3.10.0-1062.18.1.el7.x86_64 Databasesize 100 GB (entirely fit in memory :) )server_version 10.12effective_cache_size 192000 MBmaintenance_work_mem 2048 MBmax_connections 150 shared_buffers 64000 MBwork_mem 96 MBOn normal state, i have about 500 tps, 5% usage of cores, about 3% of load, whole database fits in memory, no reads from disk, only writes on about 500 IOPS level, sometimes in spikes on 1500 IOPS level, but on this hardware there is no problem with this values (no iowaits on cores). In normal state this machine does "nothing". Connections to database are created by two app servers based on Java, through connection pools, so connections count is limited by configuration of pools and max is 120, is lower value than in Postgres configuration (150). On normal state there is about 20 connections, when stuck goes into max (120).In correlation with stucks i see informations in kernel log aboutNMI watchdog: BUG: soft lockup - CPU#25 stuck for 23s! [postmaster:33935]but i don't know this is reason or effect of problemI made investigation with pgBadger and ... nothing strange happens, just normal statements Any ideas? Thanks,
Kris
BQ_END
Hi Krzysztof!
I would enable pg_stat_statements extension and check if there are long running queries that should be quick.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: luis.roberto@siscobra.com.br, kolszew73@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql server gets stuck at low load
In-Reply-To: <1732071732.32630732.1591355802776.JavaMail.zimbra@siscobra.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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