Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 7B7402E007A
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Wed,  4 Jun 2008 12:05:38 -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 37561-08
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Wed,  4 Jun 2008 12:05:27 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from web37304.mail.mud.yahoo.com (web37304.mail.mud.yahoo.com
	[209.191.90.247])
	by developer.postgresql.org (Postfix) with SMTP id 3D5A52E006F
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Jun 2008 12:05:25 -0300 (ADT)
Received: (qmail 6308 invoked by uid 60001); 4 Jun 2008 15:05:25 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type:Message-ID;
	b=ywspzEEJGyZtVa8b0I8EmM8X9NJzCcSp3qRzjB7bbhmUaj/Cof6MOgoiXNgSqzEA31QKDUvhx9HquHxwl3czuoA7WrN1zO+HAzYm0PDX9Tzb19dN2poCg3dbmhueI/H+Zrt7QM7jsneLZ1olskWu560vFaLZvRw0QB28aZqXnAo=;
Received: from [66.226.193.82] by web37304.mail.mud.yahoo.com via HTTP;
	Wed, 04 Jun 2008 08:05:24 PDT
X-Mailer: YahooMailRC/975.41 YahooMailWebService/0.7.199
Date: Wed, 4 Jun 2008 08:05:24 -0700 (PDT)
From: andrew klassen <aptklassen@yahoo.com>
Subject: Re: insert/update tps slow with indices on table > 1M rows
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1230970829-1212591924=:5431"
Message-ID: <981678.5431.qm@web37304.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200806/20
X-Sequence-Number: 30370

--0-1230970829-1212591924=:5431
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I am using multiple threads, but only one worker thread for insert/updated =
to this table.=0AI don't mind trying to add multiple threads for this table=
, but my guess is it would not =0Ahelp because basically the overall tps ra=
te is decreasing so dramatically. Since=0Athe cpu time consumed by the corr=
esponding postgres=A0server process for my thread is=0Asmall it does not se=
em to be the bottleneck. There has to be a bottleneck somewhere else. =0ADo=
 you agree or is there some flaw in my reasoning?=0A=0A----- Original Messa=
ge ----=0AFrom: Matthew Wakeling <matthew@flymine.org>=0ATo: andrew klassen=
 <aptklassen@yahoo.com>=0ACc: pgsql-performance@postgresql.org=0ASent: Wedn=
esday, June 4, 2008 5:31:22 AM=0ASubject: Re: [PERFORM] insert/update tps s=
low with indices on table > 1M rows=0A=0AOn Tue, 3 Jun 2008, andrew klassen=
 wrote:=0A> Basically, I have a somewhat constant rate of inserts/updates t=
hat go =0A> into a work queue and then get passed to postgres.=0A=0A> The c=
pu load is not that high, i.e. plenty of idle cpu. I am running an older=0A=
> version of freebsd=A0and the iostat output is not very detailed.=0A=0AIf =
you're running a "work queue" architecture, that probably means you =0Aonly=
 have one thread doing all the updates/inserts? It might be worth =0Agoing =
multi-threaded, and issuing inserts and updates through more than =0Aone co=
nnection. Postgres is designed pretty well to scale performance by =0Athe n=
umber of simultaneous connections.=0A=0AMatthew=0A=0A-- =0AContrary to popu=
lar belief, Unix is user friendly. It just happens to be=0Avery selective a=
bout who its friends are.=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -- Kyle Hearn=0A--=
 =0ASent via pgsql-performance mailing list (pgsql-performance@postgresql.o=
rg)=0ATo make changes to your subscription:=0Ahttp://www.postgresql.org/mai=
lpref/pgsql-performance=0A=0A=0A=0A      
--0-1230970829-1212591924=:5431
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">&nbsp;</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">I am using multiple threads, but only one worker thread for insert/updated to this table.</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">I don't mind trying to add multiple threads for this table, but my guess is it would not </DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">help because basically the overall tps rate is decreasing so dramatically. Since</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">the cpu time consumed by the corresponding postgres&nbsp;server process for my thread is</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">small it does not seem to be the bottleneck. There has to be a bottleneck somewhere else. </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">Do you agree or is there some flaw in my reasoning?</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: andrew klassen &lt;aptklassen@yahoo.com&gt;<BR>Cc: pgsql-performance@postgresql.org<BR>Sent: Wednesday, June 4, 2008 5:31:22 AM<BR>Subject: Re: [PERFORM] insert/update tps slow with indices on table &gt; 1M rows<BR><BR>On Tue, 3 Jun 2008, andrew klassen wrote:<BR>&gt; Basically, I have a somewhat constant rate of inserts/updates that go <BR>&gt; into a work queue and then get passed to postgres.<BR><BR>&gt; The cpu load is not that high, i.e. plenty of idle cpu. I am running an older<BR>&gt; version of freebsd&nbsp;and the iostat output is not very detailed.<BR><BR>If you're running a "work queue" architecture, that probably means you <BR>only have one thread doing all the updates/inserts? It might be worth <BR>going multi-threaded, and issuing inserts and updates through more than
 <BR>one connection. Postgres is designed pretty well to scale performance by <BR>the number of simultaneous connections.<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-1230970829-1212591924=:5431--