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
