10

We have postgresql 11.7 database in docker container.

We had an issue "pq: could not resize shared memory segment "/PostgreSQL.XXX" to XXX bytes: No space left on device", described here.

Solution helped, we do not have an issue anymore. But which size should we set for shm_size for docker container with Postgres?

Is any recommendation? How can we calculate 'optimal size' for shm_size?

Improve this question
2
  • Depends on your shared_buffers setting. That depends on your requirements. Commented Sep 11, 2020 at 13:39
  • 1
    We also found that shm size depend on max_parallel_workers_per_gather parameter, not just only shared_buffers. We want to know more detailed recommensation... We set shared_buffers to 1GB and shm size to 1.1GB but isse still persist. We set max_parallel_workers_per_gather from 2 to 1 and iisue was resolved. For now we set shm_size to 2.2GB, shared_buffers 1 GB, max_parallel_workers_per_gather =2 and all fine. Commented Sep 11, 2020 at 15:07

2 Answers 2

Reset to default
6

There are two things that use shared memory:

  • The shared memory that is allocated at server start. This consists of several parts, but the lion's share is shared_buffers, the data cache.

    So you have to have more shared memory than that.

  • Shared memory segments allocated by parallel query to communicate between parallel workers.

    Their size depends on the data transferred, and several of them can be allocated, so that is a resource that is harder to control.

On a memory-constrained system, I'd recommend that you disable parallel query. It makes queries faster, but uses more resources per query, so overall throughput doesn't improve. It is good if you want to throw a lot of resources on a single query.

To disable parallel query, set max_parallel_workers to 0.

Improve this answer
3
  • 2
    Based on postgrespro.com/list/thread-id/2448119 and observing the behavior of the PostgreSQL Docker image, I'm not convinced shared_buffers is allocated from shm. We're running with 1GB shared_buffers and shm-size of 256MB and it seems to be OK. Commented Jul 28, 2022 at 14:03
  • @lmsurprenant PostgreSQL doesn't use a lot of System V shared memory, so those limits won't apply. It uses POSIX shared memory. Commented Aug 12, 2022 at 18:20
  • 2
    I think this answer is wrong! I agree with @lmsurprenant, one of the Postgres devs says on that list: PostgreSQL creates segments in /dev/shm for parallel queries (via shm_open()), not for shared buffers. The amount used is controlled by work_mem. Queries can use up to work_mem for each node you see in the EXPLAIN plan, and for each process, so it can be quite a lot if you have lots of parallel worker processes and/or lots of tables/partitions being sorted or hashed in your query. Commented Mar 23, 2023 at 20:10
0

We do run PostgreSQL in docker container within our OpenStack trove setup (DBaaS). So in this setup user has no ability to change how docker container will start, but he may set shared_buffers and other options in his postgresql.conf by setting them in trove's configuration group.

Our approach for production is to allow of using all available memory as shared memory and let user decide of how RDBMS will use it.

Btw default value for my ubuntu desktop is:

kernel.shmall = 18446744073692774399

(no limit)

The other approach would be to disallow users to set shm_max higher than 64Mb (docker's default ShmMax), but I assume that this may impact the performance for big queries.

So the answer would be: shm_max value for docker should be higher or equal than shared_buffers

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.