Thread: The vacuum-ignore-vacuum patch
Hi, Hannu Krossing asked me about his patch to ignore transactions running VACUUM LAZY in other vacuum transactions. I attach a version of the patch updated to the current sources. Just to remind what this is about: the point of the patch is to be able to run more than one VACUUM LAZY simultaneously and not have them interefere with each other. For example, assume you have a database with two tables, one very big and another very small but with a high update rate. One usually wants to vacuum the small one very frequently in order to keep the number of dead tuples low. But if one starts to vacuum the big table, it will take a long time, during which the vacuums applied to the smaller table won't be able to recover any tuple because that transaction will think the other transaction may want to read some of the tuples that the small transaction is trying to remove. We know this is not so -- a VACUUM can only be run in a standalone transaction, and it only checks the one table it's vacuuming. Thus we can optimize the vacuuming so that if the only thing that's holding the tuples undeletable is another big vacuum operation, ignore it and delete the tuples anyway. One exception is that we can't do that with full vacuums. The reason is that full vacuum may want to run user-defined functions to be able to index the tuples it moves. This isn't a problem normally, except in the case where the function tries to scan some other table: if we ignored that transaction, then another lazy vacuum might delete tuples from that table that we need to see. In a previous version of the patch, there was a note somewhere that made the code not ignore lazy vacuums in the case where we were running database-wide vacuums. The reason was that the value we computed was also used as truncate point for pg_clog; thus if we ignored that transaction, the truncate point could be further ahead than the vacuum, so the clog page for the vacuum transaction could be gone and it wouldn't be able to commit. This is no longer the case, because with the patch I committed yesterday, the clog truncation point is calculated differently and thus we don't need to take special care about this. -- Alvaro Herrera http://www.advogato.org/person/alvherre "Uno combate cuando es necesario... ¡no cuando está de humor! El humor es para el ganado, o para hacer el amor, o para tocar el baliset. No para combatir." (Gurney Halleck)
Attachment
Am Dienstag, 11. Juli 2006 23:01 schrieb Alvaro Herrera: > One exception is that we can't do that with full vacuums. The reason is > that full vacuum may want to run user-defined functions to be able to > index the tuples it moves. This isn't a problem normally, except in the > case where the function tries to scan some other table: if we ignored > that transaction, then another lazy vacuum might delete tuples from that > table that we need to see. Functions in the index expression must be immutable, so I don't think that is a real concern. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Hannu Krossing asked me about his patch to ignore transactions running
> VACUUM LAZY in other vacuum transactions. I attach a version of the
> patch updated to the current sources.
nonInVacuumXmin seems useless ... perhaps a vestige of some earlier
version of the computation?
In general, it seems to me that a transaction running lazy vacuum could
be ignored for every purpose except truncating clog/subtrans. Since it
will never insert its own XID into the database (note: VACUUM ANALYZE is
run as two separate transactions, hence the pg_statistic rows inserted
by ANALYZE are not a counterexample), there's no need for anyone to
include it as running in their snapshots. So unless I'm missing
something, this is a safe change for lazy vacuum, but perhaps not for
full vacuum, which *does* put its XID into the database.
A possible objection to this is that it would foreclose running VACUUM
and ANALYZE as a single transaction, exactly because of the point that
we couldn't insert pg_statistic rows using a lazy vacuum's XID. I think
there was some discussion of doing that in connection with enlarging
ANALYZE's sample greatly --- if ANALYZE goes back to being a full scan
or nearly so, it'd sure be nice to combine it with the VACUUM scan.
However maybe we should just accept that as the price of not having
multiple vacuums interfere with each other.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > Hannu Krossing asked me about his patch to ignore transactions running > > VACUUM LAZY in other vacuum transactions. I attach a version of the > > patch updated to the current sources. > > nonInVacuumXmin seems useless ... perhaps a vestige of some earlier > version of the computation? Hmm ... I remember removing a now-useless variable somewhere, but maybe this one escaped me. I don't have the code handy -- will check. > In general, it seems to me that a transaction running lazy vacuum could > be ignored for every purpose except truncating clog/subtrans. Since it > will never insert its own XID into the database (note: VACUUM ANALYZE is > run as two separate transactions, hence the pg_statistic rows inserted > by ANALYZE are not a counterexample), there's no need for anyone to > include it as running in their snapshots. So unless I'm missing > something, this is a safe change for lazy vacuum, but perhaps not for > full vacuum, which *does* put its XID into the database. But keep in mind that in the current code, clog truncation takes relminxid (actually datminxid) into account, not running transactions, so AFAICS this should affect anything. Subtrans truncation is different and it certainly should consider lazy vacuum's Xids. > A possible objection to this is that it would foreclose running VACUUM > and ANALYZE as a single transaction, exactly because of the point that > we couldn't insert pg_statistic rows using a lazy vacuum's XID. I think > there was some discussion of doing that in connection with enlarging > ANALYZE's sample greatly --- if ANALYZE goes back to being a full scan > or nearly so, it'd sure be nice to combine it with the VACUUM scan. > However maybe we should just accept that as the price of not having > multiple vacuums interfere with each other. Hmm, what about having a single scan for both, and then starting a normal transaction just for the sake of inserting the pg_statistics tuple? I think the interactions of Xids and vacuum and other stuff are starting to get complex; IMHO it warrants having a README.vacuum, or something. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> A possible objection to this is that it would foreclose running VACUUM
>> and ANALYZE as a single transaction, exactly because of the point that
>> we couldn't insert pg_statistic rows using a lazy vacuum's XID.
> Hmm, what about having a single scan for both, and then starting a
> normal transaction just for the sake of inserting the pg_statistics
> tuple?
We could, but I think memory consumption would be the issue. VACUUM
wants a lotta memory for the dead-TIDs array, ANALYZE wants a lot for
its statistics gathering ... even more if it's trying to take a larger
sample than before. (This is probably why we kept them separate in
the last rewrite.)
> I think the interactions of Xids and vacuum and other stuff are starting
> to get complex; IMHO it warrants having a README.vacuum, or something.
Go for it ...
regards, tom lane
Cc: pgsql-hackers removed, as this mail contains a patch. Tom Lane wrote: > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > Hannu Krossing asked me about his patch to ignore transactions running > > VACUUM LAZY in other vacuum transactions. I attach a version of the > > patch updated to the current sources. > > nonInVacuumXmin seems useless ... perhaps a vestige of some earlier > version of the computation? Yup -- I checked that code and found out that nonInVacuumXmin can be taken out as it's not used anywhere. One upside of this is that taking it out means we can remove all diffs to GetSnapshotData. New patch attached; it's a bit smaller than the last one. I'm currently testing it. Since it appears there are no further objections, I intend to commit it. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.