Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 43D512E0055
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Wed,  4 Jun 2008 14:24:18 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 76198-10
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Wed,  4 Jun 2008 14:24:10 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from web37305.mail.mud.yahoo.com (web37305.mail.mud.yahoo.com
	[209.191.90.248])
	by developer.postgresql.org (Postfix) with SMTP id 12C952E0054
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Jun 2008 14:24:10 -0300 (ADT)
Received: (qmail 35892 invoked by uid 60001); 4 Jun 2008 17:24:09 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=Z4uZ642wn2i+7JnyzTwYRbFL+8e9dPZwq/JUb+YXqYK/JoA2o/+++IRMSNE9Pym6i3CTxXFegIzrrs/pH/asGqU1xjWmWUlc6m1zXKjdHJdfeW/R9pW8Ow1ILQ96Nf73ZT2cvBm0foD4gMubZnzFV1pXUM5KIVFFPK4SN7mCoLs=;
Received: from [66.226.193.82] by web37305.mail.mud.yahoo.com via HTTP;
	Wed, 04 Jun 2008 10:24:09 PDT
X-Mailer: YahooMailRC/975.41 YahooMailWebService/0.7.199
Date: Wed, 4 Jun 2008 10:24:09 -0700 (PDT)
From: andrew klassen <aptklassen@yahoo.com>
Subject: Re: insert/update tps slow with indices on table > 1M rows
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-83221626-1212600249=:23550"
Message-ID: <880373.23550.qm@web37305.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200806/26
X-Sequence-Number: 30376

--0-83221626-1212600249=:23550
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

=0AI agree that the discs are probably very busy. I do have 2 disks but the=
y are=0Afor redundancy. Would it help to put the data, indexes and xlog on =
separate =0Adisk partitions? =0AI'll try adding more threads to update the =
table as you suggest.=0A=0A----- Original Message ----=0AFrom: Matthew Wake=
ling <matthew@flymine.org>=0ATo: pgsql-performance@postgresql.org=0ASent: W=
ednesday, June 4, 2008 10:10:38 AM=0ASubject: Re: [PERFORM] insert/update t=
ps slow with indices on table > 1M rows=0A=0AOn Wed, 4 Jun 2008, andrew kla=
ssen wrote:=0A> I am using multiple threads, but only one worker thread for=
 insert/updated to this table.=0A> I don't mind trying to add multiple thre=
ads for this table, but my guess is it would not=0A> help because basically=
 the overall tps rate is decreasing so dramatically. Since=0A> the cpu time=
 consumed by the corresponding postgres=A0server process for my thread is=
=0A> small it does not seem to be the bottleneck. There has to be a bottlen=
eck somewhere else.=0A> Do you agree or is there some flaw in my reasoning?=
=0A=0AThere is indeed a flaw in your reasoning - there may be very little C=
PU =0Atime consumed, but that just indicates that the discs are busy. Getti=
ng =0APostgres to do multiple things at once will cause a more efficient us=
e of =0Athe disc subsystem, resulting in greater overall throughput. This i=
s =0Aespecially the case if you have multiple discs in your box.=0A=0AMatth=
ew=0A=0A-- =0AContrary to popular belief, Unix is user friendly. It just ha=
ppens to be=0Avery selective about who its friends are.=A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 -- Kyle Hearn=0A-- =0ASent via pgsql-performance mailing list (=
pgsql-performance@postgresql.org)=0ATo make changes to your subscription:=
=0Ahttp://www.postgresql.org/mailpref/pgsql-performance=0A=0A=0A=0A      
--0-83221626-1212600249=:23550
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif"><BR>I agree that the discs are probably very busy. I do have 2 disks but they are</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">for redundancy. Would it help to put the data, indexes and xlog on separate </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">disk partitions? </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">&nbsp;</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">I'll try adding more threads to update the table as you suggest.</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">&nbsp;</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif"><BR>&nbsp;</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">----- Original Message ----<BR>From: Matthew Wakeling &lt;matthew@flymine.org&gt;<BR>To: pgsql-performance@postgresql.org<BR>Sent: Wednesday, June 4, 2008 10:10:38 AM<BR>Subject: Re: [PERFORM] insert/update tps slow with indices on table &gt; 1M rows<BR><BR>On Wed, 4 Jun 2008, andrew klassen wrote:<BR>&gt; I am using multiple threads, but only one worker thread for insert/updated to this table.<BR>&gt; I don't mind trying to add multiple threads for this table, but my guess is it would not<BR>&gt; help because basically the overall tps rate is decreasing so dramatically. Since<BR>&gt; the cpu time consumed by the corresponding postgres&nbsp;server process for my thread is<BR>&gt; small it does not seem to be the bottleneck. There has to be a bottleneck somewhere else.<BR>&gt; Do you agree or is there some flaw in my reasoning?<BR><BR>There is indeed a flaw in your
 reasoning - there may be very little CPU <BR>time consumed, but that just indicates that the discs are busy. Getting <BR>Postgres to do multiple things at once will cause a more efficient use of <BR>the disc subsystem, resulting in greater overall throughput. This is <BR>especially the case if you have multiple discs in your box.<BR><BR>Matthew<BR><BR>-- <BR>Contrary to popular belief, Unix is user friendly. It just happens to be<BR>very selective about who its friends are.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- Kyle Hearn<BR>-- <BR>Sent via pgsql-performance mailing list (<A href="mailto:pgsql-performance@postgresql.org" ymailto="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</A>)<BR>To make changes to your subscription:<BR><A href="http://www.postgresql.org/mailpref/pgsql-performance" target=_blank>http://www.postgresql.org/mailpref/pgsql-performance</A><BR></DIV></div><br>

      </body></html>
--0-83221626-1212600249=:23550--