batch inserts are "slow"

От: Tim Terlegård
Тема: batch inserts are "slow"
Дата: ,
Msg-id: Pine.LNX.4.44.0505021526450.7150-100000@naskur.se.linux.org
(см: обсуждение, исходный текст)
Ответы: Re: batch inserts are "slow"  (Christopher Kings-Lynne)
Re: batch inserts are "slow"  (Christopher Petrilli)
Re: batch inserts are "slow"  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

batch inserts are "slow"  (Tim Terlegård, )
 Re: batch inserts are "slow"  (Christopher Kings-Lynne, )
 Re: batch inserts are "slow"  (Christopher Petrilli, )
  Re: batch inserts are "slow"  (Tim Terlegård, )
   Re: batch inserts are "slow"  (Steve Wampler, )
   Re: batch inserts are "slow"  (Christopher Petrilli, )
 Re: batch inserts are "slow"  (Tim Terlegård, )
  Re: batch inserts are "slow"  (Christopher Petrilli, )
 Re: batch inserts are "slow"  (Tom Lane, )
 Re: batch inserts are "slow"  ("David Parker", )
  Re: batch inserts are "slow"  (Markus Schaber, )
   Re: batch inserts are "slow"  (Josh Berkus, )
    Re: batch inserts are "slow"  (Kris Jurka, )
     Re: batch inserts are "slow"  (Dave Cramer, )
   Testing list access  (Jona, )
 Re: batch inserts are "slow"  (Chris Browne, )

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



В списке pgsql-performance по дате сообщения:

От: Chris Browne
Дата:
Сообщение: Re: batch inserts are "slow"
От: Markus Schaber
Дата:
Сообщение: Re: [HACKERS] Bad n_distinct estimation; hacks suggested?