Fwd: Query error: could not resize shared memory segment

On Wed, Jan 3, 2018 at 1:22 PM, Thuc Nguyen Canh
<thucnguyencanh@gmail.com> wrote:

I got following error when running some heavy queries
"ERROR: could not resize shared memory segment "/PostgreSQL.388782411" to
50438144 bytes: No space left on device SQL state: 53100"

I'm using a postgis 10 docker container with mounted volume on ubuntu 16
vps.

Some of failed queries can run after I increased my work_mem.

On the other hand, this issue is not producible on postgresql 9.6.

Hi,

So it couldn't allocate 50MB of dynamic shared memory. Can you show
the work_mem settings, the query plan with the two different work_mem
settings (the one that works and the one that doesn't), the value of
dynamic_shared_memory_type, and tell us how much memory and swap space
you have? Do you run many of these queries in parallel? I guess this
is probably a parallel query using parallel bitmap heapscan and seeing
the error coming from the change in commit
899bd785c0edf376077d3f5d65c316f92c1b64b5, meaning that it would have
risked death by SIGBUS before that commit. What is surprising is that
increasing work_mem helped.

--
Thomas Munro
http://www.enterprisedb.com

Hi,

The dynamic_shared_memory_type is posix, the before and after values for
work_mem are ~41MB and ~64MB.
I'm using a Digital Ocean vps of 16RAM 8 Cores.
For more information, I managed to reproduce this issue on a fresh vps
after I changed the random_page_cost from 4.0 to 1.1. So that said, I did
reduce the random_page_cost to 1.1, in order to optimize postgresql
performance on SSD (DO uses SSD) and got this issue.

On Wed, Jan 3, 2018 at 10:53 AM, Thomas Munro <thomas.munro@enterprisedb.com

On Wed, Jan 3, 2018 at 5:05 PM, Thuc Nguyen Canh
<thucnguyencanh@gmail.com> wrote:

The dynamic_shared_memory_type is posix, the before and after values for
work_mem are ~41MB and ~64MB.
I'm using a Digital Ocean vps of 16RAM 8 Cores.
For more information, I managed to reproduce this issue on a fresh vps after
I changed the random_page_cost from 4.0 to 1.1. So that said, I did reduce
the random_page_cost to 1.1, in order to optimize postgresql performance on
SSD (DO uses SSD) and got this issue.

So you have 16GB of RAM and here we're failing to posix_fallocate()
50MB (actually we can't tell if it's the ftruncate() or
posix_fallocate() call that failed, but the latter seems more likely
since the former just creates a big hole in the underlying tmpfs
file). Can you share the query plan (EXPLAIN SELECT ...)?

--
Thomas Munro
http://www.enterprisedb.com

Hi,
Here is the query plan of a query that causes above issue for any
random_page_cost < 3 (I keep the work_mem by default)

'Sort (cost=9441498.11..9542397.83 rows=40359886 width=64) (actual
time=33586.588..33586.590 rows=4 loops=1)'
' Sort Key: (to_char(b.week, 'dd-mm-yyyy'::text))'
' Sort Method: quicksort Memory: 25kB'
' CTE sumorder'
' -> GroupAggregate (cost=763614.25..775248.11 rows=513746 width=16)
(actual time=16587.507..17320.290 rows=4 loops=1)'
' Group Key: (date_trunc('month'::text,
to_timestamp("order".order_time)))'
' -> Sort (cost=763614.25..764923.47 rows=523689 width=14)
(actual time=16587.362..16913.230 rows=539089 loops=1)'
' Sort Key: (date_trunc('month'::text,
to_timestamp("order".order_time)))'
' Sort Method: quicksort Memory: 47116kB'
' -> Bitmap Heap Scan on "order" (cost=12679.94..713868.12
rows=523689 width=14) (actual time=516.465..15675.517 rows=539089 loops=1)'
' Recheck Cond: ((service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1509469200'::double
precision))'
' Heap Blocks: exact=242484'
' -> Bitmap Index Scan on
order_service_id_order_time_idx (cost=0.00..12549.02 rows=523689 width=0)
(actual time=425.697..425.697 rows=539089 loops=1)'
' Index Cond: ((service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1509469200'::double
precision))'
' CTE badorder'
' -> Finalize GroupAggregate (cost=993588.49..995549.11 rows=15712
width=16) (actual time=16257.720..16263.183 rows=13 loops=1)'
' Group Key: (date_trunc('month'::text,
to_timestamp(order_1.order_time)))'
' -> Gather Merge (cost=993588.49..995247.93 rows=13100
width=16) (actual time=16257.435..16263.107 rows=39 loops=1)'
' Workers Planned: 2'
' Workers Launched: 2'
' -> Partial GroupAggregate (cost=992588.46..992735.84
rows=6550 width=16) (actual time=16246.191..16251.348 rows=13 loops=3)'
' Group Key: (date_trunc('month'::text,
to_timestamp(order_1.order_time)))'
' -> Sort (cost=992588.46..992604.84 rows=6550
width=14) (actual time=16245.767..16248.316 rows=3715 loops=3)'
' Sort Key: (date_trunc('month'::text,
to_timestamp(order_1.order_time)))'
' Sort Method: quicksort Memory: 274kB'
' -> Parallel Seq Scan on "order" order_1
(cost=0.00..992173.28 rows=6550 width=14) (actual time=4.162..16230.174
rows=3715 loops=3)'
' Filter: ((order_time >=
'1483203600'::double precision) AND (service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (rating_by_user < 5) AND
(rating_by_user > 0))'
' Rows Removed by Filter: 1801667'
' -> Merge Join (cost=60414.85..1271289.99 rows=40359886 width=64)
(actual time=33586.471..33586.503 rows=4 loops=1)'
' Merge Cond: (b.week = s.week)'
' -> Sort (cost=1409.33..1448.61 rows=15712 width=16) (actual
time=16263.259..16263.276 rows=13 loops=1)'
' Sort Key: b.week'
' Sort Method: quicksort Memory: 25kB'
' -> CTE Scan on badorder b (cost=0.00..314.24 rows=15712
width=16) (actual time=16257.737..16263.220 rows=13 loops=1)'
' -> Sort (cost=59005.52..60289.88 rows=513746 width=16) (actual
time=17320.506..17320.509 rows=4 loops=1)'
' Sort Key: s.week'
' Sort Method: quicksort Memory: 25kB'
' -> CTE Scan on sumorder s (cost=0.00..10274.92 rows=513746
width=16) (actual time=16587.532..17320.352 rows=4 loops=1)'
'Planning time: 3.202 ms'
'Execution time: 33589.971 ms'

On Wed, Jan 3, 2018 at 11:13 AM, Thomas Munro <thomas.munro@enterprisedb.com

Thomas Munro <thomas.munro@enterprisedb.com> writes:

So you have 16GB of RAM and here we're failing to posix_fallocate()
50MB (actually we can't tell if it's the ftruncate() or
posix_fallocate() call that failed, but the latter seems more likely
since the former just creates a big hole in the underlying tmpfs
file). Can you share the query plan (EXPLAIN SELECT ...)?

I wonder if OP is running with a tmpfs size setting that's less than
the traditional Linux default of half of physical RAM size.

regards, tom lane

On Wed, Jan 3, 2018 at 5:22 PM, Thuc Nguyen Canh
<thucnguyencanh@gmail.com> wrote:

Here is the query plan of a query that causes above issue for any
random_page_cost < 3 (I keep the work_mem by default)

'Sort (cost=9441498.11..9542397.83 rows=40359886 width=64) (actual
time=33586.588..33586.590 rows=4 loops=1)'

I guess that must be EXPLAIN ANALYZE, because it includes "actual"
time, so it must be the plan when you set random_page_code >= 3,
right? Otherwise it would raise the error. Can you now set it to <
3 and do just EXPLAIN (no ANALYZE) so that we can see the failing plan
without trying to run it? I'm guessing it's different, because the
plan you showed doesn't look like it would want 50MB of DSM.

--
Thomas Munro
http://www.enterprisedb.com

The last query explain is with random_page_cost = 3.
Here is the query explain with random_page_cost = 2.5, that causes the
'shared memory segment' issue.

'Sort (cost=9255854.81..9356754.53 rows=40359886 width=64)'
' Sort Key: (to_char(b.week, 'dd-mm-yyyy'::text))'
' CTE sumorder'
' -> GroupAggregate (cost=692280.90..703914.76 rows=513746 width=16)'
' Group Key: (date_trunc('month'::text,
to_timestamp("order".order_time)))'
' -> Sort (cost=692280.90..693590.12 rows=523689 width=14)'
' Sort Key: (date_trunc('month'::text,
to_timestamp("order".order_time)))'
' -> Bitmap Heap Scan on "order" (cost=11461.44..642534.77
rows=523689 width=14)'
' Recheck Cond: ((service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1509469200'::double
precision))'
' -> Bitmap Index Scan on
order_service_id_order_time_idx (cost=0.00..11330.52 rows=523689 width=0)'
' Index Cond: ((service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1509469200'::double
precision))'
' CTE badorder'
' -> Finalize GroupAggregate (cost=987667.04..989627.66 rows=15712
width=16)'
' Group Key: (date_trunc('month'::text,
to_timestamp(order_1.order_time)))'
' -> Gather Merge (cost=987667.04..989326.48 rows=13100
width=16)'
' Workers Planned: 2'
' -> Partial GroupAggregate (cost=986667.01..986814.39
rows=6550 width=16)'
' Group Key: (date_trunc('month'::text,
to_timestamp(order_1.order_time)))'
' -> Sort (cost=986667.01..986683.39 rows=6550
width=14)'
' Sort Key: (date_trunc('month'::text,
to_timestamp(order_1.order_time)))'
' -> Parallel Bitmap Heap Scan on "order"
order_1 (cost=35678.61..986251.83 rows=6550 width=14)'
' Recheck Cond: ((service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1483203600'::double
precision))'
' Filter: ((rating_by_user < 5) AND
(rating_by_user > 0))'
' -> Bitmap Index Scan on
order_service_id_order_time_idx (cost=0.00..35674.67 rows=1740356 width=0)'
' Index Cond: ((service_id = ANY
('{SGN-BIKE,SGN-POOL}'::text[])) AND (order_time >= '1483203600'::double
precision))'
' -> Merge Join (cost=60414.85..1271289.99 rows=40359886 width=64)'
' Merge Cond: (b.week = s.week)'
' -> Sort (cost=1409.33..1448.61 rows=15712 width=16)'
' Sort Key: b.week'
' -> CTE Scan on badorder b (cost=0.00..314.24 rows=15712
width=16)'
' -> Sort (cost=59005.52..60289.88 rows=513746 width=16)'
' Sort Key: s.week'
' -> CTE Scan on sumorder s (cost=0.00..10274.92 rows=513746
width=16)'

On Wed, Jan 3, 2018 at 11:43 AM, Thomas Munro <thomas.munro@enterprisedb.com

On Wed, Jan 3, 2018 at 5:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thomas Munro <thomas.munro@enterprisedb.com> writes:

So you have 16GB of RAM and here we're failing to posix_fallocate()
50MB (actually we can't tell if it's the ftruncate() or
posix_fallocate() call that failed, but the latter seems more likely
since the former just creates a big hole in the underlying tmpfs
file). Can you share the query plan (EXPLAIN SELECT ...)?

I wonder if OP is running with a tmpfs size setting that's less than
the traditional Linux default of half of physical RAM size.

Hmm. Canh, can you please share the output of the following commands?

mount | grep /dev/shm
du -hs /dev/shm
df /dev/shm

--
Thomas Munro
http://www.enterprisedb.com

Hi,
Here is the result from host:

mount | grep /dev/shm
=> tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev)

du -hs /dev/shm
=> 0 /dev/shm

df /dev/shm
=>
Filesystem 1K-blocks Used Available Use% Mounted on
tmpfs 2023252 0 2023252 0% /dev/shm

----------------------------------------------------------------------
And here is the result from postgres container:

mount | grep /dev/shm
=> shm on /dev/shm type tmpfs (rw,nosuid,nodev,noexec,relatime,size=65536k)

du -hs /dev/shm
=> 8.0K /dev/shm

df /dev/shm
=>
Filesystem 1K-blocks Used Available Use% Mounted on
shm 65536 8 65528 1% /dev/shm

On Wed, Jan 3, 2018 at 12:05 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh
<thucnguyencanh@gmail.com> wrote:

And here is the result from postgres container:

mount | grep /dev/shm
=> shm on /dev/shm type tmpfs (rw,nosuid,nodev,noexec,relatime,size=65536k)

Bingo. Somehow your container tech is limiting shared memory. That
error is working as designed. You could figure out how to fix the
mount options, or you could disable parallelism with
max_parallel_workers_per_gather = 0.

--
Thomas Munro
http://www.enterprisedb.com

Thomas Munro <thomas.munro@enterprisedb.com> writes:

On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh
<thucnguyencanh@gmail.com> wrote:

mount | grep /dev/shm
=> shm on /dev/shm type tmpfs (rw,nosuid,nodev,noexec,relatime,size=65536k)

Bingo. Somehow your container tech is limiting shared memory.

If this is a common setup, maybe we're going to need a new section
under or near

https://www.postgresql.org/docs/current/static/kernel-resources.html#SYSVIPC

It's pretty annoying to have sweated blood to get out from under the
kernel's chosen-at-random SysV IPC limits, only to run into container
tmpfs limits that seem equally chosen with the aid of a dartboard.

regards, tom lane

Thank you Thomas,

I make it work with extra setting --shm-size=1g in my docker run script.

On Wed, Jan 3, 2018 at 12:16 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:

On Wed, Jan 3, 2018 at 6:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thomas Munro <thomas.munro@enterprisedb.com> writes:

On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh
<thucnguyencanh@gmail.com> wrote:

mount | grep /dev/shm
=> shm on /dev/shm type tmpfs (rw,nosuid,nodev,noexec,relatime,size=65536k)

Bingo. Somehow your container tech is limiting shared memory.

If this is a common setup, maybe we're going to need a new section
under or near

https://www.postgresql.org/docs/current/static/kernel-resources.html#SYSVIPC

It's pretty annoying to have sweated blood to get out from under the
kernel's chosen-at-random SysV IPC limits, only to run into container
tmpfs limits that seem equally chosen with the aid of a dartboard.

I ran into another couple of cases like this in the wild, and judging
by the popularity of Docker, I expect it to come up more with Parallel
Hash (and any future memory-gobbling parallel things... parallel
sorting, parallel repartitioning, ...) and with the increase in node
counts that partitioning can produce. Here's an example of a public
discussion about this:

https://github.com/docker-library/postgres/issues/416

I don't know too much about Docker myself but I see from the
documentation that it defaults to imposing no limited on regular
memory[1]https://docs.docker.com/config/containers/resource_constraints/, but it always imposes a limit on /dev/shm[2]https://docs.docker.com/engine/reference/run/#runtime-constraints-on-resources.

It's actually quite hard to make a recommendation for --shm-size. As
discussed elsewhere[3]/messages/by-id/CAEepm=38vXiZRgLe_Ks0pQEk8QSp=_YEUxht=Wx=6+1qiaWOcA@mail.gmail.com, our executor node-based work_mem management
means that the peak space usage depends on the number of concurrent
queries * number of executor nodes * number of parallel processes
allowed * work_mem. It's just the same for regular memory, but in
that case the Docker administrator doesn't have to set a separate
fixed limit -- it's just whatever the operating system will take. To
achieve the same behaviour for DSM memory, I think you either need to
set --shm-size sky high or side-step the issue by mounting the host's
unlimited /dev/shm in the container (a trick I saw mentioned
elsewhere, not sure if it's a good idea).

Anyway, it'd be good to hear from Docker/PostgreSQL users or experts.
Do we need to write some documentation here?

[1]: https://docs.docker.com/config/containers/resource_constraints/
[2]: https://docs.docker.com/engine/reference/run/#runtime-constraints-on-resources
[3]: /messages/by-id/CAEepm=38vXiZRgLe_Ks0pQEk8QSp=_YEUxht=Wx=6+1qiaWOcA@mail.gmail.com

--
Thomas Munro
http://www.enterprisedb.com