pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres server crash
30+ messages / 15 participants
[nested] [flat]
* Postgres server crash
@ 2006-11-16 02:20 Craig A. James <cjames@modgraph-usa.com>
0 siblings, 2 replies; 30+ messages in thread
From: Craig A. James @ 2006-11-16 02:20 UTC (permalink / raw)
To: pgsql-performance
For the third time today, our server has crashed, or frozen, actually something in between. Normally there are about 30-50 connections because of mod_perl processes that keep connections open. After the crash, there are three processes remaining:
# ps -ef | grep postgres
postgres 23832 1 0 Nov11 pts/1 00:02:53 /usr/local/pgsql/bin/postmaster -D /postgres/main
postgres 1200 23832 20 14:28 pts/1 00:58:14 postgres: pubchem pubchem 66.226.76.106(58882) SELECT
postgres 4190 23832 25 14:33 pts/1 01:09:12 postgres: asinex asinex 66.226.76.106(56298) SELECT
But they're not doing anything: No CPU time consumed, no I/O going on, no progress. If I try to connect with psql(1), it says:
psql: FATAL: the database system is in recovery mode
And the server log has:
LOG: background writer process (PID 23874) was terminated by signal 9
LOG: terminating any other active server processes
LOG: statistics collector process (PID 23875) was terminated by signal 9
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited ab
normally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited ab
... repeats about 50 times, one per process.
Questions:
1. Any idea what happened and how I can avoid this? It's a *big* problem.
2. Why didn't the database recover? Why are there two processes
that couldn't be killed?
3. Where did the "signal 9" come from? (Nobody but me ever logs
in to the server machine.)
Help!
Thanks,
Craig
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 02:28 Russell Smith <mr-russ@pws.com.au>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 30+ messages in thread
From: Russell Smith @ 2006-11-16 02:28 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
Craig A. James wrote:
> For the third time today, our server has crashed, or frozen, actually
> something in between. Normally there are about 30-50 connections
> because of mod_perl processes that keep connections open. After the
> crash, there are three processes remaining:
>
> # ps -ef | grep postgres
> postgres 23832 1 0 Nov11 pts/1 00:02:53
> /usr/local/pgsql/bin/postmaster -D /postgres/main
> postgres 1200 23832 20 14:28 pts/1 00:58:14 postgres: pubchem
> pubchem 66.226.76.106(58882) SELECT
> postgres 4190 23832 25 14:33 pts/1 01:09:12 postgres: asinex
> asinex 66.226.76.106(56298) SELECT
>
> But they're not doing anything: No CPU time consumed, no I/O going on,
> no progress. If I try to connect with psql(1), it says:
>
> psql: FATAL: the database system is in recovery mode
>
> And the server log has:
>
> LOG: background writer process (PID 23874) was terminated by signal 9
> LOG: terminating any other active server processes
> LOG: statistics collector process (PID 23875) was terminated by signal 9
> WARNING: terminating connection because of crash of another server
> process
> DETAIL: The postmaster has commanded this server process to roll back
> the current transaction and exit, because another server process
> exited ab
> normally and possibly corrupted shared memory.
> HINT: In a moment you should be able to reconnect to the database and
> repeat your command.
> WARNING: terminating connection because of crash of another server
> process
> DETAIL: The postmaster has commanded this server process to roll back
> the current transaction and exit, because another server process
> exited ab
> ... repeats about 50 times, one per process.
>
> Questions:
> 1. Any idea what happened and how I can avoid this? It's a *big*
> problem.
> 2. Why didn't the database recover? Why are there two processes
> that couldn't be killed?
> 3. Where did the "signal 9" come from? (Nobody but me ever logs
> in to the server machine.)
>
I would guess it's the linux OOM if you are running linux. You need to
turn off killing of processes when you run out of memory. Are you
getting close to running out of memory?
> Help!
>
> Thanks,
> Craig
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 10:48 Richard Huxton <dev@archonet.com>
parent: Russell Smith <mr-russ@pws.com.au>
0 siblings, 2 replies; 30+ messages in thread
From: Richard Huxton @ 2006-11-16 10:48 UTC (permalink / raw)
To: Russell Smith <mr-russ@pws.com.au>; +Cc: Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
Russell Smith wrote:
> Craig A. James wrote:
>> Questions:
>> 1. Any idea what happened and how I can avoid this? It's a *big*
>> problem.
>> 2. Why didn't the database recover? Why are there two processes
>> that couldn't be killed?
I'm guessing it didn't recover *because* there were two processes that
couldn't be killed. Responsibility for that falls to the
operating-system. I've seen it most often with faulty drivers or
hardware that's being communicated with/written to. However, see below.
>> 3. Where did the "signal 9" come from? (Nobody but me ever logs
>> in to the server machine.)
>>
> I would guess it's the linux OOM if you are running linux.
If not, it means the server is hacked or haunted. Something outside PG
issued a kill -9 and the OOM killer is the prime suspect I'd say.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 14:01 Merlin Moncure <mmoncure@gmail.com>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 0 replies; 30+ messages in thread
From: Merlin Moncure @ 2006-11-16 14:01 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
On 11/15/06, Craig A. James <cjames@modgraph-usa.com> wrote:
> Questions:
> 1. Any idea what happened and how I can avoid this? It's a *big* problem.
> 2. Why didn't the database recover? Why are there two processes
> that couldn't be killed?
> 3. Where did the "signal 9" come from? (Nobody but me ever logs
> in to the server machine.)
how much memory is in the box? maybe vmstat will give you some clues
about what is going on leading up to the crash...a spike in swap for
example. Maybe, if you can budget for it, query logging might give
some clues also. Try increasing swap.
merlin
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 17:00 Craig A. James <cjames@modgraph-usa.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 3 replies; 30+ messages in thread
From: Craig A. James @ 2006-11-16 17:00 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Russell Smith <mr-russ@pws.com.au>; pgsql-performance
Russell Smith wrote:
>> For the third time today, our server has crashed...
>
> I would guess it's the linux OOM if you are running linux. You need to
> turn off killing of processes when you run out of memory. Are you
> getting close to running out of memory?
Good suggestion, it was a memory leak in an add-on library that we plug in to the Postgres server.
OOM? Can you give me a quick pointer to what this acronym stands for and how I can reconfigure it? It sounds like a "feature" old UNIX systems like SGI IRIX had, where the system would allocate virtual memory that it didn't really have, then kill your process if you tried to use it. I.e. malloc() would never return NULL even if swap space was over allocated. Is this what you're talking about? Having this enabled on a server is deadly for reliability.
Thanks,
Craig
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 17:09 Richard Huxton <dev@archonet.com>
parent: Craig A. James <cjames@modgraph-usa.com>
2 siblings, 0 replies; 30+ messages in thread
From: Richard Huxton @ 2006-11-16 17:09 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Russell Smith <mr-russ@pws.com.au>; pgsql-performance
Craig A. James wrote:
> Russell Smith wrote:
>>> For the third time today, our server has crashed...
>>
>> I would guess it's the linux OOM if you are running linux. You need to
>> turn off killing of processes when you run out of memory. Are you
>> getting close to running out of memory?
>
> Good suggestion, it was a memory leak in an add-on library that we plug
> in to the Postgres server.
>
> OOM? Can you give me a quick pointer to what this acronym stands for
> and how I can reconfigure it?
Out Of Memory
> It sounds like a "feature" old UNIX
> systems like SGI IRIX had, where the system would allocate virtual
> memory that it didn't really have, then kill your process if you tried
> to use it.
That's it.
> I.e. malloc() would never return NULL even if swap space was
> over allocated. Is this what you're talking about? Having this enabled
> on a server is deadly for reliability.
Indeed. See the manuals for details. Section 16.4.3
http://www.postgresql.org/docs/8.1/static/kernel-resources.html#AEN18128
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 17:15 Craig A. James <cjames@modgraph-usa.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 30+ messages in thread
From: Craig A. James @ 2006-11-16 17:15 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Russell Smith <mr-russ@pws.com.au>; pgsql-performance
By the way, in spite of my questions and concerns, I was *very* impressed by the recovery process. I know it might seem like old hat to you guys to watch the WAL in action, and I know on a theoretical level it's supposed to work, but watching it recover 150 separate databases, and find and fix a couple of problems was very impressive. It gives me great confidence that I made the right choice to use Postgres.
Richard Huxton wrote:
>>> 2. Why didn't the database recover? Why are there two processes
>>> that couldn't be killed?
>
> I'm guessing it didn't recover *because* there were two processes that
> couldn't be killed. Responsibility for that falls to the
> operating-system. I've seen it most often with faulty drivers or
> hardware that's being communicated with/written to. However, see below.
It can't be a coincidence that these were the only two processes in a SELECT operation. Does the server disable signals at critical points?
I'd make a wild guess that this is some sort of deadlock problem -- these two servers have disabled signals for a critical section of SELECT, and are waiting for something from the postmaster, but postmaster is dead.
This is an ordinary system, no hardware problems, stock RH FC3 kernel, stock PG 8.1.4, with 4 GB memory, and at the moment the database is running on a single SATA disk. I'm worried that a production server can get into a state that requires manual intervention to recover.
Craig
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 17:29 Richard Huxton <dev@archonet.com>
parent: Craig A. James <cjames@modgraph-usa.com>
0 siblings, 2 replies; 30+ messages in thread
From: Richard Huxton @ 2006-11-16 17:29 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Russell Smith <mr-russ@pws.com.au>; pgsql-performance
Craig A. James wrote:
> By the way, in spite of my questions and concerns, I was *very*
> impressed by the recovery process. I know it might seem like old hat to
> you guys to watch the WAL in action, and I know on a theoretical level
> it's supposed to work, but watching it recover 150 separate databases,
> and find and fix a couple of problems was very impressive. It gives me
> great confidence that I made the right choice to use Postgres.
>
> Richard Huxton wrote:
>>>> 2. Why didn't the database recover? Why are there two processes
>>>> that couldn't be killed?
>>
>> I'm guessing it didn't recover *because* there were two processes that
>> couldn't be killed. Responsibility for that falls to the
>> operating-system. I've seen it most often with faulty drivers or
>> hardware that's being communicated with/written to. However, see below.
>
> It can't be a coincidence that these were the only two processes in a
> SELECT operation. Does the server disable signals at critical points?
If a "kill -9" as root doesn't get rid of them, I think I'm right in
saying that it's a kernel-level problem rather than something else.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 18:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 30+ messages in thread
From: Tom Lane @ 2006-11-16 18:10 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Craig A. James <cjames@modgraph-usa.com>; Russell Smith <mr-russ@pws.com.au>; pgsql-performance
Richard Huxton <dev@archonet.com> writes:
> Craig A. James wrote:
>> It can't be a coincidence that these were the only two processes in a
>> SELECT operation. Does the server disable signals at critical points?
> If a "kill -9" as root doesn't get rid of them, I think I'm right in
> saying that it's a kernel-level problem rather than something else.
I didn't actually see Craig say anywhere that he'd tried "kill -9" on
those backends. If he did and it didn't do anything, then clearly they
were in some kind of uninterruptable wait, which is certainly evidence
of a kernel or hardware issue. If he didn't do "kill -9" then we don't
really know what the issue was --- it's not improbable that they were
stuck in some loop not containing a CHECK_FOR_INTERRUPTS() test.
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 18:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig A. James <cjames@modgraph-usa.com>
2 siblings, 1 reply; 30+ messages in thread
From: Tom Lane @ 2006-11-16 18:14 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Richard Huxton <dev@archonet.com>; Russell Smith <mr-russ@pws.com.au>; pgsql-performance
"Craig A. James" <cjames@modgraph-usa.com> writes:
> OOM? Can you give me a quick pointer to what this acronym stands for
> and how I can reconfigure it?
See "Linux Memory Overcommit" at
http://www.postgresql.org/docs/8.1/static/kernel-resources.html#AEN18128
or try googling for "OOM kill" for non-Postgres-specific coverage.
> It sounds like a "feature" old UNIX
> systems like SGI IRIX had, where the system would allocate virtual
> memory that it didn't really have, then kill your process if you tried
> to use it. I.e. malloc() would never return NULL even if swap space
> was over allocated. Is this what you're talking about? Having this
> enabled on a server is deadly for reliability.
No kidding :-(. The default behavior in Linux is extremely unfortunate.
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 18:22 Richard Huxton <dev@archonet.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 30+ messages in thread
From: Richard Huxton @ 2006-11-16 18:22 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
Craig A. James wrote:
> Richard Huxton wrote:
>> If a "kill -9" as root doesn't get rid of them, I think I'm right in
>> saying that it's a kernel-level problem rather than something else.
>
> Sorry I didn't clarify that. "kill -9" did kill them. Other signals
> did not. It wasn't until I manually intervened with the "kill -9" that
> the system began the recovery process.
Ah, when you said "unkillable" in the first msg I jumped to the wrong
conclusion.
In that case, see Tom's answer.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-16 21:00 Ben <bench@silentmedia.com>
parent: Craig A. James <cjames@modgraph-usa.com>
2 siblings, 0 replies; 30+ messages in thread
From: Ben @ 2006-11-16 21:00 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Richard Huxton <dev@archonet.com>; Russell Smith <mr-russ@pws.com.au>; pgsql-performance
OOM stands for "Out Of Memory" and it does indeed seem to be the same as
what IRIX had. I believe you can turn the feature off and also configure
its overcomitment by setting something in /proc/..... and unfortunately, I
don't remember more than that.
On Thu, 16 Nov 2006, Craig A. James wrote:
> OOM? Can you give me a quick pointer to what this acronym stands for and how
> I can reconfigure it? It sounds like a "feature" old UNIX systems like SGI
> IRIX had, where the system would allocate virtual memory that it didn't
> really have, then kill your process if you tried to use it. I.e. malloc()
> would never return NULL even if swap space was over allocated. Is this what
> you're talking about? Having this enabled on a server is deadly for
> reliability.
>
> Thanks,
> Craig
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 01:28 Richard Troy <rtroy@ScienceTools.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 4 replies; 30+ messages in thread
From: Richard Troy @ 2006-11-19 01:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Craig A. James <cjames@modgraph-usa.com>; Richard Huxton <dev@archonet.com>; Russell Smith <mr-russ@pws.com.au>; pgsql-performance
On Thu, 16 Nov 2006, Tom Lane wrote:
>
> "Craig A. James" <cjames@modgraph-usa.com> writes:
> > OOM? Can you give me a quick pointer to what this acronym stands for
> > and how I can reconfigure it?
>
> See "Linux Memory Overcommit" at
> http://www.postgresql.org/docs/8.1/static/kernel-resources.html#AEN18128
> or try googling for "OOM kill" for non-Postgres-specific coverage.
I did that - spent about two f-ing hours looking for what I wanted. (Guess
I entered poor choices for my searches. -frown- ) There are a LOT of
articles that TALK ABOUT OOM, but prescious few actually tell you what you
can do about it.
Trying to save you some time:
On linux you can use the sysctl utility to muck with vm.overcommit_memory;
You can disable the "feature."
Google _that_ for more info!
>
> > It sounds like a "feature" old UNIX
> > systems like SGI IRIX had, where the system would allocate virtual
> > memory that it didn't really have, then kill your process if you tried
> > to use it. I.e. malloc() would never return NULL even if swap space
> > was over allocated. Is this what you're talking about? Having this
> > enabled on a server is deadly for reliability.
>
> No kidding :-(. The default behavior in Linux is extremely unfortunate.
>
> regards, tom lane
That's a major understatement.
The reason I spent a couple of hours looking for what I could learn on
this is that I've been absolutely beside myself on this "extremely
unfortunate" "feature." I had a badly behaving app (but didn't know which
app it was), so Linux would kill lots of things, like, oh, say, inetd.
Good luck sshing into the box. You just had to suffer with pushing the
damned reset button... It must have taken at least a week before figuring
out what not to do. (What I couldn't/can't understand is why the system
wouldn't just refuse the bad app the memory when it was short - no, you've
had enough!)
<soapbox> ...I read a large number of articles on this subject and am
absolutely dumbfounded by the -ahem- idiots who think killing a random
process is an appropriate action. I'm just taking their word for it that
there's some kind of impossibility of the existing Linux kernel not
getting itself into a potentially hung situation because it didn't save
itself any memory. Frankly, if it takes a complete kernel rewrite to fix
the problem that the damned operating system can't manage its own needs,
then the kernel needs to be rewritten! </soapbox>
These kernel hackers could learn something from VAX/VMS.
Richard
--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 06:43 Craig A. James <cjames@modgraph-usa.com>
parent: Richard Troy <rtroy@ScienceTools.com>
3 siblings, 1 reply; 30+ messages in thread
From: Craig A. James @ 2006-11-19 06:43 UTC (permalink / raw)
To: Richard Troy <rtroy@ScienceTools.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; Russell Smith <mr-russ@pws.com.au>; pgsql-performance
Richard Troy wrote:
> I did that - spent about two f-ing hours looking for what I wanted. (Guess
> I entered poor choices for my searches. -frown- ) There are a LOT of
> articles that TALK ABOUT OOM, but prescious few actually tell you what you
> can do about it.
>
> Trying to save you some time:
>
> On linux you can use the sysctl utility to muck with vm.overcommit_memory;
> You can disable the "feature."
>
> Google _that_ for more info!
Here's something I found googling for "memory overcommitment"+linux
http://archives.neohapsis.com/archives/postfix/2000-04/0512.html
From /usr/src/linux/Documentation/sysctl/vm.txt
"overcommit_memory:
This value contains a flag that enables memory overcommitment.
When this flag is 0, the kernel checks before each malloc()
to see if there's enough memory left. If the flag is nonzero,
the system pretends there's always enough memory."
This flag is settable in /proc/sys/vm
Lo and behold, here it is on my system:
$ cat /proc/sys/vm/overcommit_memory
0
$ cat /proc/sys/vm/overcommit_ratio
50
Craig
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 06:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig A. James <cjames@modgraph-usa.com>
0 siblings, 2 replies; 30+ messages in thread
From: Tom Lane @ 2006-11-19 06:51 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Richard Troy <rtroy@ScienceTools.com>; Richard Huxton <dev@archonet.com>; Russell Smith <mr-russ@pws.com.au>; pgsql-performance
"Craig A. James" <cjames@modgraph-usa.com> writes:
> Here's something I found googling for "memory overcommitment"+linux
> http://archives.neohapsis.com/archives/postfix/2000-04/0512.html
That might have been right when it was written (note the reference to a
2.2 Linux kernel), but it's 100% wrong now. 0 is the default, not-safe
setting.
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 17:01 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 30+ messages in thread
From: Ron Mayer @ 2006-11-19 17:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> "Craig A. James" <cjames@modgraph-usa.com> writes:
>> Here's something I found googling for "memory overcommitment"+linux
>> http://archives.neohapsis.com/archives/postfix/2000-04/0512.html
>
> That might have been right when it was written (note the reference to a
> 2.2 Linux kernel), but it's 100% wrong now. 0 is the default, not-safe
> setting.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 17:07 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 30+ messages in thread
From: Ron Mayer @ 2006-11-19 17:07 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote:
> "Craig A. James" <cjames@modgraph-usa.com> writes:
>> Here's something I found googling for "memory overcommitment"+linux
>> http://archives.neohapsis.com/archives/postfix/2000-04/0512.html
>
> That might have been right when it was written (note the reference to a
> 2.2 Linux kernel), but it's 100% wrong now. 0 is the default, not-safe
> setting.
Is this something we could detect and show a warning for at initdb
time; or perhaps log a warning in the log files at postmaster startup?
This dangerous choice of settings seems to catch quite a few postgresql
users.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 18:41 Michael Stone <mstone+postgres@mathom.us>
parent: Richard Troy <rtroy@ScienceTools.com>
3 siblings, 2 replies; 30+ messages in thread
From: Michael Stone @ 2006-11-19 18:41 UTC (permalink / raw)
To: pgsql-performance
On Sat, Nov 18, 2006 at 05:28:46PM -0800, Richard Troy wrote:
>On linux you can use the sysctl utility to muck with vm.overcommit_memory;
>You can disable the "feature."
Be aware that there's are "reasons" the "feature" exists before you
"cast" "aspersions" and "quote marks" all over the place, and the
"reasons" are "things" like "shared memory" and "copy on write"
memory, which are "generally" condsidered "good things".
At one point someone complained about the ability to configure, e.g.,
IRIX to allow memory overcommit. I worked on some large IRIX
installations where full memory accounting would have required on the
order of 100s of gigabytes of swap, due to large shared memory
allocations. If the swap had been configured rather than allowing
overcommit, sure it might have reduced the chance of an application
allocating memory that it couldn't use. In practice, if you're 100s of
gigs into swap the system isn't going to be useable anyway.
>itself any memory. Frankly, if it takes a complete kernel rewrite to fix
>the problem that the damned operating system can't manage its own needs,
>then the kernel needs to be rewritten! </soapbox>
>
>These kernel hackers could learn something from VAX/VMS.
Like how to make a slower system? You can configure a VM to operate
the way they did 25 years ago, but in general people have preferred to
get better performance (*much* better performance) instead. It could be
that they're all idiots, or it could be that in practice the problem
isn't as bad as you make it out to be. (Maybe other people are just
better at configuring their memory usage?)
Mike Stone
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 20:42 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Michael Stone <mstone+postgres@mathom.us>
1 sibling, 1 reply; 30+ messages in thread
From: Richard Broersma Jr @ 2006-11-19 20:42 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; pgsql-performance
> (Maybe other people are just
> better at configuring their memory usage?)
I don't mean to hijack the thread, but I am interested in learning the science behind configuring
memory usage. A lot of the docs that I have found on this subject speak in terms of generalities
and rules of thumb. Are there any resources that can show how to tune the kernels parameters to
make use of all of the available memory and at the same time allow all services to get along?
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 21:24 Michael Stone <mstone+postgres@mathom.us>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 30+ messages in thread
From: Michael Stone @ 2006-11-19 21:24 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-performance
On Sun, Nov 19, 2006 at 12:42:45PM -0800, Richard Broersma Jr wrote:
>I don't mean to hijack the thread, but I am interested in learning the science behind configuring
>memory usage.
There isn't one. You need experience, and an awareness of your
particular requirements. If it were easy, it would be autotuned. :)
Mike Stone
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 22:12 Craig A. James <cjames@modgraph-usa.com>
parent: Michael Stone <mstone+postgres@mathom.us>
1 sibling, 2 replies; 30+ messages in thread
From: Craig A. James @ 2006-11-19 22:12 UTC (permalink / raw)
To: pgsql-performance; mstone+postgres@mathom.us
Michael Stone wrote:
> At one point someone complained about the ability to configure, e.g.,
> IRIX to allow memory overcommit. I worked on some large IRIX
> installations where full memory accounting would have required on the
> order of 100s of gigabytes of swap, due to large shared memory
> allocations.
These were mostly scientific and graphical apps where reliability took a back seat to performance and to program complexity. They would allocate 100's of GB of swap space rather than taking the time to design proper data structures. If the program crashed every week or two, no big deal -- just run it again. Overallocating memory is a valuable technique for such applications.
But overallocating memory has no place in a server environment. When memory overcommittment is allowed, it is impossible to write a reliable application, because no matter how carefully and correctly you craft your code, someone else's program that leaks memory like Elmer Fudd's rowboat after his shotgun goes off, can kill your well-written application.
Installing Postgres on such a system makes Postgres unreliable.
Tom Lane wrote:
> That might have been right when it was written (note the reference to a
> 2.2 Linux kernel), but it's 100% wrong now.
> [Setting /proc/sys/vm/overcommit_memory to] 0 is the default, not-safe
> setting.
I'm surprised that the Linux kernel people take such a uncritical view of reliability that they set, as *default*, a feature that makes Linux an unreliable platform for servers.
And speaking of SGI, this very issue was among the things that sank the company. As the low-end graphics cards ate into their visualization market, they tried to become an Oracle Server platform. Their servers were *fast*. But they crashed -- a lot. And memory-overcommit was one of the reasons. IRIX admins would brag that their systems only crashed every couple of weeks. I had HP and Sun systems that would run for years.
Craig
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-19 22:32 Michael Stone <mstone+postgres@mathom.us>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 30+ messages in thread
From: Michael Stone @ 2006-11-19 22:32 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
On Sun, Nov 19, 2006 at 02:12:01PM -0800, Craig A. James wrote:
>And speaking of SGI, this very issue was among the things that sank the
>company. As the low-end graphics cards ate into their visualization
>market, they tried to become an Oracle Server platform. Their servers were
>*fast*. But they crashed -- a lot. And memory-overcommit was one of the
>reasons.
You realize that it had to be turned on explicitly on IRIX, right? But
don't let facts get in the way of a good rant...
Mike Stone
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-20 01:00 Craig A. James <cjames@modgraph-usa.com>
parent: Michael Stone <mstone+postgres@mathom.us>
0 siblings, 0 replies; 30+ messages in thread
From: Craig A. James @ 2006-11-20 01:00 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; pgsql-performance
> You realize that it had to be turned on explicitly on IRIX, right? But
> don't let facts get in the way of a good rant...
On the contrary, with Irix 4 and earlier it was the default, but it caused so many problems that SGI switched the default to OFF in IRIX 5. But because it had been available for so long, many important apps had come to rely on it, so most sites had to immediately re-enable virtual swap on every IRIX 5 server that came in. Admins just got used to doing it, so it became a "default" at most sites, and admins often couldn't be convinced to disable it for database server machines, because "That's our standard for IRIX configuration."
I worked at a big molecular modeling/visualization company; our visualization programs *required* virtual swap, and our server programs *prohibited* virtual swap. Imagine how our sales people felt about that, telling customers that they'd have to buy two $30,000 machines just because of one kernel parameter. Of course, they didn't, and our server apps took the heat as being "unreliable."
SGI called it "virtual swap" which I always thought was a hoot. You have virtual memory, which is really your swap space, and then virtual swap, which is some kind of dark hole...
Craig
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-20 01:59 Bruno Wolff III <bruno@wolff.to>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 30+ messages in thread
From: Bruno Wolff III @ 2006-11-20 01:59 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance; mstone+postgres@mathom.us
On Sun, Nov 19, 2006 at 14:12:01 -0800,
"Craig A. James" <cjames@modgraph-usa.com> wrote:
>
> These were mostly scientific and graphical apps where reliability took a
> back seat to performance and to program complexity. They would allocate
> 100's of GB of swap space rather than taking the time to design proper data
> structures. If the program crashed every week or two, no big deal -- just
> run it again. Overallocating memory is a valuable technique for such
> applications.
I don't think the above applies generally. Programmers need to be aware of
the working set of CPU bound apps. If the program is constantly paging,
the performance is going to be abysmal.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-20 06:42 Craig A. James <cjames@modgraph-usa.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 30+ messages in thread
From: Craig A. James @ 2006-11-20 06:42 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; Craig A. James <cjames@modgraph-usa.com>; pgsql-performance; mstone+postgres@mathom.us
We're getting off-topic for this group except that this is *why* we're plagued with this problem, so I'll make one more observation.
Bruno Wolff III wrote:
>> They would allocate
>> 100's of GB of swap space rather than taking the time to design proper data
>> structures. If the program crashed every week or two, no big deal -- just
>> run it again. Overallocating memory is a valuable technique for such
>> applications.
>
> I don't think the above applies generally. Programmers need to be aware of
> the working set of CPU bound apps. If the program is constantly paging,
> the performance is going to be abysmal.
You're doing planetary number crunching, so you allocate an (x,y,z) space with 10^6 points on every axis, or 10^18 points, for roughly 2^60 64-bit floating-point numbers, or 2^68 bytes. But your space is mostly empty (just like real space). Where there's a planet or star, you're actually using memory, everywhere else, the memory is never referenced. So you're using just an ordinary amount of memory, but you can access your planet's data by simply referencing a three-dimensional array in FORTRAN. Just allow infinite overcommit, let the OS do the rest, and it actually works. A lot of molecular modeling works this way too.
This is how these applications were/are actually written. I'm not defending the method, just point out that it's real and it's one of the reasons virtual swap was invented.
Craig
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-20 22:36 Mattias Kregert <mattias@kregert.se>
parent: Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 0 replies; 30+ messages in thread
From: Mattias Kregert @ 2006-11-20 22:36 UTC (permalink / raw)
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: pgsql-performance
if [ `cat /proc/sys/vm/overcommit_memory` == 0 ]; then
echo "WARNING: Watch out for the oom-killer! Consider echo 2 >
/proc/sys/vm/overcommit_memory"
fi
----- Original Message -----
From: "Ron Mayer" <rm_pg@cheapcomplexdevices.com>
To: <pgsql-performance@postgresql.org>
Sent: Sunday, November 19, 2006 6:07 PM
Subject: Re: Postgres server crash
> Tom Lane wrote:
>> "Craig A. James" <cjames@modgraph-usa.com> writes:
>>> Here's something I found googling for "memory overcommitment"+linux
>>> http://archives.neohapsis.com/archives/postfix/2000-04/0512.html
>>
>> That might have been right when it was written (note the reference to a
>> 2.2 Linux kernel), but it's 100% wrong now. 0 is the default, not-safe
>> setting.
>
> Is this something we could detect and show a warning for at initdb
> time; or perhaps log a warning in the log files at postmaster startup?
> This dangerous choice of settings seems to catch quite a few postgresql
> users.
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-21 13:22 Markus Schaber <schabi@logix-tt.com>
parent: Richard Troy <rtroy@ScienceTools.com>
3 siblings, 0 replies; 30+ messages in thread
From: Markus Schaber @ 2006-11-21 13:22 UTC (permalink / raw)
To: Richard Troy <rtroy@ScienceTools.com>; +Cc: pgsql-performance
Hi, Richard,
Richard Troy wrote:
> The reason I spent a couple of hours looking for what I could learn on
> this is that I've been absolutely beside myself on this "extremely
> unfortunate" "feature." I had a badly behaving app (but didn't know which
> app it was), so Linux would kill lots of things, like, oh, say, inetd.
Actually, AFAICT, Linux tries to kill the process(es) that use the most
memory ressources first.
Without overcommitment, the OOM killer won't kick in, and as long as the
hoggy applications don't actually exit when malloc fails, they will just
stay around, sitting on their memory.
> Good luck sshing into the box.
SSH'ing into the box will even get worse without overcommitment. When
the machine is stuck, the sshd that tries to spawn its child will get
the out of memory signal, and you won't be able to log in.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-26 23:41 Jim C. Nasby <jim@nasby.net>
parent: Richard Troy <rtroy@ScienceTools.com>
3 siblings, 2 replies; 30+ messages in thread
From: Jim C. Nasby @ 2006-11-26 23:41 UTC (permalink / raw)
To: Richard Troy <rtroy@ScienceTools.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Craig A. James <cjames@modgraph-usa.com>; Richard Huxton <dev@archonet.com>; Russell Smith <mr-russ@pws.com.au>; pgsql-performance
On Sat, Nov 18, 2006 at 05:28:46PM -0800, Richard Troy wrote:
> <soapbox> ...I read a large number of articles on this subject and am
> absolutely dumbfounded by the -ahem- idiots who think killing a random
> process is an appropriate action. I'm just taking their word for it that
> there's some kind of impossibility of the existing Linux kernel not
> getting itself into a potentially hung situation because it didn't save
> itself any memory. Frankly, if it takes a complete kernel rewrite to fix
> the problem that the damned operating system can't manage its own needs,
> then the kernel needs to be rewritten! </soapbox>
>
> These kernel hackers could learn something from VAX/VMS.
What's interesting is that apparently FreeBSD also has overcommit (and
IIRC no way to disable it), yet I never hear people going off on OOM
kills in FreeBSD. My theory is that FreeBSD admins are smart enough to
dedicate a decent amount of swap space, so that by the time you got to
an OOM kill situation you'd be so far into swapping that the box would
be nearly unusable. Many linux 'admins' think it's ok to save a few GB
of disk space by allocating a small amount of swap (or none at all), and
*kaboom*.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-27 13:20 Michael Stone <mstone+postgres@mathom.us>
parent: Jim C. Nasby <jim@nasby.net>
1 sibling, 0 replies; 30+ messages in thread
From: Michael Stone @ 2006-11-27 13:20 UTC (permalink / raw)
To: pgsql-performance
On Sun, Nov 26, 2006 at 05:41:02PM -0600, Jim C. Nasby wrote:
>What's interesting is that apparently FreeBSD also has overcommit (and
>IIRC no way to disable it), yet I never hear people going off on OOM
>kills in FreeBSD.
Could just be that nobody is using FreeBSD. <ducks>
Seriously, though, there are so many linux installations out there that
you're statistically more likely to see corner cases. FWIW, I know a
whole lotta linux machines that have never had OOM-killer problems.
Remember, though, that anecodotal evidence isn't.
Mike Stone
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Postgres server crash
@ 2006-11-27 18:11 Florian Weimer <fw@deneb.enyo.de>
parent: Jim C. Nasby <jim@nasby.net>
1 sibling, 0 replies; 30+ messages in thread
From: Florian Weimer @ 2006-11-27 18:11 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Richard Troy <rtroy@ScienceTools.com>; Tom Lane <tgl@sss.pgh.pa.us>; Craig A. James <cjames@modgraph-usa.com>; Richard Huxton <dev@archonet.com>; Russell Smith <mr-russ@pws.com.au>; pgsql-performance
* Jim C. Nasby:
> What's interesting is that apparently FreeBSD also has overcommit (and
> IIRC no way to disable it), yet I never hear people going off on OOM
> kills in FreeBSD. My theory is that FreeBSD admins are smart enough to
> dedicate a decent amount of swap space, so that by the time you got to
> an OOM kill situation you'd be so far into swapping that the box would
> be nearly unusable.
I've seen OOM situations with our regular "use twice as much swap
space as there is RAM in the machine" rule. Perhaps Linux is just
paging too fast for this to work. 8-P
By the way, the sysctl setting breaks some applications and
programming environments, such as SBCL and CMUCL.
^ permalink raw reply [nested|flat] 30+ messages in thread
end of thread, other threads:[~2006-11-27 18:11 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-16 02:20 Postgres server crash Craig A. James <cjames@modgraph-usa.com>
2006-11-16 02:28 ` Russell Smith <mr-russ@pws.com.au>
2006-11-16 10:48 ` Richard Huxton <dev@archonet.com>
2006-11-16 17:00 ` Craig A. James <cjames@modgraph-usa.com>
2006-11-16 17:09 ` Richard Huxton <dev@archonet.com>
2006-11-16 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 01:28 ` Richard Troy <rtroy@ScienceTools.com>
2006-11-19 06:43 ` Craig A. James <cjames@modgraph-usa.com>
2006-11-19 06:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 17:01 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-19 17:07 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-20 22:36 ` Mattias Kregert <mattias@kregert.se>
2006-11-19 18:41 ` Michael Stone <mstone+postgres@mathom.us>
2006-11-19 20:42 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-19 21:24 ` Michael Stone <mstone+postgres@mathom.us>
2006-11-19 22:12 ` Craig A. James <cjames@modgraph-usa.com>
2006-11-19 22:32 ` Michael Stone <mstone+postgres@mathom.us>
2006-11-20 01:00 ` Craig A. James <cjames@modgraph-usa.com>
2006-11-20 01:59 ` Bruno Wolff III <bruno@wolff.to>
2006-11-20 06:42 ` Craig A. James <cjames@modgraph-usa.com>
2006-11-21 13:22 ` Markus Schaber <schabi@logix-tt.com>
2006-11-26 23:41 ` Jim C. Nasby <jim@nasby.net>
2006-11-27 13:20 ` Michael Stone <mstone+postgres@mathom.us>
2006-11-27 18:11 ` Florian Weimer <fw@deneb.enyo.de>
2006-11-16 21:00 ` Ben <bench@silentmedia.com>
2006-11-16 17:15 ` Craig A. James <cjames@modgraph-usa.com>
2006-11-16 17:29 ` Richard Huxton <dev@archonet.com>
2006-11-16 18:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-16 18:22 ` Richard Huxton <dev@archonet.com>
2006-11-16 14:01 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox