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.mu...@enterprisedb.com > wrote: > On Wed, Jan 3, 2018 at 1:22 PM, Thuc Nguyen Canh > <thucnguyenc...@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 >
