Re: High insert rate server, unstable insert latency and load peaks with buffer_content and XidGenLock LWlocks with Postgresql 12 version

On Fri, Apr 10, 2020 at 1:16 AM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> On Fri, Apr 10, 2020 at 12:51:03AM +1000, Maxim Boguk wrote:
> > With database on dedicated server I encountered unusual load profile:
> > multi thread (200 connections static size pool via pgbouncer) insert only
> > into single table around 15.000 insert/s.
> >
> > Usually insert took 0.025ms and amount active backends (via
> > pg_stat_activity) usually stay in 1-5-10 range.
> > But every so while (few times per minute actually) number of active
> backend
> > go up to all 200 allowed connections.
> > Which lead to serious latency in latency sensitive load.
> >
> > No problem with IO latency or CPU usage found during performance analyze.
> > syncronous_commit = off
>
> Can you share other settings ? shared_buffers, checkpoint_*, bgwriter_*
> and
> max_wal_size ? And version()
>

version - PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0,
64-bit
shared_buffers 140GB
checkpoint_timeout 1h
checkpoint_flush_after 0
checkpoint_completion_target 0.9
bgwriter_delay 10ms
bgwriter_flush_after 0
bgwriter_lru_maxpages 10000
bgwriter_lru_multiplier 10
max_wal_size 128GB

Checkpoints happens every 1h and lag spiked doesn't depend on checkpointer
activity.
buffers_checkpoint 92% writes, buffers_clean 2% writes, buffers_backend 6%
writes (over course of 5 minutes).
Nothing especially suspicious on graphical monitoring of these values as
well.

--
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone RU: +7 985 433 0000
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
когда я так делаю ещё раз?"