From: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | Scott Frazer <sfrazer(at)couponcabin(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Help troubleshooting SubtransControlLock problems |
Date: | 2018-03-07 16:39:26 |
Message-ID: | 20180307163926.2xd26uqwefzowxtm@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Mueller | 2018-03-07 17:21:12 | a very primitive question about division |
Previous Message | Laurenz Albe | 2018-03-07 16:37:09 | Re: Help troubleshooting SubtransControlLock problems |