X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DEC87533ED for ; Tue, 12 Apr 2005 13:53:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59263-10 for ; Tue, 12 Apr 2005 16:53:15 +0000 (GMT) Received: from mail2.dbitech.ca (radius.wavefire.com [64.141.13.252]) by svr1.postgresql.org (Postfix) with SMTP id 7109F533F1 for ; Tue, 12 Apr 2005 13:53:13 -0300 (ADT) Received: (qmail 5848 invoked from network); 12 Apr 2005 18:29:20 -0000 Received: from unknown (HELO ?64.141.15.12?) (64.141.15.12) by radius.wavefire.com with SMTP; 12 Apr 2005 18:29:20 -0000 From: Darcy Buskermolen Organization: Wavefire Technologies Corp To: matt@followers.net Subject: Re: performance hit for replication Date: Tue, 12 Apr 2005 09:54:52 -0700 User-Agent: KMail/1.7.2 Cc: pgsql-performance@postgresql.org References: <425bf664.1fac7adf.258a.5c79@mx.gmail.com> In-Reply-To: <425bf664.1fac7adf.258a.5c79@mx.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200504120954.52701.darcy@wavefire.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.295 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, INFO_TLD X-Spam-Level: X-Archive-Number: 200504/238 X-Sequence-Number: 11635 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