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 3D3BE9DC817
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  4 Jan 2006 20:16:52 -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 31921-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  4 Jan 2006 20:16:54 -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 286389DC802
	for <pgsql-performance@postgresql.org>;
	Wed,  4 Jan 2006 20:16:48 -0400 (AST)
Received: from [10.10.10.3]
	(98.denver-06rh15rt.co.dial-access.att.net[12.73.181.98])
	by worldnet.att.net (mtiwmhc12) with ESMTP
	id <200601050016491120027ar6e>; Thu, 5 Jan 2006 00:16:50 +0000
Message-ID: <43BC656D.2090103@computer.org>
Date: Wed, 04 Jan 2006 17:16:45 -0700
From: Steve Eckmann <eckmann@computer.org>
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 <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: improving write performance for logging application
References: <43BB0C5C.8020207@computer.org> <27720.1136332812@sss.pgh.pa.us>
	<43BBD4EC.5020207@computer.org> <2898.1136389165@sss.pgh.pa.us>
In-Reply-To: <2898.1136389165@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------060807030805020204060006"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.502 required=5 tests=[AWL=0.022,
	DNS_FROM_RFC_ABUSE=0.479, HTML_MESSAGE=0.001]
X-Spam-Score: 0.502
X-Spam-Level: 
X-Archive-Number: 200601/18
X-Sequence-Number: 16496

This is a multi-part message in MIME format.
--------------060807030805020204060006
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

>Steve Eckmann <eckmann@computer.org> writes:
>  
>
>> <>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 (...); ...");
>>    
>>
>
>Hmm.  I'm not sure if that's a good idea or not.  You're causing the
>server to take 1000 times the normal amount of memory to hold the
>command parsetrees, and if there are any O(N^2) behaviors in parsing
>you could be getting hurt badly by that.  (I'd like to think there are
>not, but would definitely not swear to it.)  OTOH you're reducing the
>number of network round trips which is a good thing.  Have you actually
>measured to see what effect this approach has?  It might be worth
>building a test server with profiling enabled to see if the use of such
>long command strings creates any hot spots in the profile.
>
>			regards, tom lane
>  
>
No, I haven't measured it. I will compare this approach with others that 
have been suggested. Thanks.  -steve

--------------060807030805020204060006
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:
<blockquote cite="mid2898.1136389165@sss.pgh.pa.us" type="cite">
  <pre wrap="">Steve Eckmann <a class="moz-txt-link-rfc2396E" href="mailto:eckmann@computer.org">&lt;eckmann@computer.org&gt;</a> writes:
  </pre>
  <blockquote type="cite"><>Thanks for the suggestion, Tom. Yes, I
think I could do that. But I <br>
thought what I was doing now was effectively the same, because the <br>
PostgreSQL 8.0.0 Documentation says (section 27.3.1): "It is allowed to
    <br>
include multiple SQL commands (separated by semicolons) in the command <br>
string. Multiple queries sent in a single PQexec call are processed in
a <br>
single transaction...." Our simulation application has nearly 400 event
    <br>
types, each of which is a C++ class for which we have a corresponding <br>
database table. So every thousand events or so I issue one PQexec()
call <br>
for each event type that has unlogged instances, sending INSERT
commands <br>
for all instances. For example,<!----><br>
    </></blockquote>
  <blockquote type="cite">
    <pre wrap="">    PQexec(dbConn, "INSERT INTO FlyingObjectState VALUES (...); INSERT 
INTO FlyingObjectState VALUES (...); ...");
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Hmm.  I'm not sure if that's a good idea or not.  You're causing the
server to take 1000 times the normal amount of memory to hold the
command parsetrees, and if there are any O(N^2) behaviors in parsing
you could be getting hurt badly by that.  (I'd like to think there are
not, but would definitely not swear to it.)  OTOH you're reducing the
number of network round trips which is a good thing.  Have you actually
measured to see what effect this approach has?  It might be worth
building a test server with profiling enabled to see if the use of such
long command strings creates any hot spots in the profile.

			regards, tom lane
  </pre>
</blockquote>
No, I haven't measured it. I will compare this approach with others
that have been suggested. Thanks.&nbsp; -steve<br>
</body>
</html>

--------------060807030805020204060006--