Thread: Question about (lazy) vacuum

Question about (lazy) vacuum

From
Gregory Stark
Date:
With all this code to handle ignoring vacuum transactions in calculating the
global xmin it just occurred to me to wonder: Does lazy vacuum need a
transaction at all? It doesn't do the tuple moving tricks with xvac that
vacuum full does so does lazy vacuum's xid ever make it into tables? Couldn't
it just use GetTopTransactionId instead of its own xid? Would this cause any
problems?


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



Re: Question about (lazy) vacuum

From
Hannu Krosing
Date:
Ühel kenal päeval, K, 2006-08-23 kell 05:23, kirjutas Gregory Stark:
> With all this code to handle ignoring vacuum transactions in calculating the
> global xmin it just occurred to me to wonder: Does lazy vacuum need a
> transaction at all? It doesn't do the tuple moving tricks with xvac that
> vacuum full does so does lazy vacuum's xid ever make it into tables? Couldn't
> it just use GetTopTransactionId instead of its own xid? Would this cause any
> problems?

When I asked the same question, I was told that a lot of core
functionality vacuum uses needs to be in transaction. I guess bad things
can happen, if some other backend ends a transaction you claim to be in.

And it is not so much about what ends up in tables, but about what other
backends think.


BTW, I think that CONCURRENT CREATE INDEX should be modified to use long
transactions which actually build the index and are ignored by vacuum
and short ones which write data to system tables and are not ignored.
That way we have one less obstacle for keeping high-update tables in
shape.

> 
-- 
----------------
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




Re: Question about (lazy) vacuum

From
Greg Stark
Date:
Hannu Krosing <hannu@skype.net> writes:

> When I asked the same question, I was told that a lot of core
> functionality vacuum uses needs to be in transaction. I guess bad things
> can happen, if some other backend ends a transaction you claim to be in.
> 
> And it is not so much about what ends up in tables, but about what other
> backends think.

Well the only way other backends would find out about vacuum's xid is via
tables or via the PGProc table. But they aren't going to care about vacuum's
transaction status in PGProc unless they're vacuum and then we're going out of
our way to make sure it doesn't care.

Vacuum doesn't take any record locks so the xid isn't necessary for that.

Vacuum does WAL log some entries via log_heap_clean but I'm unclear whether
that requires a transaction, I don't see it being used anywhere. 

> BTW, I think that CONCURRENT CREATE INDEX should be modified to use long
> transactions which actually build the index and are ignored by vacuum
> and short ones which write data to system tables and are not ignored.
> That way we have one less obstacle for keeping high-update tables in
> shape.

Hm, that might be worth thinking about. Note that it locks out vacuum from
running on the table it's running on so it would only help in allowing other
tables to be vacuumed effectively. If we ever get per-table xmin then it would
be entirely unnecessary.

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



Re: Question about (lazy) vacuum

From
Hannu Krosing
Date:
Ühel kenal päeval, K, 2006-08-23 kell 08:11, kirjutas Greg Stark:
> Hannu Krosing <hannu@skype.net> writes:

> > BTW, I think that CONCURRENT CREATE INDEX should be modified to use long
> > transactions which actually build the index and are ignored by vacuum
> > and short ones which write data to system tables and are not ignored.
> > That way we have one less obstacle for keeping high-update tables in
> > shape.
> 
> Hm, that might be worth thinking about. Note that it locks out vacuum from
> running on the table it's running on so it would only help in allowing other
> tables to be vacuumed effectively. 

Exactly. The whole point of one vacuum not blocking others is to make
sure, that a vacuum on a huge table would not disable vacuuming and
thereby reusing rows of much much smaller tables.

Locking out vacuum on the table itself is ok, as the runtimes of vacuum
and concurrent create index on a same table are in the same order.

-- 
----------------
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




Re: Question about (lazy) vacuum

From
Alvaro Herrera
Date:
Gregory Stark wrote:
> 
> With all this code to handle ignoring vacuum transactions in calculating the
> global xmin it just occurred to me to wonder: Does lazy vacuum need a
> transaction at all? It doesn't do the tuple moving tricks with xvac that
> vacuum full does so does lazy vacuum's xid ever make it into tables? Couldn't
> it just use GetTopTransactionId instead of its own xid? Would this cause any
> problems?

Two points:

1. it needs to get locks

2. GetTopTransactionId returns "its own Xid" anyway ...


The point of getting locks is that it needs the lock manager to be able
to release the lock if the transaction rolls back.  (The lock object
itself does not hold anything about the transaction itself AFAIR.)

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


Re: Question about (lazy) vacuum

From
Tom Lane
Date:
Hannu Krosing <hannu@skype.net> writes:
> Ühel kenal päeval, K, 2006-08-23 kell 05:23, kirjutas Gregory Stark:
>> global xmin it just occurred to me to wonder: Does lazy vacuum need a
>> transaction at all?

> When I asked the same question, I was told that a lot of core
> functionality vacuum uses needs to be in transaction.

Locks for instance; you certainly need a lock on the table.  In general
a whole lot of the backend functionality is bound up in the transaction
start/stop mechanisms, and quite a bit of rearchitecting would be needed
to do very much outside a transaction.  Doesn't really seem worth it.
        regards, tom lane