Re: SubtransControlLock and performance problems

Lars Aksel Opsahl wrote:
> What happens is that after some minutes the CPU can fall to maybe 20% usage and most of
> the threads are blocked by SubtransControlLock, and when the number SubtransControlLock
> goes down the CPU load increases again. The jobs usually goes through without any errors,
> but it takes to long time because of the SubtransControlLock blocks.

That's typically a sign that you are using more than 64 subtransactions per transaction.

That could either be SAVEPOINT SQL statements or PL/pgSQL code with blocks
containing the EXCEPTION clause.

The data structure in shared memory that holds information for each session
can cache 64 subtransactions, beyond that it has to access "pg_subtrans" to get
the required information, which leads to contention.

Often the problem is caused by a misguided attempt to wrape every single
statement in a subtransaction to emulate the behavior of other database
systems, for example with the "autosave = always" option of the JDBC driver.

The solution is to use fewer subtransactions per transaction.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com