How to deal with "could not resize shared memory segment" error

alexandergerber · September 26, 2022, 3:23pm

I have a query on a hypertable where I get this error

ERROR:  could not resize shared memory segment "/PostgreSQL.1808576942" to 16777216 bytes: No space left on device

If I reduce the time span in the query for which I get the error the query plan is as follows:

EXPLAIN ANALYSE SELECT * 
FROM 
	core_fb_domain_expanded 
WHERE 
	time >= '2022-06-09 00:00:00+02:00' AND 
	time <= '2022-07-31 23:00:00+02:00' AND 
	iva != 0 AND 
	branchstatus = 'OK' AND 
	tso IN ('D7')

"Append  (cost=173.64..109205.44 rows=9 width=1412) (actual time=4649.607..75583.659 rows=74 loops=1)"
"  ->  Bitmap Heap Scan on _hyper_35_346_chunk  (cost=173.64..13629.09 rows=1 width=1412) (actual time=4649.606..13047.094 rows=8 loops=1)"
"        Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"        Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
"        Rows Removed by Filter: 2496188"
"        Heap Blocks: exact=26447 lossy=252821"
"        ->  Bitmap Index Scan on _hyper_35_346_chunk_core_fb_domain_expanded_time_idx  (cost=0.00..173.64 rows=12481 width=0) (actual time=3605.965..3605.966 rows=2496196 loops=1)"
"              Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"  ->  Bitmap Heap Scan on _hyper_35_344_chunk  (cost=173.20..13573.61 rows=1 width=1412) (actual time=3258.711..10279.786 rows=14 loops=1)"
"        Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"        Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
"        Rows Removed by Filter: 2487430"
"        Heap Blocks: exact=20504 lossy=252801"
"        ->  Bitmap Index Scan on _hyper_35_344_chunk_core_fb_domain_expanded_time_idx  (cost=0.00..173.20 rows=12437 width=0) (actual time=2987.768..2987.769 rows=2487444 loops=1)"
"              Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"  ->  Bitmap Heap Scan on _hyper_35_333_chunk  (cost=174.17..13688.51 rows=1 width=1412) (actual time=3336.184..5853.147 rows=4 loops=1)"
"        Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"        Rows Removed by Index Recheck: 1462"
"        Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
"        Rows Removed by Filter: 1400586"
"        Heap Blocks: exact=31969 lossy=126465"
"        ->  Bitmap Index Scan on _hyper_35_333_chunk_core_fb_domain_expanded_time_idx  (cost=0.00..174.17 rows=12534 width=0) (actual time=2032.981..2032.982 rows=1400590 loops=1)"
"              Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"  ->  Bitmap Heap Scan on _hyper_35_352_chunk  (cost=174.37..13710.94 rows=1 width=1412) (actual time=6598.528..8050.803 rows=2 loops=1)"
"        Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"        Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
"        Rows Removed by Filter: 2510804"
"        Heap Blocks: exact=29681 lossy=252820"
"        ->  Bitmap Index Scan on _hyper_35_352_chunk_core_fb_domain_expanded_time_idx  (cost=0.00..174.37 rows=12554 width=0) (actual time=3626.814..3626.814 rows=2510806 loops=1)"
"              Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"  ->  Bitmap Heap Scan on _hyper_35_338_chunk  (cost=2.87..162.62 rows=1 width=1412) (actual time=110.514..110.515 rows=0 loops=1)"
"        Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"        Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
"        Rows Removed by Filter: 29566"
"        Heap Blocks: exact=3214"
"        ->  Bitmap Index Scan on _hyper_35_338_chunk_core_fb_domain_expanded_time_idx  (cost=0.00..2.87 rows=148 width=0) (actual time=38.467..38.468 rows=29566 loops=1)"
"              Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"  ->  Bitmap Heap Scan on _hyper_35_342_chunk  (cost=173.48..13602.20 rows=1 width=1412) (actual time=3601.668..9877.954 rows=28 loops=1)"
"        Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"        Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
"        Rows Removed by Filter: 2492984"
"        Heap Blocks: exact=20294 lossy=252801"
"        ->  Bitmap Index Scan on _hyper_35_342_chunk_core_fb_domain_expanded_time_idx  (cost=0.00..173.48 rows=12465 width=0) (actual time=3443.150..3443.151 rows=2493012 loops=1)"
"              Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"  ->  Bitmap Heap Scan on _hyper_35_348_chunk  (cost=173.61..13627.52 rows=1 width=1412) (actual time=10086.098..10086.099 rows=0 loops=1)"
"        Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"        Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
"        Rows Removed by Filter: 2495548"
"        Heap Blocks: exact=27928 lossy=252818"
"        ->  Bitmap Index Scan on _hyper_35_348_chunk_core_fb_domain_expanded_time_idx  (cost=0.00..173.61 rows=12478 width=0) (actual time=3464.365..3464.366 rows=2495548 loops=1)"
"              Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"  ->  Bitmap Heap Scan on _hyper_35_339_chunk  (cost=173.14..13560.95 rows=1 width=1412) (actual time=3767.787..10110.002 rows=18 loops=1)"
"        Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"        Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
"        Rows Removed by Filter: 2486116"
"        Heap Blocks: exact=37790 lossy=231757"
"        ->  Bitmap Index Scan on _hyper_35_339_chunk_core_fb_domain_expanded_time_idx  (cost=0.00..173.14 rows=12431 width=0) (actual time=3582.164..3582.165 rows=2486134 loops=1)"
"              Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"  ->  Bitmap Heap Scan on _hyper_35_350_chunk  (cost=173.88..13649.96 rows=1 width=1412) (actual time=8168.151..8168.152 rows=0 loops=1)"
"        Recheck Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"        Filter: ((iva <> '0'::double precision) AND (branchstatus = 'OK'::text) AND (tso = 'D7'::text))"
"        Rows Removed by Filter: 2500912"
"        Heap Blocks: exact=23675 lossy=252824"
"        ->  Bitmap Index Scan on _hyper_35_350_chunk_core_fb_domain_expanded_time_idx  (cost=0.00..173.88 rows=12505 width=0) (actual time=3661.166..3661.166 rows=2500912 loops=1)"
"              Index Cond: ((""time"" >= '2022-06-08 22:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-07-31 21:00:00+00'::timestamp with time zone))"
"Planning Time: 43.946 ms"
"JIT:"
"  Functions: 36"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 6.913 ms, Inlining 0.000 ms, Optimization 5.179 ms, Emission 58.839 ms, Total 70.931 ms"
"Execution Time: 75601.326 ms"

If I understand the accepted answer here (postgresql - /dev/shm size recommendation for postgres database in docker - Database Administrators Stack Exchange
) correctly, the shared memory is mainly used for parallel queries. For me, however, it does not look like the query is parallel.

My questions are the following:

Did the query run in parallel and is this the probable reason why we need so much shared memory?
Should we simply increase the shared memory or would it be better to optimize the database or query (especially if it is not a parallel query)?

sven · September 26, 2022, 3:47pm

Postgres always uses shared memory. If your shared memory is limited to 16MB you definitely want to adjust that setting for postgres.

anatavares · April 30, 2024, 12:03pm

We recently published an article on the ‘could not resize shared memory segment’ error to help users solve this problem. Hope it helps.