Date: Wed, 19 Feb 2020 12:23:53 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	Tom Lane <tgl@sss.pgh.pa.us>,
	"pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: SubtransControlLock and performance problems
Message-ID: <20200219152353.GA2663@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <HE1P189MB0266DD7C39B4A4C5D174D5269D100@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

On 2020-Feb-19, Lars Aksel Opsahl wrote:

> With the values above I did see same performance problems and we ended
> with a lot of subtransControlLock.
> 
> So I started to change the code based on your feedbacks.
> 
> - What seems to work very good in combination with a catch exception
> and retry pattern is to insert the data in to separate table for each
> job. (I the current testcase we reduced the number of
> subtransControlLock from many hundreds to almost none.)

I think at this point your only recourse is to start taking profiles to
see where the time is going.  Without that, you're just flying blind and
whatever you do will not necessarily move your needle at all.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services