Question 325799 in Docker Question posted in
The official documentation can be found here.

Docker – Handling Out of Shared Memory Error in PostgreSQL (container) with Sub Partitioned Table of 36B Rows

I have PostgreSql 15.3 running as a docker container.
My docker run configuration is
-m 512g --memory-swap 512g --shm-size=16g

Using this configuration, I loaded 36B rows, taking up about 30T between tables and indexes.
I loaded with about 24 parallel operations, and the pg driver was using connection pooling, so there were about 150 concurrent connections but not all active.
My postgresql.conf was set to

max_connections = 512 
max_locks_per_transaction = 1024

All of this ran without any problem.

The 36B row table is sub-partitioned. The layer with actual data has about 80,000 tables.
Parent, partitioned by category, each partitioned by year, each partitioned by month, each partitioned by sensor id, which is the 80K tables of data.

My problem is that for the life of me, I cannot perform a simple COUNT(*) on the top level table without the
out of shared memory, you might need to increase max_locks_per_connection. In fact, I can count from a single table and from the month partition, but not from the year partition, which is only about 10K tables.

Reading that I need the lock table to hold max_connection * max_locks in shared memory, I’ve tried lowering the max_connections, and increasing both max_locks and shared memory but without success.
Currently I am at
-m 512g --memory-swap 512g --shm-size=64g for the container and

max_connections = 8 
max_locks_per_transaction = 163840

in the config, having gone by increments of course.

I do have a second table partitioned the same way, much smaller volume of data, but also 80K tables with data.
From what I’ve read, something like

max_connections = 100 
max_locks_per_transaction = 1600

should cover 160K tables, and if each lock takes 168 bytes, the shared memory needs only at least 26M.

Any suggestions on what I should be modifying and how I should be calculating the target values?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The final answer is indeed "you have too many partitions".

    HOWEVER, the actual solution to my specific question lies in the behavior of the PostgreSql docker image and specifically it seems to ignore max_locks_per_transaction if modifed in a config file attached to the container. It needs to be passed into the docker run command line as a -c option instead.

    Thanks @jjanes for suggesting verifying the values were read.

    When you run a postgres container, you can attach your own config file:

    $ # run postgres with custom config
$ docker run -d --name some-postgres 
             -v "$PWD/my-postgres.conf":/etc/postgresql/postgresql.conf 
             postgres -c 'config_file=/etc/postgresql/postgresql.conf'

    Of course, you usually need to stop and restart the container to modify the configuration setting. If you modify the config file with

    max_connections = 333 (some non-default number),

    start the container, and issue

    SELECT name, setting FROM pg_settings WHERE name = 'max_connections';
      name       | setting
-----------------+---------
 max_connections | 333

    Now do the same with max_locks_per_transaction and for the postgres:15.3 image no matter what you set it to you get

          name                 | setting
---------------------------+---------
 max_locks_per_transaction | 256

    This is hinted at in Cant change max_locks in docker and at Change max_locks for github actions

    The configuration seems to be respected only if passed in on the docker run command line

    $ docker run -d --name some-postgres 
             -v "$PWD/my-postgres.conf":/etc/postgresql/postgresql.conf 
             postgres -c 'config_file=/etc/postgresql/postgresql.conf' 
                      -c max_locks_per_transaction=1024

    Once I did this, and set max_locks_per_transaction to a ludicrous number (500,000), I was able to issue a count against the next higher partition level.

    That said, the second solution to my question lies in the detail that most discussions around this very issue seem to not mention. max_locks_per_transaction * max_connections needs to cover not just the number of all partitioned tables, but also each and every index on them. Thanks @jjanes again.

    In my case, each table has about 10 indexes. A query against one table needed 11 locks. Against the month partition needed 21,000 locks, and against the year partition needed 218,000 locks. Clearly unsustainable.

    The sobering fact is that PostgreSql partitioning does not scale horizontally as comfortably as many other features and may not lend itself well to matching the logical organization of your business domain.


  2. 0

    Having a ridiculous number of partitions is going to require a ridiculous number of locks. The number of max_connections * max_locks_per_connection you show should be able to handle 80000 tables in a simple case of unindexed tables with flat partitions, but indexes also need to be locked during planning even if they don’t end up being used in the execution. The deep partitioning structure will also require some more locks, but without a test script to reproduce your exact structure I haven’t tested to see how many it would need.

    Handling partitions gets more and more unwieldy the more partitions you have. That is going to put a practical limit on the number of partitions you can have long before you reach a hard limit. I would say you are already over the practical limit.

    These problems are just internal to PostgreSQL. If you were hitting memory problems at the OS/VM level, I would expect those to fail with different error messages than the one you see.

    The locking issues should be the same whether the tables are populated or just exist but empty, so if you need to test things it should be easy to do just be exporting the structure without the data (like pg_dump -s).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search