On 9/28/2004 6:45 PM, hannu at skype.net wrote:
> I've got a problem:
> 
> One of my 24/7 databases has access requirements / performance
> constraints, which make it impossible for me to do the standard
> initial hot-join (copy) on some multi-million row tables as it messes
> up pg and os caches in a way that makes standard production queries
> too slow.

Whow, this is the first time I see somebody complaining that the initial 
COPY is working too fast :-)

Anyhow, I do see your problem ... your DB suffers from cache eviction 
due to sequential scan. This should go away once you are on 8.0 with a 
properly configured ARC, but I assume you don't have 8.0BETA in production.

> 
> Is there any way I can either
> 
> 1) make the initial copy less agressive - can I somehow throttle
> it or perform it in smaller chunks ?

The code that does the COPY is in src/slon/remote_worker.c in the 
function copy_set(). What it does is to execute a COPY table TO STDOUT 
on the provider and a COPY table FROM STDIN on the subscriber. Then it 
loops and copies over the data stream. You could try and insert some 
usleep() into that loop.

> 
> or
> 
> 2) avoid it alltogether - could I load regular backup and start
> replication from there - somehow pg_dump seems to do fine ?

No, you can't avoid it. And this is the part or your email that I don't 
understand. How can pg_dump do fine? It should even be worse because it 
doesn't get slowed down by the subscriber building indexes on the fly. 
It is not that both databases are on the same physical box, right?

> 
> btw, runtime replication load is reasonable and does not seem to
> cause any problems.

Good.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #