High insert rate server, unstable insert latency and load peaks with buffer_content and XidGenLock LWlocks with Postgresql 12 version

[Maxim Boguk <maxim.boguk@xxxxxxxxx>]

Hi,

I have performance issues which I never seen before in my 20+ years experience with PostgreSQL.

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

To analyze what going with locks I run 

\o ~/tmp/watch_events.log
select wait_event_type,wait_event,count(*) from pg_stat_activity where state='active' and backend_type='client backend' group by 1,2 order by 3 desc
\watch 0.1

Normal output when all goes well:

 wait_event_type | wait_event | count
-----------------+------------+-------
 Client          | ClientRead |     5
                 |            |     4

(few processes running queries and few processes doing network IO)

Bad case (few times per minute, huge latency peak, some inserts took up to 100ms to run):

 wait_event_type |   wait_event   | count
-----------------+----------------+-------
 LWLock          | buffer_content |   178
 LWLock          | XidGenLock     |    21
 IO              | SLRUSync       |     1
                 |                |     1

So there are almost all backends waiting on buffer_content lock and some backends waiting for XidGenLock .

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.

--

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

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