Re: performance hit for replication

On Tuesday 12 April 2005 09:25, Matthew Nuzum wrote:
> I'd like to create a fail-over server in case of a problem. Ideally, it
> would be synchronized with our main database server, but I don't see any
> major problem with having a delay of up to 4 hours between syncs.
>
> My database is a little shy of 10 Gigs, with much of that data being in an
> archived log table. Every day a batch job is run which adds 100,000 records
> over the course of 3 hours (the batch job does a lot of pre/post
> processing).
>
> Doing a restore of the db backup in vmware takes about 3 hours. I suspect a
> powerful server with a better disk setup could do it faster, but I don't
> have servers like that at my disposal, so I need to assume worst-case of
> 3-4 hours is typical.
>
> So, my question is this: My server currently works great, performance wise.
> I need to add fail-over capability, but I'm afraid that introducing a
> stressful task such as replication will hurt my server's performance. Is
> there any foundation to my fears? I don't need to replicate the archived
> log data because I can easily restore that in a separate step from the
> nightly backup if disaster occurs. Also, my database load is largely
> selects. My application works great with PostgreSQL 7.3 and 7.4, but I'm
> currently using 7.3.
>
> I'm eager to hear your thoughts and experiences,

Your application sounds like a perfact candidate for Slony-I
http://www.slony.info . Using Slony-I I see about a 5-7% performance hit in
terms of the number of insert.update/delete per second i can process.

Depending on your network connection , DML volume, and the power of your
backup server, the replica could be as little as 10 seconds behind the
origin. A failover/switchover could occur in under 60 seconds.

--
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759