Re: [HACKERS] Autovacuum and OldestXmin

[Alvaro Herrera]

Alvaro Herrera wrote:
> Simon Riggs wrote:

> > [Also there is a comment saying "this is a bug" in autovacuum.c
> > Are we thinking to go production with that phrase in the code?]
> 
> Yeah, well, it's only a comment ;-)  The problem is that a worker can
> decide that a table needs to be vacuumed, if another worker has finished
> vacuuming it in the last 500 ms.  I proposed a mechanism to close the
> hole but it was too much of a hassle.

I just committed a patch that should fix this problem.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autovacuum and OldestXmin

[Bruce Momjian]

Added to TODO:

o Prevent autovacuum from running if an old transaction is still
  running from the last vacuum

  http://archives.postgresql.org/pgsql-hackers/2007-11/msg00899.php


---

Christopher Browne wrote:
> The world rejoiced as [EMAIL PROTECTED] (Alvaro Herrera) wrote:
> > Simon Riggs wrote:
> >> I notice that slony records the oldestxmin that was running when it last
> >> ran a VACUUM on its tables. This allows slony to avoid running a VACUUM
> >> when it would be clearly pointless to do so.
> >> 
> >> AFAICS autovacuum does not do this, or did I miss that?
> >
> > Hmm, I think it's just because nobody suggested it and I didn't came up
> > with the idea.
> >
> > Whether it's a useful thing to do is a different matter.  Why store it
> > per table and not more widely?  Perhaps per database would be just as
> > useful; and maybe it would allow us to skip running autovac workers
> > when there is no point in doing so.
> 
> I think I need to take blame for that feature in Slony-I ;-).
> 
> I imagine it might be useful to add it to autovac, too.  I thought it
> was pretty neat that this could be successfully handled by comparison
> with a single value (e.g. - eldest xmin), and I expect that using a
> single quasi-global value should be good enough for autovac.
> 
> If there is some elderly, long-running transaction that isn't a
> VACUUM, that will indeed inhibit VACUUM from doing any good, globally,
> across the cluster, until such time as that transaction ends.
> 
> To, at that point, "inhibit" autovac from bothering to run VACUUM,
> would seem like a good move.  There is still value to running ANALYZE
> on tables, so it doesn't warrant stopping autovac altogether, but this
> scenario suggests a case for suppressing futile vacuuming, at least...
> -- 
> If this was helpful,  rate me
> http://linuxfinances.info/info/slony.html
> It's hard to tell if someone is inconspicuous. 
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Autovacuum and OldestXmin

[Alvaro Herrera]

Simon Riggs wrote:
> On Thu, 2007-11-22 at 21:59 -0500, Christopher Browne wrote:

> > I imagine it might be useful to add it to autovac, too.  I thought it
> > was pretty neat that this could be successfully handled by comparison
> > with a single value (e.g. - eldest xmin), and I expect that using a
> > single quasi-global value should be good enough for autovac.
> 
> I've just looked at that to see if it is that easy; I don't think it is.
> 
> That works for slony currently because we vacuum all of the slony tables
> at once. Autovacuum does individual tables so we'd need to store the
> individual values otherwise we might skip doing a VACUUM when it could
> have done some useful work.

Yeah, that was my conclusion too.

-- 
Alvaro Herrera  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos / con todos los humanos acabaré (Bender)

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

Re: [HACKERS] Autovacuum and OldestXmin

[Simon Riggs]

On Thu, 2007-11-22 at 21:59 -0500, Christopher Browne wrote:
> The world rejoiced as [EMAIL PROTECTED] (Alvaro Herrera) wrote:
> > Simon Riggs wrote:
> >> I notice that slony records the oldestxmin that was running when it last
> >> ran a VACUUM on its tables. This allows slony to avoid running a VACUUM
> >> when it would be clearly pointless to do so.
> >> 
> >> AFAICS autovacuum does not do this, or did I miss that?
> >
> > Hmm, I think it's just because nobody suggested it and I didn't came up
> > with the idea.
> >
> > Whether it's a useful thing to do is a different matter.  Why store it
> > per table and not more widely?  Perhaps per database would be just as
> > useful; and maybe it would allow us to skip running autovac workers
> > when there is no point in doing so.
> 
> I think I need to take blame for that feature in Slony-I ;-).

Good thinking.

> I imagine it might be useful to add it to autovac, too.  I thought it
> was pretty neat that this could be successfully handled by comparison
> with a single value (e.g. - eldest xmin), and I expect that using a
> single quasi-global value should be good enough for autovac.

I've just looked at that to see if it is that easy; I don't think it is.

That works for slony currently because we vacuum all of the slony tables
at once. Autovacuum does individual tables so we'd need to store the
individual values otherwise we might skip doing a VACUUM when it could
have done some useful work.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.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

Re: [HACKERS] Autovacuum and OldestXmin

[Simon Riggs]

On Fri, 2007-11-23 at 01:14 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > That's a killer reason, I suppose. I was really trying to uncover what
> > the thinking was, so we can document it. Having VACUUM ignore it
> > completely seems wrong.
> 
> What you seem to be forgetting is that VACUUM is charged with cleaning
> out LP_DEAD tuples, which HOT cannot do.  And the page header fields are
> set (quite properly so) with HOT's interests in mind not VACUUM's.

OK, thanks.

Me getting confused about HOT might cause a few chuckles and it does
with me also. You didn't sit through the months of detailed discussions
of all the many possible ways of doing it (granted all were flawed in
some respect), so I figure I will need to forget those before I
understand the one exact way of doing it that has been committed.
Anyway, thanks for keeping me on track and (again) kudos to Pavan and
team.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


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

Re: [HACKERS] Autovacuum and OldestXmin

[Tom Lane]

Simon Riggs <[EMAIL PROTECTED]> writes:
> That's a killer reason, I suppose. I was really trying to uncover what
> the thinking was, so we can document it. Having VACUUM ignore it
> completely seems wrong.

What you seem to be forgetting is that VACUUM is charged with cleaning
out LP_DEAD tuples, which HOT cannot do.  And the page header fields are
set (quite properly so) with HOT's interests in mind not VACUUM's.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] Autovacuum and OldestXmin

[Christopher Browne]

The world rejoiced as [EMAIL PROTECTED] (Alvaro Herrera) wrote:
> Simon Riggs wrote:
>> I notice that slony records the oldestxmin that was running when it last
>> ran a VACUUM on its tables. This allows slony to avoid running a VACUUM
>> when it would be clearly pointless to do so.
>> 
>> AFAICS autovacuum does not do this, or did I miss that?
>
> Hmm, I think it's just because nobody suggested it and I didn't came up
> with the idea.
>
> Whether it's a useful thing to do is a different matter.  Why store it
> per table and not more widely?  Perhaps per database would be just as
> useful; and maybe it would allow us to skip running autovac workers
> when there is no point in doing so.

I think I need to take blame for that feature in Slony-I ;-).

I imagine it might be useful to add it to autovac, too.  I thought it
was pretty neat that this could be successfully handled by comparison
with a single value (e.g. - eldest xmin), and I expect that using a
single quasi-global value should be good enough for autovac.

If there is some elderly, long-running transaction that isn't a
VACUUM, that will indeed inhibit VACUUM from doing any good, globally,
across the cluster, until such time as that transaction ends.

To, at that point, "inhibit" autovac from bothering to run VACUUM,
would seem like a good move.  There is still value to running ANALYZE
on tables, so it doesn't warrant stopping autovac altogether, but this
scenario suggests a case for suppressing futile vacuuming, at least...
-- 
If this was helpful,  rate me
http://linuxfinances.info/info/slony.html
It's hard to tell if someone is inconspicuous. 

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

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

Re: [HACKERS] Autovacuum and OldestXmin

[Simon Riggs]

On Thu, 2007-11-22 at 19:02 +, Heikki Linnakangas wrote:

> Even if we could use PageIsPrunable, it would be a bad thing from a 
> robustness point of view. If we ever failed to set the Prunable-flag on 
> a page for some reason, VACUUM would never remove the dead tuples.

That's a killer reason, I suppose. I was really trying to uncover what
the thinking was, so we can document it. Having VACUUM ignore it
completely seems wrong.

> Besides, I don't remember anyone complaining about VACUUM's CPU usage, 
> so it doesn't really matter.

Recall anybody saying how much they love it? ;-)

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Autovacuum and OldestXmin

[Heikki Linnakangas]

Simon Riggs wrote:

On Thu, 2007-11-22 at 13:21 -0500, Tom Lane wrote:

Simon Riggs <[EMAIL PROTECTED]> writes:

Why isn't VACUUM optimised the same way HOT is?

It doesn't do the same things HOT does.


Thanks for the enlightenment :-)

Clearly much of the code in heap_page_prune_opt() differs, yet the test
for if (!PageIsPrunable(...)) could be repeated inside the main block
scan loop in lazy_scan_heap().

My thought-experiment:

- a long running transaction is in progress
- HOT cleans a block and then the block is not touched for a while, the
total of all uncleanable updates cause a VACUUM to be triggered, which
then scans the table, sees the block and scans the block again
because...

a) it could have checked !PageIsPrunable(), but didn't

b) it is important that it attempt to clean the block again for
reason...?


There might be dead tuples left over by aborted INSERTs, for example, 
which don't set the Prunable-flag.


Even if we could use PageIsPrunable, it would be a bad thing from a 
robustness point of view. If we ever failed to set the Prunable-flag on 
a page for some reason, VACUUM would never remove the dead tuples.


Besides, I don't remember anyone complaining about VACUUM's CPU usage, 
so it doesn't really matter.


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

Re: [HACKERS] Autovacuum and OldestXmin

[Simon Riggs]

On Thu, 2007-11-22 at 15:20 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> > I notice that slony records the oldestxmin that was running when it last
> > ran a VACUUM on its tables. This allows slony to avoid running a VACUUM
> > when it would be clearly pointless to do so.
> > 
> > AFAICS autovacuum does not do this, or did I miss that?
> 
> Hmm, I think it's just because nobody suggested it and I didn't came up
> with the idea.

OK, well, me neither :-(

...and I never thought to look at slony before now.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Autovacuum and OldestXmin

[Simon Riggs]

On Thu, 2007-11-22 at 13:21 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > Why isn't VACUUM optimised the same way HOT is?
> 
> It doesn't do the same things HOT does.

Thanks for the enlightenment :-)

Clearly much of the code in heap_page_prune_opt() differs, yet the test
for if (!PageIsPrunable(...)) could be repeated inside the main block
scan loop in lazy_scan_heap().

My thought-experiment:

- a long running transaction is in progress
- HOT cleans a block and then the block is not touched for a while, the
total of all uncleanable updates cause a VACUUM to be triggered, which
then scans the table, sees the block and scans the block again
because...

a) it could have checked !PageIsPrunable(), but didn't

b) it is important that it attempt to clean the block again for
reason...?

Seems like the thought experiment could occur frequently.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.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

Re: [HACKERS] Autovacuum and OldestXmin

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Simon Riggs wrote:
>> [Also there is a comment saying "this is a bug" in autovacuum.c
>> Are we thinking to go production with that phrase in the code?]

> Yeah, well, it's only a comment ;-)  The problem is that a worker can
> decide that a table needs to be vacuumed, if another worker has finished
> vacuuming it in the last 500 ms.  I proposed a mechanism to close the
> hole but it was too much of a hassle.

> Maybe we could remove the comment for the final release? :-)

What, you think we should try to hide our shortcomings?  There are
hundreds of XXX and FIXME comments in the sources.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] Autovacuum and OldestXmin

[Tom Lane]

Simon Riggs <[EMAIL PROTECTED]> writes:
> Why isn't VACUUM optimised the same way HOT is?

It doesn't do the same things HOT does.

regards, tom lane

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

Re: [HACKERS] Autovacuum and OldestXmin

[Alvaro Herrera]

Simon Riggs wrote:
> I notice that slony records the oldestxmin that was running when it last
> ran a VACUUM on its tables. This allows slony to avoid running a VACUUM
> when it would be clearly pointless to do so.
> 
> AFAICS autovacuum does not do this, or did I miss that?

Hmm, I think it's just because nobody suggested it and I didn't came up
with the idea.

Whether it's a useful thing to do is a different matter.  Why store it
per table and not more widely?  Perhaps per database would be just as
useful; and maybe it would allow us to skip running autovac workers
when there is no point in doing so.


> Why isn't VACUUM optimised the same way HOT is?
> Why doesn't VACUUM continue onto the next block when !PageIsPrunable().
> Nothing is documented though it seems "obvious" that it should.
> 
> Perhaps an integration oversight?

Yeah.

> [Also there is a comment saying "this is a bug" in autovacuum.c
> Are we thinking to go production with that phrase in the code?]

Yeah, well, it's only a comment ;-)  The problem is that a worker can
decide that a table needs to be vacuumed, if another worker has finished
vacuuming it in the last 500 ms.  I proposed a mechanism to close the
hole but it was too much of a hassle.

Maybe we could remove the comment for the final release? :-)

-- 
Alvaro Herrera   Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
Management by consensus: I have decided; you concede.
(Leonard Liu)

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate