Recalculating OldestXmin in a long-running vacuum

Heikki Linnakangas <heikki@enterprisedb.com> writes:

It seems to me that we could easily reclaim a bit more dead tuples in a
vacuum by recalculating the OldestXmin every now and then.

Doesn't this break relfrozenxid maintenance? In any case I'd like to
see some evidence of significant real-world benefit before adding such
a conceptual wart ...

The procarray.c change scares me as well; I'm pretty sure the original
coding was intentional.

regards, tom lane

Tom Lane wrote:

Heikki Linnakangas <heikki@enterprisedb.com> writes:

It seems to me that we could easily reclaim a bit more dead tuples in a
vacuum by recalculating the OldestXmin every now and then.

Doesn't this break relfrozenxid maintenance?

Not AFAICS. relfrozenxid is nowadays updated with FreezeLimit.

In any case I'd like to
see some evidence of significant real-world benefit before adding such
a conceptual wart ...

I've asked our testers to do a TPC-C run with and without the
patch. I'm not expecting it to make a huge difference there, but if
you're using a big cost delay, it could make quite a difference for such
a simple thing.

The procarray.c change scares me as well; I'm pretty sure the original
coding was intentional.

Well, it didn't make much difference before, since OldestXmin was always
calculated early in the transaction.

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

Heikki Linnakangas <heikki@enterprisedb.com> writes:

Tom Lane wrote:

In any case I'd like to see some evidence of significant real-world
benefit before adding such a conceptual wart ...

I've asked our testers to do a TPC-C run with and without the
patch. I'm not expecting it to make a huge difference there, but if you're
using a big cost delay, it could make quite a difference for such a simple
thing.

I think the key here is that it removes the size of the table from the list of
factors that govern the steady-state. Currently as the table grows the maximum
age of the snapshot vacuum uses gets older too which means a greater
percentage of the table is dedicated to dead tuple overhead. (which in turn
means a larger table which means an even greater percentage of dead tuples...)

With the patch the size of the table is no longer a factor. As long as the
work vacuum has on a given page can keep up with the rate of creating dead
tuples then it won't matter how large the table is. The only factors that
determine the steady state are the rate of creation of dead tuples and the
rate at which vacuum can process them.

Unfortunately indexes, again, mean that's not entirely true. As the table
grows the indexes will grow as well and that will slow vacuum down. Though
indexes are usually smaller than tables.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Have you gotten performance numbers on this yet?

---------------------------------------------------------------------------

Gregory Stark wrote:

Heikki Linnakangas <heikki@enterprisedb.com> writes:

Tom Lane wrote:

In any case I'd like to see some evidence of significant real-world
benefit before adding such a conceptual wart ...

I've asked our testers to do a TPC-C run with and without the
patch. I'm not expecting it to make a huge difference there, but if you're
using a big cost delay, it could make quite a difference for such a simple
thing.

I think the key here is that it removes the size of the table from the list of
factors that govern the steady-state. Currently as the table grows the maximum
age of the snapshot vacuum uses gets older too which means a greater
percentage of the table is dedicated to dead tuple overhead. (which in turn
means a larger table which means an even greater percentage of dead tuples...)

With the patch the size of the table is no longer a factor. As long as the
work vacuum has on a given page can keep up with the rate of creating dead
tuples then it won't matter how large the table is. The only factors that
determine the steady state are the rate of creation of dead tuples and the
rate at which vacuum can process them.

Unfortunately indexes, again, mean that's not entirely true. As the table
grows the indexes will grow as well and that will slow vacuum down. Though
indexes are usually smaller than tables.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

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

http://archives.postgresql.org

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

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

Bruce Momjian wrote:

Have you gotten performance numbers on this yet?

I have two runs of DBT-2, one with the patch and one without.

Patched:

autovac "public.stock" scans:1 pages:1285990(-0)
tuples:25303056(-2671265) CPU 95.22s/38.02u sec elapsed 10351.17 sec

Unpatched:

autovac "public.stock" scans:1 pages:1284504(-0)
tuples:25001369(-1973760) CPU 86.55s/34.70u sec elapsed 9628.13 sec

Both autovacuums started roughly at the same time after test start. The
numbers mean that without the patch, the vacuum found 1973760 dead
tuples and with the patch 2671265 dead tuples. The runs were done with
autovacuum_vacuum_scale_factor = 0.05, to trigger the autovacuum earlier
than with the default.

Before these test runs, I realized that the patch had a little
strangeness. Because we're taking new snapshot during the vacuum, some
rows that are updated while the vacuum is running might not get counted
as live. That can happen when the new updated version goes to page that
has already been vacuumed, and the old version is on a page that hasn't
yet been vacuumed. Also, because we're taking new snapshots, it makes
sense to recalculate the relation size as well to vacuum any new blocks
at the end. Attached is an updated patch that does that.

The reason I haven't posted the results earlier is that we're having
some periodic drops in performance on that server that we can't explain.
(I don't think it's checkpoint nor autovacuum). I wanted to figure
that out first, but I don't think that makes a difference for this patch.

Is this enough, or does someone want more evidence?

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

Heikki Linnakangas <heikki@enterprisedb.com> writes:

I have two runs of DBT-2, one with the patch and one without.

Patched:

autovac "public.stock" scans:1 pages:1285990(-0)
tuples:25303056(-2671265) CPU 95.22s/38.02u sec elapsed 10351.17 sec

Unpatched:

autovac "public.stock" scans:1 pages:1284504(-0)
tuples:25001369(-1973760) CPU 86.55s/34.70u sec elapsed 9628.13 sec

So that makes this patch a good idea why? (Maybe what we need to see
is the impact on the total elapsed time for the DBT-2 test, rather
than just the VACUUM runtime.)

BTW I've got serious reservations about whether this bit is safe:

+ 			/* The table could've grown since vacuum started, and there
+ 			 * might already be dead tuples on the new pages. Catch them
+ 			 * as well. Also, we want to include any live tuples in the
+ 			 * new pages in the statistics.
+ 			 */
+ 			nblocks = RelationGetNumberOfBlocks(onerel);

I seem to recall some assumptions somewhere in the system that a vacuum
won't visit newly-added pages.

regards, tom lane

Tom Lane wrote:

Heikki Linnakangas <heikki@enterprisedb.com> writes:

I have two runs of DBT-2, one with the patch and one without.

Patched:

autovac "public.stock" scans:1 pages:1285990(-0)
tuples:25303056(-2671265) CPU 95.22s/38.02u sec elapsed 10351.17 sec

Unpatched:

autovac "public.stock" scans:1 pages:1284504(-0)
tuples:25001369(-1973760) CPU 86.55s/34.70u sec elapsed 9628.13 sec

So that makes this patch a good idea why? (Maybe what we need to see
is the impact on the total elapsed time for the DBT-2 test, rather
than just the VACUUM runtime.)

Yea, in summary, it looks like the patch made the performance worse.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

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

Tom Lane wrote:

Heikki Linnakangas <heikki@enterprisedb.com> writes:

I have two runs of DBT-2, one with the patch and one without.

Patched:

autovac "public.stock" scans:1 pages:1285990(-0)
tuples:25303056(-2671265) CPU 95.22s/38.02u sec elapsed 10351.17 sec

Unpatched:

autovac "public.stock" scans:1 pages:1284504(-0)
tuples:25001369(-1973760) CPU 86.55s/34.70u sec elapsed 9628.13 sec

So that makes this patch a good idea why? (Maybe what we need to see
is the impact on the total elapsed time for the DBT-2 test, rather
than just the VACUUM runtime.)

The patch is a good idea because the vacuum collected more dead tuples,
not because it makes vacuum run faster (it doesn't).

I believe the increase in runtime is caused by some random variations in
the test. As I said, there's something going on that server that I don't
fully understand. I'll setup another test set.

The impact on the whole DBT-2 test is hard to measure, it would require
a long run so that you reach a steady state where tables are not growing
because of dead tuples anymore. We'll need to do that anyway, so
hopefully I'll get a chance to measure the impact of this patch as well.
The effect I'm expecting the patch to have is to make the steady-state
size of the stock table smaller, giving more cache hits and less I/O.

BTW I've got serious reservations about whether this bit is safe:

+ 			/* The table could've grown since vacuum started, and there
+ 			 * might already be dead tuples on the new pages. Catch them
+ 			 * as well. Also, we want to include any live tuples in the
+ 			 * new pages in the statistics.
+ 			 */
+ 			nblocks = RelationGetNumberOfBlocks(onerel);

I seem to recall some assumptions somewhere in the system that a vacuum
won't visit newly-added pages.

Hmm, I can't think of anything.

Without the patch, there can't be any dead tuples on the newly-added
pages, according to the snapshot taken at the beginning of the vacuum,
so it would be a waste of time to visit them.

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

Heikki Linnakangas <heikki@enterprisedb.com> writes:

Tom Lane wrote:

BTW I've got serious reservations about whether this bit is safe:

+ 			/* The table could've grown since vacuum started, and there
+ 			 * might already be dead tuples on the new pages. Catch them
+ 			 * as well. Also, we want to include any live tuples in the
+ 			 * new pages in the statistics.
+ 			 */
+ 			nblocks = RelationGetNumberOfBlocks(onerel);

I seem to recall some assumptions somewhere in the system that a vacuum
won't visit newly-added pages.

Hmm, I can't think of anything.

I think I was thinking of the second risk described here:
http://archives.postgresql.org/pgsql-hackers/2005-05/msg00613.php
which is now fixed so maybe there's no longer any problem. (If there
is, a change like this will convert it from a very-low-probability
problem into a significant-probability problem, so I guess we'll
find out...)

I still don't like the patch though; rechecking the relation length
every N blocks is uselessly inefficient and still doesn't create any
guarantees about having examined everything. If we think this is
worth doing at all, we should arrange to recheck the length after
processing what we think is the last block, not at any other time.

regards, tom lane

Tom Lane wrote:

Heikki Linnakangas <heikki@enterprisedb.com> writes:

Tom Lane wrote:

BTW I've got serious reservations about whether this bit is safe:

+ 			/* The table could've grown since vacuum started, and there
+ 			 * might already be dead tuples on the new pages. Catch them
+ 			 * as well. Also, we want to include any live tuples in the
+ 			 * new pages in the statistics.
+ 			 */
+ 			nblocks = RelationGetNumberOfBlocks(onerel);

I seem to recall some assumptions somewhere in the system that a vacuum
won't visit newly-added pages.

Hmm, I can't think of anything.

I think I was thinking of the second risk described here:
http://archives.postgresql.org/pgsql-hackers/2005-05/msg00613.php
which is now fixed so maybe there's no longer any problem. (If there
is, a change like this will convert it from a very-low-probability
problem into a significant-probability problem, so I guess we'll
find out...)

I still don't like the patch though; rechecking the relation length
every N blocks is uselessly inefficient and still doesn't create any
guarantees about having examined everything. If we think this is
worth doing at all, we should arrange to recheck the length after
processing what we think is the last block, not at any other time.

Was this revisited?

I'm wondering if there has been any effort to make this work in
conjunction with ITAGAKI Takahiro's patch to correct the dead tuple
count estimate.

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

Alvaro Herrera wrote:

Tom Lane wrote:

Heikki Linnakangas <heikki@enterprisedb.com> writes:

Tom Lane wrote:

BTW I've got serious reservations about whether this bit is safe:

+ 			/* The table could've grown since vacuum started, and there
+ 			 * might already be dead tuples on the new pages. Catch them
+ 			 * as well. Also, we want to include any live tuples in the
+ 			 * new pages in the statistics.
+ 			 */
+ 			nblocks = RelationGetNumberOfBlocks(onerel);

I seem to recall some assumptions somewhere in the system that a vacuum
won't visit newly-added pages.

Hmm, I can't think of anything.

I think I was thinking of the second risk described here:
http://archives.postgresql.org/pgsql-hackers/2005-05/msg00613.php
which is now fixed so maybe there's no longer any problem. (If there
is, a change like this will convert it from a very-low-probability
problem into a significant-probability problem, so I guess we'll
find out...)

I still don't like the patch though; rechecking the relation length
every N blocks is uselessly inefficient and still doesn't create any
guarantees about having examined everything. If we think this is
worth doing at all, we should arrange to recheck the length after
processing what we think is the last block, not at any other time.

Was this revisited?

Arranging the tests has taken me longer than I thought, but I now
finally have the hardware and DBT-2 set up. I just finished a pair of 2h
tests with autovacuum off, and continuous vacuum of the stock table. I'm
trying to get the results uploaded on some public website so we can all
see and discuss them.

I'm wondering if there has been any effort to make this work in
conjunction with ITAGAKI Takahiro's patch to correct the dead tuple
count estimate.

No. I'll have to take a look at that patch...

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

Alvaro Herrera wrote:

I'm wondering if there has been any effort to make this work in
conjunction with ITAGAKI Takahiro's patch to correct the dead tuple
count estimate.

I just looked at that patch. If we applied both patches, the dead_tuples
estimate would be off by the number of dead tuples removed thanks to my
patch.

In vacuum, we could count separately the tuples that were vacuumable
according to the first snapshot, and tuples that were vacuumable
according to a new snapshot. We could then get an estimate that's as
good as with just Takahiro's patch with this formula:

new_n_dead_tuples = n_dead_tuples_at_end - (n_dead_tuples_at_start +
tuples_removed_thanks_to_new_snapshot)

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

Hi,

I was wondering if we can apply the same logic of recalculating OldestXmin
within IndexBuildHeapScan which occurs as part of create index operation?

Having to index lesser number of tuples should be a good save in the "CREATE
INDEX CONCURRENTLY" case, if the above is possible?

Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas wrote:

Alvaro Herrera wrote:

Was this revisited?

Arranging the tests has taken me longer than I thought, but I now
finally have the hardware and DBT-2 set up. I just finished a pair of 2h
tests with autovacuum off, and continuous vacuum of the stock table. I'm
trying to get the results uploaded on some public website so we can all
see and discuss them.

I finally got around looking at this again.

I ran two 24h test runs with DBT-2, one with the patch and one without.
To get comparable, predictable results, I turned autovacuum off and run
a manual vacuum in a loop on the stock-table alone.

As expected, the steady-state of the stock table is smaller with the
patch. But only by ~2%, that's slightly less than I expected.

But what surprises me is that response times went up a with the patch. I
don't know why.

The full test results are here:

http://community.enterprisedb.com/oldestxmin/

92 was run with the patch, 93 without it.

BTW: The iostat chart clearly shows the vacuum WAL flush problem. The
WAL utilization jumps from ~20% to ~40% during the 2nd vacuum pass.

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

Heikki Linnakangas wrote:

I ran two 24h test runs with DBT-2, one with the patch and one without.
To get comparable, predictable results, I turned autovacuum off and run
a manual vacuum in a loop on the stock-table alone.

As expected, the steady-state of the stock table is smaller with the
patch. But only by ~2%, that's slightly less than I expected.

But what surprises me is that response times went up a with the patch. I
don't know why.

Maybe because of increased contention of ProcArrayLock? (I assume you
are using that, althought I haven't seen the patch)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

Heikki Linnakangas wrote:

I ran two 24h test runs with DBT-2, one with the patch and one without.
To get comparable, predictable results, I turned autovacuum off and run
a manual vacuum in a loop on the stock-table alone.

As expected, the steady-state of the stock table is smaller with the
patch. But only by ~2%, that's slightly less than I expected.

But what surprises me is that response times went up a with the patch. I
don't know why.

Maybe because of increased contention of ProcArrayLock? (I assume you
are using that, althought I haven't seen the patch)

I am, but I doubt that's it. The response times are dominated by I/O, so
any increase in lock contention would hardly show up. And the patch is
only adding one GetOldestXmin call every 1000 scanned pages, which is
nothing compared to the thousands of GetSnapshot calls the normal
transactions are issuing per minute.

The patch must have changed the I/O pattern in some subtle way.

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

Heikki Linnakangas wrote:

I am, but I doubt that's it. The response times are dominated by I/O, so
any increase in lock contention would hardly show up. And the patch is
only adding one GetOldestXmin call every 1000 scanned pages, which is

Sorry, should be "every 100 scanned pages". The argument still holds,
though.

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

Heikki Linnakangas wrote:

Alvaro Herrera wrote:

Heikki Linnakangas wrote:

I ran two 24h test runs with DBT-2, one with the patch and one without.
To get comparable, predictable results, I turned autovacuum off and run
a manual vacuum in a loop on the stock-table alone.

As expected, the steady-state of the stock table is smaller with the
patch. But only by ~2%, that's slightly less than I expected.

But what surprises me is that response times went up a with the patch. I
don't know why.

Maybe because of increased contention of ProcArrayLock? (I assume you
are using that, althought I haven't seen the patch)

I am, but I doubt that's it. The response times are dominated by I/O, so
any increase in lock contention would hardly show up. And the patch is
only adding one GetOldestXmin call every 1000 scanned pages, which is
nothing compared to the thousands of GetSnapshot calls the normal
transactions are issuing per minute.

The patch must have changed the I/O pattern in some subtle way.

So are you stopping work on the patch? I assume so.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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

Bruce Momjian wrote:

Heikki Linnakangas wrote:

Alvaro Herrera wrote:

Heikki Linnakangas wrote:

I ran two 24h test runs with DBT-2, one with the patch and one without.
To get comparable, predictable results, I turned autovacuum off and run
a manual vacuum in a loop on the stock-table alone.

As expected, the steady-state of the stock table is smaller with the
patch. But only by ~2%, that's slightly less than I expected.

But what surprises me is that response times went up a with the patch. I
don't know why.

Maybe because of increased contention of ProcArrayLock? (I assume you
are using that, althought I haven't seen the patch)

I am, but I doubt that's it. The response times are dominated by I/O, so
any increase in lock contention would hardly show up. And the patch is
only adding one GetOldestXmin call every 1000 scanned pages, which is
nothing compared to the thousands of GetSnapshot calls the normal
transactions are issuing per minute.

The patch must have changed the I/O pattern in some subtle way.

So are you stopping work on the patch? I assume so.

Yes, at least for now. I can't believe the patch actually hurts
performance, but I'm not going to spend time investigating it.

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