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
0001-Add-dynamic_shared_memory_segments_per_backend-GUC.patch
Description: Binary data