On E, 2005-04-04 at 09:07 -0400, Rod Taylor wrote:
> On Mon, 2005-04-04 at 15:12 +0300, Hannu Krosing wrote:
> > On T, 2005-03-29 at 11:22 -0500, Christopher Browne wrote:
> > > Rod Taylor wrote:
> > > 
> > > >
> > > >It takes me about 12 to 24 hours to copy the largest tables (that's
> > > >each, not collectively); so there are a number of blocks where it
> > > >rescans from transaction ID X to some larger number many times if I use
> > > >grouping sets less than 7000.
> > 
> > The real solution would probably be doing the initial copy without
> > indexes, especially the PK.
> 
> I did go without indexes for the initial copy but I do keep the PK in
> place since Slony seems to prefer it. It may be appropriate for Slony
> itself to remove and re-add the primary key around the copy,

That's what I had in mind.

> but indexes
> on integer columns don't really get in the way too much (IO was
> maintained at about 20MB/sec for the copy, which was reasonable for a
> busy SAN).

Still it seems, that once the index does not fit in postgresql's shared
memory, it slows down an order of magnitude, even if it still fits in
OS's disk cache. At least this seems to be the case on linux 2.4. 

I was able to speed up the initial copy of a table with 1.2GB index
several times by specifying shared mem of 1GB (instead of previous
256MB).

It may be important that this index was populated in random order, it
was not a PK filled by nextval(seq).

> There are lots of TB+ sized DBs using PostgreSQL out there, and Slony
> needs to be able to deal with them reasonably.

Agreed.

-- 
Hannu Krosing <hannu at skype.net>