Re: Memory issues with PostgreSQL 15

Greetings,

The error message you encountered, "could not fork autovacuum worker
process: Cannot allocate memory," indicates that your PostgreSQL server
attempted to start an autovacuum worker process but failed because the
system ran out of memory.

Steps to verify.
1 Check system available memory with commands.
free -m
top
2. Check PG configurations.
shared_buffers --Typically 25% of total mem.
work_mem
maintenance_work_mem--For maintenance op like autovaccume create index etc.
Increase it to 64MB or appropriate to your requirement.
max_connections

Monitor /var/log/messages file for errors.

2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize shared
memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on
device

Check share memory limits.
/etc/sysctl.conf
kernel.shmmax = 68719476736 # Example value, adjust as needed
kernel.shmall = 16777216 # Example value, adjust as needed

Restart system and db

Ensure you have enough disk space available check and monitor disk space
with command
df -h

Reduce max_parallel_workers_per_gather = 2;
If it is set to high value.

I think setting up OS parameter.
Increasing maintenance mem value and reducing max paralell workers xan help
in solution.

Regards,
Salahuddin.

On Tue, 28 May 2024, 21:40 Christian Schröder, <christian(dot)schroeder(at)wsd(dot)com>
wrote:

> Hi all,
> We migrated from PostgreSQL 9.4 to PostgreSQL 15 a while ago. Since then,
> we have a lot of memory issues in our QA environment (which is a bit tense
> in resources). We did not have these problems before the migration, and we
> do not have them in our production environment, which has a lot more
> memory. So, it is not super critical for us, but I would still like to
> understand better how we can improve our configuration.
>
> Our PostgreSQL version is "PostgreSQL 15.5 on x86_64-pc-linux-gnu,
> compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit". The
> database server is a dedicated server with 15 GB RAM (and 4 cores, if this
> matters).
> We used the following settings:
> shared_buffers = 4GB
> work_mem = 4MB
>
> After a while, we saw the following error in the logs:
>
> <2024-05-20 12:01:03 CEST - > LOG: could not fork autovacuum worker
> process: Cannot allocate memory
>
> However, according to "free", a lot of memory was available:
>
> # free -m
> total used free shared buff/cache
> available
> Mem: 15882 4992 463 4195 10427
> 6365
> Swap: 1999 271 1728
>
> Our Grafana charts showed a slow increase in memory consumption until it
> plateaus at 4.66 GB.
> We also found the following error:
>
> <2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize shared
> memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on
> device
>
> I thought this could all be related to our "shared_buffers" setting, so I
> increased it to 8 GB. This almost immediately (after a few minutes) gave me
> these errors:
>
> <2024-05-27 11:45:59 CEST - > ERROR: out of memory
> <2024-05-27 11:45:59 CEST - > DETAIL: Failed on request of size 201088574
> in memory context "TopTransactionContext".
> ...
> <2024-05-27 11:58:02 CEST - > ERROR: out of memory
> <2024-05-27 11:58:02 CEST - > DETAIL: Failed while creating memory
> context "dynahash".
> <2024-05-27 11:58:02 CEST - > LOG: background worker "parallel worker"
> (PID 21480) exited with exit code 1
> ...
> <2024-05-27 12:01:02 CEST - > LOG: could not fork new process for
> connection: Cannot allocate memory
> <2024-05-27 12:01:03 CEST - > LOG: could not fork autovacuum worker
> process: Cannot allocate memory
> <2024-05-27 12:02:02 CEST - > LOG: could not fork new process for
> connection: Cannot allocate memory
>
> Since this seemed worse than before, I changed the setting back to 4 GB. I
> noticed that "free" now reports even more available memory:
>
> # free -m
> total used free shared buff/cache
> available
> Mem: 15882 621 320 2256 14940
> 12674
> Swap: 1999 199 1800
>
> So, does the "shared_buffers" setting have the opposite effect than I
> though? If I correctly remember similar discussions years ago, the database
> needs both "normal" and shared memory. By increasing the "shared_buffers"
> to 8 GB, I may have deprived it of "normal" memory. On the other hand, I
> would have expected the remaining 7 GB to still be enough.
>
> At this point, I am out of ideas. I clearly seem to misunderstand how the
> database manages its memory. This may have changed between 9.4 and 15, so
> my prior knowledge may be useless. I definitely need some help. ☹
>
> Thanks in advance,
> Christian
>
>
> ----------------------------------------------
> SUPPORT:
> For any issues, inquiries, or assistance, please contact our support team
> at support(at)wsd(dot)com(dot) Our dedicated team is available to help you and
> provide prompt assistance.
>
> CONFIDENTIALITY NOTICE:
> This email and any attachments are confidential and intended solely for
> the use of the individual or entity to whom it is addressed. If you have
> received this email in error, please notify the sender immediately and
> delete it from your system.
>