Re: Query error: could not resize shared memory segment

On Wed, Jan 3, 2018 at 6:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> writes:
>> On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh
>> <thucnguyencanh(at)gmail(dot)com> 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 to need a new section
> under or near
>
> https://www.postgresql.org/docs/current/static/kernel-resources.html#SYSVIPC
>
> It's pretty annoying to have sweated blood to get out from under the
> kernel's chosen-at-random SysV IPC limits, only to run into container
> tmpfs limits that seem equally chosen with the aid of a dartboard.

I ran into another couple of cases like this in the wild, and judging
by the popularity of Docker, I expect it to come up more with Parallel
Hash (and any future memory-gobbling parallel things... parallel
sorting, parallel repartitioning, ...) and with the increase in node
counts that partitioning can produce. Here's an example of a public
discussion about this:

https://github.com/docker-library/postgres/issues/416

I don't know too much about Docker myself but I see from the
documentation that it defaults to imposing no limited on regular
memory[1], but it always imposes a limit on /dev/shm[2].

It's actually quite hard to make a recommendation for --shm-size. As
discussed elsewhere[3], our executor node-based work_mem management
means that the peak space usage depends on the number of concurrent
queries * number of executor nodes * number of parallel processes
allowed * work_mem. It's just the same for regular memory, but in
that case the Docker administrator doesn't have to set a separate
fixed limit -- it's just whatever the operating system will take. To
achieve the same behaviour for DSM memory, I think you either need to
set --shm-size sky high or side-step the issue by mounting the host's
unlimited /dev/shm in the container (a trick I saw mentioned
elsewhere, not sure if it's a good idea).

Anyway, it'd be good to hear from Docker/PostgreSQL users or experts.
Do we need to write some documentation here?

[1] https://docs.docker.com/config/containers/resource_constraints/
[2] https://docs.docker.com/engine/reference/run/#runtime-constraints-on-resources
[3] https://www.postgresql.org/message-id/CAEepm%3D38vXiZRgLe_Ks0pQEk8QSp%3D_YEUxht%3DWx%3D6%2B1qiaWOcA%40mail.gmail.com

--
Thomas Munro
http://www.enterprisedb.com