X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D007B9DC835 for ; Wed, 4 Jan 2006 10:13:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23861-04 for ; Wed, 4 Jan 2006 10:13:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mtiwmhc12.worldnet.att.net (mtiwmhc12.worldnet.att.net [204.127.131.116]) by postgresql.org (Postfix) with ESMTP id 9919A9DC84E for ; Wed, 4 Jan 2006 10:13:18 -0400 (AST) Received: from [10.10.10.3] (166.denver-06rh16rt-07rh15rt.co.dial-access.att.net[12.73.182.166]) by worldnet.att.net (mtiwmhc12) with ESMTP id <20060104141316112002870be>; Wed, 4 Jan 2006 14:13:21 +0000 Message-ID: <43BBD7F9.2060406@computer.org> Date: Wed, 04 Jan 2006 07:13:13 -0700 From: Steve Eckmann User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: dlang CC: pgsql-performance@postgresql.org Subject: Re: improving write performance for logging application References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------050703080005020101070803" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.597 required=5 tests=[AWL=0.117, DNS_FROM_RFC_ABUSE=0.479, HTML_MESSAGE=0.001] X-Spam-Score: 0.597 X-Spam-Level: X-Archive-Number: 200601/11 X-Sequence-Number: 16489 This is a multi-part message in MIME format. --------------050703080005020101070803 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit dlang wrote: >On Tue, 3 Jan 2006, Tom Lane wrote: > > > >>Steve Eckmann 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. --------------050703080005020101070803 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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.
--------------050703080005020101070803--