Thread

Memory issues with PostgreSQL 15

Christian Schröder <christian.schroeder@wsd.com> — 2024-05-27T11:32:37Z

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@wsd.com. 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.

Re: Memory issues with PostgreSQL 15

Francisco Olarte <folarte@peoplecall.com> — 2024-05-28T17:14:49Z

Hi Christian:

On Tue, 28 May 2024 at 18:40, Christian Schröder
<christian.schroeder@wsd.com> wrote:

> <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

This hints at some shm function getting an ENOSPC: Coupled with...

> 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:

A faster fail when increasing it I would start by checking your IPC
shared memory limits are ok, especially if you upgraded something in
the OS when going from 9 to 15, which seems likely.

IIRC in linux you can read them in /proc/sys/kernel/shm*, and they
were configured via sysctl.

Francisco Olarte.

RE: Memory issues with PostgreSQL 15

Christian Schröder <christian.schroeder@wsd.com> — 2024-05-28T22:59:13Z

Hi Francisco,
Thank you for your advice. I used "ipcs" to get more readable information about the shared memory:

# ipcs -m -l --human

------ Shared Memory Limits --------
max number of segments = 4096
max seg size = 16E
max total shared memory = 16E
min seg size = 1B

# ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x04000194 35         postgres   600        56         19

# ipcs -m -i 35

Shared memory Segment shmid=35
uid=26  gid=26  cuid=26 cgid=26
mode=0600       access_perms=0600
bytes=56        lpid=7653       cpid=3875       nattch=19
att_time=Tue May 28 22:56:35 2024
det_time=Tue May 28 22:56:35 2024
change_time=Tue May 28 07:59:59 2024

As far as I understand, there is no upper limit to the size of the shared memory. The database only holds a single shared memory segment, which doesn't seem to have a relevant size.
I am surprised to see this since I would have expected much more shared memory to be used by the database. Is there anything in the configuration that prevents the shared memory from being used?

Best,
Christian

-----Original Message-----
From: Francisco Olarte <folarte@peoplecall.com>
Sent: Tuesday, May 28, 2024 7:15 PM
To: Christian Schröder <christian.schroeder@wsd.com>
Cc: pgsql-general@lists.postgresql.org; Eric Wong <eric.wong@wsd.com>
Subject: Re: Memory issues with PostgreSQL 15

[EXTERNAL]

Hi Christian:

On Tue, 28 May 2024 at 18:40, Christian Schröder <christian.schroeder@wsd.com> wrote:

> <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

This hints at some shm function getting an ENOSPC: Coupled with...

> 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:

A faster fail when increasing it I would start by checking your IPC shared memory limits are ok, especially if you upgraded something in the OS when going from 9 to 15, which seems likely.

IIRC in linux you can read them in /proc/sys/kernel/shm*, and they were configured via sysctl.

Francisco Olarte.

----------------------------------------------
SUPPORT:
For any issues, inquiries, or assistance, please contact our support team at support@wsd.com. 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.

Re: Memory issues with PostgreSQL 15

Francisco Olarte <folarte@peoplecall.com> — 2024-05-29T08:23:38Z

Hi Christian:

On Wed, 29 May 2024 at 00:59, Christian Schröder
<christian.schroeder@wsd.com> wrote:
> Thank you for your advice. I used "ipcs" to get more readable information about the shared memory:
...
> As far as I understand, there is no upper limit to the size of the shared memory. The database only holds a single shared memory segment, which doesn't seem to have a relevant size.

Seems the same to me, so I will disregard that.

> I am surprised to see this since I would have expected much more shared memory to be used by the database. Is there anything in the configuration that prevents the shared memory from being used?

I am not too current with postgres, that one was a thing which
happened to me when I did more administration, and is one you always
want to check. I assume you have all checked, but I would follow by
insuring every mounted partition in your system has space. I am not
current on the details, but I know Pg can be mmaping things and doing
other stuff. Your problem seems more of resource exhaustion, so I
would follow by checking that, both disk, tmpfs and all the other
stuff. I cannot give you advice on that as it depends a lot on your
server configuration and from the age in the mssages I suspect you
have the usual suspects debugged. But as you have a configuration
crahsing in minutes and it seems to be a dev server you could do it
easily.

Sorry.

   Francisco Olarte.

Re: Memory issues with PostgreSQL 15

Muhammad Salahuddin Manzoor <salahuddin.m@bitnine.net> — 2024-05-29T09:40:45Z

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.schroeder@wsd.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@wsd.com. 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.
>

Re: Memory issues with PostgreSQL 15

Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-29T21:43:41Z

=?utf-8?B?Q2hyaXN0aWFuIFNjaHLDtmRlcg==?= <christian.schroeder@wsd.com> writes:
> # ipcs -m

> ------ Shared Memory Segments --------
> key        shmid      owner      perms      bytes      nattch     status
> 0x04000194 35         postgres   600        56         19

> I am surprised to see this since I would have expected much more shared memory to be used by the database. Is there anything in the configuration that prevents the shared memory from being used?

SysV shared memory isn't that relevant to Postgres anymore.  Most
of what we allocate goes into POSIX-style shared memory segments,
which are not shown by "ipcs".  We do still create one small
fixed-size data structure in SysV memory, which is what you're
seeing here, for arcane reasons having to do with the lifespan of
the shared memory segments being different in those two APIs.

>> <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

This seems to indicate that you're hitting some kernel limit on
the amount of POSIX shared memory.  Not sure where to look for
that.

			regards, tom lane

Re: Memory issues with PostgreSQL 15

Andrea Gelmini <andrea.gelmini@gmail.com> — 2024-05-30T06:25:34Z

Il giorno mar 28 mag 2024 alle ore 18:40 Christian Schröder
<christian.schroeder@wsd.com> ha scritto:
> 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).

Maybe you have PostgreSQL running inside a container with capped
resources (I saw this on some recent distro, running it from systemd)?

RE: Memory issues with PostgreSQL 15

Christian Schröder <christian.schroeder@wsd.com> — 2024-05-30T07:37:10Z

Hi Francisco,
Unfortunately, all disks have plenty of free space, so this can be ruled out as a reason.
I will follow up on the other suggestions from the list.

Best,
Christian

-----Original Message-----
From: Francisco Olarte <folarte@peoplecall.com>
Sent: Wednesday, May 29, 2024 10:24 AM
To: Christian Schröder <christian.schroeder@wsd.com>
Cc: pgsql-general@lists.postgresql.org; Eric Wong <eric.wong@wsd.com>
Subject: Re: Memory issues with PostgreSQL 15

[EXTERNAL]

Hi Christian:

On Wed, 29 May 2024 at 00:59, Christian Schröder <christian.schroeder@wsd.com> wrote:
> Thank you for your advice. I used "ipcs" to get more readable information about the shared memory:
...
> As far as I understand, there is no upper limit to the size of the shared memory. The database only holds a single shared memory segment, which doesn't seem to have a relevant size.

Seems the same to me, so I will disregard that.

> I am surprised to see this since I would have expected much more shared memory to be used by the database. Is there anything in the configuration that prevents the shared memory from being used?

I am not too current with postgres, that one was a thing which happened to me when I did more administration, and is one you always want to check. I assume you have all checked, but I would follow by insuring every mounted partition in your system has space. I am not current on the details, but I know Pg can be mmaping things and doing other stuff. Your problem seems more of resource exhaustion, so I would follow by checking that, both disk, tmpfs and all the other stuff. I cannot give you advice on that as it depends a lot on your server configuration and from the age in the mssages I suspect you have the usual suspects debugged. But as you have a configuration crahsing in minutes and it seems to be a dev server you could do it easily.

Sorry.

   Francisco Olarte.

----------------------------------------------
SUPPORT:
For any issues, inquiries, or assistance, please contact our support team at support@wsd.com. 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.

RE: Memory issues with PostgreSQL 15

Christian Schröder <christian.schroeder@wsd.com> — 2024-05-30T08:26:11Z

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 <salahuddin.m@bitnine.net>
Sent: Wednesday, May 29, 2024 11:41 AM
To: Christian Schröder <christian.schroeder@wsd.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>; Eric Wong <eric.wong@wsd.com>
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, <christian.schroeder@wsd.com<mailto:christian.schroeder@wsd.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@wsd.com<mailto:support@wsd.com>. 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 support@wsd.com. 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.

Re: Memory issues with PostgreSQL 15

Francisco Olarte <folarte@peoplecall.com> — 2024-05-30T10:13:50Z

On Thu, 30 May 2024 at 09:37, Christian Schröder
<christian.schroeder@wsd.com> wrote:
> Unfortunately, all disks have plenty of free space, so this can be ruled out as a reason.
> I will follow up on the other suggestions from the list.

Do not forget to check all mounted filesystems, not only disks.
Specially /dev/shm, IIRC its mounted size is one limit for posix
shared memory.

Francisco Olarte.

Re: Memory issues with PostgreSQL 15

Francisco Olarte <folarte@peoplecall.com> — 2024-05-30T11:02:52Z

Hi christian:

On Thu, 30 May 2024 at 12:51, Christian Schröder
<christian.schroeder@wsd.com> wrote:
...
> I had already checked most of your points, but I double checked them now.
...
> Shared memory limits look good to me:
> # sudo sysctl -a | grep kernel.shm
> kernel.shmall = 18446744073692774399
> kernel.shmmax = 18446744073692774399
> kernel.shmmni = 4096

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.

RE: Memory issues with PostgreSQL 15

Christian Schröder <christian.schroeder@wsd.com> — 2024-07-25T10:58:55Z

Hi all,
I started this discussion in May and was then dragged into other topics, so I could never follow up. Sorry for that!
Since then, the problem has resurfaced from time to time. Right now, we seem to have issues again, which gives me the opportunity to follow up on your various suggestions.

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:

# ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x04000194 45         postgres   600        56         20

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

# ls -lhR /dev/shm
/dev/shm:
total 6.6M
-rw------- 1 postgres postgres 1.0M Jul 25 06:26 PostgreSQL.1095217316
-rw------- 1 postgres postgres 1.0M Jul 23 06:20 PostgreSQL.124772332
-rw------- 1 postgres postgres 1.0M Jul 23 06:18 PostgreSQL.1475196260
-rw------- 1 postgres postgres 1.0M Jul 23 06:18 PostgreSQL.1725210234
-rw------- 1 postgres postgres 193K Jul 23 06:15 PostgreSQL.2581015990
-rw------- 1 postgres postgres 193K Jul 23 06:15 PostgreSQL.2929101952
-rw------- 1 postgres postgres 193K Jul 23 06:17 PostgreSQL.3018875836
-rw------- 1 postgres postgres  65K Jul 23 06:15 PostgreSQL.3403523208
-rw------- 1 postgres postgres 193K Jul 23 06:15 PostgreSQL.3482890896
-rw------- 1 postgres postgres 193K Jul 23 06:18 PostgreSQL.3824279998
-rw------- 1 postgres postgres 193K Jul 23 06:18 PostgreSQL.3891977516
-rw------- 1 postgres postgres 193K Jul 23 06:15 PostgreSQL.3929720846
-rw------- 1 postgres postgres 1.0M Jul 23 06:34 PostgreSQL.3969232506
-rw------- 1 postgres postgres 193K Jul 23 06:18 PostgreSQL.4222425006

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.

Best,
Christian

PS: The database does not run in a Docker container.

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, May 29, 2024 11:44 PM
To: Christian Schröder <christian.schroeder@wsd.com>
Cc: Francisco Olarte <folarte@peoplecall.com>; pgsql-general@lists.postgresql.org; Eric Wong <eric.wong@wsd.com>
Subject: Re: Memory issues with PostgreSQL 15

[EXTERNAL]

=?utf-8?B?Q2hyaXN0aWFuIFNjaHLDtmRlcg==?= <christian.schroeder@wsd.com> writes:
> # ipcs -m

> ------ Shared Memory Segments --------
> key        shmid      owner      perms      bytes      nattch     status
> 0x04000194 35         postgres   600        56         19

> I am surprised to see this since I would have expected much more shared memory to be used by the database. Is there anything in the configuration that prevents the shared memory from being used?

SysV shared memory isn't that relevant to Postgres anymore.  Most of what we allocate goes into POSIX-style shared memory segments, which are not shown by "ipcs".  We do still create one small fixed-size data structure in SysV memory, which is what you're seeing here, for arcane reasons having to do with the lifespan of the shared memory segments being different in those two APIs.

>> <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

This seems to indicate that you're hitting some kernel limit on the amount of POSIX shared memory.  Not sure where to look for that.

                        regards, tom lane

----------------------------------------------
SUPPORT:
For any issues, inquiries, or assistance, please contact our support team at support@wsd.com. 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.

Re: Memory issues with PostgreSQL 15

Laurenz Albe <laurenz.albe@cybertec.at> — 2024-07-25T12:26:03Z

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

Re: Memory issues with PostgreSQL 15

Alban Hertroys <haramrae@gmail.com> — 2024-07-25T12:34:49Z

> On 25 Jul 2024, at 12:58, Christian Schröder <christian.schroeder@wsd.com> wrote:
> 
> Hi all,
> I started this discussion in May and was then dragged into other topics, so I could never follow up. Sorry for that!
> Since then, the problem has resurfaced from time to time. Right now, we seem to have issues again, which gives me the opportunity to follow up on your various suggestions.
> 
> 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

We sometimes encounter a similar issue, but with disk space - on a 1TB virtual disk of which usually only about 1/4th is in use.
Our hypothesis is that sometimes some long-running transactions need to process a lot of data and put so much of it in temporary tables that they fill up the remaining space. We’ve seen the disk space climb and hit the ’No space left on device’ mark - at which point the transactions get aborted and rolled back, putting us back at the 1/4th of space in use situation.

Have you been able to catch your shared memory shortage in the act? I suspect that the stats you showed in your message were those after rollback.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Memory issues with PostgreSQL 15

Ron Johnson <ronljohnsonjr@gmail.com> — 2024-07-25T13:14:12Z

On Thu, Jul 25, 2024 at 6:59 AM Christian Schröder <
christian.schroeder@wsd.com> wrote:

> Hi all,
> I started this discussion in May and was then dragged into other topics,
> so I could never follow up. Sorry for that!
> Since then, the problem has resurfaced from time to time. Right now, we
> seem to have issues again, which gives me the opportunity to follow up on
> your various suggestions.
>
> 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
>

What's mailprocessor?  Maybe it's using some tmpfs device.

>
>