Re: Query error: could not resize shared memory segment

2018-02-23 Thread Thomas Munro
On Wed, Jan 3, 2018 at 6:34 PM, Tom Lane wrote: > Thomas Munro writes: >> On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh >> 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 shar

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thuc Nguyen Canh
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 wrote: > On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh > wrote: >> And here is the result from postgres container: >> >> mount | grep /dev/shm >> => shm on

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Tom Lane
Thomas Munro writes: > On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh > 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

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thomas Munro
On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh 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 a

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thuc Nguyen Canh
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 tmpfs2023252 0 2023252 0% /dev/shm ---

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thomas Munro
On Wed, Jan 3, 2018 at 5:39 PM, Tom Lane wrote: > Thomas Munro 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

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thuc Nguyen Canh
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-'::text))' ' CTE sumorder' '-> Grou

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thomas Munro
On Wed, Jan 3, 2018 at 5:22 PM, Thuc Nguyen Canh 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)'

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Tom Lane
Thomas Munro 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). C

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thuc Nguyen Canh
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-'::text))' ' Sort Met

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thomas Munro
On Wed, Jan 3, 2018 at 5:05 PM, Thuc Nguyen Canh 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 ch

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thuc Nguyen Canh
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 di

Re: Query error: could not resize shared memory segment

2018-01-02 Thread Thomas Munro
On Wed, Jan 3, 2018 at 1:22 PM, Thuc Nguyen Canh 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 m

Fwd: Query error: could not resize shared memory segment

2018-01-02 Thread Thuc Nguyen Canh
Hello, 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 ca