From: Thomas Finneid <tfinneid@student.matnat.uio.no>
To: pgsql-performance@postgresql.org
Subject: Re: slow update of index during insert/copy
Date: Mon, 01 Sep 2008 12:16:23 +0100
Message-ID: <48BBCF07.8020709@ifi.uio.no> (raw)
In-Reply-To: <a1ec7d000809010108m58d12c36v7086376caf3cf195@mail.gmail.com>
References: <48BA9D65.2020001@ifi.uio.no>
<48BB4A2D.9080404@postnewspapers.com.au>
<a1ec7d000809010108m58d12c36v7086376caf3cf195@mail.gmail.com>
Scott Carey wrote:
> Are you even getting COPY to work with JDBC? As far as I am aware, COPY
> doesn't work with JDBC at the moment:
I used a patched jdbc driver, provided by someone on the list, dont have
the reference at hand. It works perfectly and its about 5 times faster,
for my job, than insert.
> As suggested, you should determine if you are disk bound or CPU bound.
> My experience with COPY is that it is suprisingly easy to make it CPU
> bound, but the conditions for that can vary quire a bit from schema to
> schema and hardware to hardware.
COPY is not the problem, as far as I see. The problem is the update
speed of the index. I tested the same procedure on a table with and
without an index. Having an index makes it 200-250% slower, than without.
But as you state I should check whether the problem is cpu or disk
bound. In addition, as someone else suggested, I might need to move the
indexes to a different disk, which is not a bad idea considering the
index becomes quite large with up 125K rows a second.
But I haver another consern, which is the db server configuration. I am
not entirely convinced the db is configured prperly. I had one problem
where the disk started thrashing after the table had reached a certainb
size, so when I configured shmmax, and the corresponding in pg, properly
I got rid of the trashing. I will have to read through the documentation
properly.
regards
thomas
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: tfinneid@student.matnat.uio.no
Subject: Re: slow update of index during insert/copy
In-Reply-To: <48BBCF07.8020709@ifi.uio.no>
* 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