From: Thomas Finneid <tfinneid@student.matnat.uio.no>
To: Kalle Hallivuori <kato@iki.fi>
Cc: pgsql-performance@postgresql.org
Subject: Re: improvement suggestions for performance design
Date: Wed, 18 Jul 2007 21:24:18 +0200
Message-ID: <469E68E2.9080206@ifi.uio.no> (raw)
In-Reply-To: <c637d8bb0707080925u4cd0871dmced4458d19b10c1@mail.gmail.com>
References: <40362.134.32.140.234.1183637748.squirrel@webmail.uio.no>
<468CEB32.30109@enterprisedb.com>
<40891.134.32.140.234.1183642557.squirrel@webmail.uio.no>
<468D3AF7.4070505@enterprisedb.com>
<468D6F68.7090903@ifi.uio.no>
<c637d8bb0707060430x1656a796y728c88e9e2103ea5@mail.gmail.com>
<4690B725.5070303@ifi.uio.no>
<c637d8bb0707080925u4cd0871dmced4458d19b10c1@mail.gmail.com>
Kalle Hallivuori wrote:
> Hi.
>
> 2007/7/8, Thomas Finneid <tfinneid@student.matnat.uio.no>:
>>
>> Kalle Hallivuori wrote:
>>
>> > COPY is plentitudes faster than INSERT:
>> > http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
>> >
>> > If you can't just push the data straight into the final table with
>> > COPY, push it into a temporary table that you go through with the
>> > database procedure.
>> >
>> > Shameless plug: If you use Java and miss COPY functionality in the
>> > driver, it's available at
>> >
>> > http://kato.iki.fi/sw/db/postgresql/jdbc/copy/
>> >
>> > I was able to practically nullify time spent inserting with that.
>>
>> Interresting, I will definately have a look at it.
>> What is the maturity level of the code at this point? and what is
>> potentially missing to bring it up to production quality? (stability is
>> of the utmost importance in my usage scenario.)
>
> It's my third implementation, based on earlier work by Kris Jurka, a
> maintainer of the JDBC driver. (It is really quite short so it's easy
> to keep it clear.) I consider it mature enough to have accommodated it
> as part of an upcoming large application, but I'd really like to hear
> others' opinions. Documentation I should add one of these days, maybe
> even rewrite the javadoc.
Hi I have tested your COPY patch (actually I tested
postgresql-jdbc-8.2-505-copy-20070716.jdbc3.jar) and it is really fast,
actually just as fast as serverside COPY (boths tests was performed on
local machine).
This means I am interrested in using it in my project, but I have some
concerns that needs to be adressed, (and I am prepared to help in any
way I can). The following are the concerns I have
- While testing I got some errors, which needs to be fixed (detailed below)
- The patch must be of production grade quality
- I would like the patch to be part of the official pg JDBC driver.
The error I got the most is :
This command runs a single run, single thread and generates 10000 rows
of data
tofi@duplo:~/svn/pores$ java -server -Xms20m -Xmx256m -cp
/usr/java/jdk1.5.0_06/jre/lib/rt.jar:.:src/:test/:conf/:lib/postgresql-jdbc-8.2-505-copy-20070716.jdbc3.jar
wg.daemon.Main -m SINGLE_WRITE -t 1 -r 1 -c 10000 -p CPBulk
Initialising connection...
Performing insert...
Build bulk data time: 0s 211ms
toString() bulk data time: 0s 4ms
time: 0s 205ms
org.postgresql.util.PSQLException: Unexpected command completion while
copying: COPY
at
org.postgresql.core.v3.QueryExecutorImpl.executeCopy(QueryExecutorImpl.java:706)
at org.postgresql.copy.CopyManager.copyIntoDB(CopyManager.java:50)
at org.postgresql.copy.CopyManager.copyIntoDB(CopyManager.java:37)
at wg.storage.pores1.CPBulk.addAttributes(CPBulk.java:72)
at wg.daemon.Daemon.run(Daemon.java:57)
tofi@duplo:~/svn/pores$ ls -al lib/
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, kato@iki.fi
Subject: Re: improvement suggestions for performance design
In-Reply-To: <469E68E2.9080206@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