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 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