Subject: RE: Memory issues with PostgreSQL 15

From: Christian Schröder <[email-masked]>
Date: Thu, 30 May 2024 08:26:11 +0000

Hi Salahuddin,
I had already checked most of your points, but I double checked them now.

# free -m
              total        used        free      shared  buff/cache   available
Mem:          15882        1466         269        2110       14147       11976
Swap:          1999         254        1745

Free memory seems to be low, which is normal because most of the memory is used by buffers and caches. As you can see, the available memory is almost 12 GB.

shared_buffers, work_mem, etc.
Our initial setting for “shared_buffers” was 4 GB, which is roughly 25% of the system memory; however, I tried different values (see my original message), but none of them seemed to work. We also played around with the other settings but couldn’t find any combination that worked.

Shared memory limits look good to me:
# sudo sysctl -a | grep kernel.shm
kernel.shmall = 18446744073692774399
kernel.shmmax = 18446744073692774399
kernel.shmmni = 4096

Thanks,
Christian

From: Muhammad Salahuddin Manzoor <[email-masked]>
Sent: Wednesday, May 29, 2024 11:41 AM
To: Christian Schröder <[email-masked]>
Cc: pgsql-general <[email-masked]>; Eric Wong <[email-masked]>
Subject: Re: Memory issues with PostgreSQL 15

[EXTERNAL]
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-masked]<mailto:[email-masked]>> 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-masked]<mailto:[email-masked]>. 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.

----------------------------------------------
SUPPORT:
For any issues, inquiries, or assistance, please contact our support team at [email-masked]. 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.

---

From: Francisco Olarte <[email-masked]>
Date: Thu, 30 May 2024 13:02:52 +0200

Hi christian:

On Thu, 30 May 2024 at 12:51, Christian Schröder
<[email-masked]> wrote:
...
...
> kernel.shmmax = 18446744073692774399

Bear in mind this is SysV shared memory. IIRC Pg uses POSIX shared
memory for shared buffers, which I think is backed normally in Linux
by files in a tmpfs mounted on /dev/shm. It still uses some amount of
SysV due to some special properties lacking from POSIX, for control
purposes, but only a little.

You could try "df -h /dev/shm" and "ls -lhR /dev/shm/" to see if you
have problems there.

Francisco Olarte.