public inbox for [email protected]
help / color / mirror / Atom feedRE: Memory issues with PostgreSQL 15
3+ messages / 3 participants
[nested] [flat]
* RE: Memory issues with PostgreSQL 15
@ 2024-07-25 10:58 Christian Schröder <[email protected]>
2024-07-25 12:34 ` Re: Memory issues with PostgreSQL 15 Alban Hertroys <[email protected]>
2024-07-25 13:14 ` Re: Memory issues with PostgreSQL 15 Ron Johnson <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Christian Schröder @ 2024-07-25 10:58 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: Francisco Olarte <[email protected]>; Tom Lane <[email protected]>; Eric Wong <[email protected]>
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 <[email protected]>
Sent: Wednesday, May 29, 2024 11:44 PM
To: Christian Schröder <[email protected]>
Cc: Francisco Olarte <[email protected]>; [email protected]; Eric Wong <[email protected]>
Subject: Re: Memory issues with PostgreSQL 15
[EXTERNAL]
=?utf-8?B?Q2hyaXN0aWFuIFNjaHLDtmRlcg==?= <[email protected]> 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 [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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Memory issues with PostgreSQL 15
2024-07-25 10:58 RE: Memory issues with PostgreSQL 15 Christian Schröder <[email protected]>
@ 2024-07-25 12:34 ` Alban Hertroys <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Alban Hertroys @ 2024-07-25 12:34 UTC (permalink / raw)
To: Christian Schröder <[email protected]>; +Cc: [email protected] <[email protected]>; Francisco Olarte <[email protected]>; Tom Lane <[email protected]>; Eric Wong <[email protected]>
> On 25 Jul 2024, at 12:58, Christian Schröder <[email protected]> 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Memory issues with PostgreSQL 15
2024-07-25 10:58 RE: Memory issues with PostgreSQL 15 Christian Schröder <[email protected]>
@ 2024-07-25 13:14 ` Ron Johnson <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Ron Johnson @ 2024-07-25 13:14 UTC (permalink / raw)
To: pgsql-general
On Thu, Jul 25, 2024 at 6:59 AM Christian Schröder <
[email protected]> 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.
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-07-25 13:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-25 10:58 RE: Memory issues with PostgreSQL 15 Christian Schröder <[email protected]>
2024-07-25 12:34 ` Alban Hertroys <[email protected]>
2024-07-25 13:14 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox