Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id ABE8E2E00C6 for ; Tue, 3 Jun 2008 23:44:17 -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 22383-06 for ; Tue, 3 Jun 2008 23:44:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from web37307.mail.mud.yahoo.com (web37307.mail.mud.yahoo.com [209.191.90.250]) by developer.postgresql.org (Postfix) with SMTP id 0700A2E00C4 for ; Tue, 3 Jun 2008 23:44:13 -0300 (ADT) Received: (qmail 31251 invoked by uid 60001); 4 Jun 2008 02:44:12 -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=yFqEhKnz+sU2Z5Lm3KkVK65eO5ghsczS2rwH55ngyxcCWkDjZvDfID9m22sDZQvmkqPKTM28Oss0wcmaT412F8/nyplgVZ8j0IIlLaVGpwDhZXxV2Yf9ZJS4kyBKM6T6gnBhQt7Zlc/x974tqFm4PLWxO2FI0IdX+o+eSjQQggs=; X-YMail-OSG: l.oSGnIVM1ltADbVYDaa64PekPGHF2hF.tZ3tI7bbkOCBDDGTKUmsP9CisYqrP_bmuzNjbBCVG97wL.rnONkCPYPF69SVstyV9cxNZyLszME6dIQ1vi0vE5l4Lo- Received: from [70.248.63.73] by web37307.mail.mud.yahoo.com via HTTP; Tue, 03 Jun 2008 19:44:12 PDT X-Mailer: YahooMailRC/975.41 YahooMailWebService/0.7.185 Date: Tue, 3 Jun 2008 19:44:12 -0700 (PDT) From: andrew klassen Subject: Re: insert/update tps slow with indices on table > 1M rows To: PFC , pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-803102203-1212547452=:30824" Message-ID: <761633.30824.qm@web37307.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200806/15 X-Sequence-Number: 30365 --0-803102203-1212547452=:30824 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I am not currently using copy, but=A0 I am using prepared statements=A0=A0= =0Afor table insert/updates so the overhead for the actual data transfer = =0Ashould be pretty good. I am sending at most=A0 300 inserts/updates =0Ape= r transaction, but that is just an arbitrary value. When the queue =0Agrows= , I could easily send more per transaction. I=A0 did experiment =0Aa little= , but it did not seem to help significantly at the time.=0A=A0=0AThe system= has 4G total memory. Shared memory is locked by the OS,=0Ai.e. not paged= =A0so I am only using shared_buffers=3D28MB.=0A=A0=0AThe=A0maximum data per= row is 324 bytes assuming maximum expected length of two =0Atext fields. T= here are 5 total indices: 1 8-byte, 2 4-byte and 2 text fields. =0AAs menti= oned all indices are btree.=0A=A0=0A=0A=A0=0A----- Original Message ----=0A= From: PFC =0ATo: andrew klassen ; p= gsql-performance@postgresql.org=0ASent: Tuesday, June 3, 2008 7:15:10 PM=0A= Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M ro= ws=0A=0AOn Wed, 04 Jun 2008 00:36:09 +0200, andrew klassen =A0 =0Awrote:=0A=0A> Running postgres 8.2.5=0A> =A0=0A> I have a tabl= e that has 5 indices, no foreign keys or any=0A> dependency on any other ta= ble. If delete the database and=0A> start entering entries, everything work= s very well until I get=0A> to some point (let's say 1M rows). Basically, I= have a somewhat=0A> constant rate of inserts/updates that go into a work q= ueue and then=0A> get passed to postgres. The work queue starts filling up = as the=0A> responsiveness slows down. For example=A0at 1.5M=0A> rows it tak= es >2=A0seconds for 300 inserts issued in one transaction.=0A> =A0=0A> Prio= r to this point I had added regular VACUUM ANALYZE on=0A> the table and it = did help.=A0 I increased maintenance work memory to=0A> 128M. I also set th= e fillfactor on the table indices to 50% (not sure=0A> if that made any dif= ference have to study results more closely).=A0=0A> =A0=0A> In an effort to= figure out the bottleneck, I DROPed 4 of the indices=0A> on the table and = the tps increased to over 1000. I don't really know=0A> which index removal= gave the best performance improvement. I=0A> dropped 2 32-bit indices and = 2=A0text indices which all using btree.=0A> =A0=0A> The cpu load is not tha= t high, i.e. plenty of idle cpu. I am running an=A0 =0A> older=0A> version = of freebsd=A0and the iostat output is not very detailed.=0A> During this ti= me, the number is low < 10Mbs. The system has an=0A> LSI Logic MegaRAID con= troller with 2 disks.=0A> =A0=0A> Any ideas on how to find the bottleneck/d= ecrease overhead of index usage.=0A> =A0=0A> Thanks.=0A=0A=A0=A0=A0 If you = are filling an empty table it is generally faster to create the=A0 =0Aindex= es after the data import.=0A=A0=A0=A0 Of course if this is a live table or = you need the indexes during the=A0 =0Aimport, this is not an option.=0A=A0= =A0=A0 I find it generally faster to lightly preprocess the data and genera= te=A0 =0Atext files that I then import using COPY, then doing the rest of t= he=A0 =0Aprocessing in SQL.=0A=0A=A0=A0=A0 How much RAM in the box ? size o= f the data & indexes ?=0A=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-803102203-1212547452=:30824 Content-Type: text/html; charset=us-ascii
I am not currently using copy, but  I am using prepared statements  
for table insert/updates so the overhead for the actual data transfer
should be pretty good. I am sending at most  300 inserts/updates
per transaction, but that is just an arbitrary value. When the queue
grows, I could easily send more per transaction. I  did experiment
a little, but it did not seem to help significantly at the time.
 
The system has 4G total memory. Shared memory is locked by the OS,
i.e. not paged so I am only using shared_buffers=28MB.
 
The maximum data per row is 324 bytes assuming maximum expected length of two
text fields. There are 5 total indices: 1 8-byte, 2 4-byte and 2 text fields.
As mentioned all indices are btree.
 

 
----- Original Message ----
From: PFC <lists@peufeu.com>
To: andrew klassen <aptklassen@yahoo.com>; pgsql-performance@postgresql.org
Sent: Tuesday, June 3, 2008 7:15:10 PM
Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M rows

On Wed, 04 Jun 2008 00:36:09 +0200, andrew klassen <aptklassen@yahoo.com> 
wrote:

> Running postgres 8.2.5
>  
> I have a table that has 5 indices, no foreign keys or any
> dependency on any other table. If delete the database and
> start entering entries, everything works very well until I get
> to some point (let's say 1M rows). Basically, I have a somewhat
> constant rate of inserts/updates that go into a work queue and then
> get passed to postgres. The work queue starts filling up as the
> responsiveness slows down. For example at 1.5M
> rows it takes >2 seconds for 300 inserts issued in one transaction.
>  
> Prior to this point I had added regular VACUUM ANALYZE on
> the table and it did help.  I increased maintenance work memory to
> 128M. I also set the fillfactor on the table indices to 50% (not sure
> if that made any difference have to study results more closely). 
>  
> In an effort to figure out the bottleneck, I DROPed 4 of the indices
> on the table and the tps increased to over 1000. I don't really know
> which index removal gave the best performance improvement. I
> dropped 2 32-bit indices and 2 text indices which all using btree.
>  
> The cpu load is not that high, i.e. plenty of idle cpu. I am running an 
> older
> version of freebsd and the iostat output is not very detailed.
> During this time, the number is low < 10Mbs. The system has an
> LSI Logic MegaRAID controller with 2 disks.
>  
> Any ideas on how to find the bottleneck/decrease overhead of index usage.
>  
> Thanks.

    If you are filling an empty table it is generally faster to create the 
indexes after the data import.
    Of course if this is a live table or you need the indexes during the 
import, this is not an option.
    I find it generally faster to lightly preprocess the data and generate 
text files that I then import using COPY, then doing the rest of the 
processing in SQL.

    How much RAM in the box ? size of the data & indexes ?

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--0-803102203-1212547452=:30824--