Date: Mon, 17 Feb 2020 16:10:05 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: SubtransControlLock and performance problems
Message-ID: <20200217191005.GA20055@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <HE1P189MB0266B070D1452B04A7C9F6D99D170@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

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

> On a server with 32 cores and 250 GB memory, with CentOS 7 and kernel
> 4.4.214-1.el7.elrepo.x86_64, I try to run 30 parallel threads using
> dblink. (https://github.com/larsop/postgres_execute_parallel) . I have
> tried to disconnect and reconnect in the dblink code and that did not
> help.

I think one issue is that pg_clog has 128 buffers (per commit
5364b357fb1) while subtrans only has 32.  It might be productive to
raise the number of subtrans buffers (see #define NUM_SUBTRANS_BUFFERS
in src/include/access/subtrans.h; requires a recompile.)  Considering
that each subtrans entry is 16 times larger than clog (2 bits vs. 4
bytes), you'd require 2048 subtrans buffers to cover the same XID range
without I/O if my math is right.  That's only 16 MB ...  though slru.c
code might not be prepared to deal with that many buffers.  Worth some
experimentation, I guess.

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