Date: Thu, 16 Aug 2018 15:36:32 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Fred Habash <fmhabash@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Guideline To Resolve LWLock:SubtransControlLock
Message-ID: <20180816183632.lo34je5dudbros4a@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CADpeV5wA_Ekz9yNEMKbazwvm+jfhk02tQGjK=G+_4BcGF-2GOw@mail.gmail.com>
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
Precedence: bulk

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