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@xxxxxxxxxxxxxxxx> wrote:
On Wed, Jan 3, 2018 at 1:22 PM, Thuc Nguyen Canh
<thucnguyencanh@xxxxxxxxx> 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
