Re: Help troubleshooting SubtransControlLock problems

On Wed, Mar 7, 2018 at 10:39 AM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
wrote:
>
> Laurenz Albe wrote:
> 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.

Currently this seems to be happening when the server just starts taking
connections. It doesn't even need time to scale up anymore (previously it
took about 8 hours for the problem to re-appear)

It's only happening on the read replicas, though. I've just set my master
to handle all the traffic, but that's not really sustainable