Re: improving write performance for logging application

On Tue, 3 Jan 2006, Tom Lane wrote:

> Steve Eckmann <eckmann(at)computer(dot)org> 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