On Fri, Apr 10, 2020 at 1:16 AM Justin Pryzby <pryzby@xxxxxxxxxxxxx> 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()
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
"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"
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
"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"
