Postgres Pro
Downloads
Home   >   mailing lists

Re: High insert rate server, unstable insert latency and load peakswith buffer_content and XidGenLock LWlocks with Postgresql 12 version - Mailing list pgsql-performance

From Maxim Boguk
Subject Re: High insert rate server, unstable insert latency and load peakswith buffer_content and XidGenLock LWlocks with Postgresql 12 version
Date
Msg-id CAK-MWwRBaW_0hTsurxSJh9W2PK+Y4ZcCM-kSdwBJiRqb5ttS9w@mail.gmail.com
Whole thread Raw
In response to Re: High insert rate server, unstable insert latency and load peakswith buffer_content and XidGenLock LWlocks with Postgresql 12 version  (Justin Pryzby <pryzby@telsasoft.com>)
List pgsql-performance
Tree view


On Fri, Apr 10, 2020 at 1:16 AM Justin Pryzby <pryzby@telsasoft.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

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

pgsql-performance by date:

Previous
From: Justin Pryzby
Date:
Subject: Re: High insert rate server, unstable insert latency and load peakswith buffer_content and XidGenLock LWlocks with Postgresql 12 version
Next
From: Don Seiler
Date:
Subject: High kswapd