Date: Thu, 9 Apr 2020 10:16:33 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Maxim Boguk <maxim.boguk@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: High insert rate server, unstable insert latency and load peaks
 with buffer_content and XidGenLock LWlocks with Postgresql 12 version
Message-ID: <20200409151633.GO2228@telsasoft.com>
References: 
 <CAK-MWwQ46LUfmeN=fF-ykUnrtoShq+p1T=hCHFNS2Fny46ierA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAK-MWwQ46LUfmeN=fF-ykUnrtoShq+p1T=hCHFNS2Fny46ierA@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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