Re: Help troubleshooting SubtransControlLock problems

Laurenz Albe wrote:
> Scott Frazer wrote:
> > Hi, we have a Postgres 9.6 setup using replication that has recently started seeing a lot of processes stuck in
> > "SubtransControlLock" as a wait_event on the read-replicas. Like this, only usually about 300-800 of them:
> > 
> > 
> >  179706 | LWLockNamed     | SubtransControlLock

I think you could get in this situation if the range of open
transactions exceeds what fits in the buffers for subtrans.c pages, and
the subtransaction cache overflows (64 entries apiece;
PGPROC_MAX_CACHED_SUBXIDS in src/include/storage/proc.h).  Each page is
2048 transactions (4 bytes per xact, 8192 bytes per page;
SUBTRANS_XACTS_PER_PAGE in src/backend/access/transam/subtrans.c), and
we keep 32 pages (NUM_SUBTRANS_BUFFERS src/include/access/subtrans.h).
So if your oldest transaction is over 64k XIDs old, and you have
transactions with more than 64 subtransactions, you get in trouble.

A simple solution is to increase NUM_SUBTRANS_BUFFERS and recompile.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services