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