Autovacuum and OldestXmin

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)

Simon Riggs <simon@2ndquadrant.com> writes:

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

It doesn't do the same things HOT does.

regards, tom lane

Alvaro Herrera <alvherre@alvh.no-ip.org> 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

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

Simon Riggs <simon@2ndquadrant.com> 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

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

Simon Riggs wrote:

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

Simon Riggs <simon@2ndquadrant.com> 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

On Thu, 2007-11-22 at 19:02 +0000, 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

The world rejoiced as alvherre@alvh.no-ip.org (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, <http://svcs.affero.net/rm.php?r=cbbrowne&gt; rate me
http://linuxfinances.info/info/slony.html
It's hard to tell if someone is inconspicuous.

Simon Riggs <simon@2ndquadrant.com> 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

On Fri, 2007-11-23 at 01:14 -0500, Tom Lane wrote:

Simon Riggs <simon@2ndquadrant.com> 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

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

The world rejoiced as alvherre@alvh.no-ip.org (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

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)

Hi to all.

I'm new. I'd like to integrate my code into PostgreSQL. It's the
implementation of some refinements of Replacement Selection algorithm used
for External Sorting.
I have got some issue and preferibly I'd like to be supported by some
developers that have something to do with it.

Who can I talk to?

Thanks for your attentions.
Good Luck!

Manolo.

mac_man2005@hotmail.it wrote:

I'm new. I'd like to integrate my code into PostgreSQL. It's the
implementation of some refinements of Replacement Selection algorithm
used for External Sorting.
I have got some issue and preferibly I'd like to be supported by some
developers that have something to do with it.

Who can I talk to?

This mailing list is the right place to discuss that.

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

Thanks for your support.

I downloaded the source code of the last stable version of PostgreSQL. Where
can I find the part related to the External Sorting algorithm (supposed to
be Replacement Selection)?
I mean, which is the file to be studied and/or modified and/or substituted?

Thanks for your attention.

--------------------------------------------------
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
Sent: Monday, November 26, 2007 1:35 PM
To: <mac_man2005@hotmail.it>
Cc: <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Replacement Selection

mac_man2005@hotmail.it wrote:

Thanks for your support.

I downloaded the source code of the last stable version of PostgreSQL.
Where can I find the part related to the External Sorting algorithm
(supposed to be Replacement Selection)?
I mean, which is the file to be studied and/or modified and/or substituted?

src/backend/utils/sort/tuplesort.c

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"I would rather have GNU than GNOT." (ccchips, lwn.net/Articles/37595/)

mac_man2005@hotmail.it wrote:

I downloaded the source code of the last stable version of PostgreSQL.
Where can I find the part related to the External Sorting algorithm
(supposed to be Replacement Selection)?
I mean, which is the file to be studied and/or modified and/or substituted?

In src/backend/utils/sort/tuplesort.c. The comments at the top of that
file is a good place to start.

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

Ok guys!
Thanks for your help.

Unfortunately I'm lost into the code... any good soul helping me to
understand what should be the precise part to be modified?

Thanks for your time!

--------------------------------------------------
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
Sent: Monday, November 26, 2007 2:34 PM
To: <mac_man2005@hotmail.it>
Cc: <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Replacement Selection

mac_man2005@hotmail.it wrote:

Ok guys!
Thanks for your help.

Unfortunately I'm lost into the code... any good soul helping me to
understand what should be the precise part to be modified?

I think you should print the file and read it several times until you
understand what's going on. Then you can start thinking where and how
to modify it.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Oh, great altar of passive entertainment, bestow upon me thy discordant images
at such speed as to render linear thought impossible" (Calvin a la TV)