Thread: Cause: org.postgresql.util.PSQLException: ERROR: could not resizeshared memory segment "/PostgreSQL.1946998112" to 8388608 bytes: No spaceleft on device

On Mon, Jun 3, 2019 at 5:56 AM Daulat Ram <Daulat.Ram@exponential.com> wrote:
> Cause: org.postgresql.util.PSQLException: ERROR: could not resize shared memory segment "/PostgreSQL.1946998112" to
8388608bytes: No space left on device
 

> shm on /dev/shm type tmpfs
(rw,context="system_u:object_r:container_file_t:s0:c127,c569",nosuid,nodev,noexec,relatime,size=65536k)

I don't use Docker myself but I believe you can either tell it to
expose the host's /dev/shm or you can start it with something like
--shm-size="4096m".

It's surprisingly difficult to know how much shared memory PostgreSQL
really needs, today.  Without parallel query, it's complicated but
roughly: you can use work_mem for each executor node, and that is (at
a first approximation) linked to how many joins there are in your
query.  With the advent of parallel query, it's multiplied by the
number of workers.  If any of the joins happen to be parallel hash
join, then the memory comes out of shared memory instead of private
memory.  It's not fundamentally different in terms of the amount
needed, but Docker users are forced to confront the question so they
can set --shm-size.  One argument is that you should set it sky-high,
since private memory has no such limit, and from a virtual memory
point of view it's all the same in the end, it's just memory.  The
difficulty with choosing a limit here is that --shm-size is a
system-wide limit, but PostgreSQL not only has no system-wide memory
budgeting, it doesn't even have a per-query memory budget.  It just
has this "per operation" (usually executor node) thing.  The total
amount of memory you need to run PostgreSQL queries is a function of
work_mem * number of concurrent queries you expect to run * number of
tables you expect them to join * number of parallel workers you expect
to run.  The amount of it that happens to be in /dev/shm on a Linux
system (rather than private memory) is controlled by what fraction of
your joins are parallel hash joins.  Making our memory limits better
is really hard.

-- 
Thomas Munro
https://enterprisedb.com