From ssutjiono@wc-group.com Tue May  7 23:02:12 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 2662F475A7F
	for <pgsql-general@postgresql.org>;
	Tue,  7 May 2002 19:02:12 -0400 (EDT)
Received: from qmsql.netservers.net (hpmail3.netservers.net [209.196.128.174])
	by postgresql.org (Postfix) with SMTP id 4D771475BF5
	for <pgsql-general@postgresql.org>;
	Tue,  7 May 2002 18:40:58 -0400 (EDT)
Received: (qmail 91121 invoked from network); 7 May 2002 22:41:00 -0000
Received: from unknown (HELO AARON) (208.61.134.223)
	by hpmail3.netservers.net with SMTP; 7 May 2002 22:41:00 -0000
Message-ID: <001501c1f618$32d0d780$110a010a@headquarters.wcgroup.com>
From: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
To: <pgsql-general@postgresql.org>, <pgsql-sql@postgresql.org>
Subject: Performance issues with compaq server
Date: Tue, 7 May 2002 18:40:32 -0400
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0012_01C1F5F6.AB343590"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Archive-Number: 200205/278
X-Sequence-Number: 24258

This is a multi-part message in MIME format.

------=_NextPart_000_0012_01C1F5F6.AB343590
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I tried to load data using insert statement to our new database server (Com=
paq DL580, DL590, DL380 using SCSI driver) but it was very very slow.  The =
database runs on Red Hat Linux 7.2 and PostgreSQL version 7.1.3=20=20

Does anybody know any idea to what cause this problem ? or things that I ne=
ed to look or check (in the config. file)

I appreciate any help.

Regards,
Samuel

------=_NextPart_000_0012_01C1F5F6.AB343590
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I tried to load data using insert statemen=
t to our=20
new database server (Compaq DL580, DL590, DL380&nbsp;using SCSI driver)&nbs=
p;but=20
it&nbsp;was very very slow.&nbsp; The database runs on Red Hat Linux 7.2=20
and&nbsp;PostgreSQL version 7.1.3 &nbsp;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Does anybody know any idea to what cause t=
his=20
problem ? or things that I need to look or check&nbsp;(in the config.=20
file)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I appreciate any help.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Samuel</FONT></DIV></BODY></HTML>

------=_NextPart_000_0012_01C1F5F6.AB343590--




From postgres@vrane.com Tue May  7 23:26:19 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 3235D476238
	for <pgsql-general@postgresql.org>;
	Tue,  7 May 2002 19:26:19 -0400 (EDT)
Received: from amd.universe (pool-151-203-178-225.wma.east.verizon.net
	[151.203.178.225])
	by postgresql.org (Postfix) with ESMTP id 7A56F475A7F
	for <pgsql-general@postgresql.org>;
	Tue,  7 May 2002 19:21:30 -0400 (EDT)
Received: (from win@localhost) by amd.universe (8.11.6/8.11.2) id
 g47NLWc03949;
	Tue, 7 May 2002 19:21:32 -0400
Date: Tue, 7 May 2002 19:21:32 -0400
From: postgres@vrane.com
X-Authentication-Warning: amd.universe: win set sender to postgres@vrane.com
	using -f
To: pgsql-general@postgresql.org
Subject: Re: Performance issues with compaq server
Message-ID: <20020507192132.A3944@amd.universe>
References: <001501c1f618$32d0d780$110a010a@headquarters.wcgroup.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5.1i
In-Reply-To: <001501c1f618$32d0d780$110a010a@headquarters.wcgroup.com>;
	from ssutjiono@wc-group.com on Tue, May 07, 2002 at 06:40:32PM
	-0400
X-Archive-Number: 200205/281
X-Sequence-Number: 24261

One thing you can try is to use "-F"
switch which will turn off fsync.

$ man postmaster

There are other possibilities however

On Tue, May 07, 2002 at 06:40:32PM -0400, Samuel J. Sutjiono wrote:
> I tried to load data using insert statement to our new database server (Compaq DL580, DL590, DL380 using SCSI driver) but it was very very slow.  The database runs on Red Hat Linux 7.2 and PostgreSQL version 7.1.3  
> 
> Does anybody know any idea to what cause this problem ? or things that I need to look or check (in the config. file)
> 
> I appreciate any help.
> 
> Regards,
> Samuel



From kleptog@svana.org Wed May  8 01:25:40 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP
	id A0ECD475A98; Tue,  7 May 2002 21:25:40 -0400 (EDT)
Received: from svana.org (svana.org [210.9.66.30])
	by postgresql.org (Postfix) with ESMTP
	id E75CD475A61; Tue,  7 May 2002 21:25:37 -0400 (EDT)
Received: from kleptog by svana.org with local (Exim 3.12 #1 (Debian))
	id 175GCt-0004ct-00; Wed, 08 May 2002 11:25:27 +1000
Date: Wed, 8 May 2002 11:25:27 +1000
From: Martijn van Oosterhout <kleptog@svana.org>
To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
Cc: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: Performance issues with compaq server
Message-ID: <20020508112527.A17584@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
Mail-Followup-To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>,
	pgsql-general@postgresql.org, pgsql-sql@postgresql.org
References: <001501c1f618$32d0d780$110a010a@headquarters.wcgroup.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5i
In-Reply-To: <001501c1f618$32d0d780$110a010a@headquarters.wcgroup.com>;
	from ssutjiono@wc-group.com on Tue, May 07, 2002 at 06:40:32PM
	-0400
X-Archive-Number: 200205/282
X-Sequence-Number: 24262

On Tue, May 07, 2002 at 06:40:32PM -0400, Samuel J. Sutjiono wrote:

> I tried to load data using insert statement to our new database server
> (Compaq DL580, DL590, DL380 using SCSI driver) but it was very very slow. 
> The database runs on Red Hat Linux 7.2 and PostgreSQL version 7.1.3
> 
> Does anybody know any idea to what cause this problem ? or things that I
> need to look or check (in the config. file)

Put them in a trasaction (begin/commit). Without that, each insert becomes
it's own transaction which is rather expensive. Postgresql 7.2 improves this
a bit but the transaction will help anyway.

HTH,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America



From holger@marzen.de Wed May  8 07:16:53 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP
	id EF59A475F42; Wed,  8 May 2002 03:16:53 -0400 (EDT)
Received: from bellona.wg.saar.de (bellona.wg.saar.de [192.109.53.23])
	by postgresql.org (Postfix) with ESMTP
	id D97C1475BDD; Wed,  8 May 2002 03:10:57 -0400 (EDT)
Received: (from uucp@localhost)
	by bellona.wg.saar.de (8.10.2/8.10.2) with UUCP id g487AJx14547;
	Wed, 8 May 2002 09:10:19 +0200
Received: from localhost (localhost [127.0.0.1])
	by bluebell.marzen.de (8.10.1/8.10.1) with ESMTP id g4875oe11814;
	Wed, 8 May 2002 09:05:50 +0200
Date: Wed, 8 May 2002 09:05:50 +0200 (CEST)
From: Holger Marzen <holger@marzen.de>
X-X-Sender: hm@bluebell.marzen.de
Reply-To: holger@marzen.de
To: Martijn van Oosterhout <kleptog@svana.org>
Cc: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>,
	<pgsql-general@postgresql.org>, <pgsql-sql@postgresql.org>
Subject: Re: Performance issues with compaq server
In-Reply-To: <20020508112527.A17584@svana.org>
Message-ID: <Pine.LNX.4.44.0205080903080.11632-100000@bluebell.marzen.de>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200205/286
X-Sequence-Number: 24266

On Wed, 8 May 2002, Martijn van Oosterhout wrote:

> On Tue, May 07, 2002 at 06:40:32PM -0400, Samuel J. Sutjiono wrote:
>
> > I tried to load data using insert statement to our new database server
> > (Compaq DL580, DL590, DL380 using SCSI driver) but it was very very slow.
> > The database runs on Red Hat Linux 7.2 and PostgreSQL version 7.1.3
> >
> > Does anybody know any idea to what cause this problem ? or things that I
> > need to look or check (in the config. file)
>
> Put them in a trasaction (begin/commit). Without that, each insert becomes
> it's own transaction which is rather expensive. Postgresql 7.2 improves this
> a bit but the transaction will help anyway.

ACK. On a given hardware I get about 150 inserts per second. Using a
begin/end transaction for a group of 100 inserts speeds it up to about
450 inserts per second.

But beware: if one insert fails (duplicate key, faulty data) then you
have to re-insert the remaining rows as single transactions, else all
rows of the previous transaction are discarded.

-- 
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1




From doug@wireboard.com Wed May  8 16:31:28 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP
	id EDD53476144; Wed,  8 May 2002 12:31:28 -0400 (EDT)
Received: from varsoon.wireboard.com (www.wireboard.com [216.151.155.101])
	by postgresql.org (Postfix) with ESMTP
	id 04D85476C46; Wed,  8 May 2002 11:03:05 -0400 (EDT)
Received: from doug by varsoon.wireboard.com with local (Exim 3.33 #1)
	id 175Sxg-0000Bs-00; Wed, 08 May 2002 11:02:36 -0400
To: holger@marzen.de
Cc: Martijn van Oosterhout <kleptog@svana.org>,
	"Samuel J. Sutjiono" <ssutjiono@wc-group.com>,
	<pgsql-general@postgresql.org>, <pgsql-sql@postgresql.org>
Subject: Re: Performance issues with compaq server
References: <Pine.LNX.4.44.0205080903080.11632-100000@bluebell.marzen.de>
From: Doug McNaught <doug@wireboard.com>
Date: 08 May 2002 11:02:35 -0400
In-Reply-To: Holger Marzen's message of "Wed,
 8 May 2002 09:05:50 +0200 (CEST)"
Message-ID: <m3offqk7hw.fsf@varsoon.wireboard.com>
Lines: 18
User-Agent: Gnus/5.0806 (Gnus v5.8.6) Emacs/20.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Archive-Number: 200205/295
X-Sequence-Number: 24275

Holger Marzen <holger@marzen.de> writes:

> ACK. On a given hardware I get about 150 inserts per second. Using a
> begin/end transaction for a group of 100 inserts speeds it up to about
> 450 inserts per second.

COPY is even faster as there is less query parsing to be done, plus
you get a transaction per COPY statement even without BEGIN/END.

> But beware: if one insert fails (duplicate key, faulty data) then you
> have to re-insert the remaining rows as single transactions, else all
> rows of the previous transaction are discarded.

Hmm don't you have to ROLLBACK and redo the whole transaction without
the offending row(s), since you can't commit while in ABORT state?  Or
am I misunderstanding?

-Doug



From steve@blighty.com Wed May  8 19:08:09 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id ABB9E4764B5
	for <pgsql-general@postgresql.org>;
	Wed,  8 May 2002 15:08:09 -0400 (EDT)
Received: from blighty.com (m206-91.dsl.tsoft.com [198.144.206.91])
	by postgresql.org (Postfix) with ESMTP id B74A247643C
	for <pgsql-general@postgresql.org>;
	Wed,  8 May 2002 13:43:34 -0400 (EDT)
Received: by blighty.com (Postfix, from userid 1000)
	id 6C7BA683; Wed,  8 May 2002 10:53:08 -0700 (PDT)
Date: Wed, 8 May 2002 10:53:08 -0700
From: Steve Atkins <steve@blighty.com>
To: pgsql-general@postgresql.org
Subject: Re: Performance issues with compaq server
Message-ID: <20020508105308.A14127@blighty.com>
References: <Pine.LNX.4.44.0205080903080.11632-100000@bluebell.marzen.de>
	<m3offqk7hw.fsf@varsoon.wireboard.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5i
In-Reply-To: <m3offqk7hw.fsf@varsoon.wireboard.com>;
	from doug@wireboard.com on Wed, May 08, 2002 at 11:02:35AM -0400
X-Archive-Number: 200205/303
X-Sequence-Number: 24283

On Wed, May 08, 2002 at 11:02:35AM -0400, Doug McNaught wrote:
> Holger Marzen <holger@marzen.de> writes:
> 
> > ACK. On a given hardware I get about 150 inserts per second. Using a
> > begin/end transaction for a group of 100 inserts speeds it up to about
> > 450 inserts per second.
> 
> COPY is even faster as there is less query parsing to be done, plus
> you get a transaction per COPY statement even without BEGIN/END.

Does anyone have any performance figures to hand on COPY vs BEGIN/INSERT/END,
on indexed or unindexed tables?

Cheers,
  Steve



From holger@marzen.de Wed May  8 21:07:58 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP
	id 855CB475CA4; Wed,  8 May 2002 17:07:58 -0400 (EDT)
Received: from bellona.wg.saar.de (bellona.wg.saar.de [192.109.53.23])
	by postgresql.org (Postfix) with ESMTP
	id 27FD6476609; Wed,  8 May 2002 16:10:34 -0400 (EDT)
Received: (from uucp@localhost)
	by bellona.wg.saar.de (8.10.2/8.10.2) with UUCP id g48KAMx21679;
	Wed, 8 May 2002 22:10:22 +0200
Received: from localhost (localhost [127.0.0.1])
	by bluebell.marzen.de (8.10.1/8.10.1) with ESMTP id g48K15H07586;
	Wed, 8 May 2002 22:01:05 +0200
Date: Wed, 8 May 2002 22:01:05 +0200 (CEST)
From: Holger Marzen <holger@marzen.de>
X-X-Sender: hm@bluebell.marzen.de
Reply-To: holger@marzen.de
To: Doug McNaught <doug@wireboard.com>
Cc: Martijn van Oosterhout <kleptog@svana.org>,
	"Samuel J. Sutjiono" <ssutjiono@wc-group.com>,
	<pgsql-general@postgresql.org>, <pgsql-sql@postgresql.org>
Subject: Re: Performance issues with compaq server
In-Reply-To: <m3offqk7hw.fsf@varsoon.wireboard.com>
Message-ID: <Pine.LNX.4.44.0205082155550.7269-100000@bluebell.marzen.de>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200205/304
X-Sequence-Number: 24284

On 8 May 2002, Doug McNaught wrote:

> Holger Marzen <holger@marzen.de> writes:
>
> > ACK. On a given hardware I get about 150 inserts per second. Using a
> > begin/end transaction for a group of 100 inserts speeds it up to about
> > 450 inserts per second.
>
> COPY is even faster as there is less query parsing to be done, plus
> you get a transaction per COPY statement even without BEGIN/END.

Yes, but I wanted to change something in some rows, so I used perl and
insert.

> > But beware: if one insert fails (duplicate key, faulty data) then you
> > have to re-insert the remaining rows as single transactions, else all
> > rows of the previous transaction are discarded.
>
> Hmm don't you have to ROLLBACK and redo the whole transaction without
> the offending row(s), since you can't commit while in ABORT state?  Or
> am I misunderstanding?

Postgres complains and doesn't accept the following inserts after a
failed one until end of transaction. I didn't have the time yet to
figure out if it rolls back the preceeding inserts.

Is there a rule in SQL standards that describes what should happen if
some statemens in a transaction fail and the program issues a commit?

-- 
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1




From doug@wireboard.com Thu May  9 04:27:07 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 9A28F475951
	for <pgsql-general@postgresql.org>;
	Thu,  9 May 2002 00:27:07 -0400 (EDT)
Received: from varsoon.wireboard.com (www.wireboard.com [216.151.155.101])
	by postgresql.org (Postfix) with ESMTP id 758C6475903
	for <pgsql-general@postgresql.org>;
	Thu,  9 May 2002 00:27:06 -0400 (EDT)
Received: from doug by varsoon.wireboard.com with local (Exim 3.33 #1)
	id 175fU4-0001bF-00; Thu, 09 May 2002 00:24:52 -0400
To: holger@marzen.de
Cc: Martijn van Oosterhout <kleptog@svana.org>,
	"Samuel J. Sutjiono" <ssutjiono@wc-group.com>,
	<pgsql-general@postgresql.org>
Subject: Re: Performance issues with compaq server
References: <Pine.LNX.4.44.0205082155550.7269-100000@bluebell.marzen.de>
From: Doug McNaught <doug@wireboard.com>
Date: 09 May 2002 00:24:52 -0400
In-Reply-To: Holger Marzen's message of "Wed,
 8 May 2002 22:01:05 +0200 (CEST)"
Message-ID: <m3offqhrsb.fsf@varsoon.wireboard.com>
Lines: 22
User-Agent: Gnus/5.0806 (Gnus v5.8.6) Emacs/20.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Archive-Number: 200205/313
X-Sequence-Number: 24293

Holger Marzen <holger@marzen.de> writes:

> On 8 May 2002, Doug McNaught wrote:
> 
> > Hmm don't you have to ROLLBACK and redo the whole transaction without
> > the offending row(s), since you can't commit while in ABORT state?  Or
> > am I misunderstanding?
> 
> Postgres complains and doesn't accept the following inserts after a
> failed one until end of transaction. I didn't have the time yet to
> figure out if it rolls back the preceeding inserts.

I'm pretty sure that the transaction goes into ABORT state and all you
can do is rollback and start over, minus the offending row(s).

> Is there a rule in SQL standards that describes what should happen if
> some statemens in a transaction fail and the program issues a commit?

I think PG's is standard behavior; that's kind of the whole point of
having transactions. 

-Doug



From cjs@cynic.net Thu May  9 07:15:33 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP
	id A74C647585D; Thu,  9 May 2002 03:15:33 -0400 (EDT)
Received: from angelic.cynic.net (unknown [61.206.138.158])
	by postgresql.org (Postfix) with ESMTP
	id CC611475909; Thu,  9 May 2002 03:12:06 -0400 (EDT)
Received: from localhost (localhost [127.0.0.1])
	by angelic.cynic.net (Postfix) with ESMTP
	id 548FA873E; Thu,  9 May 2002 16:12:00 +0900 (JST)
Date: Thu, 9 May 2002 16:11:59 +0900 (JST)
From: Curt Sampson <cjs@cynic.net>
To: Holger Marzen <holger@marzen.de>
Cc: pgsql-general@postgresql.org, <pgsql-sql@postgresql.org>
Subject: Re: Performance issues with compaq server
In-Reply-To: <Pine.LNX.4.44.0205082155550.7269-100000@bluebell.marzen.de>
Message-ID: <Pine.NEB.4.43.0205091611370.454-100000@angelic.cynic.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200205/315
X-Sequence-Number: 24295

On Wed, 8 May 2002, Holger Marzen wrote:

> > COPY is even faster as there is less query parsing to be done, plus
> > you get a transaction per COPY statement even without BEGIN/END.
>
> Yes, but I wanted to change something in some rows, so I used perl and
> insert.

Why not use Perl and COPY?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC




From holger@marzen.de Thu May  9 11:30:41 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id BFEC3475944
	for <pgsql-general@postgresql.org>;
	Thu,  9 May 2002 07:30:41 -0400 (EDT)
Received: from bellona.wg.saar.de (bellona.wg.saar.de [192.109.53.23])
	by postgresql.org (Postfix) with ESMTP id 5B25347593C
	for <pgsql-general@postgresql.org>;
	Thu,  9 May 2002 07:30:39 -0400 (EDT)
Received: (from uucp@localhost)
	by bellona.wg.saar.de (8.10.2/8.10.2) with UUCP id g49BUFn08425;
	Thu, 9 May 2002 13:30:15 +0200
Received: from localhost (localhost [127.0.0.1])
	by bluebell.marzen.de (8.10.1/8.10.1) with ESMTP id g49BNHb05099;
	Thu, 9 May 2002 13:23:17 +0200
Date: Thu, 9 May 2002 13:23:17 +0200 (CEST)
From: Holger Marzen <holger@marzen.de>
X-X-Sender: hm@bluebell.marzen.de
Reply-To: holger@marzen.de
To: Doug McNaught <doug@wireboard.com>
Cc: Martijn van Oosterhout <kleptog@svana.org>,
	"Samuel J. Sutjiono" <ssutjiono@wc-group.com>,
	<pgsql-general@postgresql.org>
Subject: Re: Performance issues with compaq server
In-Reply-To: <m3offqhrsb.fsf@varsoon.wireboard.com>
Message-ID: <Pine.LNX.4.44.0205091321110.4909-100000@bluebell.marzen.de>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200205/316
X-Sequence-Number: 24296

On 9 May 2002, Doug McNaught wrote:

> Holger Marzen <holger@marzen.de> writes:
>
> > Is there a rule in SQL standards that describes what should happen if
> > some statemens in a transaction fail and the program issues a commit?
>
> I think PG's is standard behavior; that's kind of the whole point of
> having transactions.

- rolling back the whole transaction if you do a COMMIT
  or
- keeping the changes until the first failing statement and ignoring
  everything that follows if you do a COMMIT

?

-- 
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1




From holger@marzen.de Thu May  9 11:31:24 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP
	id 762BF475D36; Thu,  9 May 2002 07:31:24 -0400 (EDT)
Received: from bellona.wg.saar.de (bellona.wg.saar.de [192.109.53.23])
	by postgresql.org (Postfix) with ESMTP
	id 21D4F475F58; Thu,  9 May 2002 07:30:48 -0400 (EDT)
Received: (from uucp@localhost)
	by bellona.wg.saar.de (8.10.2/8.10.2) with UUCP id g49BUgk08468;
	Thu, 9 May 2002 13:30:42 +0200
Received: from localhost (localhost [127.0.0.1])
	by bluebell.marzen.de (8.10.1/8.10.1) with ESMTP id g49BOC005105;
	Thu, 9 May 2002 13:24:12 +0200
Date: Thu, 9 May 2002 13:24:12 +0200 (CEST)
From: Holger Marzen <holger@marzen.de>
X-X-Sender: hm@bluebell.marzen.de
Reply-To: holger@marzen.de
To: Curt Sampson <cjs@cynic.net>
Cc: pgsql-general@postgresql.org, <pgsql-sql@postgresql.org>
Subject: Re: Performance issues with compaq server
In-Reply-To: <Pine.NEB.4.43.0205091611370.454-100000@angelic.cynic.net>
Message-ID: <Pine.LNX.4.44.0205091323390.4909-100000@bluebell.marzen.de>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200205/317
X-Sequence-Number: 24297

On Thu, 9 May 2002, Curt Sampson wrote:

> On Wed, 8 May 2002, Holger Marzen wrote:
>
> > > COPY is even faster as there is less query parsing to be done, plus
> > > you get a transaction per COPY statement even without BEGIN/END.
> >
> > Yes, but I wanted to change something in some rows, so I used perl and
> > insert.
>
> Why not use Perl and COPY?

A good idea. I'll try it if the customer complains about the speed.

-- 
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1




From jgray@azuli.co.uk Thu May  9 11:49:01 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id E1DC047594D
	for <pgsql-general@postgresql.org>;
	Thu,  9 May 2002 07:49:01 -0400 (EDT)
Received: from azuli.gotadsl.co.uk (azuli.gotadsl.co.uk [213.208.123.57])
	by postgresql.org (Postfix) with ESMTP id BC593475944
	for <pgsql-general@postgresql.org>;
	Thu,  9 May 2002 07:49:00 -0400 (EDT)
Received: from localhost.localdomain ([127.0.0.1])
	by azuli.gotadsl.co.uk with esmtp (Exim 3.22 #2)
	id 175mKe-0000ai-00; Thu, 09 May 2002 12:43:36 +0100
Subject: Re: Performance issues with compaq server
From: John Gray <jgray@azuli.co.uk>
To: holger@marzen.de
Cc: Doug McNaught <doug@wireboard.com>,
	Martijn van Oosterhout <kleptog@svana.org>,
	"Samuel J. Sutjiono" <ssutjiono@wc-group.com>, pgsql-general@postgresql.org
In-Reply-To: <Pine.LNX.4.44.0205091321110.4909-100000@bluebell.marzen.de>
References: <Pine.LNX.4.44.0205091321110.4909-100000@bluebell.marzen.de>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.3 
Date: 09 May 2002 12:43:33 +0100
Message-Id: <1020944616.1592.103.camel@adzuki>
Mime-Version: 1.0
X-Archive-Number: 200205/318
X-Sequence-Number: 24298

On Thu, 2002-05-09 at 12:23, Holger Marzen wrote:
> On 9 May 2002, Doug McNaught wrote:
> 
> > Holger Marzen <holger@marzen.de> writes:
> >
> > > Is there a rule in SQL standards that describes what should happen if
> > > some statemens in a transaction fail and the program issues a commit?
> >
> > I think PG's is standard behavior; that's kind of the whole point of
> > having transactions.
> 
> - rolling back the whole transaction if you do a COMMIT
>   or
> - keeping the changes until the first failing statement and ignoring
>   everything that follows if you do a COMMIT
> 

I can't speak to the phrasing of the standard, but PG behaviour is:

Everything in the transaction is ignored. All the inserts made before
the error, since the BEGIN, are rolled back. All subsequent inserts are
ignored (generating an error that transaction is in ABORT state). The
subsequent COMMIT will end the transaction (and thus clear the abort
state) but *will not commit anything*. 

This means that scripts will work as intended -namely that all or none
of the commands within a transaction block will succeed[*]. So if you
split your insert into blocks, and one block fails, you will only need
to correct that block and try to load it again.

Regards

John

[*] This is handy for doing things like reindexing or drop/recreate
table in a script. The drop table stands a good chance of succeeding,
whereas the prospect of a syntax error in the create table makes it more
likely to fail. A subsequent commit of the DROP TABLE would not produce
the end result you intended....

-- 
John Gray	
Azuli IT		
www.azuli.co.uk		





From denis@edistar.com Thu May  9 13:05:04 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 3C9A3476294
	for <pgsql-general@postgresql.org>;
	Thu,  9 May 2002 09:05:04 -0400 (EDT)
Received: from edinet.it (unknown [195.223.174.3])
	by postgresql.org (Postfix) with SMTP id 2BC014762E1
	for <pgsql-general@postgresql.org>;
	Thu,  9 May 2002 08:29:57 -0400 (EDT)
Received: (qmail 9736 invoked by uid 505); 9 May 2002 12:29:53 -0000
Received: from proxy.edinet.it (127.0.0.1)
	by proxy.edinet.it with SMTP; 9 May 2002 12:29:50 -0000
Subject: Re: Performance issues with compaq server
From: Denis Gasparin <denis@edistar.com>
To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
Cc: Postgresql General Mailing List <pgsql-general@postgresql.org>,
	pgsql-sql@postgresql.org
In-Reply-To: <001501c1f618$32d0d780$110a010a@headquarters.wcgroup.com>
References: <001501c1f618$32d0d780$110a010a@headquarters.wcgroup.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Evolution/1.0.2-5mdk 
Date: 09 May 2002 14:29:50 +0200
Message-Id: <1020947390.2035.3.camel@edspctec12>
Mime-Version: 1.0
X-Virus-Scanned: by AMaViS perl-11
X-Archive-Number: 200205/319
X-Sequence-Number: 24299

The Compaq servers you indicated use the smart array controller which is
very very slow in storing data into hard disks. Are you using the RAID?

I suggest you to buy a controller with a larger write cache (the smart
array controller does not have w-cache)... You should note a large
performance progress...

Also do not forget to set fsync to false and to use the COPY command
whenever is possible...

Bye!
-- 
Doct. Eng. Denis Gasparin: denis@edistar.com
---------------------------
Programmer & System Administrator - Edistar srl


Il mer, 2002-05-08 alle 00:40, Samuel J. Sutjiono ha scritto:
> I tried to load data using insert statement to our new database server (Compaq DL580, DL590, DL380 using SCSI driver) but it was very very slow.  The database runs on Red Hat Linux 7.2 and PostgreSQL version 7.1.3  
> 
> Does anybody know any idea to what cause this problem ? or things that I need to look or check (in the config. file)
> 
> I appreciate any help.
> 
> Regards,
> Samuel





From cjs@cynic.net Fri May  10 07:49:31 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id EB0DD475936
	for <pgsql-general@postgresql.org>;
	Fri, 10 May 2002 03:49:31 -0400 (EDT)
Received: from angelic.cynic.net (unknown [61.206.138.158])
	by postgresql.org (Postfix) with ESMTP id 6DBB9475873
	for <pgsql-general@postgresql.org>;
	Fri, 10 May 2002 03:49:30 -0400 (EDT)
Received: from localhost (localhost [127.0.0.1])
	by angelic.cynic.net (Postfix) with ESMTP
	id E5FE58742; Fri, 10 May 2002 16:49:29 +0900 (JST)
Date: Fri, 10 May 2002 16:49:29 +0900 (JST)
From: Curt Sampson <cjs@cynic.net>
To: Denis Gasparin <denis@edistar.com>
Cc: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>,
	Postgresql General Mailing List <pgsql-general@postgresql.org>
Subject: Re: Performance issues with compaq server
In-Reply-To: <1020947390.2035.3.camel@edspctec12>
Message-ID: <Pine.NEB.4.43.0205101648540.541-100000@angelic.cynic.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200205/348
X-Sequence-Number: 24328

On 9 May 2002, Denis Gasparin wrote:

> The Compaq servers you indicated use the smart array controller which is
> very very slow in storing data into hard disks. Are you using the RAID?
>
> I suggest you to buy a controller with a larger write cache (the smart
> array controller does not have w-cache)... You should note a large
> performance progress...

And take your transaction log off of RAID entirely and put it on
a mirrored pair of disks instead.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC