Re: improving write performance for logging application

dlang wrote:

>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
>
Thanks, David. I will look more carefully at how to batch multiple rows
per PQexec() call. Regards, Steve.