Re: Guideline To Resolve LWLock:SubtransControlLock

Aurora Postgres 9.6.3
So, no chance to recompile (AFAIK).

Is there a design anti-pattern at the schema or data access level that we
should look for and correct?

And as for the recompile, are you thinking 'NUM_SUBTRANS_BUFFERS'?

Thanks

On Thu, Aug 16, 2018 at 2:36 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> On 2018-Aug-16, Fred Habash wrote:
>
> > One of our database API's is run concurrently by near 40 sessions. We see
> > all of them waiting back and forth on this wait state.
>
> What version are you running?
>
> > Why is it called Subtrans Control Lock?
>
> It controls access to the pg_subtrans structure, which is used to record
> parent/child transaction relationships (as you say, savepoints and
> EXCEPTIONs in plpgsql are the most common uses, but not the only ones).
> Normally lookup of these is optimized away, but once you cross a
> threshold it cannot any longer.
>
> > What are the common user session scenarios causing this wait?
> > - I have read some describe the use of SQL savepoints or PL/pgSQL
> > exception handling.
> > What are known resolution measures?
>
> Are you in a position to recompile Postgres?
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

--

----------------------------------------
Thank you