Tweaking DSM and DSA limits - Mailing list pgsql-hackers

From Thomas Munro
Subject Tweaking DSM and DSA limits
Date
Msg-id CA+hUKGL6H2BpGbiF7Lj6QiTjTGyTLW_vLR=Sn2tEBeTcYXiMKw@mail.gmail.com
Whole thread Raw
Responses Re: Tweaking DSM and DSA limits
List pgsql-hackers
Hello,

If you run a lot of parallel queries that use big parallel hash joins
simultaneously, you can run out of DSM slots (for example, when
testing many concurrent parallel queries).  That's because we allow 64
slots + 2 * MaxBackends, but allocating seriously large amounts of
dynamic shared memory requires lots of slots.

Originally the DSM system was designed to support one segment per
parallel query, but now we also use one for the session and any number
for parallel executor nodes that want space limited by work_mem.

The number of slots it takes for a given total amount of shared memory
depends on the macro DSA_NUM_SEGMENTS_AT_EACH_SIZE.  Since DSM slots
are relatively scarce (we use inefficient algorithms to access them,
and we think that some operating systems won't like us if we create
too many, so we impose this scarcity on ourselves), each DSA area
allocates bigger and bigger segments as it goes, starting with 1MB.
The approximate number of segments required to allocate various sizes
incrementally using different values of DSA_NUM_SEGMENTS_AT_EACH_SIZE
can be seen in this table:

 N =   1   2   3   4

  1MB  1   1   1   1
 64MB  6  10  13  16
512MB  9  16  22  28
  1GB 10  18  25  32
  8GB 13  24  34  44
 16GB 14  26  37  48
 32GB 15  28  40  52
  1TB 20  38  55  72

It's currently set to 4, but I now think that was too cautious.  It
tries to avoid fragmentation by ramping up slowly (that is, memory
allocated and in some cases committed by the operating system that we
don't turn out to need), but it's pretty wasteful of slots.  Perhaps
it should be set to 2?

Perhaps also the number of slots per backend should be dynamic, so
that you have the option to increase it from the current hard-coded
value of 2 if you don't want to increase max_connections but find
yourself running out of slots (this GUC was a request from Andres but
the name was made up by me -- if someone has a better suggestion I'm
all ears).

Also, there are some outdated comments near
PG_DYNSHMEM_SLOTS_PER_BACKEND's definition that we might as well drop
along with the macro.

Draft patch attached.

-- 
Thomas Munro
https://enterprisedb.com

Attachment

pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: ANALYZE: ERROR: tuple already updated by self
Next
From: Michael Paquier
Date:
Subject: Re: nbtdesc.c and nbtpage.c are inconsistent withXLOG_BTREE_META_CLEANUP (11~)