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
