Howdy!

I'm converting an application to be using postgresql instead of oracle.
There seems to be only one issue left, batch inserts in postgresql seem
significant slower than in oracle. I have about 200 batch jobs, each
consisting of about 14 000 inserts. Each job takes 1.3 seconds in
postgresql and 0.25 seconds in oracle. With 200 jobs this means several
more minutes to complete the task. By fixing this I think the
application using postgresql over all would be faster than when using
oracle.

I'd like some advice of what could enhance the performance. I use
PostgreSQL 8. The table that is loaded with a bunch of data has no
indexes. I use Gentoo Linux on a P4 3GHz with 1GB RAM. I use JDBC from
jdbc.postgresql.org, postgresql-8.0-311.jdbc3.jar.

I've changed a few parameters as I hoped that would help me:
wal_buffers = 64
checkpoint_segments = 10
shared_buffers = 15000
work_mem = 4096
maintenance_work_mem = 70000
effective_cache_size = 30000
shmmax is 150000000

These settings made creating index faster for instance. Don't know if
they can be tweaked further so these batch jobs are executed faster?
Some setting I forgot to tweak? I tried setting fsync to false, but that
didnt change anything.

Something like this is what runs and takes a bit too long imho:

conn.setAutoCommit(false);
pst = conn.prepareStatement("INSERT INTO tmp (...) VALUES (?,?)");
for (int i = 0; i < len; i++) {
pst.setInt(0, 2);
pst.setString(1, "xxx");
pst.addBatch();
}
pst.executeBatch();
conn.commit();

This snip takes 1.3 secs in postgresql. How can I lower that?

Thanks, Tim


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

> conn.setAutoCommit(false);
> pst = conn.prepareStatement("INSERT INTO tmp (...) VALUES (?,?)");
> for (int i = 0; i < len; i++) {
> pst.setInt(0, 2);
> pst.setString(1, "xxx");
> pst.addBatch();
> }
> pst.executeBatch();
> conn.commit();
>
> This snip takes 1.3 secs in postgresql. How can I lower that?

You're batching them as one transaction, and using a prepared query both
of which are good. I guess the next step for a great performance
improvement is to use the COPY command. However, you'd have to find out
how to access that via Java.

I have a nasty suspicion that the release JDBC driver doesn't support it
and you may have to apply a patch.

Ask on pgsql-jdbc@postgresql.org perhaps.

Chris

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On 5/2/05, Tim Terlegård <tim@se.linux.org> wrote:
> Howdy!
>
> I'm converting an application to be using postgresql instead of oracle.
> There seems to be only one issue left, batch inserts in postgresql seem
> significant slower than in oracle. I have about 200 batch jobs, each
> consisting of about 14 000 inserts. Each job takes 1.3 seconds in
> postgresql and 0.25 seconds in oracle. With 200 jobs this means several
> more minutes to complete the task. By fixing this I think the
> application using postgresql over all would be faster than when using
> oracle.

Just as on Oracle you would use SQL*Loader for this application, you
should use the COPY syntax for PostgreSQL. You will find it a lot
faster. I have used it by building the input files and executing
'psql' with a COPY command, and also by using it with a subprocess,
both are quite effective.

Chris
--
| Christopher Petrilli
| petrilli@gmail.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

=?iso-8859-1?Q?Tim_Terleg=E5rd?= <tim@se.linux.org> writes:
> There seems to be only one issue left, batch inserts in postgresql seem
> significant slower than in oracle. I have about 200 batch jobs, each
> consisting of about 14 000 inserts.

> conn.setAutoCommit(false);
> pst = conn.prepareStatement("INSERT INTO tmp (...) VALUES (?,?)");
> for (int i = 0; i < len; i++) {
> pst.setInt(0, 2);
> pst.setString(1, "xxx");
> pst.addBatch();
> }
> pst.executeBatch();
> conn.commit();

Hmm. It's good that you are wrapping this in a transaction, but I
wonder about doing it as a single "batch". I have no idea what the
internal implementation of batches in JDBC is like, but it seems
possible that it would have some performance issues with 14000
statements in a batch.

Have you checked whether the bulk of the runtime is being consumed
on the server or client side?

Also, make sure that the JDBC driver is using "real" prepared statements
--- until pretty recently, it faked them. I think build 311 is new
enough, but it would be good to check in the docs or by asking on pgsql-jdbc.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

petrilli@gmail.com (Christopher Petrilli) writes:
> On 5/2/05, Tim Terlegård <tim@se.linux.org> wrote:
>> Howdy!
>>
>> I'm converting an application to be using postgresql instead of
>> oracle. There seems to be only one issue left, batch inserts in
>> postgresql seem significant slower than in oracle. I have about 200
>> batch jobs, each consisting of about 14 000 inserts. Each job takes
>> 1.3 seconds in postgresql and 0.25 seconds in oracle. With 200 jobs
>> this means several more minutes to complete the task. By fixing
>> this I think the application using postgresql over all would be
>> faster than when using oracle.
>
> Just as on Oracle you would use SQL*Loader for this application, you
> should use the COPY syntax for PostgreSQL. You will find it a lot
> faster. I have used it by building the input files and executing
> 'psql' with a COPY command, and also by using it with a subprocess,
> both are quite effective.

I'd suggest taking a peek at the PGForge project, pgloader
<http://pgfoundry.org/projects/pgloader/>.

This is intended to provide somewhat analagous functionality to
SQL*Loader; a particularly useful thing about it is that it will load
those records that it can, and generate a file consisting of just the
failures.

It uses COPY, internally, so it does run reasonably fast.

To the extent to which it is inadequate, it would be neat to see some
enhancements...
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>

> > I'm converting an application to be using postgresql instead of oracle.
> > There seems to be only one issue left, batch inserts in postgresql seem
> > significant slower than in oracle. I have about 200 batch jobs, each
> > consisting of about 14 000 inserts. Each job takes 1.3 seconds in
> > postgresql and 0.25 seconds in oracle. With 200 jobs this means several
> > more minutes to complete the task. By fixing this I think the
> > application using postgresql over all would be faster than when using
> > oracle.
>
> Just as on Oracle you would use SQL*Loader for this application, you
> should use the COPY syntax for PostgreSQL. You will find it a lot
> faster. I have used it by building the input files and executing
> 'psql' with a COPY command, and also by using it with a subprocess,
> both are quite effective.

I tried this now. Now it's down to 0.45 seconds. It feels a bit hacky to
run /usr/bin/psql from java, but it sure works. Thanks for the hint!

Tim


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tim Terlegård wrote:
>>
>>Just as on Oracle you would use SQL*Loader for this application, you
>>should use the COPY syntax for PostgreSQL. You will find it a lot
>>faster. I have used it by building the input files and executing
>>'psql' with a COPY command, and also by using it with a subprocess,
>>both are quite effective.
>
>
> I tried this now. Now it's down to 0.45 seconds. It feels a bit hacky to
> run /usr/bin/psql from java, but it sure works. Thanks for the hint!

There was a patch against 7.4 that provided direct JDBC access to
PostgreSQL's COPY. (I have it installed here and *love* it - it
gives outstanding performance.) However, it hasn't made into an
official release yet. I don't know why, perhaps there's
a problem yet to be solved with it ('works for me', though)?

Is this still on the board? I won't upgrade past 7.4 without it.

--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On 5/3/05, Tim Terlegård <tim@se.linux.org> wrote:
> > Just as on Oracle you would use SQL*Loader for this application, you
> > should use the COPY syntax for PostgreSQL. You will find it a lot
> > faster. I have used it by building the input files and executing
> > 'psql' with a COPY command, and also by using it with a subprocess,
> > both are quite effective.
>
> I tried this now. Now it's down to 0.45 seconds. It feels a bit hacky to
> run /usr/bin/psql from java, but it sure works. Thanks for the hint!

It may feel hacky, but I think if you want to use SQL*Loader on
Oracle, you have to do the same thing. I know a C++ app that I use
that runs SQL*Loader about once per second to deal with a HUGE volume
(10K/sec). In fact, moving the load files onto ramdisk has helped a
lot.

Chris
--
| Christopher Petrilli
| petrilli@gmail.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly