Re: Proposal: Snapshot cloning

Ühel kenal päeval, N, 2007-01-25 kell 22:19, kirjutas Jan Wieck:
> Granted this one has a few open ends so far and I'd like to receive some 
> constructive input on how to actually implement it.
> 
> The idea is to clone an existing serializable transactions snapshot 
> visibility information from one backend to another. The semantics would 
> be like this:
> 
>      backend1: start transaction;
>      backend1: set transaction isolation level serializable;
>      backend1: select pg_backend_pid();
>      backend1: select publish_snapshot(); -- will block
> 
>      backend2: start transaction;
>      backend2: set transaction isolation level serializable;
>      backend2: select clone_snapshot(<pid>); -- will unblock backend1
> 
>      backend1: select publish_snapshot();
> 
>      backend3: start transaction;
>      backend3: set transaction isolation level serializable;
>      backend3: select clone_snapshot(<pid>);
> 
>      ...
> 
> This will allow a number of separate backends to assume the same MVCC 
> visibility, so that they can query independent but the overall result 
> will be according to one consistent snapshot of the database.

I see uses for this in implementing query parallelism in user level
code, like querying two child tables in two separate processes. 

> What I try to accomplish with this is to widen a bottleneck, many 
> current Slony users are facing. The initial copy of a database is 
> currently limited to one single reader to copy a snapshot of the data 
> provider. With the above functionality, several tables could be copied 
> in parallel by different client threads, feeding separate backends on 
> the receiving side at the same time.

I'm afraid that for most configurations this would make the copy slower,
as there will be mode random disk i/o.

Maybe better fix slony so that it allows initial copies in different
parallel transactions, or just do initial copy in several sets and merge
the sets later.

> The feature could also be used by a parallel version of pg_dump as well 
> as data mining tools.
> 
> The cloning process needs to make sure that the clone_snapshot() call is 
> made from the same DB user in the same database as corresponding 
> publish_snapshot() call was done. 

Why ? Snapshot is universal and same for whole db instance, so why limit
it to same user/database ?

> Since publish_snapshot() only 
> publishes the information, it gained legally and that is visible in the 
> PGPROC shared memory (xmin, xmax being the crucial part here), there is 
> no risk of creating a snapshot for which data might have been removed by 
> vacuum already.
> 
> What I am not sure about yet is what IPC method would best suit the 
> transfer of the arbitrarily sized xip vector. Ideas?
> 
> 
> Jan
> 
-- 
----------------
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