From: Steve Eckmann <eckmann@computer.org>
To: dlang <dlang@invendra.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: improving write performance for logging application
Date: Wed, 04 Jan 2006 07:13:13 -0700
Message-ID: <43BBD7F9.2060406@computer.org> (raw)
In-Reply-To: <Pine.LNX.4.44.0601031916340.29612-100000@web.lang.hm>
References: <Pine.LNX.4.44.0601031916340.29612-100000@web.lang.hm>
dlang wrote:
>On Tue, 3 Jan 2006, Tom Lane wrote:
>
>
>
>>Steve Eckmann <eckmann@computer.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: eckmann@computer.org, dlang@invendra.net
Subject: Re: improving write performance for logging application
In-Reply-To: <43BBD7F9.2060406@computer.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox