Re: ERROR: too many dynamic shared memory segments

Hello,
I also get high amount of "too many dynamic shared memory segments" errors.
Upgraded Postgres version to 12.2, but that did not help.
Server has 64GB Ram/16 CPU. Postgres params:
        "max_connections":500,
        "shared_buffers":"16GB",
        "effective_cache_size":"48GB",
        "maintenance_work_mem":"2GB",
        "checkpoint_completion_target":0.9,
        "wal_buffers":"32MB",
        "default_statistics_target":500,
        "random_page_cost":1.1,
        "effective_io_concurrency":200,
        "work_mem":"20971kB",
        "min_wal_size":"2GB",
        "max_wal_size":"8GB",
        "max_worker_processes":8,
        "max_parallel_workers_per_gather":4,
        "max_parallel_workers":8,
        "log_statement":"none"

This error happens when executing many parallel queries that have quite
complex plan:

Limit  (cost=163243.91..163250.02 rows=51 width=464) (actual
time=1224.817..1224.834 rows=31 loops=1)
  ->  Gather Merge  (cost=163243.91..165477.68 rows=18656 width=464) (actual
time=1224.815..1254.031 rows=31 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Sort  (cost=162243.85..162255.51 rows=4664 width=464) (actual
time=1214.032..1214.032 rows=6 loops=5)
              Sort Key: (...)
              Sort Method: quicksort  Memory: 30kB
              Worker 0:  Sort Method: quicksort  Memory: 28kB
              Worker 1:  Sort Method: quicksort  Memory: 27kB
              Worker 2:  Sort Method: quicksort  Memory: 27kB
              Worker 3:  Sort Method: quicksort  Memory: 27kB
              ->  Parallel Hash Semi Join  (cost=41604.51..162088.25
rows=4664 width=464) (actual time=409.437..1213.922 rows=6 loops=5)
                    Hash Cond:  (...)
                    ->  Parallel Hash Join  (cost=28073.57..148289.42
rows=17880 width=464) (actual time=234.973..1165.754 rows=36930 loops=5)
                          Hash Cond:  (...)
                          ->  Parallel Hash Left Join 
(cost=20732.39..140901.30 rows=17880 width=445) (actual
time=187.482..1083.629 rows=36930 loops=5)
                                Hash Cond:  (...)
                                ->  Parallel Hash Left Join 
(cost=14850.80..134972.78 rows=17880 width=435) (actual
time=148.107..1010.915 rows=36930 loops=5)
                                      Hash Cond:  (...)
                                      ->  Parallel Hash Left Join 
(cost=8969.21..129044.25 rows=17880 width=425) (actual time=110.696..938.602
rows=36930 loops=5)
                                            Hash Cond:  (...)
                                            ->  Nested Loop 
(cost=3087.61..123115.72 rows=17880 width=411) (actual time=70.827..861.142
rows=36930 loops=5)
                                                  ->  Nested Loop 
(cost=3087.19..104038.13 rows=38340 width=263) (actual time=70.742..621.262
rows=37073 loops=5)
                                                        ->  Parallel Bitmap
Heap Scan on (...)  (cost=3086.76..73462.00 rows=39271 width=167) (actual
time=70.653..358.576 rows=37103 loops=5)
                                                              Recheck Cond: 
(...)
                                                              Filter: (...)
                                                              Rows Removed
by Filter: 42915
                                                              Heap Blocks:
exact=17872
                                                              ->  Bitmap
Index Scan on  (...) (cost=0.00..3047.49 rows=378465 width=0) (actual
time=52.331..52.331 rows=400144 loops=1)
                                                                    Index
Cond:  (...)
                                                        ->  Index Scan using 
(...)  (cost=0.43..0.78 rows=1 width=96) (actual time=0.006..0.006 rows=1
loops=185514)
                                                              Index Cond: 
(...)
                                                              Filter:  (...)
                                                              Rows Removed
by Filter: 0
                                                  ->  Index Scan using 
(...)(cost=0.43..0.50 rows=1 width=152) (actual time=0.006..0.006 rows=1
loops=185367)
                                                        Index Cond:  (...)
                                                        Filter:  (...)
                                                        Rows Removed by
Filter: 0
                                            ->  Parallel Hash 
(cost=3675.71..3675.71 rows=176471 width=18) (actual time=38.590..38.590
rows=60000 loops=5)
                                                  Buckets: 524288  Batches:
1  Memory Usage: 20640kB
                                                  ->  Parallel Seq Scan on 
(...) (cost=0.00..3675.71 rows=176471 width=18) (actual time=0.020..11.378
rows=60000 loops=5)
                                      ->  Parallel Hash 
(cost=3675.71..3675.71 rows=176471 width=18) (actual time=36.769..36.770
rows=60000 loops=5)
                                            Buckets: 524288  Batches: 1 
Memory Usage: 20608kB
                                            ->  Parallel Seq Scan on  (...) 
(cost=0.00..3675.71 rows=176471 width=18) (actual time=0.018..11.665
rows=60000 loops=5)
                                ->  Parallel Hash  (cost=3675.71..3675.71
rows=176471 width=18) (actual time=38.415..38.415 rows=60000 loops=5)
                                      Buckets: 524288  Batches: 1  Memory
Usage: 20640kB
                                      ->  Parallel Seq Scan on  (...) 
(cost=0.00..3675.71 rows=176471 width=18) (actual time=0.021..11.781
rows=60000 loops=5)
                          ->  Parallel Hash  (cost=5619.97..5619.97
rows=137697 width=27) (actual time=46.665..46.665 rows=66096 loops=5)
                                Buckets: 524288  Batches: 1  Memory Usage:
24544kB
                                ->  Parallel Seq Scan on (...) 
(cost=0.00..5619.97 rows=137697 width=27) (actual time=0.024..16.629
rows=66096 loops=5)
                    ->  Parallel Hash  (cost=12679.65..12679.65 rows=68103
width=4) (actual time=28.674..28.674 rows=41176 loops=5)
                          Buckets: 262144  Batches: 1  Memory Usage: 10176kB
                          ->  Parallel Index Only Scan using (...) 
(cost=0.57..12679.65 rows=68103 width=4) (actual time=0.048..15.443
rows=41176 loops=5)
                                Index Cond: ()
                                Heap Fetches: 205881
Planning Time: 6.941 ms
Execution Time: 1254.251 ms

So to avoid getting these errors the only solution is to decrease work_mem
or turn off paralelism? Because I hoped that in such case it would take
longer time to complete the queries but instead I get high amount of queries
just crashing.


Thomas Munro-5 wrote
> On Fri, Jan 31, 2020 at 11:05 PM Nicola Contu <

> nicola.contu@

> > wrote:
>> Do you still recommend to increase max_conn?
> 
> Yes, as a workaround of last resort.  The best thing would be to
> figure out why you are hitting the segment limit, and see if there is
> something we could tune to fix that. If you EXPLAIN your queries, do
> you see plans that have a lot of "Gather" nodes in them, perhaps
> involving many partitions?  Or are you running a lot of parallel
> queries at the same time?  Or are you running queries that do very,
> very large parallel hash joins?  Or something else?





--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html