Re: Memory issues with PostgreSQL 15

On Thu, 2024-07-25 at 10:58 +0000, Christian Schröder wrote:
> The current error messages are similar to what we have seen before:
>
> <2024-07-25 12:27:38 CEST - > LOG:  could not fork autovacuum worker process: Cannot allocate memory
> <2024-07-25 12:27:38 CEST - mailprocessor> ERROR:  could not resize shared memory segment "/PostgreSQL.1226901392" to 189280 bytes: No space left on device
>
> As far as I understand, it does not make much sense to look into SysV shared
> memory (which is what ipcs does). Indeed, there is only the same small shared
> memory segment as we have seen back then:
>
> [...]
>
> Francisco and Tom both pointed at Posix shared memory instead; however, this
> also does not seem to be used a lot:
>
> # df -h /dev/shm
> Filesystem      Size  Used Avail Use% Mounted on
> tmpfs           7.8G  6.6M  7.8G   1% /dev/shm
>
> We also still see a lot of available memory:
>
> # free -m
>               total        used        free      shared  buff/cache   available
> Mem:          15882        6966         191        2109        8725        6477
> Swap:          1999         271        1728
>
> Again, exactly the same situation as before.
>
> Tom suggested that we hit some kernel limits, but I could not find any related
> kernel setting. The only limit I am aware of is the size of the /dev/shm filesystem
> itself. This could be changed, but the default value of 8 GB (which is half of
> the machine's memory) seems to be enough (given that it is not even used).
>
> Is there anything else I can analyze? Sorry again for reviving this old thread.

It could be dynamic shared memory segments created temporarily during parallel
query execution.

Try setting "max_parallel_workers_per_gather = 0", that should make that problem
disappear.

Yours,
Laurenz Albe