public inbox for [email protected]
help / color / mirror / Atom feedFrom: Muhammad Salahuddin Manzoor <[email protected]>
To: Christian Schröder <[email protected]>
Cc: pgsql-general <[email protected]>
Cc: Eric Wong <[email protected]>
Subject: Re: Memory issues with PostgreSQL 15
Date: Wed, 29 May 2024 14:40:45 +0500
Message-ID: <CAKD7CDkhQLGWm71nkAFBbuoQ92BGhz3FUkPDGrgrn1v8515H8g@mail.gmail.com> (raw)
In-Reply-To: <LO0P265MB2700E0D30CC9A427956FC121F9F02@LO0P265MB2700.GBRP265.PROD.OUTLOOK.COM>
References: <LO0P265MB2700E0D30CC9A427956FC121F9F02@LO0P265MB2700.GBRP265.PROD.OUTLOOK.COM>
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, <[email protected]>
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 [email protected]. 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.
>
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Memory issues with PostgreSQL 15
In-Reply-To: <CAKD7CDkhQLGWm71nkAFBbuoQ92BGhz3FUkPDGrgrn1v8515H8g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox