Re: AW: Plans for solving the VACUUM problem

Actually I don't like the problems with rollback segments in oracle at
all. I am just concerned that using WAL for UNDO will have all of the
same problems if it isn't designed carefully. At least in oracle's
rollback segments there are multiple of them, in WAL there is just one,
thus you will potentially have that 20Gig all in your single log
directory. People are already reporting the log directory growing to a
gig or more when running vacuum in 7.1.

Of the points you raised about oracle's rollback segment problems:

1. configuration (for every different workload you need a different config)

Postgres should be able to do a better job here.

2. snapshot too old

Shouldn't be a problem as long as postgres continues to use a non-overwriting storage manager. However under an overwriting storage manager, you need to keep all of the changes in the UNDO records to satisfy the query snapshot, thus if you want to limit the size of UNDO you may need to kill long running queries.

3. tx abort because rollback segments are full
If you want to limit the size of the UNDO, then this is a corresponding
byproduct. I believe a mail note was sent out yesterday suggesting that
limits like this be added to the todo list.

4. They use up huge amounts of space (e.g. 20 Gb rollback seg for a 120 Gb SAP)
You need to store the UNDO information somewhere. And on active
databases that can amount to alot of information, especially for bulk
loads or massive updates.

thanks,
--Barry

Zeugswetter Andreas SB wrote:

>
>
>> REDO in oracle is done by something known as a 'rollback segment'.
>
>
> You are not seriously saying that you like the "rollback segments" in Oracle.
> They only cause trouble:
> 1. configuration (for every different workload you need a different config)
> 2. snapshot too old
> 3. tx abort because rollback segments are full
> 4. They use up huge amounts of space (e.g. 20 Gb rollback seg for a 120 Gb SAP)
>
> If I read the papers correctly Version 9 gets rid of Point 1 but the rest ...
>
> Andreas
>
>