Re: Postgresql server gets stuck at low load

I had log_min_duration_statement set to 0 for a short period, just before
stuck and just after, so I have full list of SQL statements, next analyzed
in pgBadger, there is no increase of amount of statements, and I can see,
all statements are longer processed than before stuck. But following Your
advice I'll check the results from pg_stat_statements.

pt., 5 cze 2020 o 13:16 <luis(dot)roberto(at)siscobra(dot)com(dot)br> napisał(a):

>
> *De: *"Krzysztof Olszewski" <kolszew73(at)gmail(dot)com>
> *Para: *pgsql-performance(at)lists(dot)postgresql(dot)org
> *Enviadas: *Sexta-feira, 5 de junho de 2020 7:07:02
> *Assunto: *Postgresql server gets stuck at low load
>
> 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
>
> Hi Krzysztof!
>
> I would enable pg_stat_statements extension and check if there are long
> running queries that should be quick.
>