Date: Fri, 5 Jun 2020 08:16:42 -0300 (BRT)
From: luis.roberto@siscobra.com.br
To: Krzysztof Olszewski <kolszew73@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Message-ID: 
 <1732071732.32630732.1591355802776.JavaMail.zimbra@siscobra.com.br>
In-Reply-To: 
 <CAHihO3wAmb_b=uMT86PhJYk3-C-4_pNBqJ-TUehZVk82ifFFDw@mail.gmail.com>
References: 
 <CAHihO3wAmb_b=uMT86PhJYk3-C-4_pNBqJ-TUehZVk82ifFFDw@mail.gmail.com>
Subject: Re: Postgresql server gets stuck at low load
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=_5580d095-f2ee-45d4-8701-03608fd2f652"
Thread-Topic: Postgresql server gets stuck at low load
Thread-Index: Cp9NvVIFXP4GCEFWagmY7MmE7ynO6Q==
Precedence: bulk

--=_5580d095-f2ee-45d4-8701-03608fd2f652
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


De: "Krzysztof Olszewski" <kolszew73@gmail.com>=20
Para: pgsql-performance@lists.postgresql.org=20
Enviadas: Sexta-feira, 5 de junho de 2020 7:07:02=20
Assunto: Postgresql server gets stuck at low load=20


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 state=
ments amount, machine gets stuck. Cores goes up to 100%, load up to 160%. W=
hen 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 h=
ost).=20
My hardware56 cores (Intel Core Processor (Skylake, IBRS))400 GB RAMRAID10 =
with about 40k IOPS=20
Os=20
CentOS Linux release 7.7.1908=20
kernel 3.10.0-1062.18.1.el7.x86_64 Databasesize 100 GB (entirely fit in mem=
ory :) )server_version 10.12effective_cache_size 192000 MBmaintenance_work_=
mem 2048 MBmax_connections 150 shared_buffers 64000 MBwork_mem 96 MBOn norm=
al 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 i=
s no problem with this values (no iowaits on cores). In normal state this m=
achine does "nothing". Connections to database are created by two app serve=
rs 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 st=
uck goes into max (120).In correlation with stucks i see informations in ke=
rnel log aboutNMI watchdog: BUG: soft lockup - CPU#25 stuck for 23s! [postm=
aster:33935]but i don't know this is reason or effect of problemI made inve=
stigation with pgBadger and ... nothing strange happens, just normal statem=
ents Any ideas? Thanks,=20
Kris=20

BQ_END

Hi Krzysztof!=20

I would enable pg_stat_statements extension and check if there are long run=
ning queries that should be quick.=20

--=_5580d095-f2ee-45d4-8701-03608fd2f652
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"font-family: arial, helvetica, sans-serif; font-s=
ize: 12pt; color: #000000"><div><br></div><blockquote><div data-marker=3D"_=
_HEADERS__"><b>De: </b>"Krzysztof Olszewski" &lt;kolszew73@gmail.com&gt;<br=
><b>Para: </b>pgsql-performance@lists.postgresql.org<br><b>Enviadas: </b>Se=
xta-feira, 5 de junho de 2020 7:07:02<br><b>Assunto: </b>Postgresql server =
gets stuck at low load</div><div><br></div></blockquote><div data-marker=3D=
"__QUOTED_TEXT__"><div dir=3D"ltr"><blockquote><div>I have problem with one=
 of my Postgres production server. Server works fine almost always, but som=
etimes without any increase of transactions or statements amount, machine g=
ets stuck. Cores goes up to 100%, load up to 160%. When it happens then the=
re are problems with connect to database and even it will succeed, simple q=
ueries works several seconds instead of milliseconds.Problem sometimes stop=
s after a period a time (e.g. 35 min), sometimes we must restart Postgres, =
Linux, or even KVM (which exists as virtualization host).</div>My hardware5=
6 cores (Intel Core Processor (Skylake, IBRS))400 GB RAMRAID10 with about 4=
0k IOPS<div>Os</div><div>CentOS Linux release 7.7.1908</div>kernel 3.10.0-1=
062.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 hav=
e about 500 tps, 5% usage of cores, about 3% of load, whole database fits i=
n memory, no reads from disk, only writes on about 500 IOPS level, sometime=
s in spikes on 1500 IOPS level, but on this hardware there is no problem wi=
th this values (no iowaits on cores). In normal state this machine does "no=
thing". Connections to database are created by two app servers based on Jav=
a, through connection pools, so connections count is limited by configurati=
on 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 m=
ax (120).In correlation with stucks i see informations in kernel log aboutN=
MI 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,<div>Kris</div></blockquote></div></div><div data-marker=3D"__QUOTE=
D_TEXT__">Hi Krzysztof!</div><div data-marker=3D"__QUOTED_TEXT__"><br data-=
mce-bogus=3D"1"></div><div data-marker=3D"__QUOTED_TEXT__">I would enable p=
g_stat_statements extension and check if there are long running queries tha=
t should be quick.</div></div></body></html>
--=_5580d095-f2ee-45d4-8701-03608fd2f652--