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 E32D39DC83F for ; Wed, 4 Jan 2006 10:00: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 17422-09 for ; Wed, 4 Jan 2006 10:00:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mtiwmhc13.worldnet.att.net (mtiwmhc13.worldnet.att.net [204.127.131.117]) by postgresql.org (Postfix) with ESMTP id 2CAFF9DC814 for ; Wed, 4 Jan 2006 10:00:16 -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 (mtiwmhc13) with ESMTP id <20060104140017113005cacee>; Wed, 4 Jan 2006 14:00:18 +0000 Message-ID: <43BBD4EC.5020207@computer.org> Date: Wed, 04 Jan 2006 07:00:12 -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: Tom Lane CC: pgsql-performance@postgresql.org Subject: Re: improving write performance for logging application References: <43BB0C5C.8020207@computer.org> <27720.1136332812@sss.pgh.pa.us> In-Reply-To: <27720.1136332812@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------060004050500060909060100" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.712 required=5 tests=[AWL=-0.233, HTML_10_20=0.945, HTML_MESSAGE=0.001] X-Spam-Score: 0.712 X-Spam-Level: X-Archive-Number: 200601/9 X-Sequence-Number: 16487 This is a multi-part message in MIME format. --------------060004050500060909060100 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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. > > regards, tom lane > > Thanks for the suggestion, Tom. Yes, I think I could do that. But I thought what I was doing now was effectively the same, because the PostgreSQL 8.0.0 Documentation says (section 27.3.1): "It is allowed to include multiple SQL commands (separated by semicolons) in the command string. Multiple queries sent in a single PQexec call are processed in a single transaction...." Our simulation application has nearly 400 event types, each of which is a C++ class for which we have a corresponding database table. So every thousand events or so I issue one PQexec() call for each event type that has unlogged instances, sending INSERT commands for all instances. For example, PQexec(dbConn, "INSERT INTO FlyingObjectState VALUES (...); INSERT INTO FlyingObjectState VALUES (...); ..."); My thought was that this would be a good compromise between minimizing transactions (one per event class per buffering interval instead of one per event) and minimizing disk seeking (since all queries in a single transaction insert rows into the same table). Am I overlooking something here? One thing I haven't tried is increasing the buffering interval from 1000 events to, say, 10,000. It turns out that 1000 is a good number for Versant, the object database system we're replacing, and for MySQL, so I assumed it would be a good number for PostgreSQL, too. Regards, Steve --------------060004050500060909060100 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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.

			regards, tom lane
Thanks for the suggestion, Tom. Yes, I think I could do that. But I thought what I was doing now was effectively the same, because the PostgreSQL 8.0.0 Documentation says (section 27.3.1): "It is allowed to include multiple SQL commands (separated by semicolons) in the command string. Multiple queries sent in a single PQexec call are processed in a single transaction...." Our simulation application has nearly 400 event types, each of which is a C++ class for which we have a corresponding database table. So every thousand events or so I issue one PQexec() call for each event type that has unlogged instances, sending INSERT commands for all instances. For example,

    PQexec(dbConn, "INSERT INTO FlyingObjectState VALUES (...); INSERT INTO FlyingObjectState VALUES (...); ...");

My thought was that this would be a good compromise between minimizing transactions (one per event class per buffering interval instead of one per event) and minimizing disk seeking (since all queries in a single transaction insert rows into the same table). Am I overlooking something here? One thing I haven't tried is increasing the buffering interval from 1000 events to, say, 10,000. It turns out that 1000 is a good number for Versant, the object database system we're replacing, and for MySQL, so I assumed it would be a good number for PostgreSQL, too.

Regards,  Steve

--------------060004050500060909060100--