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 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()

> And always one backend in SLRUSync.
>
> If anyone can have any good idea whats going on in that case and how I can
> fix it - any ideas welcome.
> So far I out of ideas.

This might be useful: pg_stat_bgwriter view.

I suggest to follow others advice and make a cronjob to do this every ~5 minutes:
| INSERT INTO jrn_pg_stat_bgwriter SELECT now(), * FROM pg_stat_bgwriter;
and write a window function to show values/time, or rrd graphs or whatever.

--
Justin