Re: [pgsql-patches] Recalculating OldestXmin in a long-running vacuum

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