Re: SubtransControlLock and performance problems

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