From: Rick Schumeyer <rschumeyer@ieee.org>
To: 'Rick Schumeyer' <rschumeyer@ieee.org>
To: pgsql-performance@postgresql.org
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> (raw)
In-Reply-To: <00b101c5f6a0$d0ffea40$0200a8c0@dell8200>
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:
%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 = 5000 # min 16 or max_connections*2, 8KB each
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transactions = 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 = 20000 # min 64, size in KB
maintenance_work_mem = 20000 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
Any suggestions for improvement?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rschumeyer@ieee.org
Subject: Re: COPY into table too slow with index: now an I/O question
In-Reply-To: <00df01c5f6c5$2e84c310$0200a8c0@dell8200>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox