From lists@on-track.ca Sun Apr  5 18:36:33 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 7AC85633F7B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  5 Apr 2009 15:36:25 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 95219-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  5 Apr 2009 15:36:22 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx1.ctgameinfo.com (eureka.ctgameinfo.com [67.228.20.98])
	by mail.postgresql.org (Postfix) with ESMTP id 4CDE9630733
	for <pgsql-performance@postgresql.org>;
	Sun,  5 Apr 2009 15:36:22 -0300 (ADT)
Received: from [192.168.1.103] (S01060016b606ed02.vc.shawcable.net
	[24.84.173.161])
	by mx1.ctgameinfo.com (Postfix) with ESMTP id 7622F5643F
	for <pgsql-performance@postgresql.org>;
	Sun,  5 Apr 2009 11:36:20 -0700 (PDT)
Message-ID: <49D8FA31.8010407@on-track.ca>
Date: Sun, 05 Apr 2009 11:36:33 -0700
From: Lists <lists@on-track.ca>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Best replication solution?
Content-Type: multipart/alternative;
	boundary="------------030001040307050106080703"
X-Virus-Scanned: ClamAV 0.94.2/9205/Sat Apr 4 20:09:43 2009 on
	eureka.ctgameinfo.com
X-Virus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.9 tagged_above=0 required=5 tests=FS_REPLICA=1.179,
	HTML_MESSAGE=0.001, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/107
X-Sequence-Number: 33474

This is a multi-part message in MIME format.
--------------030001040307050106080703
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I am looking to setup replication of my postgresql database, primarily 
for performance reasons.

The searching I've done shows a lot of different options, can anyone 
give suggestions about which one(s) are best? I've read the archives, 
but there seems to be more replication solutions since the last thread 
on this subject and it seems to change frequently.

I'd really like a solution that replicates DDL, but very few do so I 
think I'm out of luck for that. I can live without it.
Multi-master support would be nice too, but also seems to cause too many 
problems so it looks like I'll have to do without it too.


*Slony-I* - I've used this in the past, but it's a huge pain to work 
with, caused serious performance issues under heavy load due to long 
running transactions (may not be the case anymore, it's been a while 
since I used it on a large database with many writes), and doesn't seem 
very reliable (I've had replication break on me multiple times).

*Mammoth Replicator* - This is open source now, is it any good? It 
sounds like it's trigger based like Slony. Is it based on Slony, or 
simply use a similar solution?

*pgpool* - Won't work for us reliably for replication because we have 
some triggers and stored procedures that write data.

*PGCluster* - Sounds cool, but based on the mailing list traffic and the 
last news post on the site being from 2005, development seems to be near 
dead. Also, no releases seems to make it beyond the RC stage -- for 
multi-master stability is particularly important for data integrity.

*PGReplicator - *Don't know anything special about it.
*
Bucardo* - Don't know anything special about it.

*Postgres-R* - Don't know anything special about it.

*SkyTools/Londiste* - Don't know anything special about it.

--------------030001040307050106080703
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
I am looking to setup replication of my postgresql database, primarily
for performance reasons.<br>
<br>
The searching I've done shows a lot of different options, can anyone
give suggestions about which one(s) are best? I've read the archives,
but there seems to be more replication solutions since the last thread
on this subject and it seems to change frequently. <br>
<br>
I'd really like a solution that replicates DDL, but very few do so I
think I'm out of luck for that. I can live without it. <br>
Multi-master support would be nice too, but also seems to cause too
many problems so it looks like I'll have to do without it too. <br>
<br>
<br>
<b>Slony-I</b> - I've used this in the past, but it's a huge pain to
work with, caused serious performance issues under heavy load due to
long running transactions (may not be the case anymore, it's been a
while since I used it on a large database with many writes), and
doesn't seem very reliable (I've had replication break on me multiple
times).<br>
<br>
<b>Mammoth Replicator</b> - This is open source now, is it any good? It
sounds like it's trigger based like Slony. Is it based on Slony, or
simply use a similar solution?<br>
<br>
<b>pgpool</b> - Won't work for us reliably for replication because we
have some triggers and stored procedures that write data.<br>
<br>
<b>PGCluster</b> - Sounds cool, but based on the mailing list traffic
and the last news post on the site being from 2005, development seems
to be near dead. Also, no releases seems to make it beyond the RC stage
-- for multi-master stability is particularly important for data
integrity.<br>
<br>
<b>PGReplicator - </b>Don't know anything special about it.<br>
<b><br>
Bucardo</b> - Don't know anything special about it.<br>
<br>
<b>Postgres-R</b> - Don't know anything special about it.<br>
<br>
<b>SkyTools/Londiste</b> - Don't know anything special about it.
</body>
</html>

--------------030001040307050106080703--



From lists@on-track.ca Sun Apr  5 21:57:17 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id E7BB4633C39
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  5 Apr 2009 18:57:07 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 99304-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  5 Apr 2009 18:57:05 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx1.ctgameinfo.com (eureka.ctgameinfo.com [67.228.20.98])
	by mail.postgresql.org (Postfix) with ESMTP id 0F617633928
	for <pgsql-performance@postgresql.org>;
	Sun,  5 Apr 2009 18:57:05 -0300 (ADT)
Received: from [192.168.1.103] (S01060016b606ed02.vc.shawcable.net
	[24.84.173.161])
	by mx1.ctgameinfo.com (Postfix) with ESMTP id 674425643F;
	Sun,  5 Apr 2009 14:57:03 -0700 (PDT)
Message-ID: <49D9293D.2020101@on-track.ca>
Date: Sun, 05 Apr 2009 14:57:17 -0700
From: Lists <lists@on-track.ca>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: ries van Twisk <typo3@rvt.dds.nl>
CC: pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
References: <49D8FA31.8010407@on-track.ca>
	<480ED41A-B4AF-4A14-A10B-9F7181D7949D@rvt.dds.nl>
In-Reply-To: <480ED41A-B4AF-4A14-A10B-9F7181D7949D@rvt.dds.nl>
Content-Type: multipart/alternative;
	boundary="------------020007060408090106010004"
X-Virus-Scanned: ClamAV 0.94.2/9206/Sun Apr 5 11:23:11 2009 on
	eureka.ctgameinfo.com
X-Virus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.215 tagged_above=0 required=5
	tests=FS_REPLICA=1.179, HTML_MESSAGE=0.001, SARE_MILLIONSOF=0.315,
	SARE_SPEC_REPLICA=0.72
X-Spam-Level: **
X-Archive-Number: 200904/108
X-Sequence-Number: 33475

This is a multi-part message in MIME format.
--------------020007060408090106010004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I have a high traffic database with high volumes of reads, and moderate 
volumes of writes. Millions of queries a day.

Running the latest version of Postgresql 8.2.x (I want to upgrade to 
8.3, but the dump/reload requires an unacceptable amount of downtime)

Server is a dual core xeon 3GB ram and 2 mirrors of 15k SAS drives (1 
for most data, 1 for wal and a few tables and indexes)

In total all databases on the server are about 10G on disk (about 2GB in 
pgdump format).


The IO on the disks is being maxed out and I don't have the budget to 
add more disks at this time. The web server has a raid10 of sata drives 
with some io bandwidth to spare so I would like to replicate all data 
over, and send some read queries to that server -- in particular the 
very IO intensive FTI based search queries.


ries van Twisk wrote:
> Dr Mr No Name,
>
> what replication solution is the best depends on your requirements.
> May be you can tell a bit more what your situation is?
> Since you didn't gave us to much information about your requirements 
> it's hard to give you any advice.
>
> Ries
>
> On Apr 5, 2009, at 1:36 PM, Lists wrote:
>
>> I am looking to setup replication of my postgresql database, 
>> primarily for performance reasons.
>>
>> The searching I've done shows a lot of different options, can anyone 
>> give suggestions about which one(s) are best? I've read the archives, 
>> but there seems to be more replication solutions since the last 
>> thread on this subject and it seems to change frequently.
>>
>> I'd really like a solution that replicates DDL, but very few do so I 
>> think I'm out of luck for that. I can live without it.
>> Multi-master support would be nice too, but also seems to cause too 
>> many problems so it looks like I'll have to do without it too.
>>
>>
>> *Slony-I* - I've used this in the past, but it's a huge pain to work 
>> with, caused serious performance issues under heavy load due to long 
>> running transactions (may not be the case anymore, it's been a while 
>> since I used it on a large database with many writes), and doesn't 
>> seem very reliable (I've had replication break on me multiple times).
>>
>> *Mammoth Replicator* - This is open source now, is it any good? It 
>> sounds like it's trigger based like Slony. Is it based on Slony, or 
>> simply use a similar solution?
>>
>> *pgpool* - Won't work for us reliably for replication because we have 
>> some triggers and stored procedures that write data.
>>
>> *PGCluster* - Sounds cool, but based on the mailing list traffic and 
>> the last news post on the site being from 2005, development seems to 
>> be near dead. Also, no releases seems to make it beyond the RC stage 
>> -- for multi-master stability is particularly important for data 
>> integrity.
>>
>> *PGReplicator - *Don't know anything special about it.
>> *
>> Bucardo* - Don't know anything special about it.
>>
>> *Postgres-R* - Don't know anything special about it.
>>
>> *SkyTools/Londiste* - Don't know anything special about it.
>
>
>
>
>
>


--------------020007060408090106010004
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
I have a high traffic database with high volumes of reads, and moderate
volumes of writes. Millions of queries a day.<br>
<br>
Running the latest version of Postgresql 8.2.x (I want to upgrade to
8.3, but the dump/reload requires an unacceptable amount of downtime)<br>
<br>
Server is a dual core xeon 3GB ram and 2 mirrors of 15k SAS drives (1
for most data, 1 for wal and a few tables and indexes) <br>
<br>
In total all databases on the server are about 10G on disk (about 2GB
in pgdump format).<br>
<br>
<br>
The IO on the disks is being maxed out and I don't have the budget to
add more disks at this time. The web server has a raid10 of sata drives
with some io bandwidth to spare so I would like to replicate all data
over, and send some read queries to that server -- in particular the
very IO intensive FTI based search queries. <br>
<br>
<br>
ries van Twisk wrote:
<blockquote cite="mid:480ED41A-B4AF-4A14-A10B-9F7181D7949D@rvt.dds.nl"
 type="cite">Dr Mr No Name,
  <div><br>
  </div>
  <div>what replication solution is the best depends on your
requirements.</div>
  <div>May be you can tell a bit more what your situation is?</div>
  <div>Since you didn't gave us to much information about your
requirements it's hard to give you any advice.</div>
  <div><br>
  </div>
  <div>Ries</div>
  <div><br>
  </div>
  <div>
  <div>
  <div>On Apr 5, 2009, at 1:36 PM, Lists wrote:</div>
  <br class="Apple-interchange-newline">
  <blockquote type="cite">
    <div bgcolor="#ffffff" text="#000000"> I am looking to setup
replication of my postgresql database, primarily for performance
reasons.<br>
    <br>
The searching I've done shows a lot of different options, can anyone
give suggestions about which one(s) are best? I've read the archives,
but there seems to be more replication solutions since the last thread
on this subject and it seems to change frequently. <br>
    <br>
I'd really like a solution that replicates DDL, but very few do so I
think I'm out of luck for that. I can live without it. <br>
Multi-master support would be nice too, but also seems to cause too
many problems so it looks like I'll have to do without it too. <br>
    <br>
    <br>
    <b>Slony-I</b> - I've used this in the past, but it's a huge pain
to work with, caused serious performance issues under heavy load due to
long running transactions (may not be the case anymore, it's been a
while since I used it on a large database with many writes), and
doesn't seem very reliable (I've had replication break on me multiple
times).<br>
    <br>
    <b>Mammoth Replicator</b> - This is open source now, is it any
good? It sounds like it's trigger based like Slony. Is it based on
Slony, or simply use a similar solution?<br>
    <br>
    <b>pgpool</b> - Won't work for us reliably for replication because
we have some triggers and stored procedures that write data.<br>
    <br>
    <b>PGCluster</b> - Sounds cool, but based on the mailing list
traffic and the last news post on the site being from 2005, development
seems to be near dead. Also, no releases seems to make it beyond the RC
stage -- for multi-master stability is particularly important for data
integrity.<br>
    <br>
    <b>PGReplicator - </b>Don't know anything special about it.<br>
    <b><br>
Bucardo</b> - Don't know anything special about it.<br>
    <br>
    <b>Postgres-R</b> - Don't know anything special about it.<br>
    <br>
    <b>SkyTools/Londiste</b> - Don't know anything special about it. </div>
  </blockquote>
  </div>
  <br>
  <div apple-content-edited="true"> <span class="Apple-style-span"
 style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
  <div style=""><span class="Apple-style-span"
 style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
  <div style=""><span class="Apple-style-span"
 style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span
 class="Apple-style-span"
 style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
  <div style=""><span class="Apple-style-span"
 style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
  <div style=""><span class="Apple-style-span"
 style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
  <div style=""><span class="Apple-style-span"
 style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
  <div style=""><span class="Apple-style-span"
 style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
  <div style="">
  <div>
  <div style="">
  <div style="">
  <div style="">
  <div style="">
  <div style="">
  <div>
  <div>
  <div>
  <div><br>
  </div>
  </div>
  </div>
  </div>
  </div>
  </div>
  </div>
  </div>
  </div>
  </div>
  </div>
  </span></div>
  </span></div>
  </span></div>
  </span><br class="Apple-interchange-newline">
  </div>
  </span><br class="Apple-interchange-newline">
  </span><br class="Apple-interchange-newline">
  </div>
  </span></div>
  </span> </div>
  <br>
  </div>
</blockquote>
<br>
</body>
</html>

--------------020007060408090106010004--



From greg@turnstep.com Mon Apr  6 02:11:21 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id E7DB76335EC
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  5 Apr 2009 23:11:38 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 19236-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  5 Apr 2009 23:11:28 -0300 (ADT)
Received: from mx1.hub.org (mx1.hub.org [200.46.208.106])
	by mail.postgresql.org (Postfix) with ESMTP id 45C71633103
	for <pgsql-performance@postgresql.org>;
	Sun,  5 Apr 2009 23:11:37 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from tinlc.com (unknown [72.18.206.65])
	by mx1.hub.org (Postfix) with ESMTP id D76E4125B0A2
	for <pgsql-performance@postgresql.org>;
	Sun,  5 Apr 2009 23:11:36 -0300 (ADT)
Received: from biglumber.com ([72.18.206.64] helo=localhost)
	by tinlc.com with smtp (Exim 4.60)
	(envelope-from <greg@turnstep.com>)
	id 1LqeIz-0003wH-Nx; Sun, 05 Apr 2009 21:11:21 -0500
From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
X-PGP-Key: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8
X-Request-PGP: 
 http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
In-Reply-To: <49D9293D.2020101@on-track.ca>
Date: Mon,  6 Apr 2009 02:11:21 -0000
X-Mailer: JoyMail 2.01
Message-ID: <b8fffafc4ac92b027368d9b50db4eea5@biglumber.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.971 tagged_above=0 required=5 tests=AWL=-0.928,
	FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: 
X-Archive-Number: 200904/115
X-Sequence-Number: 33482


-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Running the latest version of Postgresql 8.2.x (I want to upgrade to
> 8.3, but the dump/reload requires an unacceptable amount of downtime)

You can use Slony or Bucardo to ugrade in place. Both will incur some
overhead and more overall complexity than a dump/reload, but going to
8.3 is well worth it (and will bring your IO down).

> The IO on the disks is being maxed out and I don't have the budget to
> add more disks at this time. The web server has a raid10 of sata drives
> with some io bandwidth to spare so I would like to replicate all data
> over, and send some read queries to that server -- in particular the
> very IO intensive FTI based search queries.

Sounds like a good solution for a table-based, read-only-slaves solutions,
especially if you only need enough of the schema to perform some of the
more intense queries. Again, Slony and Bucardo are probably the best fit.
All this assumes that the tables in question have some sort of unique key,
you aren't using large objects, or changing DDL frequently. I'd give Slony a
second try and Bucardo a first one on your QA/test cluster and see how
they work out for you. You could even make the read-only slaves 8.3, since
they will be starting from scratch.

Of course, if the underlying problem replication is trying to solve is too
much search traffic (e.g. select queries) on the main database, there are other
solutions you could consider (e.g. external search such as Sphinx or SOLR,
caching solutions such as Squid or Varnish, moving the slaves to the cloud, etc.)

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200904052158
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAknZZMgACgkQvJuQZxSWSsjbcgCfWqTUEDGlDqAnLaCAhcJlSLCk
EVMAni0oCevrnMdZ2Fuw8Tysaxp3q+/U
=0vu6
-----END PGP SIGNATURE-----





From heikki.linnakangas@enterprisedb.com Mon Apr  6 08:26:50 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id ABCC8632886
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  6 Apr 2009 05:27:04 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 58925-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  6 Apr 2009 05:26:59 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from exprod7og107.obsmtp.com (exprod7og107.obsmtp.com [64.18.2.167])
	by mail.postgresql.org (Postfix) with SMTP id 66E3C632585
	for <pgsql-performance@postgresql.org>;
	Mon,  6 Apr 2009 05:26:59 -0300 (ADT)
Received: from source ([209.85.219.174]) by exprod7ob107.postini.com
	([64.18.6.12]) with SMTP
	ID DSNKSdm80DxWwhwqx1zR2oO3if/P9f9XMPgh@postini.com;
	Mon, 06 Apr 2009 01:26:59 PDT
Received: by ewy22 with SMTP id 22so1714956ewy.35
	for <pgsql-performance@postgresql.org>;
	Mon, 06 Apr 2009 01:26:56 -0700 (PDT)
Received: by 10.210.141.17 with SMTP id o17mr2728965ebd.29.1239006416134;
	Mon, 06 Apr 2009 01:26:56 -0700 (PDT)
Received: from ?80.223.212.7? (dsl-hkibrasgw2-fed4df00-7.dhcp.inet.fi
	[80.223.212.7])
	by mx.google.com with ESMTPS id 10sm1237254eyz.39.2009.04.06.01.26.55
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Mon, 06 Apr 2009 01:26:55 -0700 (PDT)
Message-ID: <49D9BCCA.7060609@enterprisedb.com>
Date: Mon, 06 Apr 2009 11:26:50 +0300
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.19 (X11/20090103)
MIME-Version: 1.0
To: Lists <lists@on-track.ca>
CC: ries van Twisk <typo3@rvt.dds.nl>, pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
References: <49D8FA31.8010407@on-track.ca>
	<480ED41A-B4AF-4A14-A10B-9F7181D7949D@rvt.dds.nl>
	<49D9293D.2020101@on-track.ca>
In-Reply-To: <49D9293D.2020101@on-track.ca>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200904/118
X-Sequence-Number: 33485

Lists wrote:
> Server is a dual core xeon 3GB ram and 2 mirrors of 15k SAS drives (1 
> for most data, 1 for wal and a few tables and indexes)
> 
> In total all databases on the server are about 10G on disk (about 2GB in 
> pgdump format).

I'd suggest buying as much RAM as you can fit into the server. RAM is 
cheap, and with a database of that size more cache could have a dramatic 
effect.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com



From ajs@crankycanuck.ca Mon Apr  6 12:35:30 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id E22E8632BA6
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  6 Apr 2009 09:41:53 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 18804-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  6 Apr 2009 09:41:52 -0300 (ADT)
X-Greylist: delayed 00:06:17.789758 by SQLgrey-1.7.6
Received: from mail.yitter.info (mail.yitter.info [208.86.224.201])
	by mail.postgresql.org (Postfix) with ESMTP id 54C51632B61
	for <pgsql-performance@postgresql.org>;
	Mon,  6 Apr 2009 09:41:52 -0300 (ADT)
Received: from crankycanuck.ca (69-196-144-230.dsl.teksavvy.com
	[69.196.144.230])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.yitter.info (Postfix) with ESMTPSA id 309CC2FEA05E
	for <pgsql-performance@postgresql.org>;
	Mon,  6 Apr 2009 12:35:32 +0000 (UTC)
Date: Mon, 6 Apr 2009 08:35:30 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
Message-ID: <20090406123529.GA14675@shinkuro.com>
Mail-Followup-To: Andrew Sullivan <ajs@crankycanuck.ca>,
	pgsql-performance@postgresql.org
References: <49D8FA31.8010407@on-track.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <49D8FA31.8010407@on-track.ca>
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.899 tagged_above=0 required=5
	tests=FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/125
X-Sequence-Number: 33492

On Sun, Apr 05, 2009 at 11:36:33AM -0700, Lists wrote:

> *Slony-I* - I've used this in the past, but it's a huge pain to work  
> with, caused serious performance issues under heavy load due to long  
> running transactions (may not be the case anymore, it's been a while  
> since I used it on a large database with many writes), and doesn't seem  
> very reliable (I've had replication break on me multiple times).

It is indeed a pain to work with, but I find it hard to believe that
it is the actual source of performance issues.  What's more likely
true is that it wasn't tuned to your write load -- that _will_ cause
performance issues.  Of course, tuning it is a major pain, as
mentioned.  I'm also somewhat puzzled by the claim of unreliability:
most of the actual replication failures I've ever seen under Slony are
due to operator error (these are trivial to induce, alas --
aforementioned pain to work with again).  Slony is baroque and
confusing, but it's specifically designed to fail in safe ways (which
is not true of some of the other systems: several of them have modes
in which it's possible to have systems out of sync with each other,
but with no way to detect as much.  IMO, that's much worse, so we
designed Slony to fail noisily if it was going to fail at all).  

> *Mammoth Replicator* - This is open source now, is it any good? It  
> sounds like it's trigger based like Slony. Is it based on Slony, or  
> simply use a similar solution?

It's completely unrelated, and it doesn't use triggers.  I think the
people programming it are first-rate.  Last I looked at it, I felt a
little uncomfortable with certain design choices, which seemed to me
to be a little hacky.  They were all on the TODO list, though.

> *SkyTools/Londiste* - Don't know anything special about it.

I've been quite impressed by the usability.  It's not quite as
flexible as Slony, but it has the same theory of operation.  The
documentation is not as voluminous, although it's also much handier as
reference material than Slony's (which is, in my experience, a little
hard to navigate if you don't already know the system pretty well).

A

-- 
Andrew Sullivan
ajs@crankycanuck.ca



From lists@on-track.ca Tue Apr  7 03:44:20 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 2836D6336AE
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 00:44:19 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 58039-10
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 00:44:04 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx1.ctgameinfo.com (eureka.ctgameinfo.com [67.228.20.98])
	by mail.postgresql.org (Postfix) with ESMTP id A3D59632B9D
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 00:44:04 -0300 (ADT)
Received: from [192.168.1.103] (S01060016b606ed02.vc.shawcable.net
	[24.84.173.161])
	by mx1.ctgameinfo.com (Postfix) with ESMTP id 1E6275645D;
	Mon,  6 Apr 2009 20:44:03 -0700 (PDT)
Message-ID: <49DACC14.3050601@on-track.ca>
Date: Mon, 06 Apr 2009 20:44:20 -0700
From: Lists <lists@on-track.ca>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
CC: ries van Twisk <typo3@rvt.dds.nl>, pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
References: <49D8FA31.8010407@on-track.ca>
	<480ED41A-B4AF-4A14-A10B-9F7181D7949D@rvt.dds.nl>
	<49D9293D.2020101@on-track.ca> <49D9BCCA.7060609@enterprisedb.com>
In-Reply-To: <49D9BCCA.7060609@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: ClamAV 0.94.2/9209/Mon Apr 6 10:51:36 2009 on
	eureka.ctgameinfo.com
X-Virus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.899 tagged_above=0 required=5
	tests=FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/134
X-Sequence-Number: 33501

I'm currently running 32bit FreeBSD so I can't really add more ram (PAE 
doesn't work well under FreeBSD from what I've read) and there are 
enough writes that more ram won't solve the problem completely.

However I will add plenty more ram next time I rebuild it.


Heikki Linnakangas wrote:
> Lists wrote:
>> Server is a dual core xeon 3GB ram and 2 mirrors of 15k SAS drives (1 
>> for most data, 1 for wal and a few tables and indexes)
>>
>> In total all databases on the server are about 10G on disk (about 2GB 
>> in pgdump format).
>
> I'd suggest buying as much RAM as you can fit into the server. RAM is 
> cheap, and with a database of that size more cache could have a 
> dramatic effect.
>




From lists@on-track.ca Tue Apr  7 04:07:05 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 7C8936334F4
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 01:06:53 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 85089-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 01:06:49 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mx1.ctgameinfo.com (eureka.ctgameinfo.com [67.228.20.98])
	by mail.postgresql.org (Postfix) with ESMTP id B447B6330FF
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 01:06:48 -0300 (ADT)
Received: from [192.168.1.103] (S01060016b606ed02.vc.shawcable.net
	[24.84.173.161])
	by mx1.ctgameinfo.com (Postfix) with ESMTP id 0E4155647D;
	Mon,  6 Apr 2009 21:06:46 -0700 (PDT)
Message-ID: <49DAD169.2000707@on-track.ca>
Date: Mon, 06 Apr 2009 21:07:05 -0700
From: Lists <lists@on-track.ca>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: Andrew Sullivan <ajs@crankycanuck.ca>,
 pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
References: <49D8FA31.8010407@on-track.ca>
	<20090406123529.GA14675@shinkuro.com>
In-Reply-To: <20090406123529.GA14675@shinkuro.com>
Content-Type: multipart/alternative;
	boundary="------------060705000706040405080307"
X-Virus-Scanned: ClamAV 0.94.2/9209/Mon Apr 6 10:51:36 2009 on
	eureka.ctgameinfo.com
X-Virus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.9 tagged_above=0 required=5 tests=FS_REPLICA=1.179,
	HTML_MESSAGE=0.001, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/135
X-Sequence-Number: 33502

This is a multi-part message in MIME format.
--------------060705000706040405080307
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andrew Sullivan wrote:
> On Sun, Apr 05, 2009 at 11:36:33AM -0700, Lists wrote:
>
>   
>> *Slony-I* - I've used this in the past, but it's a huge pain to work  
>> with, caused serious performance issues under heavy load due to long  
>> running transactions (may not be the case anymore, it's been a while  
>> since I used it on a large database with many writes), and doesn't seem  
>> very reliable (I've had replication break on me multiple times).
>>     
>
> It is indeed a pain to work with, but I find it hard to believe that
> it is the actual source of performance issues.  What's more likely
> true is that it wasn't tuned to your write load -- that _will_ cause
> performance issues.  
Can you point me in the direction of the documentation for tuning it? I 
don't see anything in the documentation for tuning for write load.

> Of course, tuning it is a major pain, as
> mentioned.  I'm also somewhat puzzled by the claim of unreliability:
> most of the actual replication failures I've ever seen under Slony are
> due to operator error (these are trivial to induce, alas --
> aforementioned pain to work with again).  
Recently I had a problem with "duplicate key" errors on the slave, which 
shouldn't be possible since they keys are the same.
I've just noticed in the documentation that

    The Duplicate Key Violation
    <http://www.slony.info/documentation/faq.html#DUPKEY> bug has helped
    track down a number of rather obscure PostgreSQL race conditions, so
    that in modern versions of Slony-I and PostgreSQL, there should be
    little to worry about.

so that may no longer be an issue. However I experienced with this the 
latest Slony (as of late last year) and Postgresql 8.3.

Also the dupe key error linked appears to be duplicate key of slony 
meta-data were as this was a duplicate key of one of my table's primary 
key.
> Slony is baroque and
> confusing, but it's specifically designed to fail in safe ways (which
> is not true of some of the other systems: several of them have modes
> in which it's possible to have systems out of sync with each other,
> but with no way to detect as much.  IMO, that's much worse, so we
> designed Slony to fail noisily if it was going to fail at all).  
>   
An error is better than silently failing, but of course neither is optimal.

The slony project could really benefit from a simpler user interface and 
simpler documentation. It's integration into pgadminIII is a good step, 
but even with that it is still a bit of a pain so I hope it continues to 
improve in ease of use.

Being powerful and flexable is good, but ease of use with sensible 
defaults for complex items that can be easily overridden is even better.

>   
>> *Mammoth Replicator* - This is open source now, is it any good? It  
>> sounds like it's trigger based like Slony. Is it based on Slony, or  
>> simply use a similar solution?
>>     
>
> It's completely unrelated, and it doesn't use triggers.  I think the
> people programming it are first-rate.  Last I looked at it, I felt a
> little uncomfortable with certain design choices, which seemed to me
> to be a little hacky.  They were all on the TODO list, though.
>
>   
>> *SkyTools/Londiste* - Don't know anything special about it.
>>     
>
> I've been quite impressed by the usability.  It's not quite as
> flexible as Slony, but it has the same theory of operation.  The
> documentation is not as voluminous, although it's also much handier as
> reference material than Slony's (which is, in my experience, a little
> hard to navigate if you don't already know the system pretty well).
>
> A
>
>   
Thanks, I'll look into both of those as well.


--------------060705000706040405080307
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Andrew Sullivan wrote:
<blockquote cite="mid:20090406123529.GA14675@shinkuro.com" type="cite">
  <pre wrap="">On Sun, Apr 05, 2009 at 11:36:33AM -0700, Lists wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">*Slony-I* - I've used this in the past, but it's a huge pain to work  
with, caused serious performance issues under heavy load due to long  
running transactions (may not be the case anymore, it's been a while  
since I used it on a large database with many writes), and doesn't seem  
very reliable (I've had replication break on me multiple times).
    </pre>
  </blockquote>
  <pre wrap=""><!---->
It is indeed a pain to work with, but I find it hard to believe that
it is the actual source of performance issues.  What's more likely
true is that it wasn't tuned to your write load -- that _will_ cause
performance issues.  </pre>
</blockquote>
Can you point me in the direction of the documentation for tuning it? I
don't see anything in the documentation for tuning for write load.<br>
<br>
<blockquote cite="mid:20090406123529.GA14675@shinkuro.com" type="cite">
  <pre wrap="">Of course, tuning it is a major pain, as
mentioned.  I'm also somewhat puzzled by the claim of unreliability:
most of the actual replication failures I've ever seen under Slony are
due to operator error (these are trivial to induce, alas --
aforementioned pain to work with again).  </pre>
</blockquote>
Recently I had a problem with "duplicate key" errors on the slave,
which shouldn't be possible since they keys are the same. <br>
I've just noticed in the documentation that <br>
<blockquote>The <a
 href="http://www.slony.info/documentation/faq.html#DUPKEY"> Duplicate
Key Violation</a> bug has helped track down a number of rather obscure
  <span class="PRODUCTNAME">PostgreSQL</span> race conditions, so that
in modern versions of <span class="PRODUCTNAME">Slony-I</span> and <span
 class="PRODUCTNAME">PostgreSQL</span>, there should be little to worry
about.<br>
</blockquote>
so that may no longer be an issue. However I experienced with this the
latest Slony (as of late last year) and Postgresql 8.3. <br>
<br>
Also the dupe key error linked appears to be duplicate key of slony
meta-data were as this was a duplicate key of one of my table's primary
key. <br>
<blockquote cite="mid:20090406123529.GA14675@shinkuro.com" type="cite">
  <pre wrap="">Slony is baroque and
confusing, but it's specifically designed to fail in safe ways (which
is not true of some of the other systems: several of them have modes
in which it's possible to have systems out of sync with each other,
but with no way to detect as much.  IMO, that's much worse, so we
designed Slony to fail noisily if it was going to fail at all).  
  </pre>
</blockquote>
An error is better than silently failing, but of course neither is
optimal. <br>
<br>
The slony project could really benefit from a simpler user interface
and simpler documentation. It's integration into pgadminIII is a good
step, but even with that it is still a bit of a pain so I hope it
continues to improve in ease of use. <br>
<br>
Being powerful and flexable is good, but ease of use with sensible
defaults for complex items that can be easily overridden is even
better. <br>
<br>
<blockquote cite="mid:20090406123529.GA14675@shinkuro.com" type="cite">
  <pre wrap="">
  </pre>
  <blockquote type="cite">
    <pre wrap="">*Mammoth Replicator* - This is open source now, is it any good? It  
sounds like it's trigger based like Slony. Is it based on Slony, or  
simply use a similar solution?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
It's completely unrelated, and it doesn't use triggers.  I think the
people programming it are first-rate.  Last I looked at it, I felt a
little uncomfortable with certain design choices, which seemed to me
to be a little hacky.  They were all on the TODO list, though.

  </pre>
  <blockquote type="cite">
    <pre wrap="">*SkyTools/Londiste* - Don't know anything special about it.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
I've been quite impressed by the usability.  It's not quite as
flexible as Slony, but it has the same theory of operation.  The
documentation is not as voluminous, although it's also much handier as
reference material than Slony's (which is, in my experience, a little
hard to navigate if you don't already know the system pretty well).

A

  </pre>
</blockquote>
Thanks, I'll look into both of those as well. <br>
<br>
</body>
</html>

--------------060705000706040405080307--



From dfontaine@hi-media.com Tue Apr  7 09:35:54 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 46FD66336AD
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 06:36:29 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 22320-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 06:36:27 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp1.hi-media-techno.com (smtp1.hi-media-techno.com
	[195.158.241.85])
	by mail.postgresql.org (Postfix) with ESMTP id B83586322EF
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 06:36:27 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by smtp1.hi-media-techno.com (Postfix) with ESMTP id 24BD293D54;
	Tue,  7 Apr 2009 11:36:25 +0200 (CEST)
X-Virus-Scanned: Debian amavisd-new at smtp1.backbone.hi-media-techno.com
Received: from smtp1.hi-media-techno.com ([127.0.0.1])
	by localhost (smtp1.backbone.hi-media-techno.com [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id 2v+5CL5ZbfDH; Tue,  7 Apr 2009 11:36:17 +0200 (CEST)
Received: from mail1.hi-media-techno.com (unknown [192.168.27.12])
	by smtp1.hi-media-techno.com (Postfix) with ESMTP id 8F6A23863C2;
	Tue,  7 Apr 2009 11:36:15 +0200 (CEST)
Received: from localhost (localhost [127.0.0.1])
	by mail1.hi-media-techno.com (Postfix) with ESMTP id 894796D87FC;
	Tue,  7 Apr 2009 11:36:15 +0200 (CEST)
X-Virus-Scanned: amavisd-new at 
Received: from mail1.hi-media-techno.com ([127.0.0.1])
	by localhost (mail1.hi-media-techno.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id W192q+1Z3f9U; Tue,  7 Apr 2009 11:36:15 +0200 (CEST)
Received: from dim.localnet (unknown [83.167.62.196])
	by mail1.hi-media-techno.com (Postfix) with ESMTP id 502866D87FB;
	Tue,  7 Apr 2009 11:36:15 +0200 (CEST)
From: Dimitri Fontaine <dfontaine@hi-media.com>
Organization: Hi-Media
To: pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
Date: Tue, 7 Apr 2009 11:35:54 +0200
User-Agent: KMail/1.11.0 (Linux/2.6.26-1-686; KDE/4.2.0; i686; ; )
Cc: Andrew Sullivan <ajs@crankycanuck.ca>
References: <49D8FA31.8010407@on-track.ca>
	<20090406123529.GA14675@shinkuro.com>
In-Reply-To: <20090406123529.GA14675@shinkuro.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart4567879.s55gMmaIsG";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200904071135.54970.dfontaine@hi-media.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.899 tagged_above=0 required=5
	tests=FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/136
X-Sequence-Number: 33503

--nextPart4567879.s55gMmaIsG
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On Monday 06 April 2009 14:35:30 Andrew Sullivan wrote:
> > *SkyTools/Londiste* - Don't know anything special about it.
>
> I've been quite impressed by the usability.  It's not quite as
> flexible as Slony, but it has the same theory of operation.  The
> documentation is not as voluminous, although it's also much handier as
> reference material than Slony's (which is, in my experience, a little
> hard to navigate if you don't already know the system pretty well).

As a londiste user I find it really trustworthy solution, and very easy to =
use=20
and understand. We made some recent efforts on documentation front:
  http://wiki.postgresql.org/wiki/SkyTools
  http://wiki.postgresql.org/wiki/Londiste_Tutorial

Regards,
=2D-=20
dim

--nextPart4567879.s55gMmaIsG
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAknbHnoACgkQlBXRlnbh1bmzfQCfQs9iQ/zLzSIi3coavD+S7QdG
j7QAn1RcOP+3ldXOoyYQSghfTCDyrych
=eGYR
-----END PGP SIGNATURE-----

--nextPart4567879.s55gMmaIsG--



From ivoras@freebsd.org Tue Apr  7 10:11:00 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id DD3B6633D5B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 07:11:25 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 62677-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 07:11:24 -0300 (ADT)
Received: from mx1.hub.org (mx1.hub.org [200.46.208.106])
	by mail.postgresql.org (Postfix) with ESMTP id 61062633BF2
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 07:11:24 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from ciao.gmane.org (main.gmane.org [80.91.229.2])
	by mx1.hub.org (Postfix) with ESMTP id F0A1E125A17E
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 07:11:23 -0300 (ADT)
Received: from list by ciao.gmane.org with local (Exim 4.43)
	id 1Lr8Gv-00077E-8Z
	for pgsql-performance@postgresql.org; Tue, 07 Apr 2009 10:11:13 +0000
Received: from lara.cc.fer.hr ([161.53.72.113])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-performance@postgresql.org>; Tue, 07 Apr 2009 10:11:13 +0000
Received: from ivoras by lara.cc.fer.hr with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-performance@postgresql.org>; Tue, 07 Apr 2009 10:11:13 +0000
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: Best replication solution?
Date: Tue, 07 Apr 2009 12:11:00 +0200
Lines: 34
Message-ID: <grf8rj$6mo$1@ger.gmane.org>
References: <49D8FA31.8010407@on-track.ca>
	<480ED41A-B4AF-4A14-A10B-9F7181D7949D@rvt.dds.nl>
	<49D9293D.2020101@on-track.ca> <49D9BCCA.7060609@enterprisedb.com>
	<49DACC14.3050601@on-track.ca>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig6A85EC0F96BD59BB35565F7D"
X-Complaints-To: usenet@ger.gmane.org
X-Gmane-NNTP-Posting-Host: lara.cc.fer.hr
User-Agent: Thunderbird 2.0.0.21 (X11/20090318)
In-Reply-To: <49DACC14.3050601@on-track.ca>
X-Enigmail-Version: 0.95.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.899 tagged_above=0 required=5
	tests=FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/137
X-Sequence-Number: 33504

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig6A85EC0F96BD59BB35565F7D
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Lists wrote:
> I'm currently running 32bit FreeBSD so I can't really add more ram (PAE=

> doesn't work well under FreeBSD from what I've read)=20

That's probably left-over from the time many drivers were not 64-bit
friendly. I've yet to see a new configuration that doesn't work with PAE
(also, the default "PAE" configuration file is too conservative. Drivers
that work on amd64 should work on PAE without problems). In any case,
it's easy to try it - you can always boot the kernel.old.




--------------enig6A85EC0F96BD59BB35565F7D
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJ2ya0ldnAQVacBcgRAvxhAJ9NTVtwSYDAhPJG/gxVpCsOmWHt7ACbBa/I
s7Rg6rkAnwlY5MSqfDaL/c8=
=UxsW
-----END PGP SIGNATURE-----

--------------enig6A85EC0F96BD59BB35565F7D--




From markir@paradise.net.nz Tue Apr  7 10:31:02 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id DE494633FE8
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 07:31:14 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 91479-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 07:31:02 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp4.clear.net.nz (smtp4.clear.net.nz [203.97.37.64])
	by mail.postgresql.org (Postfix) with ESMTP id 2CE5B633AFD
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 07:31:08 -0300 (ADT)
Received: from zmori.markir.net
	(121-73-162-246.dsl.telstraclear.net [121.73.162.246])
	by smtp4.clear.net.nz (CLEAR Net Mail)
	with ESMTP id <0KHQ004L87VRFK10@smtp4.clear.net.nz> for
	pgsql-performance@postgresql.org; Tue, 07 Apr 2009 22:31:05 +1200 (NZST)
Date: Tue, 07 Apr 2009 22:31:02 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Best replication solution?
In-reply-to: <20090406123529.GA14675@shinkuro.com>
To: Andrew Sullivan <ajs@crankycanuck.ca>, pgsql-performance@postgresql.org
Message-id: <49DB2B66.1080801@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <49D8FA31.8010407@on-track.ca>
	<20090406123529.GA14675@shinkuro.com>
User-Agent: Thunderbird 2.0.0.19 (X11/20090117)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.508 tagged_above=0 required=5 tests=AWL=-1.508,
	FS_REPLICA=1.179, RCVD_IN_SORBS_WEB=1.117, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/138
X-Sequence-Number: 33505

Andrew Sullivan wrote:
> On Sun, Apr 05, 2009 at 11:36:33AM -0700, Lists wrote:
>
>   
>> *Slony-I* - I've used this in the past, but it's a huge pain to work  
>> with, caused serious performance issues under heavy load due to long  
>> running transactions (may not be the case anymore, it's been a while  
>> since I used it on a large database with many writes), and doesn't seem  
>> very reliable (I've had replication break on me multiple times).
>>     
>
>  
> I'm also somewhat puzzled by the claim of unreliability:
> most of the actual replication failures I've ever seen under Slony are
> due to operator error (these are trivial to induce, alas --
> aforementioned pain to work with again).  Slony is baroque and
> confusing, but it's specifically designed to fail in safe ways (which
> is not true of some of the other systems: several of them have modes
> in which it's possible to have systems out of sync with each other,
> but with no way to detect as much.  IMO, that's much worse, so we
> designed Slony to fail noisily if it was going to fail at all).  
>
>   

 From my experience - gained from unwittingly being in the wrong place 
at the wrong time and so being volunteered into helping people with 
Slony failures - it seems to be quite possible to have nodes out of sync 
and not be entirely aware of it - in addition to there being numerous 
ways to shoot yourself in the foot via operator error. Complexity seems 
to be the major evil here.

I've briefly experimented with Londiste, and it is certainly much 
simpler to administer. Currently it lacks a couple of features Slony has 
(chained slaves and partial DDL support), but I'll be following its 
development closely - because if these can be added - whilst keeping the 
operator overhead (and the foot-gun) small, then this looks like a winner.

regards

Mark




From ajs@crankycanuck.ca Tue Apr  7 16:21:18 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 219FD6330FF
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 13:21:34 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 47954-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 13:21:28 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.yitter.info (mail.yitter.info [208.86.224.201])
	by mail.postgresql.org (Postfix) with ESMTP id 478F1632CA7
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 13:21:27 -0300 (ADT)
Received: from crankycanuck.ca (69-196-144-230.dsl.teksavvy.com
	[69.196.144.230])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.yitter.info (Postfix) with ESMTPSA id 497F12FEA05E
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 16:21:25 +0000 (UTC)
Date: Tue, 7 Apr 2009 12:21:18 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
Message-ID: <20090407162118.GP14950@shinkuro.com>
Mail-Followup-To: Andrew Sullivan <ajs@crankycanuck.ca>,
	pgsql-performance@postgresql.org
References: <49D8FA31.8010407@on-track.ca>
	<20090406123529.GA14675@shinkuro.com>
	<49DAD169.2000707@on-track.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <49DAD169.2000707@on-track.ca>
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.899 tagged_above=0 required=5
	tests=FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/144
X-Sequence-Number: 33511

On Mon, Apr 06, 2009 at 09:07:05PM -0700, Lists wrote:

> Can you point me in the direction of the documentation for tuning it? I  
> don't see anything in the documentation for tuning for write load.

No, exactly.  As I said, it's a pain.  The main thing you need to do
is to make sure that your set size is just right for your workload.
The only way to get this right, unhappily, is trial and error and a
bunch of oral-tradition rules of thumb.  It's one of the weakest parts
of Slony from a user's point of view, IMO, but nobody's ever offered
to do the work to make really good tuning tools.

> Recently I had a problem with "duplicate key" errors on the slave, which  
> shouldn't be possible since they keys are the same.
> I've just noticed in the documentation that
>
>    The Duplicate Key Violation
>    <http://www.slony.info/documentation/faq.html#DUPKEY> bug has helped
>    track down a number of rather obscure PostgreSQL race conditions, so
>    that in modern versions of Slony-I and PostgreSQL, there should be
>    little to worry about.
>
> so that may no longer be an issue. However I experienced with this the  
> latest Slony (as of late last year) and Postgresql 8.3.

That problem was quite an old one.  "8.3" doesn't tell me very much,
but the issues should be covered there anyway.  It is of course
logically possible that there is a bug.  Often, however, the duplicate
key violations I've seen turn out to be from operator error.  There
are a _lot_ of sharp, pointy bits in Slony administration, and it's
nearly trivial to make an apparently innocuous error that causes you
this sort of big pain.

> Also the dupe key error linked appears to be duplicate key of slony  
> meta-data were as this was a duplicate key of one of my table's primary  
> key.

This really ought to be impossible -- Slony just speaks standard SQL
statements between nodes.  But I won't say there's no possible bug
there.  Your best bet is the Slony list.  It's a smallish community,
though, so you don't always get the response as fast as you want.  

A
-- 
Andrew Sullivan
ajs@crankycanuck.ca



From ajs@crankycanuck.ca Tue Apr  7 17:18:04 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id B48D7633FFC
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 14:18:10 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 07668-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Apr 2009 14:18:09 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.yitter.info (mail.yitter.info [208.86.224.201])
	by mail.postgresql.org (Postfix) with ESMTP id 6B8F8633FA4
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 14:18:07 -0300 (ADT)
Received: from crankycanuck.ca (69-196-144-230.dsl.teksavvy.com
	[69.196.144.230])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.yitter.info (Postfix) with ESMTPSA id C9E222FEA05E
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Apr 2009 17:18:05 +0000 (UTC)
Date: Tue, 7 Apr 2009 13:18:04 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
Message-ID: <20090407171803.GQ14950@shinkuro.com>
Mail-Followup-To: Andrew Sullivan <ajs@crankycanuck.ca>,
	pgsql-performance@postgresql.org
References: <49D8FA31.8010407@on-track.ca>
	<20090406123529.GA14675@shinkuro.com>
	<49DB2B66.1080801@paradise.net.nz>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <49DB2B66.1080801@paradise.net.nz>
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.899 tagged_above=0 required=5
	tests=FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/146
X-Sequence-Number: 33513

On Tue, Apr 07, 2009 at 10:31:02PM +1200, Mark Kirkwood wrote:
>
> From my experience - gained from unwittingly being in the wrong place at 
> the wrong time and so being volunteered into helping people with Slony 
> failures - it seems to be quite possible to have nodes out of sync and 
> not be entirely aware of it 

I should have stated that differently.  First, you're right that if
you don't know where to look or what to look for, you can easily be
unaware of nodes being out of sync.  What's not a problem with Slony
is that the nodes can get out of internally consistent sync state: if
you have a node that is badly lagged, at least it represents, for
sure, an actual point in time of the origin set's history.  Some of
the replication systems aren't as careful about this, and it's
possible to get the replica into a state that never happened on the
origin.  That's much worse, in my view.

In addition, it is not possible that Slony's system tables report the
replica as being up to date without them actually being so, because
the system tables are updated in the same transaction as the data is
sent.  It's hard to read those tables, however, because you have to
check every node and understand all the states.

>  Complexity seems to be the major evil here.

Yes.  Slony is massively complex.

> simpler to administer. Currently it lacks a couple of features Slony has  
> (chained slaves and partial DDL support), but I'll be following its  
> development closely - because if these can be added - whilst keeping the  
> operator overhead (and the foot-gun) small, then this looks like a 
> winner.

Well, those particular features -- which are indeed the source of much
of the complexity in Slony -- were planned in from the beginning.
Londiste aimed to be simpler, so it would be interesting to see
whether those features could be incorporated without the same
complication.

A

-- 
Andrew Sullivan
ajs@crankycanuck.ca



From markir@paradise.net.nz Wed Apr  8 07:15:28 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id DE0F0633662
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 04:15:39 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 50507-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 04:15:32 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp3.clear.net.nz (smtp3.clear.net.nz [203.97.33.64])
	by mail.postgresql.org (Postfix) with ESMTP id 1BF73633377
	for <pgsql-performance@postgresql.org>;
	Wed,  8 Apr 2009 04:15:31 -0300 (ADT)
Received: from zmori.markir.net
	(121-73-184-32.dsl.telstraclear.net [121.73.184.32])
	by smtp3.clear.net.nz (CLEAR Net Mail)
	with ESMTP id <0KHR00ACDTHSD630@smtp3.clear.net.nz> for
	pgsql-performance@postgresql.org; Wed, 08 Apr 2009 19:15:29 +1200 (NZST)
Date: Wed, 08 Apr 2009 19:15:28 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Best replication solution?
In-reply-to: <20090407171803.GQ14950@shinkuro.com>
To: Andrew Sullivan <ajs@crankycanuck.ca>, pgsql-performance@postgresql.org
Message-id: <49DC4F10.6090302@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <49D8FA31.8010407@on-track.ca>
	<20090406123529.GA14675@shinkuro.com>
	<49DB2B66.1080801@paradise.net.nz>
	<20090407171803.GQ14950@shinkuro.com>
User-Agent: Thunderbird 2.0.0.19 (X11/20090117)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.117 tagged_above=0 required=5 tests=AWL=-0.782,
	FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/151
X-Sequence-Number: 33518

Andrew Sullivan wrote:
>
> I should have stated that differently.  First, you're right that if
> you don't know where to look or what to look for, you can easily be
> unaware of nodes being out of sync.  What's not a problem with Slony
> is that the nodes can get out of internally consistent sync state: if
> you have a node that is badly lagged, at least it represents, for
> sure, an actual point in time of the origin set's history.  Some of
> the replication systems aren't as careful about this, and it's
> possible to get the replica into a state that never happened on the
> origin.  That's much worse, in my view.
>
> In addition, it is not possible that Slony's system tables report the
> replica as being up to date without them actually being so, because
> the system tables are updated in the same transaction as the data is
> sent.  It's hard to read those tables, however, because you have to
> check every node and understand all the states.
>
>   
>
Yes, and nicely explained!

> (on Londiste DDL + slave chaining)...
> Well, those particular features -- which are indeed the source of much
> of the complexity in Slony -- were planned in from the beginning.
> Londiste aimed to be simpler, so it would be interesting to see
> whether those features could be incorporated without the same
> complication.
>
>
>
>   
Yeah, that's the challenge!

Personally I would like DDL to be possible without any special wrappers 
or precautions, as the usual (accidental) breakage I end up looking at 
in Slony is because someone (or an app's upgrade script) has performed 
an ALTER TABLE directly on the master schema...

Cheers

Mark




From mjy@geizhals.at Wed Apr  8 11:45:04 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id E854F6325E2
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 08:45:35 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 09465-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 08:45:27 -0300 (ADT)
Received: from mx1.hub.org (mx1.hub.org [200.46.208.106])
	by mail.postgresql.org (Postfix) with ESMTP id 8B3DC632545
	for <pgsql-performance@postgresql.org>;
	Wed,  8 Apr 2009 08:45:34 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from morework.geizhals.at (home.geizhals.at [213.229.14.34])
	by mx1.hub.org (Postfix) with ESMTP id 24046125A75F
	for <pgsql-performance@postgresql.org>;
	Wed,  8 Apr 2009 08:45:34 -0300 (ADT)
Received: from localhost (morework [127.0.0.1])
	by morework.geizhals.at (Postfix) with ESMTP id 0B19918C1C3;
	Wed,  8 Apr 2009 13:45:25 +0200 (CEST)
X-Virus-Scanned: amavisd-new at geizhals.at
Received: from morework.geizhals.at ([127.0.0.1])
	by localhost (morework.geizhals.at [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id MMLtB0QyN2ne; Wed,  8 Apr 2009 13:45:17 +0200 (CEST)
Received: from [172.16.43.70] (unknown [172.16.43.70])
	by morework.geizhals.at (Postfix) with ESMTP;
	Wed,  8 Apr 2009 13:45:17 +0200 (CEST)
Message-ID: <49DC8E40.2010503@geizhals.at>
Date: Wed, 08 Apr 2009 13:45:04 +0200
From: Marinos Yannikos <mjy@geizhals.at>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: Lists <lists@on-track.ca>
CC: pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
References: <49D8FA31.8010407@on-track.ca>
	<480ED41A-B4AF-4A14-A10B-9F7181D7949D@rvt.dds.nl>
	<49D9293D.2020101@on-track.ca> <49D9BCCA.7060609@enterprisedb.com>
In-Reply-To: <49D9BCCA.7060609@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.695 tagged_above=0 required=5 tests=AWL=-1.204,
	FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: 
X-Archive-Number: 200904/153
X-Sequence-Number: 33520

Heikki Linnakangas wrote:
> Lists wrote:
>> Server is a dual core xeon 3GB ram and 2 mirrors of 15k SAS drives (1 
>> for most data, 1 for wal and a few tables and indexes)
>>
>> In total all databases on the server are about 10G on disk (about 2GB 
>> in pgdump format).
> 
> I'd suggest buying as much RAM as you can fit into the server. RAM is 
> cheap, and with a database of that size more cache could have a dramatic 
> effect.

I'll second this. Although it doesn't really answer the original 
question, you have to keep in mind that for read-intensive workloads, 
caching will give you the biggest benefit by far, orders of magnitude 
more than replication solutions unless you want to spend a lot of $ on 
hardware (which I take it you don't if you are reluctant to add new 
disks). Keeping the interesting parts of the DB completely in RAM makes 
a big difference, common older (P4-based) Xeon boards can usually be 
upgraded to 12-16GB RAM, newer ones to anywhere between 16 and 192GB ...

As for replication solutions - Slony I wouldn't recommend (tried it for 
workloads with large writes - bad idea), but PgQ looks very solid and 
you could either use Londiste or build your own very fast non-RDBMS 
slaves using PgQ by keeping the data in an optimized format for your 
queries (e.g. if you don't need joins - use TokyoCabinet/Berkeley DB).

Regards,
  Marinos




From threshar@torgo.978.org Wed Apr  8 18:20:14 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 71DAA634236
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 16:20:53 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 44428-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 16:20:50 -0300 (ADT)
X-Greylist: delayed 01:00:02.817702 by SQLgrey-1.7.6
Received: from vms173017pub.verizon.net (vms173017pub.verizon.net
	[206.46.173.17])
	by mail.postgresql.org (Postfix) with ESMTP id 6780A6341E3
	for <pgsql-performance@postgresql.org>;
	Wed,  8 Apr 2009 16:20:49 -0300 (ADT)
Received: from jefftrout.com ([173.76.169.195]) by vms173017.mailsrvcs.net
	(Sun Java(tm) System Messaging Server 6.3-7.04 (built Sep 26 2008;
	32bit)) with ESMTPA id <0KHS000HCO9TYC75@vms173017.mailsrvcs.net> for
	pgsql-performance@postgresql.org; Wed, 08 Apr 2009 13:20:24 -0500 (CDT)
Received: from localhost (localhost [127.0.0.1])	by jefftrout.com (Postfix)
	with ESMTP id 9A1AC5C4E; Wed, 08 Apr 2009 14:20:16 -0400 (EDT)
X-Virus-Scanned: amavisd-new at jefftrout.com
Received: from jefftrout.com ([127.0.0.1])
	by localhost (torgo.home [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id vUs9qz6MmyHi; Wed, 08 Apr 2009 14:20:14 -0400 (EDT)
Received: from [192.168.1.25] (unknown [192.168.1.25])
	by jefftrout.com (Postfix) with ESMTP id BE0675C48; Wed,
	08 Apr 2009 14:20:14 -0400 (EDT)
Cc: pgsql-performance@postgresql.org
Message-id: <8BCF1E1D-B78A-4FC8-9B76-E523280BFF47@torgo.978.org>
From: Jeff <threshar@torgo.978.org>
To: Andrew Sullivan <ajs@crankycanuck.ca>
In-reply-to: <20090407171803.GQ14950@shinkuro.com>
Content-type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-transfer-encoding: 7bit
MIME-version: 1.0 (Apple Message framework v930.3)
Subject: Re: Best replication solution?
Date: Wed, 08 Apr 2009 14:20:14 -0400
References: <49D8FA31.8010407@on-track.ca>
	<20090406123529.GA14675@shinkuro.com>
	<49DB2B66.1080801@paradise.net.nz>
	<20090407171803.GQ14950@shinkuro.com>
X-Mailer: Apple Mail (2.930.3)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.899 tagged_above=0 required=5
	tests=FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: *
X-Archive-Number: 200904/158
X-Sequence-Number: 33525


On Apr 7, 2009, at 1:18 PM, Andrew Sullivan wrote:

> I should have stated that differently.  First, you're right that if
> you don't know where to look or what to look for, you can easily be
> unaware of nodes being out of sync.  What's not a problem with Slony

_$cluster.sl_status on the origin is a very handy tool to see your  
slaves, how many sync's behind they are and whatnot.  Maybe I'm lucky,  
but I haven't got into a funky state that didn't cause my alarms that  
watch sl_status to go nuts.

>> Complexity seems to be the major evil here.
>
> Yes.  Slony is massively complex.
>

Configuring slony by hand using slonik commands does suck horribly.
But the included altperl tools that come with it, along with  
slon_tools.conf removes a HUGE amount of that suck.

To add a table with a pk you edit slon_tools.conf and add something  
along the lines of:

"someset" => {
	"set_id" => 5,
	"table_id" => 5,
	"pkeyedtables" => [ "tacos", "burritos", "gorditas" ]
}

then you just run

[create tables on slave(s)]
slonik_create_set someset;
slonik_subscribe_set 1 2;

there are other handy scripts in there as well for failing over,  
adding tables, merging, etc. that hide a lot of the suck.  Especially  
the suck of adding a node and creating the store paths.

I'm running slony on a rather write intensive system, works fine, just  
make sure you've got beefy IO.  One sucky thing though is if a slave  
is down sl_log can grow very large (I've had it get over 30M rows, the  
slave was only down for hours) and this causes major cpu churn while  
the queries slon issues sift through tons of data.  But, to be fair,  
that'll hurt any replication system.

--
Jeff Trout <jeff@jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/






From dfontaine@hi-media.com Wed Apr  8 20:46:47 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 1CFD3633949
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 17:47:01 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 32248-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Apr 2009 17:46:54 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp1.hi-media-techno.com (smtp1.hi-media-techno.com
	[195.158.241.85])
	by mail.postgresql.org (Postfix) with ESMTP id 9E42D632D6D
	for <pgsql-performance@postgresql.org>;
	Wed,  8 Apr 2009 17:46:58 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by smtp1.hi-media-techno.com (Postfix) with ESMTP id C9BFF93D50;
	Wed,  8 Apr 2009 22:46:55 +0200 (CEST)
X-Virus-Scanned: Debian amavisd-new at smtp1.backbone.hi-media-techno.com
Received: from smtp1.hi-media-techno.com ([127.0.0.1])
	by localhost (smtp1.backbone.hi-media-techno.com [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id 6l++wdrj6BUJ; Wed,  8 Apr 2009 22:46:49 +0200 (CEST)
Received: from mail1.hi-media-techno.com (unknown [192.168.27.12])
	by smtp1.hi-media-techno.com (Postfix) with ESMTP id 4D9CA93D2A;
	Wed,  8 Apr 2009 22:46:49 +0200 (CEST)
Received: from localhost (localhost [127.0.0.1])
	by mail1.hi-media-techno.com (Postfix) with ESMTP id 472C26D8BC9;
	Wed,  8 Apr 2009 22:46:49 +0200 (CEST)
X-Virus-Scanned: amavisd-new at 
Received: from mail1.hi-media-techno.com ([127.0.0.1])
	by localhost (mail1.hi-media-techno.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id o5vCnCw-hLId; Wed,  8 Apr 2009 22:46:49 +0200 (CEST)
Received: from [192.168.12.17] (mar92-14-88-173-196-148.fbx.proxad.net
	[88.173.196.148])
	by mail1.hi-media-techno.com (Postfix) with ESMTP id A28D46D8BBB;
	Wed,  8 Apr 2009 22:46:48 +0200 (CEST)
Cc: Andrew Sullivan <ajs@crankycanuck.ca>,
 pgsql-performance@postgresql.org
Message-Id: <8D8FF317-3328-471A-B4D9-83279251C097@hi-media.com>
From: Dimitri Fontaine <dfontaine@hi-media.com>
To: Jeff <threshar@torgo.978.org>
In-Reply-To: <8BCF1E1D-B78A-4FC8-9B76-E523280BFF47@torgo.978.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Apple Message framework v930.3)
Subject: Re: Best replication solution?
Date: Wed, 8 Apr 2009 22:46:47 +0200
References: <49D8FA31.8010407@on-track.ca>
	<20090406123529.GA14675@shinkuro.com>
	<49DB2B66.1080801@paradise.net.nz>
	<20090407171803.GQ14950@shinkuro.com>
	<8BCF1E1D-B78A-4FC8-9B76-E523280BFF47@torgo.978.org>
X-Mailer: Apple Mail (2.930.3)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.95 tagged_above=0 required=5 tests=AWL=-0.950,
	FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72
X-Spam-Level: 
X-Archive-Number: 200904/159
X-Sequence-Number: 33526

Hi,

Ok I need to answer some more :)

Le 8 avr. 09 =E0 20:20, Jeff a =E9crit :
> To add a table with a pk you edit slon_tools.conf and add something =20=

> along the lines of:
>
> "someset" =3D> {
> 	"set_id" =3D> 5,
> 	"table_id" =3D> 5,
> 	"pkeyedtables" =3D> [ "tacos", "burritos", "gorditas" ]
> }
>
> then you just run
>
> [create tables on slave(s)]
> slonik_create_set someset;
> slonik_subscribe_set 1 2;

  $ londiste.py setup.ini provider add schema.table
  $ londiste.py setup.ini subscriber add schema.table

Note both of those commands are to be run from the same host (often =20
enough, the slave), if you have more than one slave, issue the second =20=

of them only on the remaining ones.

> there are other handy scripts in there as well for failing over, =20
> adding tables, merging, etc. that hide a lot of the suck.  =20
> Especially the suck of adding a node and creating the store paths.

There's no set in Londiste, so you just don't manage them. You add =20
tables to queues (referencing the provider in fact) and the subscriber =20=

is free to subscribe to only a subset of the provider queue's tables. =20=

And any table could participate into more than one queue at any time =20
too, of course.

> I'm running slony on a rather write intensive system, works fine, =20
> just make sure you've got beefy IO.  One sucky thing though is if a =20=

> slave is down sl_log can grow very large (I've had it get over 30M =20
> rows, the slave was only down for hours) and this causes major cpu =20
> churn while the queries slon issues sift through tons of data.  But, =20=

> to be fair, that'll hurt any replication system.

This could happen in Londiste too, just set pgq_lazy_fetch to a =20
reasonable value and Londiste will use a cursor to fetch the events, =20
lowering the load. Events are just tuples in an INSERT only table, =20
which when not used anymore is TRUNCATEd away. PGQ will use 3 tables =20
where to store events and will rotate its choice of where to insert =20
new envents, allowing to use TRUNCATE rather than DELETE. And =20
PostgreSQL is quite efficient to manage this :)
   =
http://wiki.postgresql.org/wiki/Londiste_Tutorial#Londiste_is_eating_all_m=
y_CPU_and_lag_is_raising


Oh and some people asked what Londiste with failover and DDL would =20
look like. Here's what the API being cooked looks like at the moment:
  $ londiste setup.ini execute myddl.script.sql

  $ londiste conf/londiste_db3.ini change-provider --provider=3Drnode1
  $ londiste conf/londiste_db1.ini switchover --target=3Drnode2

But I'm not the one who should be unveiling all of this, which is =20
currently being prepared to reach alpha soon'ish.

Regards,
--=20
dim




From threshar@torgo.978.org Thu Apr  9 12:19:22 2009
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 69A45632216
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  9 Apr 2009 09:19:43 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 05826-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  9 Apr 2009 09:19:40 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from vms173015pub.verizon.net (vms173015pub.verizon.net
	[206.46.173.15])
	by mail.postgresql.org (Postfix) with ESMTP id 9207B632207
	for <pgsql-performance@postgresql.org>;
	Thu,  9 Apr 2009 09:19:39 -0300 (ADT)
Received: from jefftrout.com ([173.76.169.195]) by vms173015.mailsrvcs.net
	(Sun Java(tm) System Messaging Server 6.3-7.04 (built Sep 26 2008;
	32bit)) with ESMTPA id <0KHU005OJ28D3WU5@vms173015.mailsrvcs.net> for
	pgsql-performance@postgresql.org; Thu, 09 Apr 2009 07:19:26 -0500 (CDT)
Received: from localhost (localhost [127.0.0.1])	by jefftrout.com (Postfix)
	with ESMTP id C21155C4E; Thu, 09 Apr 2009 08:19:24 -0400 (EDT)
X-Virus-Scanned: amavisd-new at jefftrout.com
Received: from jefftrout.com ([127.0.0.1])
	by localhost (torgo.home [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id zWqwuUhzSRKj; Thu, 09 Apr 2009 08:19:22 -0400 (EDT)
Received: from [192.168.1.25] (unknown [192.168.1.25])
	by jefftrout.com (Postfix) with ESMTP id 88A7B5C3B; Thu,
	09 Apr 2009 08:19:22 -0400 (EDT)
Cc: pgsql-performance@postgresql.org
Message-id: <77A7A940-78F9-4729-A28E-44270CB7C330@torgo.978.org>
From: Jeff <threshar@torgo.978.org>
To: Dimitri Fontaine <dfontaine@hi-media.com>
In-reply-to: <8D8FF317-3328-471A-B4D9-83279251C097@hi-media.com>
Content-type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-transfer-encoding: 7bit
MIME-version: 1.0 (Apple Message framework v930.3)
Subject: Re: Best replication solution?
Date: Thu, 09 Apr 2009 08:19:22 -0400
References: <49D8FA31.8010407@on-track.ca>
	<20090406123529.GA14675@shinkuro.com>
	<49DB2B66.1080801@paradise.net.nz>
	<20090407171803.GQ14950@shinkuro.com>
	<8BCF1E1D-B78A-4FC8-9B76-E523280BFF47@torgo.978.org>
	<8D8FF317-3328-471A-B4D9-83279251C097@hi-media.com>
X-Mailer: Apple Mail (2.930.3)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.214 tagged_above=0 required=5
	tests=FS_REPLICA=1.179, SARE_MILLIONSOF=0.315, SARE_SPEC_REPLICA=0.72
X-Spam-Level: **
X-Archive-Number: 200904/166
X-Sequence-Number: 33533


On Apr 8, 2009, at 4:46 PM, Dimitri Fontaine wrote:

>
> $ londiste.py setup.ini provider add schema.table
> $ londiste.py setup.ini subscriber add schema.table
>

That is nice.  One could probably do that for slony too.

I may try some tests out with londiste.. I'm always open to new  
(ideally, better) things.
>
> This could happen in Londiste too, just set pgq_lazy_fetch to a  
> reasonable value and Londiste will use a cursor to fetch the events,  
> lowering the load. Events are just tuples in an INSERT only table,  
> which when not used anymore is TRUNCATEd away. PGQ will use 3 tables  
> where to store events and will rotate its choice of where to insert  
> new envents, allowing to use TRUNCATE rather than DELETE. And  
> PostgreSQL is quite efficient to manage this :)
>  http://wiki.postgresql.org/wiki/Londiste_Tutorial#Londiste_is_eating_all_my_CPU_and_lag_is_raising
>

Well, Slony always uses a cursor to fetch, the problem is it may have  
to slog through millions of rows to find the new data - I've analyzed  
the queries and there isn't much it can do -  lots of calls to the  
xxid_ functions to determine whats to be used, whats not to be used.   
When all slaves have a sync event ack'd it is free to be removed by  
the cleanup routine which is run every few minutes.

>
> Oh and some people asked what Londiste with failover and DDL would  
> look like. Here's what the API being cooked looks like at the moment:
> $ londiste setup.ini execute myddl.script.sql
>
> $ londiste conf/londiste_db3.ini change-provider --provider=rnode1
> $ londiste conf/londiste_db1.ini switchover --target=rnode2
>

ok, so londiste can't do failover yet, or is it just somewhat  
convoluted at this point?

--
Jeff Trout <jeff@jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/