On Tue, 3 Jan 2006, Tom Lane wrote:
Steve Eckmann <eckmann@xxxxxxxxxxxx> writes:
We also found that we could improve MySQL performance significantly
using MySQL's "INSERT" command extension allowing multiple value-list
tuples in a single command; the rate for MyISAM tables improved to
about 2600 objects/second. PostgreSQL doesn't support that language
extension. Using the COPY command instead of INSERT might help, but
since rows are being generated on the fly, I don't see how to use COPY
without running a separate process that reads rows from the
application and uses COPY to write to the database.
Can you conveniently alter your application to batch INSERT commands
into transactions? Ie
BEGIN;
INSERT ...;
... maybe 100 or so inserts ...
COMMIT;
BEGIN;
... lather, rinse, repeat ...
This cuts down the transactional overhead quite a bit. A downside is
that you lose multiple rows if any INSERT fails, but then the same would
be true of multiple VALUES lists per INSERT.
Steve, you mentioned that you data collector buffers the data before
sending it to the database, modify it so that each time it goes to send
things to the database you send all the data that's in the buffer as a
single transaction.
I am working on useing postgres to deal with log data and wrote a simple
perl script that read in the log files a line at a time, and then wrote
them 1000 at a time to the database. On a dual Opteron 240 box with 2G of
ram 1x 15krpm SCSI drive (and a untuned postgress install with the compile
time defaults) I was getting 5000-8000 lines/sec (I think this was with
fsync disabled, but I don't remember for sure). and postgres was
complaining that it was overrunning it's log sizes (which limits the speed
as it then has to pause to flush the logs)
the key thing is to send multiple lines with one transaction as tom shows
above.
David Lang
Thanks, David. I will look more carefully at how to batch multiple rows
per PQexec() call. Regards, Steve.