Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 33D842E0056
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Tue,  3 Jun 2008 19:42:54 -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 93399-07
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Tue,  3 Jun 2008 19:42:46 -0300 (ADT)
X-Greylist: delayed 00:06:38.983806 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 433A22E0041
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Jun 2008 19:42:50 -0300 (ADT)
Received: (qmail 3535 invoked by uid 60001); 3 Jun 2008 22:36:09 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=bxouOGey3UtcO51xd94nnDWlxeZdPx/2d8Ua9dYhyq3tmx22tPtce9t/KWZWSoiGOLaJWMq9CPcp6T6b0jU7chUibW/pn7LXN5ECRx+pgt0tinNirV1AuKrcuj5xSd31ySSLOOUWwJl3XdQAZK+FAZhM9xrO3ZR1PNqnUaqbhpk=;
X-YMail-OSG: 
 XMCeAwgVM1m4P0owBbYJA72XDwTo12.edJzImUQU9a4MtAjhWoBafW4pRKV_DqNgEw--
Received: from [66.226.193.82] by web37304.mail.mud.yahoo.com via HTTP;
	Tue, 03 Jun 2008 15:36:09 PDT
X-Mailer: YahooMailRC/975.41 YahooMailWebService/0.7.185
Date: Tue, 3 Jun 2008 15:36:09 -0700 (PDT)
From: andrew klassen <aptklassen@yahoo.com>
Subject: 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-1513909772-1212532569=:3510"
Message-ID: <691681.3510.qm@web37304.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200806/12
X-Sequence-Number: 30362

--0-1513909772-1212532569=:3510
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Running postgres 8.2.5=0A=A0=0AI have a table that has 5 indices, no foreig=
n keys or any =0Adependency on any other table. If delete the database and =
=0Astart entering entries, everything works very well until I get=0Ato some=
 point (let's say 1M rows). Basically, I have a somewhat=0Aconstant rate of=
 inserts/updates that go into a work queue and then=0Aget passed to postgre=
s. The work queue starts filling up as the=0Aresponsiveness slows down. For=
 example=A0at 1.5M =0Arows it takes >2=A0seconds for 300 inserts issued in =
one transaction. =0A=A0=0APrior to this point I had added regular VACUUM AN=
ALYZE on =0Athe table and it did help.=A0 I increased maintenance work memo=
ry to =0A128M. I also set the fillfactor on the table indices to 50% (not s=
ure =0Aif that made any difference have to study results more closely).=A0 =
=0A=A0=0AIn an effort to figure out the bottleneck, I DROPed 4 of the indic=
es =0Aon the table and the tps increased to over 1000. I don't really know =
=0Awhich index removal gave the best performance improvement. I =0Adropped =
2 32-bit indices and 2=A0text indices which all using btree. =0A=A0=0AThe 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.=0ADuring t=
his time, the number is low < 10Mbs. The system has an =0ALSI Logic MegaRAI=
D controller with 2 disks.=0A=A0=0AAny ideas on how to find the bottleneck/=
decrease overhead of index usage. =0A=A0=0AThanks.=0A=0A=0A      
--0-1513909772-1212532569=:3510
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>Running postgres 8.2.5</DIV>
<DIV>&nbsp;</DIV>
<DIV>I have a table that has 5 indices, no foreign keys or any </DIV>
<DIV>dependency on any other table. If delete the database and </DIV>
<DIV>start entering entries, everything works very well until I get</DIV>
<DIV>to some point (let's say 1M rows). Basically, I have a somewhat</DIV>
<DIV>constant rate of inserts/updates that go into a work queue and then</DIV>
<DIV>get passed to postgres. The work queue starts filling up as the</DIV>
<DIV>responsiveness slows down. For example&nbsp;at 1.5M </DIV>
<DIV>rows it takes &gt;2&nbsp;seconds for 300 inserts issued in one transaction. </DIV>
<DIV>&nbsp;</DIV>
<DIV>Prior to this point I had added regular VACUUM ANALYZE on </DIV>
<DIV>the table and it did help.&nbsp; I increased maintenance work memory to </DIV>
<DIV>128M. I also set the fillfactor on the table indices to 50% (not sure </DIV>
<DIV>if that made any difference have to study results more closely).&nbsp; </DIV>
<DIV>&nbsp;</DIV>
<DIV>In an effort to figure out the bottleneck, I DROPed 4 of the indices </DIV>
<DIV>on the table and the tps increased to over 1000. I don't really know </DIV>
<DIV>which index removal gave the best performance improvement. I </DIV>
<DIV>dropped 2 32-bit indices and 2&nbsp;text indices which all using btree. </DIV>
<DIV>&nbsp;</DIV>
<DIV>The cpu load is not that high, i.e. plenty of idle cpu. I am running an older</DIV>
<DIV>version of freebsd&nbsp;and the iostat output is not very detailed.</DIV>
<DIV>During this time, the number is low &lt; 10Mbs. The system has an </DIV>
<DIV>LSI Logic MegaRAID controller with 2 disks.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Any ideas on how to find the bottleneck/decrease overhead of index usage. </DIV>
<DIV>&nbsp;</DIV>
<DIV>Thanks.</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV></div><br>

      </body></html>
--0-1513909772-1212532569=:3510--