PostgreSQL 8.0.6 crash

"Mark Woodward" <pgsql@mohawksoft.com> writes:

PostgreSQL promptly uses all available memory for the query and
subsequently crashes.

I'll bet a nickel this is on a Linux machine with OOM kill enabled.
What does the postmaster log show --- or look in the kernel log to
see if it mentions anything about an out-of-memory kill.

freedb=# create table ucode as select distinct ucode from cdtitles group
by ucode having count(ucode)>1 ;
server closed the connection unexpectedly

What does EXPLAIN show as the plan for that? If it's a hash aggregate,
try with "enable_hashagg" turned off. How many distinct ucode values
are there in the table?

regards, tom lane

More info: the machine has 512M RAM and 512M swap
Work mem is set to:work_mem = 1024

This should't have crashed, should it?

"Mark Woodward" <pgsql@mohawksoft.com> writes:

-> HashAggregate (cost=106527.68..106528.68 rows=200 width=32)
Filter: (count(ucode) > 1)
-> Seq Scan on cdtitles (cost=0.00..96888.12 rows=1927912
width=32)

Well, shouldn't hash aggregate respect work memory limits?

If the planner thought there were 1.7M distinct values, it wouldn't have
used hash aggregate ... but it only thinks there are 200, which IIRC is
the default assumption. Have you ANALYZEd this table lately?

Meanwhile, I'd strongly recommend turning off OOM kill. That's got to
be the single worst design decision in the entire Linux kernel.

regards, tom lane

"Mark Woodward" <pgsql@mohawksoft.com> writes:

PostgreSQL promptly uses all available memory for the query and
subsequently crashes.

I'll bet a nickel this is on a Linux machine with OOM kill enabled.
What does the postmaster log show --- or look in the kernel log to
see if it mentions anything about an out-of-memory kill.

That's a no brainer. Maybe I should have rephrased the condition, crash
may be the wrong word, it was definitely killed by out of memory. Sorry.

freedb=# create table ucode as select distinct ucode from cdtitles group
by ucode having count(ucode)>1 ;
server closed the connection unexpectedly

What does EXPLAIN show as the plan for that? If it's a hash aggregate,
try with "enable_hashagg" turned off. How many distinct ucode values
are there in the table?

There are over 1.7M distinct rows, about 200K non-distinct that I want to
somehow remove.

It does have hash aggregate:
freedb=# explain select distinct ucode from cdtitles group by ucode having
count(ucode)>1 ;
QUERY PLAN
-------------------------------------------------------------------------------------
Unique (cost=106536.32..106537.32 rows=200 width=32)
-> Sort (cost=106536.32..106536.82 rows=200 width=32)
Sort Key: ucode
-> HashAggregate (cost=106527.68..106528.68 rows=200 width=32)
Filter: (count(ucode) > 1)
-> Seq Scan on cdtitles (cost=0.00..96888.12 rows=1927912
width=32)
(6 rows)

Well, shouldn't hash aggregate respect work memory limits?

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Still, I would say that is is extremly bad behavior for not having
stats, wouldn't you think?

Think of it as a kernel bug.

Meanwhile, I'd strongly recommend turning off OOM kill. That's got to
be the single worst design decision in the entire Linux kernel.

How is this any different than the FreeBSD having a default 512M process
size limit? On FreeBSD, the process would have been killed earlier.

No, the process would have been politely told it was out of memory, and
would have told you the same. If the kernel's way of notifying a
process that it's out of memory is SIGKILL, there is not a damn thing
that we can do to operate robustly.

regards, tom lane

"Mark Woodward" <pgsql@mohawksoft.com> writes:

-> HashAggregate (cost=106527.68..106528.68 rows=200
width=32)
Filter: (count(ucode) > 1)
-> Seq Scan on cdtitles (cost=0.00..96888.12
rows=1927912
width=32)

Well, shouldn't hash aggregate respect work memory limits?

If the planner thought there were 1.7M distinct values, it wouldn't have
used hash aggregate ... but it only thinks there are 200, which IIRC is
the default assumption. Have you ANALYZEd this table lately?

I thought that I had, but I did CLUSTER at some point. Or maybe I didn't
I'm, not sure. I have been working on a file reader/parser/importer
program. I created and dropped the DB so many times it is hard to keep
track. Still, I would say that is is extremly bad behavior for not having
stats, wouldn't you think?

Meanwhile, I'd strongly recommend turning off OOM kill. That's got to
be the single worst design decision in the entire Linux kernel.

How is this any different than the FreeBSD having a default 512M process
size limit? On FreeBSD, the process would have been killed earlier.

Tom Lane wrote:

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Still, I would say that is is extremly bad behavior for not having
stats, wouldn't you think?

Think of it as a kernel bug.

Meanwhile, I'd strongly recommend turning off OOM kill. That's got to
be the single worst design decision in the entire Linux kernel.

How is this any different than the FreeBSD having a default 512M process
size limit? On FreeBSD, the process would have been killed earlier.

No, the process would have been politely told it was out of memory, and
would have told you the same. If the kernel's way of notifying a
process that it's out of memory is SIGKILL, there is not a damn thing
that we can do to operate robustly.

And we have docco on it:
http://www.postgresql.org/docs/current/static/kernel-resources.html#AEN18105
which I assume is still current. Back in October I mentioned the OOM
killer to Andrew Morton - his reaction was a very pained look and a curt
admonition: "turn it off".

cheers

andrew

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Still, I would say that is is extremly bad behavior for not having
stats, wouldn't you think?

Think of it as a kernel bug.

While I respect your viewpoint that the Linux kernel should not kill an
offending process if the system runs out of memory, I sort of disagree in
that OOM is a disaster preventor. It should be viewed as a last ditch "him
or me" choice the kernel needs to make and it should not get into that
position in the first place.

Regardless, it is troubling that failing to have current stats can cause
the system, with a large data set, to exceed working memory limits.

I think it is still a bug. While it may manifest itself as a pg crash on
Linux because of a feature with which you have issue, the fact remains
that PG is exeeding its working memory limit.

Should failing to run ANALYZE cause this behavior?
If so, how does this get clearly documented?
If not, can it be prevented?

Meanwhile, I'd strongly recommend turning off OOM kill. That's got to
be the single worst design decision in the entire Linux kernel.

How is this any different than the FreeBSD having a default 512M process
size limit? On FreeBSD, the process would have been killed earlier.

No, the process would have been politely told it was out of memory, and
would have told you the same. If the kernel's way of notifying a
process that it's out of memory is SIGKILL, there is not a damn thing
that we can do to operate robustly.

Lets not waste time on a Linux discussion. Linux and FreeBSD have their
strengths, and a debate on the dubious merits of either is a long and
contentious debate. Both systems are fine, just with some subtle
differences in design goals.

"Mark Woodward" <pgsql@mohawksoft.com> writes:

I think it is still a bug. While it may manifest itself as a pg crash on
Linux because of a feature with which you have issue, the fact remains
that PG is exeeding its working memory limit.

The problem is that *we have no way to know what that limit is* ---
short of exceeding it and being summarily killed. (BTW, the kernel
doesn't know what the limit is either.) There is simply not any way
to operate robustly under the OOM-kill regime.

While I'll certainly acknowledge that it'd be nice if hashagg had
spill-to-disk capability, that wouldn't alter the fundamental fact that
if you want reliable behavior you MUST turn off OOM kill. There is not
anything we can do at the database level to work around that kernel-level
misdesign.

regards, tom lane

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Again, regardless of OS used, hashagg will exceed "working memory" as
defined in postgresql.conf.

So? If you've got OOM kill enabled, it can zap a process whether it's
strictly adhered to work_mem or not. The OOM killer is entirely capable
of choosing a victim process whose memory footprint hasn't changed
materially since it started (eg, the postmaster).

regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Again, regardless of OS used, hashagg will exceed "working memory" as
defined in postgresql.conf.

So? If you've got OOM kill enabled, it can zap a process whether it's
strictly adhered to work_mem or not. The OOM killer is entirely capable
of choosing a victim process whose memory footprint hasn't changed
materially since it started (eg, the postmaster).

Unless I've missed something here, disabling the OOM killer doesn't
really solve the problem here. What solves the problem is running
ANALYZE but it's still certainly the case that it would make some sense
for the Postmaster, upon realizing that it's gone well beyond its
work_mem boundary, to ideally switch plans to one which isn't going to
exceed its work_mem limit. Less ideally, it could give up and issue an
error to the user instead of running the box out of memory.

I appriciate that this is probably not very easy to implement but I
do believe the current situation could be improved in this regard.

Thanks,

Stephen

"Mark Woodward" <pgsql@mohawksoft.com> writes:

I think it is still a bug. While it may manifest itself as a pg crash on
Linux because of a feature with which you have issue, the fact remains
that PG is exeeding its working memory limit.

The problem is that *we have no way to know what that limit is* ---
short of exceeding it and being summarily killed. (BTW, the kernel
doesn't know what the limit is either.) There is simply not any way
to operate robustly under the OOM-kill regime.

No, you misunderstand what I said, the "working memory" as defined in
postgresql.conf. I don't care about the OS debate.

While I'll certainly acknowledge that it'd be nice if hashagg had
spill-to-disk capability, that wouldn't alter the fundamental fact that
if you want reliable behavior you MUST turn off OOM kill. There is not
anything we can do at the database level to work around that kernel-level
misdesign.

Again, regardless of OS used, hashagg will exceed "working memory" as
defined in postgresql.conf.

At issue is would a lack of ANALYZE justify this behavior? If so, it
should be documented.

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Again, regardless of OS used, hashagg will exceed "working memory" as
defined in postgresql.conf.

So? If you've got OOM kill enabled, it can zap a process whether it's
strictly adhered to work_mem or not. The OOM killer is entirely capable
of choosing a victim process whose memory footprint hasn't changed
materially since it started (eg, the postmaster).

Sorry, I must strongly disagree here. The postgresql.conf "working mem" is
a VERY IMPORTANT setting, it is intended to limit the consumption of
memory by the postgresql process. Often times PostgreSQL will work along
side other application servers on the same system, infact, may be a
sub-part of application servers on the same system. (This is, in fact, how
it is used on one of my site servers.)

Clearly, if the server will use 1000 times this number (Set for 1024K, but
exceeds 1G) this is broken, and it may cause other systems to fail or
perform very poorly.

If it is not something that can be fixed, it should be clearly documented.

On Thu, Feb 09, 2006 at 02:03:41PM -0500, Mark Woodward wrote:

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Again, regardless of OS used, hashagg will exceed "working memory" as
defined in postgresql.conf.

So? If you've got OOM kill enabled, it can zap a process whether it's
strictly adhered to work_mem or not. The OOM killer is entirely capable
of choosing a victim process whose memory footprint hasn't changed
materially since it started (eg, the postmaster).

Sorry, I must strongly disagree here. The postgresql.conf "working mem" is
a VERY IMPORTANT setting, it is intended to limit the consumption of
memory by the postgresql process. Often times PostgreSQL will work along

Actually, no, it's not designed for that at all.

side other application servers on the same system, infact, may be a
sub-part of application servers on the same system. (This is, in fact, how
it is used on one of my site servers.)

Clearly, if the server will use 1000 times this number (Set for 1024K, but
exceeds 1G) this is broken, and it may cause other systems to fail or
perform very poorly.

If it is not something that can be fixed, it should be clearly documented.

work_mem (integer)

Specifies the amount of memory to be used by internal sort
operations and hash tables before switching to temporary disk files.
The value is specified in kilobytes, and defaults to 1024 kilobytes
(1 MB). Note that for a complex query, several sort or hash
operations might be running in parallel; each one will be allowed to
use as much memory as this value specifies before it starts to put
data into temporary files. Also, several running sessions could be
doing such operations concurrently. So the total memory used could
be many times the value of work_mem; it is necessary to keep this
fact in mind when choosing the value. Sort operations are used for
ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash
joins, hash-based aggregation, and hash-based processing of IN
subqueries.

So it says right there that it's very easy to exceed work_mem by a very
large amount. Granted, this is a very painful problem to deal with and
will hopefully be changed at some point, but it's pretty clear as to how
this works.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Thu, Feb 09, 2006 at 11:42:57AM -0500, Mark Woodward wrote:

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Still, I would say that is is extremly bad behavior for not having
stats, wouldn't you think?

Think of it as a kernel bug.

While I respect your viewpoint that the Linux kernel should not kill an
offending process if the system runs out of memory, I sort of disagree in
that OOM is a disaster preventor. It should be viewed as a last ditch "him
or me" choice the kernel needs to make and it should not get into that
position in the first place.

I've had processes run away on a FreeBSD box before, to the extent of
running entirely out of swap and memory. Instead of random processes
just dying for no apparent reason, I instead started getting a bunch of
out-of-memory errors. No disaster, I just fixed the problem and life
went on.

Well, ok, the box did become rather unresponsive when my fix for the
problem meant that all the sudden there were about 950 perl processes
trying to run at the same time. I wish I'd captured top showing 900+
runnable processes. But after a few minutes the processes started
completing and exiting and everything was soon back to normal. I rather
doubt Linux would survive that...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

* Jim C. Nasby (jnasby@pervasive.com) wrote:

On Thu, Feb 09, 2006 at 02:03:41PM -0500, Mark Woodward wrote:

If it is not something that can be fixed, it should be clearly documented.

work_mem (integer)

Specifies the amount of memory to be used by internal sort
operations and hash tables before switching to temporary disk files.
The value is specified in kilobytes, and defaults to 1024 kilobytes
(1 MB). Note that for a complex query, several sort or hash
operations might be running in parallel; each one will be allowed to
use as much memory as this value specifies before it starts to put
data into temporary files. Also, several running sessions could be
doing such operations concurrently. So the total memory used could
be many times the value of work_mem; it is necessary to keep this
fact in mind when choosing the value. Sort operations are used for
ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash
joins, hash-based aggregation, and hash-based processing of IN
subqueries.

So it says right there that it's very easy to exceed work_mem by a very
large amount. Granted, this is a very painful problem to deal with and
will hopefully be changed at some point, but it's pretty clear as to how
this works.

It also says that when it goes over, it'll spill to disk. Additionally,
we're talking about one hash here, not multiple ones. It seems at least
misleading as, if I understand correctly, Postgres isn't actually
actively checking to see if the amount of memory used by an in-progress
hash creation has gone over the limit but rather it guesses at how much
memory will be used during the planning stage to decide if a hash plan
is possible or not. That guess can certainly be wrong but there's
nothing in place to handle the situation where the guess is wrong...

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

Unless I've missed something here, disabling the OOM killer doesn't
really solve the problem here.

Well in a way it does. Postgres would get an out-of-memory error from malloc
which it would handle properly and the world would be happy.

Except not quite, since I think an out of memory error still means that
backend exits instead of just that query failing. That means if you have an
application running such as apache then all subsequent transactions on that
connection fail too, instead of just the transaction that misbehaved.

And as the other poster mentioned, having Postgres use up every available byte
of memory isn't really very friendly to anything else running on the box.

It doesn't seem like a bad idea to have a max_memory parameter that if a
backend ever exceeded it would immediately abort the current transaction. That
would let an application continue operating normally after getting an error.

--
greg

Greg Stark <gsstark@mit.edu> writes:

Except not quite, since I think an out of memory error still means that
backend exits instead of just that query failing.

Not at all! PG will recover from this perfectly well ... if it's given
the opportunity, rather than being SIGKILLed.

It doesn't seem like a bad idea to have a max_memory parameter that if a
backend ever exceeded it would immediately abort the current
transaction.

See ulimit (or local equivalent).

regards, tom lane

On Thu, Feb 09, 2006 at 02:03:41PM -0500, Mark Woodward wrote:

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Again, regardless of OS used, hashagg will exceed "working memory" as
defined in postgresql.conf.

So? If you've got OOM kill enabled, it can zap a process whether it's
strictly adhered to work_mem or not. The OOM killer is entirely

capable

of choosing a victim process whose memory footprint hasn't changed
materially since it started (eg, the postmaster).

Sorry, I must strongly disagree here. The postgresql.conf "working mem"
is
a VERY IMPORTANT setting, it is intended to limit the consumption of
memory by the postgresql process. Often times PostgreSQL will work along

Actually, no, it's not designed for that at all.

I guess that's a matter of opinion.

side other application servers on the same system, infact, may be a
sub-part of application servers on the same system. (This is, in fact,
how
it is used on one of my site servers.)

Clearly, if the server will use 1000 times this number (Set for 1024K,
but
exceeds 1G) this is broken, and it may cause other systems to fail or
perform very poorly.

If it is not something that can be fixed, it should be clearly
documented.

work_mem (integer)

Specifies the amount of memory to be used by internal sort
operations and hash tables before switching to temporary disk files.
The value is specified in kilobytes, and defaults to 1024 kilobytes
(1 MB). Note that for a complex query, several sort or hash
operations might be running in parallel; each one will be allowed to
use as much memory as this value specifies before it starts to put
data into temporary files. Also, several running sessions could be
doing such operations concurrently. So the total memory used could
be many times the value of work_mem; it is necessary to keep this
fact in mind when choosing the value. Sort operations are used for
ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash
joins, hash-based aggregation, and hash-based processing of IN
subqueries.

So it says right there that it's very easy to exceed work_mem by a very
large amount. Granted, this is a very painful problem to deal with and
will hopefully be changed at some point, but it's pretty clear as to how
this works.

Well, if you read that paragraph carefully, I'll admit that I was a little
too literal in my statement apliying it to the "process" and not specific
functions within the process, but in the documentation:

"each one will be allowed to use as much memory as this value specifies
before it starts to put data into temporary files."

According to the documentation the behavior of hashagg is broken. It did
not use up to this amount and then start to use temporary files, it used
1000 times this limit and was killed by the OS.

I think it should be documented as the behavior is unpredictable.

Greg Stark wrote:

Well in a way it does. Postgres would get an out-of-memory error from malloc
which it would handle properly and the world would be happy.

Except not quite, since I think an out of memory error still means that
backend exits instead of just that query failing.

Not at all -- the transaction is aborted, but the backend can continue
working perfectly fine.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Greg Stark <gsstark@mit.edu> writes:

It doesn't seem like a bad idea to have a max_memory parameter that if a
backend ever exceeded it would immediately abort the current
transaction.

See ulimit (or local equivalent).

As much as setting ulimit in shell scripts is fun, I have to admit that
I really don't see it happening very much. Having Postgres set a ulimit
for itself may not be a bad idea and would perhaps provide a "least
suprise" for new users. Perhaps shared_buffers + 10*work_mem +
maintenance_work_mem + max_stack_depth? Then errors from running out of
memory could provide a 'HINT: Memory consumption went well over allowed
work_mem, perhaps you need to run ANALYZE or raise work_mem?'.

Just some thoughts,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Greg Stark <gsstark@mit.edu> writes:

It doesn't seem like a bad idea to have a max_memory parameter that if a
backend ever exceeded it would immediately abort the current
transaction.

See ulimit (or local equivalent).

As much as setting ulimit in shell scripts is fun, I have to admit that
I really don't see it happening very much.

For one thing it requires admin access to the startup scripts to arrange this.
And it's always cluster-wide.

Having a GUC parameter would mean it could be set per-session. Even if the GUC
parameter were just implemented by calling setrlimit it might be useful.

--
greg

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Greg Stark <gsstark@mit.edu> writes:

It doesn't seem like a bad idea to have a max_memory parameter that

if a

backend ever exceeded it would immediately abort the current
transaction.

See ulimit (or local equivalent).

As much as setting ulimit in shell scripts is fun, I have to admit that
I really don't see it happening very much.

For one thing it requires admin access to the startup scripts to arrange
this.
And it's always cluster-wide.

Having a GUC parameter would mean it could be set per-session. Even if the
GUC
parameter were just implemented by calling setrlimit it might be useful.

I don't think it needs a new GUC parameter, just having hashagg respect
work_mem would fix the problem.

On Thu, Feb 09, 2006 at 02:35:34PM -0500, Tom Lane wrote:

Greg Stark <gsstark@mit.edu> writes:

Except not quite, since I think an out of memory error still means that
backend exits instead of just that query failing.

Not at all! PG will recover from this perfectly well ... if it's given
the opportunity, rather than being SIGKILLed.

FWIW, the problem is mainly from the situation where some process
accesses a piece of memory that has been swapped out, but there is no
memory available to swap the page in. Or write to a page marked
copy-on-write. What do you do? There's is no way to return -ENOMEM from
a normal memory access and PostgreSQL wouldn't handle that anyway.

When people talk about disabling the OOM killer, it doesn't stop the
SIGKILL behaviour, it just causes the kernel to return -ENOMEM for
malloc() much much earlier... (ie when you still actually have memory
available).

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Feb 9, 2006, at 11:22 AM, Stephen Frost wrote:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Again, regardless of OS used, hashagg will exceed "working
memory" as
defined in postgresql.conf.

So? If you've got OOM kill enabled, it can zap a process whether
it's
strictly adhered to work_mem or not. The OOM killer is entirely
capable
of choosing a victim process whose memory footprint hasn't changed
materially since it started (eg, the postmaster).

Unless I've missed something here, disabling the OOM killer doesn't
really solve the problem here. What solves the problem is running
ANALYZE but it's still certainly the case that it would make some
sense
for the Postmaster, upon realizing that it's gone well beyond its
work_mem boundary, to ideally switch plans to one which isn't going to
exceed its work_mem limit. Less ideally, it could give up and
issue an
error to the user instead of running the box out of memory.

So is the work_mem paramater that is set not strictly enforced? Is
it more like a suggestions as to what it SHOULD use and not a hard
limit on how much memory the each process is ALLOWED to use?

If his work_mem is set to 1 mb and that process is using 500 mb for
tasks that are supposed to stay in work_mem then doesn't that mean
that that limit is not really a hard limit but rather a suggestion?

Rick

Martijn van Oosterhout <kleptog@svana.org> writes:

When people talk about disabling the OOM killer, it doesn't stop the
SIGKILL behaviour,

Yes it does, because the situation will never arise.

it just causes the kernel to return -ENOMEM for
malloc() much much earlier... (ie when you still actually have memory
available).

Given the current price of disk, there is no sane reason not to have
enough swap space configured to make this not-a-problem. The OOM kill
mechanism was a reasonable solution for running systems that were not
expected to be too reliable anyway on small hardware, but if you're
trying to run a 24/7 server you're simply incompetent if you don't
disable it.

regards, tom lane

Martijn van Oosterhout <kleptog@svana.org> writes:

When people talk about disabling the OOM killer, it doesn't stop the
SIGKILL behaviour,

Yes it does, because the situation will never arise.

it just causes the kernel to return -ENOMEM for
malloc() much much earlier... (ie when you still actually have memory
available).

Given the current price of disk, there is no sane reason not to have
enough swap space configured to make this not-a-problem. The OOM kill
mechanism was a reasonable solution for running systems that were not
expected to be too reliable anyway on small hardware, but if you're
trying to run a 24/7 server you're simply incompetent if you don't
disable it.

And people say I have STRONG opinions. Don't hold back Tom, let us know
what you really think.

On Friday 10 February 2006 00:53, Mark Woodward wrote:

Martijn van Oosterhout <kleptog@svana.org> writes:

When people talk about disabling the OOM killer, it doesn't stop the
SIGKILL behaviour,

Yes it does, because the situation will never arise.

it just causes the kernel to return -ENOMEM for
malloc() much much earlier... (ie when you still actually have memory
available).

Given the current price of disk, there is no sane reason not to have
enough swap space configured to make this not-a-problem. The OOM kill
mechanism was a reasonable solution for running systems that were not
expected to be too reliable anyway on small hardware, but if you're
trying to run a 24/7 server you're simply incompetent if you don't
disable it.

And people say I have STRONG opinions. Don't hold back Tom, let us know
what you really think.

Read
http://linux-mm.org/OverCommitAccounting
or
file://usr/src/linux/Documentation/vm/overcommit-accounting

It is a good idea to have enough swap space. If not, set
vm.overcommit_memory=2

On Feb 9, 2006, at 12:49 PM, Mark Woodward wrote:

On Thu, Feb 09, 2006 at 02:03:41PM -0500, Mark Woodward wrote:

"Mark Woodward" <pgsql@mohawksoft.com> writes:

Again, regardless of OS used, hashagg will exceed "working
memory" as
defined in postgresql.conf.

So? If you've got OOM kill enabled, it can zap a process
whether it's
strictly adhered to work_mem or not. The OOM killer is entirely

capable

of choosing a victim process whose memory footprint hasn't changed
materially since it started (eg, the postmaster).

Sorry, I must strongly disagree here. The postgresql.conf
"working mem"
is
a VERY IMPORTANT setting, it is intended to limit the consumption of
memory by the postgresql process. Often times PostgreSQL will
work along

Actually, no, it's not designed for that at all.

I guess that's a matter of opinion.

side other application servers on the same system, infact, may be a
sub-part of application servers on the same system. (This is, in
fact,
how
it is used on one of my site servers.)

Clearly, if the server will use 1000 times this number (Set for
1024K,
but
exceeds 1G) this is broken, and it may cause other systems to
fail or
perform very poorly.

If it is not something that can be fixed, it should be clearly
documented.

work_mem (integer)

Specifies the amount of memory to be used by internal sort
operations and hash tables before switching to temporary disk
files.
The value is specified in kilobytes, and defaults to 1024
kilobytes
(1 MB). Note that for a complex query, several sort or hash
operations might be running in parallel; each one will be
allowed to
use as much memory as this value specifies before it starts to
put
data into temporary files. Also, several running sessions
could be
doing such operations concurrently. So the total memory used
could
be many times the value of work_mem; it is necessary to keep this
fact in mind when choosing the value. Sort operations are used
for
ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash
joins, hash-based aggregation, and hash-based processing of IN
subqueries.

So it says right there that it's very easy to exceed work_mem by a
very
large amount. Granted, this is a very painful problem to deal with
and
will hopefully be changed at some point, but it's pretty clear as
to how
this works.

Well, if you read that paragraph carefully, I'll admit that I was a
little
too literal in my statement apliying it to the "process" and not
specific
functions within the process, but in the documentation:

"each one will be allowed to use as much memory as this value
specifies
before it starts to put data into temporary files."

According to the documentation the behavior of hashagg is broken.
It did
not use up to this amount and then start to use temporary files, it
used
1000 times this limit and was killed by the OS.

I think it should be documented as the behavior is unpredictable.

It seems to me that the solution for THIS INCIDENT is to run an
analyze. That should fix the problem at hand. I have nothing to say
about the OOM issue except that hopefully the analyze will prevent
him from running out of memory at all.

However if hashagg truly does not obey the limit that is supposed to
be imposed by work_mem then it really ought to be documented. Is
there a misunderstanding here and it really does obey it? Or is
hashagg an exception but the other work_mem associated operations
work fine? Or is it possible for them all to go out of bounds?

Even if you've got 100 terabyts of swap space though if seems like if
your system is very heavy on reads then you would really want that
single backend to start using up your disk space and leave your
memory alone so that most of your data can stay cached and largely
unaffeted by the problem of one backend.

If your bottleneck is writing to the disk then it doesn't really seem
to matter. You just need to make sure that huge out of control
hashagg never occurs. If your disks get saturated with writes
because of the hashagg of one backend then all other processes that
need to write a lot of info to disk are going to come to a grinding
halt and queries are not going to complete quickly and build up and
you will have a huge mess on your hands that will essentially prevent
postgres from being able to do it's job even if it doesn't actually
die. In this situation disk bandwidth is a scarce commodity and
whether you let the OS handle it all with virtual memory or you let
postgres swap everything out to disc for that one operation you are
still using disc to make up for a lack of RAM. At some point you
you've either got to stock up on enough RAM to run your queries
properly or alter how your queries run to use less RAM. Having a
process go out of control in resource usage is going to cause big
problems one way or another.

Rick Gigger <rick@alpinenetworking.com> writes:

However if hashagg truly does not obey the limit that is supposed to
be imposed by work_mem then it really ought to be documented. Is
there a misunderstanding here and it really does obey it? Or is
hashagg an exception but the other work_mem associated operations
work fine? Or is it possible for them all to go out of bounds?

hashagg is the exception. It should be fixed, not documented, but no
one's got round to that.

One point to consider is that if the planner's estimate is as far off
as exhibited in the OP's example, a hashagg that does spill to disk
is likely to take so long that he'll be back here complaining that
the query never terminates ;-). In most practical situations, I think
exceeding work_mem is really the best solution, as long as it's not
by more than 10x or 100x. It's when the estimate is off by many
orders of magnitude that you've got a problem. Running out of memory
is not necessarily the worst response ... as long as the system doesn't
kill the process in response to that.

regards, tom lane

Rick Gigger <rick@alpinenetworking.com> writes:

However if hashagg truly does not obey the limit that is supposed to
be imposed by work_mem then it really ought to be documented. Is
there a misunderstanding here and it really does obey it? Or is
hashagg an exception but the other work_mem associated operations
work fine? Or is it possible for them all to go out of bounds?

hashagg is the exception. It should be fixed, not documented, but no
one's got round to that.

Well, it is clearly a pathalogical condition. Fixed? Sure, but someone
should document it so that others don't stumble across it.

One point to consider is that if the planner's estimate is as far off
as exhibited in the OP's example, a hashagg that does spill to disk
is likely to take so long that he'll be back here complaining that
the query never terminates ;-).

That's not fair, now is it? This isn't about the OP (me), it is about
PostgreSQL behaving badly.

In most practical situations, I think
exceeding work_mem is really the best solution, as long as it's not
by more than 10x or 100x. It's when the estimate is off by many
orders of magnitude that you've got a problem. Running out of memory
is not necessarily the worst response ... as long as the system doesn't
kill the process in response to that.

I don't agree with you here. Many PostgreSQL installations use PostgreSQL
as part of a larger whole. Adjusting "work_mem" should give the admin some
control over the memory footprint of the system. It is documented as the
limit a specific function path will use before spilling to disk.

I set up a lot of systems and I write a lot of software that uses
PostgreSQL. Periodically I run across features/problems/limitations of
PostgreSQL and post them.

This was/is an example of where the behavior of PostgreSQL is clearly
unacceptable. OK, yes, this problem goes away with an ANALYZE, but it
isn't clear how anyone could have known this, and unexpected behavior is
bad in any product.

In your statement, "he'll be back here complaining that the query never
terminates," that's not true. A long query typically gets examined with
explain (or in Oracle, explain plan) and evaluated from there. When the
process exhibits runaway memory use, that's a problem.

On Thu, Feb 09, 2006 at 03:13:22PM -0500, Greg Stark wrote:

Stephen Frost <sfrost@snowman.net> writes:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Greg Stark <gsstark@mit.edu> writes:

It doesn't seem like a bad idea to have a max_memory parameter that if a
backend ever exceeded it would immediately abort the current
transaction.

See ulimit (or local equivalent).

As much as setting ulimit in shell scripts is fun, I have to admit that
I really don't see it happening very much.

For one thing it requires admin access to the startup scripts to arrange this.
And it's always cluster-wide.

Having a GUC parameter would mean it could be set per-session. Even if the GUC
parameter were just implemented by calling setrlimit it might be useful.

Trying to tune work_mem is extremely difficult in PostgreSQL, because
you are constantly running the risk of sending the server into a
swap-storm. Having a set-able per-backend memory limit would allow a lot
more flexability in setting work_mem, because you could now ensure that
you wouldn't push the server into serious swapping.

Even better would be a means to set a cluster-wide memory limit, but of
course that's substantially more work.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Thu, Feb 09, 2006 at 05:04:38PM -0500, Tom Lane wrote:

Martijn van Oosterhout <kleptog@svana.org> writes:

When people talk about disabling the OOM killer, it doesn't stop the
SIGKILL behaviour,

Yes it does, because the situation will never arise.

it just causes the kernel to return -ENOMEM for
malloc() much much earlier... (ie when you still actually have memory
available).

Given the current price of disk, there is no sane reason not to have
enough swap space configured to make this not-a-problem. The OOM kill
mechanism was a reasonable solution for running systems that were not
expected to be too reliable anyway on small hardware, but if you're
trying to run a 24/7 server you're simply incompetent if you don't
disable it.

BTW, I was shocked when I found out that FreeBSD actually has an OOM
killer itself. Yet I've never heard of anyone having problems with it.
Granted, the FreeBSD OOM could be better designed to pick the right
process to kill, but I'd bet that the real reason you never hear about
it is because FreeBSD admins are clued enough to a) setup a reasonable
amount of swap and b) do a better job of monitoring memory usage so that
you don't start swapping in the first place.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Fri, Feb 10, 2006 at 09:57:12AM -0500, Mark Woodward wrote:

In most practical situations, I think
exceeding work_mem is really the best solution, as long as it's not
by more than 10x or 100x. It's when the estimate is off by many
orders of magnitude that you've got a problem. Running out of memory
is not necessarily the worst response ... as long as the system doesn't
kill the process in response to that.

I don't agree with you here. Many PostgreSQL installations use PostgreSQL
as part of a larger whole. Adjusting "work_mem" should give the admin some
control over the memory footprint of the system. It is documented as the
limit a specific function path will use before spilling to disk.

And even when PostgreSQL has the server all to itself, having a hashagg
spill to disk is *way* better than pushing the machine into a swap
storm. At least if you spill the hashagg you only have one backend
running at a snail's pace; a swap storm means next to nothing gets done.

This was/is an example of where the behavior of PostgreSQL is clearly
unacceptable. OK, yes, this problem goes away with an ANALYZE, but it
isn't clear how anyone could have known this, and unexpected behavior is
bad in any product.

Care to submit a documentation patch before releases are bundled (I
think on Sunday?) At least then people would be aware that work_mem is
just a suggestion to hash_aggs. I'd do a patch myself but I doubt I'll
have time before the release. :(
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Fri, Feb 10, 2006 at 10:01:18AM -0600, Jim C. Nasby wrote:

BTW, I was shocked when I found out that FreeBSD actually has an OOM
killer itself. Yet I've never heard of anyone having problems with it.
Granted, the FreeBSD OOM could be better designed to pick the right
process to kill, but I'd bet that the real reason you never hear about
it is because FreeBSD admins are clued enough to a) setup a reasonable
amount of swap and b) do a better job of monitoring memory usage so that
you don't start swapping in the first place.

Hmm, I do wonder what FreeBSDs overcommit policy is. For example on my
computer right now the total allocated VM is approximately 3 times the
actual memory in the computer and about twice if you include swap. By a
strict policy of overcommit my computer wouldn't complete the boot
sequence, whereas as currently it runs without using any swap.

Disabling overcommit has a serious cost in that most of your VM will
never be used. Are people really suggesting that I can't run a few
daemons, X and a web-browser on FreeBSD without allocating 3 times my
physical memory in swap?

However, my real question is: while trying to find info about FreeBSDs
overcommit policy, I just get lot of people complaining about freebsd
killing random processes. Does anyone know a site that describes how it
works? I understand Linux's overcommit policy just fine.

Disclaimer: The Linux OOM killer has never killed the wrong process for
me, so I don't have any bad experiences with overcommit.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Fri, Feb 10, 2006 at 09:57:12AM -0500, Mark Woodward wrote:

In most practical situations, I think
exceeding work_mem is really the best solution, as long as it's not
by more than 10x or 100x. It's when the estimate is off by many
orders of magnitude that you've got a problem. Running out of memory
is not necessarily the worst response ... as long as the system

doesn't

kill the process in response to that.

I don't agree with you here. Many PostgreSQL installations use
PostgreSQL
as part of a larger whole. Adjusting "work_mem" should give the admin
some
control over the memory footprint of the system. It is documented as the
limit a specific function path will use before spilling to disk.

And even when PostgreSQL has the server all to itself, having a hashagg
spill to disk is *way* better than pushing the machine into a swap
storm. At least if you spill the hashagg you only have one backend
running at a snail's pace; a swap storm means next to nothing gets done.

This was/is an example of where the behavior of PostgreSQL is clearly
unacceptable. OK, yes, this problem goes away with an ANALYZE, but it
isn't clear how anyone could have known this, and unexpected behavior is
bad in any product.

Care to submit a documentation patch before releases are bundled (I
think on Sunday?) At least then people would be aware that work_mem is
just a suggestion to hash_aggs. I'd do a patch myself but I doubt I'll
have time before the release. :(

I would be glad too. What's the process?

Martijn van Oosterhout <kleptog@svana.org> writes:

Disclaimer: The Linux OOM killer has never killed the wrong process for
me, so I don't have any bad experiences with overcommit.

You haven't tried real hard. What I've seen recently when I do something
that makes a PG backend go overboard is that the kernel zaps both the
misbehaving backend and the bgwriter process. No idea what it's got
against the bgwriter, but the behavior's been pretty consistent under
recent Fedora 4 kernels ...

(This is on a development machine, not a server, so I'm not particularly
worried by leaving the default overcommit policy in place. I wouldn't
do that on a server --- but it's not worth my time to change it on a
devel machine.)

regards, tom lane

Martijn van Oosterhout <kleptog@svana.org> writes:

Disabling overcommit has a serious cost in that most of your VM will
never be used. Are people really suggesting that I can't run a few
daemons, X and a web-browser on FreeBSD without allocating 3 times my
physical memory in swap?

There's a possibility you're misreading your process info there. The X server
maps large areas of graphics memory in its address space which shows up as
virtual memory used in ps on some OSes. I'm not sure if BSD is included there.
That's not memory that can ever be swapped out and it doesn't take up any
memory from the non-video memory.

That said, it's true that some applications allocate much more memory than
needed. There's a bit of a feedback loop here. Because application writers
know that OSes overcommit they don't worry about avoiding unnecessary
allocations. Sun's original Java memory management system just started off
with allocating 20MB before it ran a single bytecode for example.

That's why merely allocating tons of swap doesn't necessarily protect you.
It's still possible for a process (or several processes if you allocate more
swap than you have address space) to mmap gigabytes of memory without touching
it and then start touching those pages. Hopefully the OOM killer targets the
offender but there's no real way for it to guarantee it.

--
greg

Greg Stark <gsstark@mit.edu> writes:

That's why merely allocating tons of swap doesn't necessarily protect you.
It's still possible for a process (or several processes if you allocate more
swap than you have address space) to mmap gigabytes of memory without touching
it and then start touching those pages.

So? If the swap exists to back that memory, there's no problem. It
might be slow, but it will not fail.

regards, tom lane

On Fri, Feb 10, 2006 at 12:16:04PM -0500, Mark Woodward wrote:

And even when PostgreSQL has the server all to itself, having a hashagg
spill to disk is *way* better than pushing the machine into a swap
storm. At least if you spill the hashagg you only have one backend
running at a snail's pace; a swap storm means next to nothing gets done.

This was/is an example of where the behavior of PostgreSQL is clearly
unacceptable. OK, yes, this problem goes away with an ANALYZE, but it
isn't clear how anyone could have known this, and unexpected behavior is
bad in any product.

Care to submit a documentation patch before releases are bundled (I
think on Sunday?) At least then people would be aware that work_mem is
just a suggestion to hash_aggs. I'd do a patch myself but I doubt I'll
have time before the release. :(

I would be glad too. What's the process?

Well, find the appropriate file in doc/src/sgml, make a copy, edit the
file, generate a diff with diff -u, and email that diff/patch to
pgsql-patches.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Tom Lane <tgl@sss.pgh.pa.us> writes:

Greg Stark <gsstark@mit.edu> writes:

That's why merely allocating tons of swap doesn't necessarily protect you.
It's still possible for a process (or several processes if you allocate more
swap than you have address space) to mmap gigabytes of memory without touching
it and then start touching those pages.

So? If the swap exists to back that memory, there's no problem. It
might be slow, but it will not fail.

Sure, but there's no way to know how much swap you need. No matter how much
swap you allocate these processes can allocate more pages of untouched RAM and
then blow up.

Of course realistically allocating 4G of swap is enough to deal with something
like Postgres where you're not being maliciously attacked. One process on ia32
can't accidentally allocate more than 4G of ram.

I was just trying to clarify the situation since someone made some comment
about it having to do with memory being swapped out and then finding nowhere
to swap in when needed. That's not exactly what's happening.

--
greg

Greg Stark wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Greg Stark <gsstark@mit.edu> writes:

That's why merely allocating tons of swap doesn't necessarily protect you.
It's still possible for a process (or several processes if you allocate more
swap than you have address space) to mmap gigabytes of memory without touching
it and then start touching those pages.

So? If the swap exists to back that memory, there's no problem. It
might be slow, but it will not fail.

Sure, but there's no way to know how much swap you need. No matter how much
swap you allocate these processes can allocate more pages of untouched RAM and
then blow up.

Of course realistically allocating 4G of swap is enough to deal with something
like Postgres where you're not being maliciously attacked. One process on ia32
can't accidentally allocate more than 4G of ram.

I was just trying to clarify the situation since someone made some comment
about it having to do with memory being swapped out and then finding nowhere
to swap in when needed. That's not exactly what's happening.

I guess the fundamental issue is whether Linux requires all mmap()'ed
file contents to be in memory, or whether it pushes data to disk and
unmaps it as it runs low on memory. I don't know the answer to that.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Greg Stark <gsstark@mit.edu> writes:

I was just trying to clarify the situation since someone made some comment
about it having to do with memory being swapped out and then finding nowhere
to swap in when needed. That's not exactly what's happening.

No. I believe the case that is actually hard for the kernel to predict
comes from copy-on-write: when a process forks, you could potentially
need twice its current memory image, but in reality you probably won't
ever need that much since many of the shared pages won't ever be written
by either process. However, a non-overcommitting kernel must assume
that worst case, and hence fail the fork() if it doesn't have enough
swap space to cover both processes. If it does not, then the crunch
comes when one process does touch a shared page. If there is no
available swap space at that time, kill -9 is the only recourse, because
there is no way in the Unix API to fail a write to valid memory.

The reason for having a lot more swap space than you really need is just
to cover the potential demand from copy-on-write of pages that are
currently shared. But given the price of disk these days, it's pretty
cheap insurance.

regards, tom lane