High insert rate server, unstable insert latency and load peaks withbuffer_content and XidGenLock LWlocks with Postgresql 12 version - Mailing list pgsql-performance

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.