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
Date: Thu, 9 Apr 2020 10:16:33 -0500
Message-ID: <20200409151633.GO2228@telsasoft.com> (raw)
In-Reply-To: <CAK-MWwQ46LUfmeN=fF-ykUnrtoShq+p1T=hCHFNS2Fny46ierA@mail.gmail.com>
References: <CAK-MWwQ46LUfmeN=fF-ykUnrtoShq+p1T=hCHFNS2Fny46ierA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, maxim.boguk@gmail.com
Subject: Re: High insert rate server, unstable insert latency and load peaks with buffer_content and XidGenLock LWlocks with Postgresql 12 version
In-Reply-To: <20200409151633.GO2228@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox