Re: Query error: could not resize shared memory segment

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(dot)munro(at)enterprisedb(dot)com
> wrote:

> On Wed, Jan 3, 2018 at 1:22 PM, Thuc Nguyen Canh
> <thucnguyencanh(at)gmail(dot)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
>