Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2232B651734 for ; Thu, 26 Jun 2008 15:43:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 35642-04 for ; Thu, 26 Jun 2008 15:43:44 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.248]) by postgresql.org (Postfix) with ESMTP id D87036519C9 for ; Thu, 26 Jun 2008 15:20:59 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d30so36281and.33 for ; Thu, 26 Jun 2008 11:20:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=DKv6quFCS22pCYOb2lJzmftj8AKBWH+nqzWueCkKsbM=; b=W58WBoNXN6HkF5fwGI+INmmIZ6eUSSPhyiRJBzZYyjiWkp2pAsscJXZFsyOO15Tz23 6TSUJR9eF9d0XtU+P5kkP1v+a7/EbjrRKunI8eV7vtET9PdjZUqfV7ORcJ+muSRmO6th fKDW45R56O/6mUsvjF3y6l50mLARkGNKkth2E= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=JD/InTLt4IzvjZwCpYZZkGzCbFgNBQypOGijMvOyf5UbkLNeJn63pMqPAVmMb9zPJU vyJoNmQ6YqDPU2R/VDikrxZV6HbbIAdk/u0PChMDKN6T8x/SH6chNv2YHA4gDW7sAPM2 fuxmAvIEq3fwiYJajO4LAq6fv/LfuzZhGVjUs= Received: by 10.100.110.16 with SMTP id i16mr376289anc.40.1214504457676; Thu, 26 Jun 2008 11:20:57 -0700 (PDT) Received: by 10.100.190.19 with HTTP; Thu, 26 Jun 2008 11:20:57 -0700 (PDT) Message-ID: <8078a1730806261120v44d222ecx46dd345ca2b184e@mail.gmail.com> Date: Thu, 26 Jun 2008 11:20:57 -0700 From: Montaseri To: "Scott Whitney" Subject: Re: Warm standby server Cc: "Simon Riggs" , pgsql-admin@postgresql.org In-Reply-To: <20080626175743.4E1B33E216E@mail.int.journyx.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_18532_16280171.1214504457671" References: <8078a1730806261051x6befaa27uf3c95affe9c37cc2@mail.gmail.com> <20080626175743.4E1B33E216E@mail.int.journyx.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/226 X-Sequence-Number: 29564 ------=_Part_18532_16280171.1214504457671 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline While I am not an expert on WAL, but again I question the merits of such sophisticated HA configuration. Of course there are use cases for such configs, but I am only advocating best price performance kind of mentality As WAL writes the journals all the way down to the disk (ie write thru and not write behind) before ack-ing toward the next step in a DB operation, increasing the number of mirrors (one production, one on-site, one off-site, I count 3 plexes here) will prolong each operation, with the following exponentially increasing write latencies production DB writes are at the rate of SCSI, SATA or system bus (30 MBps) on-site DB writes are at the rate of LAN (10 MBps) off-site DB writes are at the rate of WAN (200 KBps) Then if a three-way WAL writes is considered completed after the last WAN write, then you have effectively lowered your performance to 200 KBytes per sec writes. Now the gain. If the building gets destroyed, my data is protected. Ok. what kind of business are we running in that building? .... what is the rate of writes to database vs probability of building coming down vs value of data from 2 hours ago vs 10 seconds ago. Thanks Medi On Thu, Jun 26, 2008 at 11:01 AM, Scott Whitney wrote: > Fully agreed, and it's just a concept at the moment. After I have a > prototype standby working next week in the first place, we'll be discussing > those very merits. > > A 2nd question: Is it possible to have 2 standby servers with a single > master duplicating to standby1 (at my coloc), and standby2 (at my office)? > Assume no auto-failover. > > -----Original Message----- > From: Montaseri [mailto:montaseri@gmail.com] > Sent: Jun 26, 2008 12:51 PM > To: Simon Riggs > Cc: Scott Whitney; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Warm standby server > > I am not so sure of this arrangement's mertis > > From HA (High Availability) point of view, the host/server is a single > point > of failure which will bring your entire infrastructure down if any of the > server hardware components fail. > > From Performance point of view, you have increased the load on your server > by 3 folds as all instances would be using your I/O bandwidth to write to > secondary storage > > Given $300 to $400 price of headless servers these days, its much > economical > to split the workload on three boxes > > Cheers > Medi > > > On Thu, Jun 26, 2008 at 9:06 AM, Simon Riggs > wrote: > > > > On Thu, 2008-06-26 at 10:19 -0500, Scott Whitney wrote: > > I've got 3 different database servers (db01, db02 and db03). > > > > I would like to have a WAL standby server that replays logs for > all 3 in > > case one goes down, so I can promote that particular server. > > > > Can I do this by installing 3 separate postmasters on this > machine? > > Obviously, if 2 went down at the same time, I'd have to do some > magic to > > bring up another machine, but I'm not sure that's a concern. > > > Yes, that will work. > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > > > > > ------=_Part_18532_16280171.1214504457671 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline While I am not an expert on WAL, but again I question the merits of such sophisticated HA configuration. Of course there are use cases for such configs, but I am only advocating best price performance kind of mentality

As WAL writes the journals all the way down to the disk (ie write thru and not write behind) before ack-ing toward the next step in a DB operation, increasing the number of mirrors (one production, one on-site, one off-site, I count 3 plexes here) will prolong each operation, with the following exponentially increasing write latencies

production DB writes are at the rate of SCSI, SATA or system bus (30 MBps)
on-site DB writes are at the rate of LAN (10 MBps)
off-site DB writes are at the rate of WAN  (200 KBps)

Then if a three-way WAL writes is considered completed after the last WAN write, then you have effectively lowered your performance to 200 KBytes per sec writes. Now the gain. If the building gets destroyed, my data is protected. Ok. what kind of business are we running in that building? .... what is the rate of writes to database vs probability of building coming down vs value of data from 2 hours ago vs 10 seconds ago.

Thanks
Medi

On Thu, Jun 26, 2008 at 11:01 AM, Scott Whitney <swhitney@journyx.com> wrote:
Fully agreed, and it's just a concept at the moment. After I have a
prototype standby working next week in the first place, we'll be discussing
those very merits.

A 2nd question: Is it possible to have 2 standby servers with a single
master duplicating to standby1 (at my coloc), and standby2 (at my office)?
Assume no auto-failover.

-----Original Message-----
From: Montaseri [mailto:montaseri@gmail.com]
Sent: Jun 26, 2008 12:51 PM
To: Simon Riggs
Cc: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server

I am not so sure of this arrangement's mertis

From HA (High Availability) point of view, the host/server is a single point
of failure which will bring your entire infrastructure down if any of the
server hardware components fail.

From Performance point of view, you have increased the load on your server
by 3 folds as all instances would be using your I/O bandwidth to write to
secondary storage

Given $300 to $400 price of headless servers these days, its much economical
to split the workload on three boxes

Cheers
Medi


On Thu, Jun 26, 2008 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:



       On Thu, 2008-06-26 at 10:19 -0500, Scott Whitney wrote:
       > I've got 3 different database servers (db01, db02 and db03).
       >
       > I would like to have a WAL standby server that replays logs for
all 3 in
       > case one goes down, so I can promote that particular server.
       >
       > Can I do this by installing 3 separate postmasters on this
machine?
       > Obviously, if 2 went down at the same time, I'd have to do some
magic to
       > bring up another machine, but I'm not sure that's a concern.


       Yes, that will work.

       --
        Simon Riggs           www.2ndQuadrant.com
        PostgreSQL Training, Services and Support



       --
       Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
       To make changes to your subscription:
       http://www.postgresql.org/mailpref/pgsql-admin





------=_Part_18532_16280171.1214504457671--