Re: A Question About Insertions -- Performance

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> My best guess is that the dropoffs occur because of background checkpoint
TL> operations, but there's not enough info here to prove it.  Four inserts
TL> per second seems horrendously slow in any case.

I'll concur with this diagnosis.  I've been doing a bunch of
performance testing with various parameter settings, and the
checkpoint frequency is a big influence.  For me, by making the
checkpoints occur as far apart as possible, the overall speed
improvement was incredible.  Try bumping the number of
checkpoint_segments in your postgresql.conf file.  For my tests I
compared the default 3 with 50 segments.

Check your logs to see if you are checkpointing too frequently.

Another thing that *realy* picks up speed is to batch your inserts in
transactions.  I just altered an application yesterday that had a loop
like this:

foreach row fetched from table c:
  update table a where id=row.id
  update table b where id2=row.id2
  send notice to id
end

there were several such loops going on for distinct sets of rows in
the same tables.

changing it so that it was inside a transaction, and every 100 times
thru the loop to do a commit pretty much made the time it took to run
on a large loop from 2.5 hours down to 1 hour, and another that took 2
hours down to 40 minutes.

I had to put in a bunch of additional error checking and rollback
logic, but in the last two years none of those error conditions have
ever triggered so I think I'm pretty safe even with having to redo up
to 100 records on a transaction error (ie, it is unlikely to happen).


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/