Re: The vacuum-ignore-vacuum patch

Ühel kenal päeval, N, 2006-07-27 kell 19:29, kirjutas Alvaro Herrera:

>
> We could either add it anew, beside nonInVacuumXmin, or replace
> nonInVacuumXmin. The difference will be whether we will have something
> to be used to vacuum shared relations or not. I think in general,
> shared relations are not vacuumed much so it shouldn't be too much of a
> problem if we leave them to be vacuumed with the regular, all-databases,
> include-vacuum Xmin.

Yes. I don't think that vacuuming shared relations will ever be a
significant performance concern.

> The other POV is that we don't really care about long-running
> transaction in other databases unless they are lazy vacuum, a case which
> is appropiately covered by the patch as it currently stands. This seems
> to be the POV that Hannu takes: the only long-running transactions he
> cares about are lazy vacuums.

Yes. The original target audience of this patch are users running 24/7
OLTP databases with big slow changing tables and small fast-changing
tables which need to stay small even at the time when the big ones are
vacuumed.

The other possible transactions which _could_ possibly be ignored while
VACUUMING are those from ANALYSE and non-lazy VACUUMs.

I don't care about them as:

ANALYSE is relatively fast, even on huge tables, and thus can be
ignored.

If you do run VACUUM FULL on anything bigger than a few thousand
lines then you are not running a 24/7 OLTP database anyway.

I also can't see a usecase for OLTP database where VACUUM FREEZE is
required.

Maybe we could also start ignoring the transactions that are running the
new CONCURRENT CREATE INDEX command, as it also runs inside its own
transaction(s) which can't possibly touch the tuples in the table being
vacuumed as it locks out VACUUM on the indexed table.

That would probably be quite easy to do by just having CONCURRENT CREATE
INDEX also mark its transactions as ignorable by VACUUM. Maybe the
variable name for that (proc->inVacuum) needs to be changed to something
like trxSafeToIgnoreByVacuum.

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com