X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1DBA69DD6C4 for ; Thu, 1 Dec 2005 18:23:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16379-07 for ; Thu, 1 Dec 2005 18:23:43 -0400 (AST) X-Greylist: delayed 00:05:01.683534 by SQLgrey- Received: from rwcrmhc12.comcast.net (rwcrmhc14.comcast.net [216.148.227.154]) by postgresql.org (Postfix) with ESMTP id D56B79DD6B5 for ; Thu, 1 Dec 2005 18:23:44 -0400 (AST) Received: from dell8200 (pcp09613107pcs.rte20201.de.comcast.net[68.82.197.68]) by comcast.net (rwcrmhc14) with SMTP id <2005120122184301400qh8uae>; Thu, 1 Dec 2005 22:18:43 +0000 From: "Rick Schumeyer" To: "'Rick Schumeyer'" , Subject: Re: COPY into table too slow with index: now an I/O question Date: Thu, 1 Dec 2005 17:18:38 -0500 Message-ID: <00df01c5f6c5$2e84c310$0200a8c0@dell8200> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00E0_01C5F69B.45AEBB10" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: <00b101c5f6a0$d0ffea40$0200a8c0@dell8200> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200512/45 X-Sequence-Number: 15866 This is a multi-part message in MIME format. ------=_NextPart_000_00E0_01C5F69B.45AEBB10 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable As a follow up to my own question: =20 I reran the COPY both ways (with the index and without) while running iostat. The following values are averages: %user %nice %sys %iowait %idle no index 39 0 2.8 11 47 index 16 1.5 2.1 34 46 =20 I'm no performance guru, so please indulge a couple of silly questions: =20 1) Why is there so much idle time? I would think the CPU would = either be busy or waiting for IO. 2) It seems that I need to improve my disk situation. Would it = help to add another drive to my PC and keep the input data on a separate drive from my pg tables? If so, some pointers on the best way to set that up would be appreciated. =20 Please let me know if anyone has additional ideas. =20 -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Rick = Schumeyer Sent: Thursday, December 01, 2005 12:58 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] COPY into table too slow with index =20 I'm running postgresql 8.1.0 with postgis 1.0.4 on a FC3 system, 3Ghz, 1 = GB memory. =20 I am using COPY to fill a table that contains one postgis geometry = column. =20 With no geometry index, it takes about 45 seconds to COPY one file. =20 If I add a geometry index, this time degrades. It keeps getting worse = as more records are added to the table. It was up to over three minutes per file on my most recent test. =20 The problem is that each file contains about 5 - 10 minutes of data. Eventually, I want to add the data to the table in "real time". So the COPY needs to take = less time than=20 actually generating the data. =20 Here is the relevant section of my postgresql.conf. =20 # - Memory - =20 shared_buffers =3D 5000 # min 16 or max_connections*2, 8KB = each #temp_buffers =3D 1000 # min 100, 8KB each #max_prepared_transactions =3D 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem =3D 20000 # min 64, size in KB maintenance_work_mem =3D 20000 # min 1024, size in KB #max_stack_depth =3D 2048 # min 100, size in KB =20 Any suggestions for improvement? ------=_NextPart_000_00E0_01C5F69B.45AEBB10 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

As a follow up to my own = question:

 

I reran the COPY both ways (with = the index and without) while running iostat.  The following = values

are averages:

         &n= bsp;   %user  %nice  %sys  %iowait %idle

no = index      39      0     2.8    11      = 47

index         16      1.5   2.1    34      46

 

I’m no performance guru, so = please indulge a couple of silly questions:

 

1)       Why is there so = much idle time?  I would think the CPU would either be busy or waiting for = IO.

2)       It seems that I = need to improve my disk situation.  Would it help to add another drive to = my PC and

keep the input data on a separate = drive from my pg tables?  If so, some pointers on the best way to set = that up

would be = appreciated.

 

Please let me know if anyone has additional ideas.

 

-----Original Message-----
From: pgsql-performance-owner@postgresql.org = [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Rick = Schumeyer
Sent: Thursday, December 01, 2005 12:58 = PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] COPY = into table too slow with index

 

I’m running postgresql 8.1.0 with postgis 1.0.4 = on a FC3 system, 3Ghz, 1 GB memory.

 

I am using COPY to fill a table that contains one = postgis geometry column.

 

With no geometry index, it takes about 45 seconds to = COPY one file.

 

If I add a geometry index, this time degrades.  = It keeps getting worse as more records are

added to the table.  It was up to over three = minutes per file on my most recent test.

 

The problem is that each file contains about 5 = – 10 minutes of data.  Eventually, I want to

add the data to the table in “real = time”.  So the COPY needs to take less time than

actually generating the data.

 

Here is the relevant section of my = postgresql.conf.

 

# - Memory = -

 

shared_buffers =3D 5000           &nb= sp;   # min 16 or max_connections*2, 8KB each

#temp_buffers =3D 1000           &nb= sp;    # min 100, 8KB each

#max_prepared_transactions =3D 5            # = can be 0 or more

# note: increasing max_prepared_transactions costs ~600 bytes of shared = memory

# per transaction = slot, plus lock space (see max_locks_per_transaction).

work_mem =3D 20000           &n= bsp;  # min 64, size in KB

maintenance_work_mem =3D 20000        # min 1024, size in = KB

#max_stack_depth = =3D 2048           &nb= sp; # min 100, size in KB

 

Any suggestions for improvement?

------=_NextPart_000_00E0_01C5F69B.45AEBB10--