Forum OpenACS Q&A: Response to Any way to speed up inserts of multiple rows?

Collapse
Posted by Don Baccus on
I don't think it's the AOLserver driver, as it is very lightweight.

I get faster inserts in Oracle vs. PG when doing such tests as migrating ACS 3 forum contents into OpenACS 4.  Much faster (in Oracle I can migrate the openacs.org forum contents about 3x faster than in PG) so I think this exonorates the db_dml Tcl code to some extent, at least.

So that leaves us wondering about PG's speed, right?  Something you might do is to check to see if the machine's CPU or I/O bound.  If I/O bound try things like moving the write-ahead logs to a different disk, index files to a different disk, etc.  You might try turning off fsync for testing purposes as this gets rid of a lot of file I/O.  This would give you baseline data on insert speed (without as much safety, but it would help to pinpoint where your problems lie).

Look into the COPY command, since you can bunch of the data.  COPY is known to be *much* faster than INSERT.

Oh, yes, before doing that even wrap the bunched-up INSERTs in BEGIN/END to make them all happen in a single transaction.  This won't speed things up as much as it did in <PG 7.1 but should still speed things up some.

But the fastest thing to do is COPY.

These are just some simple, top-of-the-head ideas.