SEO

Any time this happens it's generally a nasty surprise for users. It
would be nice to throw them an explicit warning that it's occurring.

decibel=# vacuum i;
WARNING: exceeded maintenance_work_mem while vacuuming relation
"public.i"
HINT: Consider increasing maintenance_work_mem
VACUUM
decibel=#

Attached passes regression tests...
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jim Nasby <decibel@decibel.org> writes:
> Any time this happens it's generally a nasty surprise for users.

Really? Running out of work memory is expected on large tables.

> It would be nice to throw them an explicit warning that it's occurring.

I think this is a bad idea. It's furthermore pretty useless in the
autovacuum world, since no one is likely to see the warning.

regards, tom lane

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

On May 9, 2007, at 1:27 PM, Tom Lane wrote:
> Jim Nasby <decibel@decibel.org> writes:
>> Any time this happens it's generally a nasty surprise for users.
>
> Really? Running out of work memory is expected on large tables.

I guess I shouldn't complain since I've made some good money fixing
this in the past. And someone has been posting on -performance this
week with what looks like the same issue.

>> It would be nice to throw them an explicit warning that it's
>> occurring.
>
> I think this is a bad idea. It's furthermore pretty useless in the
> autovacuum world, since no one is likely to see the warning.

Well, it would at least make it into the log file...
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 5/9/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jim Nasby <decibel@decibel.org> writes:
> > Any time this happens it's generally a nasty surprise for users.
>
> Really? Running out of work memory is expected on large tables.

Sure. Perhaps we should find a better error message but it's an
interesting information. Personnaly, I try to choose a sane value
depending on my database but I'm never sure it's really sufficient or
if I added 100MB it would have made a real difference.

> > It would be nice to throw them an explicit warning that it's occurring.
>
> I think this is a bad idea. It's furthermore pretty useless in the
> autovacuum world, since no one is likely to see the warning.

IMHO we're far from having everyone using autovacuum. For instance,
for most of our customers, we prefer having a window for vacuuming
(from 3am for example) instead of having autovacuum fired in the
middle of the day during a load peak.
If we can shorten the window by having a sufficient value for
maintenance_work_mem, it's even nicer and Jim's patch could help us
with this point.

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Wednesday 09 May 2007 19:41, Guillaume Smet wrote:
> On 5/9/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Jim Nasby <decibel@decibel.org> writes:
> > > Any time this happens it's generally a nasty surprise for users.
> >
> > Really? Running out of work memory is expected on large tables.
>
> Sure. Perhaps we should find a better error message but it's an
> interesting information. Personnaly, I try to choose a sane value
> depending on my database but I'm never sure it's really sufficient or
> if I added 100MB it would have made a real difference.
>

If we were going to implement this (and I'm a tad skeptical as well), wouldn't
it be better if the warning occured at the end of vacuum, and told you how
much memory was actually needed, so you'd know what maintainence_work_mem
should be.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

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

On Fri, May 11, 2007 at 10:18:30PM -0400, Robert Treat wrote:
> On Wednesday 09 May 2007 19:41, Guillaume Smet wrote:
> > On 5/9/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > Jim Nasby <decibel@decibel.org> writes:
> > > > Any time this happens it's generally a nasty surprise for users.
> > >
> > > Really? Running out of work memory is expected on large tables.
> >
> > Sure. Perhaps we should find a better error message but it's an
> > interesting information. Personnaly, I try to choose a sane value
> > depending on my database but I'm never sure it's really sufficient or
> > if I added 100MB it would have made a real difference.

Unfortunately, a lot of users aren't as knowledgeable as folks here,
that's why I made it a warning and gave it a hint. But if people think
that's too high a level we can change it to something lower...

> If we were going to implement this (and I'm a tad skeptical as well), wouldn't
> it be better if the warning occured at the end of vacuum, and told you how
> much memory was actually needed, so you'd know what maintainence_work_mem
> should be.

Maybe... the problem is that for really large tables you simply won't
have a choice; it will have to fall to disk. So I think we'd have to
keep per-table warnings, unless you've got an idea for how we could
account for tables that wouldn't reasonably fit?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Or we could switch to a more compact representation of the dead tuples,
and not need such a big maintenance_work_mem in the first place.

Jim C. Nasby wrote:
> On Fri, May 11, 2007 at 10:18:30PM -0400, Robert Treat wrote:
>> On Wednesday 09 May 2007 19:41, Guillaume Smet wrote:
>>> On 5/9/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> Jim Nasby <decibel@decibel.org> writes:
>>>>> Any time this happens it's generally a nasty surprise for users.
>>>> Really? Running out of work memory is expected on large tables.
>>> Sure. Perhaps we should find a better error message but it's an
>>> interesting information. Personnaly, I try to choose a sane value
>>> depending on my database but I'm never sure it's really sufficient or
>>> if I added 100MB it would have made a real difference.
>
> Unfortunately, a lot of users aren't as knowledgeable as folks here,
> that's why I made it a warning and gave it a hint. But if people think
> that's too high a level we can change it to something lower...
>
>> If we were going to implement this (and I'm a tad skeptical as well), wouldn't
>> it be better if the warning occured at the end of vacuum, and told you how
>> much memory was actually needed, so you'd know what maintainence_work_mem
>> should be.
>
> Maybe... the problem is that for really large tables you simply won't
> have a choice; it will have to fall to disk. So I think we'd have to
> keep per-table warnings, unless you've got an idea for how we could
> account for tables that wouldn't reasonably fit?


--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Heikki Linnakangas <heikki@enterprisedb.com> writes:
> Or we could switch to a more compact representation of the dead tuples,
> and not need such a big maintenance_work_mem in the first place.

Hm, you got any ideas? One constraint is that it doesn't seem
acceptable to make the search function any slower.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Sat, May 12, 2007 at 07:57:44PM +0100, Heikki Linnakangas wrote:
> Or we could switch to a more compact representation of the dead tuples,
> and not need such a big maintenance_work_mem in the first place.

Sure, but even with a more compact representation you can still run out
of maintenance_work_mem... unless we allow this to spill to disk. At
first guess that sounds insane, but if you've got a large enough set of
indexes it *might* actually be faster.

Either way, as long as maintenance_work_mem is an issue I think we need
a way to warn users.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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

Tom Lane wrote:
> Heikki Linnakangas <heikki@enterprisedb.com> writes:
>> Or we could switch to a more compact representation of the dead tuples,
>> and not need such a big maintenance_work_mem in the first place.
>
> Hm, you got any ideas? One constraint is that it doesn't seem
> acceptable to make the search function any slower.

That's the biggest problem.

One idea is to use a compressed bitmap like in the bitmap index patch,
and a tree of block numbers or ranges to allow random access to it.

Another idea is to use the current array representation, but instead of
storing a item pointer on every slot, you store either a normal item
pointer, or three extra offsets on the previous block. To make the
binary search work, set the high bit (which isn't used otherwise) of the
extra offsets to tell them apart from normal item pointers. When the
binary search lands on those extra offsets, scan backwards to the
closest normal item to get the block number.

Performance is a bit hard to predict. A more compact representation
might be more cache-efficient, which might offset the cost of a more
complex search function.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

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