From aptklassen@yahoo.com Tue Jun 3 22:42:54 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 33D842E0056 for ; 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 ; 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 ; 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 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
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.
 
 
 
 
 
 

--0-1513909772-1212532569=:3510-- From lists@peufeu.com Wed Jun 4 00:08:31 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id BD7172E0144 for ; Tue, 3 Jun 2008 21:08:31 -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 80503-02-3 for ; Tue, 3 Jun 2008 21:08:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 30.mail-out.ovh.net (30.mail-out.ovh.net [213.186.62.213]) by developer.postgresql.org (Postfix) with SMTP id EBC692E0045 for ; Tue, 3 Jun 2008 21:07:44 -0300 (ADT) Received: (qmail 3824 invoked by uid 503); 4 Jun 2008 00:08:03 -0000 Received: from unknown (HELO mail243.ha.ovh.net) (213.186.33.59) by 30.mail-out.ovh.net with SMTP; 4 Jun 2008 00:08:03 -0000 Received: from b0.ovh.net (HELO queue-out) (213.186.33.50) by b0.ovh.net with SMTP; 4 Jun 2008 00:07:36 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13.peufeu.com) (88.161.102.87) by ns0.ovh.net with SMTP; 4 Jun 2008 00:07:35 -0000 Date: Wed, 04 Jun 2008 02:15:10 +0200 To: "andrew klassen" , pgsql-performance@postgresql.org Subject: Re: insert/update tps slow with indices on table > 1M rows From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: <691681.3510.qm@web37304.mail.mud.yahoo.com> Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <691681.3510.qm@web37304.mail.mud.yahoo.com> User-Agent: Opera Mail/9.24 (Linux) X-Ovh-Tracer-Id: 15908402735668135556 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|H 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.633 tagged_above=0 required=5 tests=AWL=0.633 X-Spam-Level: X-Archive-Number: 200806/14 X-Sequence-Number: 30364 On Wed, 04 Jun 2008 00:36:09 +0200, andrew klassen 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 ? From aptklassen@yahoo.com Wed Jun 4 02:44:17 2008 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-- From scott.marlowe@gmail.com Wed Jun 4 06:04:16 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 0A7E82E0131 for ; Wed, 4 Jun 2008 03:04:16 -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 09249-05-3 for ; Wed, 4 Jun 2008 03:03:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.182]) by developer.postgresql.org (Postfix) with ESMTP id 5AEDF2E00DE for ; Wed, 4 Jun 2008 03:02:35 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id n4so1061008wag.5 for ; Tue, 03 Jun 2008 23:02:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=OzUkDFvybT3V+dFque2J9l6jVC1c1zHG3alvLFM/IyQ=; b=YW80rFktVy6cdWjf/6FU4kOEm3yArIOrz8U6104kQDEyKoQPjvWnWC6vujt77fkWy7Lo/DymDOJAKMj+G7K0nZb5KFE+M7GDYuZ5ETdkT/+I6cZC9WpHsI0UvXeRQzWapoJqPRznSlJMIq5r1hRjXIp1Kdwf8lHKoBSjH+pFlcg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=YOVeIgvbuCrlF14s5Dn5doOc7ToQGEm6sDYJT1eSsjqJ01wuNFdeBTehSQTFrIR0sAFHD2pHadZImT6ua6v8NgBIrk3AnOyGFKFJgB7C8G6ZR6Yt6w6vAg3dRufGeAfywLYGi4rgJn4KmMfY3MNcSR4v/QcNgsjAP0AbxDyG5k4= Received: by 10.114.38.2 with SMTP id l2mr12312630wal.69.1212559354539; Tue, 03 Jun 2008 23:02:34 -0700 (PDT) Received: by 10.114.130.17 with HTTP; Tue, 3 Jun 2008 23:02:34 -0700 (PDT) Message-ID: Date: Wed, 4 Jun 2008 00:02:34 -0600 From: "Scott Marlowe" To: "andrew klassen" Subject: Re: insert/update tps slow with indices on table > 1M rows Cc: pgsql-performance@postgresql.org In-Reply-To: <691681.3510.qm@web37304.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <691681.3510.qm@web37304.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/16 X-Sequence-Number: 30366 On Tue, Jun 3, 2008 at 4:36 PM, andrew klassen wrote: > 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. Older versions of BSD can be pretty pokey compared to the 6.x and 7.x branches. I seriously consider upgrading to 7 if possible. The cost of maintaining indexes is always an issue. There are a few things you can do to help out. Partitioning and having fewer indexes are what I'd recommend. From matthew@flymine.org Wed Jun 4 10:31:38 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id C00462E0073 for ; Wed, 4 Jun 2008 07:31: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 88663-01 for ; Wed, 4 Jun 2008 07:31:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ppsw-6.csi.cam.ac.uk (ppsw-6.csi.cam.ac.uk [131.111.8.136]) by developer.postgresql.org (Postfix) with ESMTP id 8D5F22E0072 for ; Wed, 4 Jun 2008 07:31:29 -0300 (ADT) X-Cam-SpamDetails: Not scanned X-Cam-AntiVirus: No virus found X-Cam-ScannerInfo: http://www.cam.ac.uk/cs/email/scanner/ Received: from flymine.sysbiol.cam.ac.uk ([131.111.60.120]:40659 helo=flymine.flymine.org) by ppsw-6.csi.cam.ac.uk (ppsw.cam.ac.uk [131.111.8.136]:25) with esmtp id 1K3qH5-0002ES-Jt (Exim 4.67) (return-path ); Wed, 04 Jun 2008 11:31:23 +0100 Received: from host103 ([192.168.128.103] helo=aragorn.flymine.org ident=Debian-exim) by flymine.flymine.org with esmtp (Exim 4.12) id 1K3qH5-0005Dk-00; Wed, 04 Jun 2008 11:31:23 +0100 Received: from mnw21 (helo=localhost) by aragorn.flymine.org with local-esmtp (Exim 4.69) (envelope-from ) id 1K3qH4-000404-TJ; Wed, 04 Jun 2008 11:31:22 +0100 Date: Wed, 4 Jun 2008 11:31:22 +0100 (BST) From: Matthew Wakeling X-X-Sender: mnw21@aragorn.flymine.org To: andrew klassen cc: pgsql-performance@postgresql.org Subject: Re: insert/update tps slow with indices on table > 1M rows In-Reply-To: <691681.3510.qm@web37304.mail.mud.yahoo.com> Message-ID: References: <691681.3510.qm@web37304.mail.mud.yahoo.com> MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 Content-Type: TEXT/PLAIN; charset=iso-8859-1; format=flowed Content-Transfer-Encoding: QUOTED-PRINTABLE X-Archive-Number: 200806/17 X-Sequence-Number: 30367 On Tue, 3 Jun 2008, andrew klassen wrote: > Basically, I have a somewhat constant rate of inserts/updates that go=20 > into a work queue and then get passed to postgres. > The cpu load is not that high, i.e. plenty of idle cpu. I am running an o= lder > version of freebsd=A0and the iostat output is not very detailed. If you're running a "work queue" architecture, that probably means you=20 only have one thread doing all the updates/inserts? It might be worth=20 going multi-threaded, and issuing inserts and updates through more than=20 one connection. Postgres is designed pretty well to scale performance by=20 the number of simultaneous connections. Matthew --=20 Contrary to popular belief, Unix is user friendly. It just happens to be very selective about who its friends are. -- Kyle Hearn= From aptklassen@yahoo.com Wed Jun 4 15:05:38 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 7B7402E007A for ; 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 ; 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 ; 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 Subject: Re: insert/update tps slow with indices on table > 1M rows To: Matthew Wakeling 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 =0ATo: andrew klassen= =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
 
I am using multiple threads, but only one worker thread for insert/updated to this table.
I don't mind trying to add multiple threads for this table, but my guess is it would not
help because basically the overall tps rate is decreasing so dramatically. Since
the cpu time consumed by the corresponding postgres server process for my thread is
small it does not seem to be the bottleneck. There has to be a bottleneck somewhere else.
 
Do you agree or is there some flaw in my reasoning?

 
----- Original Message ----
From: Matthew Wakeling <matthew@flymine.org>
To: andrew klassen <aptklassen@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Wednesday, June 4, 2008 5:31:22 AM
Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M rows

On Tue, 3 Jun 2008, andrew klassen wrote:
> Basically, I have a somewhat constant rate of inserts/updates that go
> into a work queue and then get passed to postgres.

> 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.

If you're running a "work queue" architecture, that probably means you
only have one thread doing all the updates/inserts? It might be worth
going multi-threaded, and issuing inserts and updates through more than
one connection. Postgres is designed pretty well to scale performance by
the number of simultaneous connections.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are.                -- Kyle Hearn
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--0-1230970829-1212591924=:5431-- From matthew@flymine.org Wed Jun 4 15:11:05 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id C4C0C2E0078 for ; Wed, 4 Jun 2008 12:11:05 -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 44143-04 for ; Wed, 4 Jun 2008 12:10:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ppsw-1.csi.cam.ac.uk (ppsw-1.csi.cam.ac.uk [131.111.8.131]) by developer.postgresql.org (Postfix) with ESMTP id 333C42E007A for ; Wed, 4 Jun 2008 12:10:51 -0300 (ADT) X-Cam-SpamDetails: Not scanned X-Cam-AntiVirus: No virus found X-Cam-ScannerInfo: http://www.cam.ac.uk/cs/email/scanner/ Received: from flymine.sysbiol.cam.ac.uk ([131.111.60.120]:40910 helo=flymine.flymine.org) by ppsw-1.csi.cam.ac.uk (ppsw.cam.ac.uk [131.111.8.131]:25) with esmtp id 1K3udK-0007Je-5X (Exim 4.67) for pgsql-performance@postgresql.org (return-path ); Wed, 04 Jun 2008 16:10:38 +0100 Received: from host103 ([192.168.128.103] helo=aragorn.flymine.org ident=Debian-exim) by flymine.flymine.org with esmtp (Exim 4.12) id 1K3udK-0005yr-00 for pgsql-performance@postgresql.org; Wed, 04 Jun 2008 16:10:38 +0100 Received: from mnw21 (helo=localhost) by aragorn.flymine.org with local-esmtp (Exim 4.69) (envelope-from ) id 1K3udK-0004sU-LI for pgsql-performance@postgresql.org; Wed, 04 Jun 2008 16:10:38 +0100 Date: Wed, 4 Jun 2008 16:10:38 +0100 (BST) From: Matthew Wakeling X-X-Sender: mnw21@aragorn.flymine.org To: pgsql-performance@postgresql.org Subject: Re: insert/update tps slow with indices on table > 1M rows In-Reply-To: <981678.5431.qm@web37304.mail.mud.yahoo.com> Message-ID: References: <981678.5431.qm@web37304.mail.mud.yahoo.com> MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 Content-Type: TEXT/PLAIN; charset=iso-8859-1; format=flowed Content-Transfer-Encoding: QUOTED-PRINTABLE X-Archive-Number: 200806/23 X-Sequence-Number: 30373 On Wed, 4 Jun 2008, andrew klassen wrote: > I am using multiple threads, but only one worker thread for insert/update= d to this table. > I don't mind trying to add multiple threads for this table, but my guess = is it would not > help because basically the overall tps rate is decreasing so dramatically= . Since > the cpu time consumed by the corresponding postgres=A0server process for = my thread is > small it does not seem to be the bottleneck. There has to be a bottleneck= somewhere else. > Do you agree or is there some flaw in my reasoning? There is indeed a flaw in your reasoning - there may be very little CPU=20 time consumed, but that just indicates that the discs are busy. Getting=20 Postgres to do multiple things at once will cause a more efficient use of= =20 the disc subsystem, resulting in greater overall throughput. This is=20 especially the case if you have multiple discs in your box. Matthew --=20 Contrary to popular belief, Unix is user friendly. It just happens to be very selective about who its friends are. -- Kyle Hearn= From aptklassen@yahoo.com Wed Jun 4 17:24:18 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 43D512E0055 for ; 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 ; 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 ; 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 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 =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

I agree that the discs are probably very busy. I do have 2 disks but they are
for redundancy. Would it help to put the data, indexes and xlog on separate
disk partitions?
 
I'll try adding more threads to update the table as you suggest.
 

 
----- Original Message ----
From: Matthew Wakeling <matthew@flymine.org>
To: pgsql-performance@postgresql.org
Sent: Wednesday, June 4, 2008 10:10:38 AM
Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M rows

On Wed, 4 Jun 2008, andrew klassen wrote:
> I am using multiple threads, but only one worker thread for insert/updated to this table.
> I don't mind trying to add multiple threads for this table, but my guess is it would not
> help because basically the overall tps rate is decreasing so dramatically. Since
> the cpu time consumed by the corresponding postgres server process for my thread is
> small it does not seem to be the bottleneck. There has to be a bottleneck somewhere else.
> Do you agree or is there some flaw in my reasoning?

There is indeed a flaw in your reasoning - there may be very little CPU
time consumed, but that just indicates that the discs are busy. Getting
Postgres to do multiple things at once will cause a more efficient use of
the disc subsystem, resulting in greater overall throughput. This is
especially the case if you have multiple discs in your box.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are.                -- Kyle Hearn
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--0-83221626-1212600249=:23550-- From james@mansionfamily.plus.com Wed Jun 4 20:10:47 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 184A12E0033 for ; Wed, 4 Jun 2008 17:10:47 -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 45831-05 for ; Wed, 4 Jun 2008 17:10:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ptb-relay03.plus.net (ptb-relay03.plus.net [212.159.14.214]) by developer.postgresql.org (Postfix) with ESMTP id 57B012E0038 for ; Wed, 4 Jun 2008 17:10:41 -0300 (ADT) Received: from [80.229.150.39] (helo=pd600.barnhouse) by ptb-relay03.plus.net with esmtp (Exim) id 1K3zJe-0006uc-Sp; Wed, 04 Jun 2008 21:10:39 +0100 Received: from [192.168.0.87] (unknown [192.168.0.87]) by pd600.barnhouse (Postfix) with ESMTP id 296A94E1C2B; Wed, 4 Jun 2008 21:17:23 +0100 (BST) Message-ID: <4846F7F1.1050408@mansionfamily.plus.com> Date: Wed, 04 Jun 2008 21:15:45 +0100 From: James Mansion Organization: MsgWare User-Agent: Thunderbird 2.0.0.14 (Windows/20080421) MIME-Version: 1.0 To: Matthew Wakeling CC: andrew klassen , pgsql-performance@postgresql.org Subject: Re: insert/update tps slow with indices on table > 1M rows References: <691681.3510.qm@web37304.mail.mud.yahoo.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Plusnet-Relay: 1aac9a3bc1efca50e2198302ffcd82d8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/27 X-Sequence-Number: 30377 Matthew Wakeling wrote: > If you're running a "work queue" architecture, that probably means you > only have one thread doing all the updates/inserts? It might be worth > going multi-threaded, and issuing inserts and updates through more > than one connection. Postgres is designed pretty well to scale > performance by the number of simultaneous connections. That would explain a disappointing upper limit on insert rate, but not any sort of cliff for the rate. Nor, really, any material slowdown, if the single thread implies that we're stuck on round trip latency as a material limiting factor. James From james@mansionfamily.plus.com Wed Jun 4 20:15:30 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 041602E0033 for ; Wed, 4 Jun 2008 17:15:30 -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 51757-04 for ; Wed, 4 Jun 2008 17:15:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from pih-relay05.plus.net (pih-relay05.plus.net [212.159.14.132]) by developer.postgresql.org (Postfix) with ESMTP id A1DB82E0035 for ; Wed, 4 Jun 2008 17:15:23 -0300 (ADT) Received: from [80.229.150.39] (helo=pd600.barnhouse) by pih-relay05.plus.net with esmtp (Exim) id 1K3zOC-0004H4-9O; Wed, 04 Jun 2008 21:15:20 +0100 Received: from [192.168.0.87] (unknown [192.168.0.87]) by pd600.barnhouse (Postfix) with ESMTP id C70434E1C2B; Wed, 4 Jun 2008 21:22:04 +0100 (BST) Message-ID: <4846F90A.7030700@mansionfamily.plus.com> Date: Wed, 04 Jun 2008 21:20:26 +0100 From: James Mansion Organization: MsgWare User-Agent: Thunderbird 2.0.0.14 (Windows/20080421) MIME-Version: 1.0 To: andrew klassen CC: pgsql-performance@postgresql.org Subject: Re: insert/update tps slow with indices on table > 1M rows References: <880373.23550.qm@web37305.mail.mud.yahoo.com> In-Reply-To: <880373.23550.qm@web37305.mail.mud.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Plusnet-Relay: 1482fead5d3a94395c1c05bea1b894b5 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/28 X-Sequence-Number: 30378 andrew klassen wrote: > I'll try adding more threads to update the table as you suggest. You could try materially increasing the update batch size too. As an exercise you could see what the performance of COPY is by backing out the data and reloading it from a suitable file. From aptklassen@yahoo.com Wed Jun 4 21:30:18 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id B45932E0044 for ; Wed, 4 Jun 2008 18:30: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 34214-05 for ; Wed, 4 Jun 2008 18:30:11 -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 A15272E0039 for ; Wed, 4 Jun 2008 18:30:14 -0300 (ADT) Received: (qmail 7797 invoked by uid 60001); 4 Jun 2008 21:30:13 -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=sCK/eZ1TjQbxHqOshYTJRMu6VWgfgvqA6xa2SMN7xEhow7gnjJ9zS0MYntlg2KUHVdupfgSEjb7IzQ8/Cjy1wp/eVcilOUOSseWKYo/xiZ/Zpjc42rgZuVwNqnn2vB0qE4PlvDgOyj1gZ7oG4QltnnqNqr7aSy3YWVCfYyNH4bE=; Received: from [66.226.193.82] by web37304.mail.mud.yahoo.com via HTTP; Wed, 04 Jun 2008 14:30:13 PDT X-Mailer: YahooMailRC/975.41 YahooMailWebService/0.7.199 Date: Wed, 4 Jun 2008 14:30:13 -0700 (PDT) From: andrew klassen Subject: Re: insert/update tps slow with indices on table > 1M rows To: James Mansion Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1473167840-1212615013=:6724" Message-ID: <354284.6724.qm@web37304.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/29 X-Sequence-Number: 30379 --0-1473167840-1212615013=:6724 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I am using the c-library interface and for these particular transactions=0A= I preload PREPARE statements. Then as I get requests, I issue a BEGIN, =0Af= ollowed by at most 300 EXECUTES and then a COMMIT. That is the=0Ageneral sc= enario. What value beyond 300 should I try? =0AAlso, how=A0might COPY (whic= h involves file I/O) improve the =0Aabove scenario? =0AThanks.=0A=0A=0A----= - Original Message ----=0AFrom: James Mansion =0ATo: andrew klassen =0ACc: pgsql-performance@postg= resql.org=0ASent: Wednesday, June 4, 2008 3:20:26 PM=0ASubject: Re: [PERFOR= M] insert/update tps slow with indices on table > 1M rows=0A=0Aandrew klass= en wrote:=0A> I'll try adding more threads to update the table as you sugge= st.=0AYou could try materially increasing the update batch size too.=A0 As = an =0Aexercise you could=0Asee what the performance of COPY is by backing o= ut the data and =0Areloading it from=0Aa suitable file.=0A=0A=0A --0-1473167840-1212615013=:6724 Content-Type: text/html; charset=us-ascii

 

I am using the c-library interface and for these particular transactions

I preload PREPARE statements. Then as I get requests, I issue a BEGIN,

followed by at most 300 EXECUTES and then a COMMIT. That is the

general scenario. What value beyond 300 should I try?

 

Also, how might COPY (which involves file I/O) improve the

above scenario?

 

Thanks.

----- Original Message ----
From: James Mansion <james@mansionfamily.plus.com>
To: andrew klassen <aptklassen@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Wednesday, June 4, 2008 3:20:26 PM
Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M rows

andrew klassen wrote:
> I'll try adding more threads to update the table as you suggest.
You could try materially increasing the update batch size too.  As an
exercise you could
see what the performance of COPY is by backing out the data and
reloading it from
a suitable file.


--0-1473167840-1212615013=:6724-- From lists@peufeu.com Wed Jun 4 21:42:45 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 092B92E0054 for ; Wed, 4 Jun 2008 18:42:45 -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 48117-02 for ; Wed, 4 Jun 2008 18:42:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 30.mail-out.ovh.net (30.mail-out.ovh.net [213.186.62.213]) by developer.postgresql.org (Postfix) with SMTP id B94FB2E002F for ; Wed, 4 Jun 2008 18:42:41 -0300 (ADT) Received: (qmail 889 invoked by uid 503); 4 Jun 2008 21:43:00 -0000 Received: from unknown (HELO mail243.ha.ovh.net) (213.186.33.59) by 30.mail-out.ovh.net with SMTP; 4 Jun 2008 21:43:00 -0000 Received: from b0.ovh.net (HELO queue-out) (213.186.33.50) by b0.ovh.net with SMTP; 4 Jun 2008 21:42:31 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13.peufeu.com) (88.161.102.87) by ns0.ovh.net with SMTP; 4 Jun 2008 21:42:30 -0000 Date: Wed, 04 Jun 2008 23:50:09 +0200 To: "andrew klassen" , "James Mansion" Subject: Re: insert/update tps slow with indices on table > 1M rows From: PFC Cc: pgsql-performance@postgresql.org Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: <354284.6724.qm@web37304.mail.mud.yahoo.com> Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <354284.6724.qm@web37304.mail.mud.yahoo.com> User-Agent: Opera Mail/9.24 (Linux) X-Ovh-Tracer-Id: 884394378667558532 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|H 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.632 tagged_above=0 required=5 tests=AWL=0.632 X-Spam-Level: X-Archive-Number: 200806/30 X-Sequence-Number: 30380 > I am using the c-library interface and for these particular transactions > I preload PREPARE statements. Then as I get requests, I issue a BEGIN, > followed by at most 300 EXECUTES and then a COMMIT. That is the > general scenario. What value beyond 300 should I try? > Thanks. Do you have PREPARE statements whose performance might change as the table grows ? I mean, some selects, etc... in that case if you start with an empty table, after inserting say 100K rows you might want to just disconnect, reconnect and analyze to trigger replanning of those statements. > Also, how might COPY (which involves file I/O) improve the > above scenario? It won't but if you see that COPY is very much faster than your INSERT based process it will give you a useful piece of information. I understand your problem is : - Create table with indexes - Insert batches of rows - After a while it gets slow Try : - Create table with indexes - COPY huge batch of rows - Compare time with above Since COPY also updates the indexes just like your inserts do it will tell you if it's the indexes which slow you down or something else. Also for insert heavy loads it's a good idea to put the xlog on a separate disk (to double your disk bandwidth) unless you have a monster disk setup. During your INSERTs, do you also make some SELECTs ? Do you have triggers on the table ? Foreign keys ? Anything ? How much RAM you have ? And can you measure the size of the table+indexes when it gets slow ? > > > ----- Original Message ---- > From: James Mansion > To: andrew klassen > Cc: pgsql-performance@postgresql.org > Sent: Wednesday, June 4, 2008 3:20:26 PM > Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M > rows > > andrew klassen wrote: >> I'll try adding more threads to update the table as you suggest. > You could try materially increasing the update batch size too.  As an > exercise you could > see what the performance of COPY is by backing out the data and > reloading it from > a suitable file. > > > From tgl@sss.pgh.pa.us Wed Jun 4 22:52:25 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 2677B2E002C for ; Wed, 4 Jun 2008 19:52:25 -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 15376-08 for ; Wed, 4 Jun 2008 19:52:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by developer.postgresql.org (Postfix) with ESMTP id 4A4742E002E for ; Wed, 4 Jun 2008 19:52:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m54MqJcv006657; Wed, 4 Jun 2008 18:52:19 -0400 (EDT) To: andrew klassen cc: James Mansion , pgsql-performance@postgresql.org Subject: Re: insert/update tps slow with indices on table > 1M rows In-reply-to: <354284.6724.qm@web37304.mail.mud.yahoo.com> References: <354284.6724.qm@web37304.mail.mud.yahoo.com> Comments: In-reply-to andrew klassen message dated "Wed, 04 Jun 2008 14:30:13 -0700" Date: Wed, 04 Jun 2008 18:52:19 -0400 Message-ID: <6656.1212619939@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/31 X-Sequence-Number: 30381 andrew klassen writes: > I am using the c-library interface and for these particular transactions > I preload PREPARE statements. Then as I get requests, I issue a BEGIN, > followed by at most 300 EXECUTES and then a COMMIT. That is the > general scenario. What value beyond 300 should I try? Well, you could try numbers in the low thousands, but you'll probably get only incremental improvement. > Also, how might COPY (which involves file I/O) improve the > above scenario? COPY needn't involve file I/O. If you are using libpq you can push anything you want into PQputCopyData. This would involve formatting the data according to COPY's escaping rules, which are rather different from straight SQL, but I doubt it'd be a huge amount of code. Seems worth trying. regards, tom lane From heikki@enterprisedb.com Thu Jun 5 06:18:45 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 674542E0030 for ; Thu, 5 Jun 2008 03:18:45 -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 55275-01 for ; Thu, 5 Jun 2008 03:18:37 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail01.enterprisedb.com (webmail.enterprisedb.com [63.246.7.168]) by developer.postgresql.org (Postfix) with ESMTP id ED9B02E002C for ; Thu, 5 Jun 2008 03:18:38 -0300 (ADT) thread-index: AcjG1BlQB3PJJ47JQt6RhbN5q+MFDg== Received: from [192.168.1.105] ([82.181.212.226]) by mail01.enterprisedb.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.3959); Thu, 5 Jun 2008 02:19:21 -0400 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4133 Message-ID: <48478535.2010905@enterprisedb.com> Date: Thu, 05 Jun 2008 09:18:29 +0300 From: "Heikki Linnakangas" Organization: EnterpriseDB User-Agent: Mozilla-Thunderbird 2.0.0.12 (X11/20080420) MIME-Version: 1.0 To: "andrew klassen" Cc: "James Mansion" , Subject: Re: insert/update tps slow with indices on table > 1M rows References: <354284.6724.qm@web37304.mail.mud.yahoo.com> In-Reply-To: <354284.6724.qm@web37304.mail.mud.yahoo.com> Content-Type: text/plain; format=flowed; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 05 Jun 2008 06:19:23.0156 (UTC) FILETIME=[18CDB540:01C8C6D4] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/33 X-Sequence-Number: 30383 andrew klassen wrote: > I am using the c-library interface and for these particular transactions > I preload PREPARE statements. Then as I get requests, I issue a BEGIN, > followed by at most 300 EXECUTES and then a COMMIT. That is the > general scenario. What value beyond 300 should I try? Make sure you use the asynchronous PQsendQuery, instead of plain PQexec. Otherwise you'll be doing a round-trip for each EXECUTE anyway regardless of the batch size. Of course, if the bottleneck is somewhere else, it won't make a difference.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com