From: Steve Eckmann <eckmann@computer.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: improving write performance for logging application
Date: Wed, 04 Jan 2006 17:16:45 -0700
Message-ID: <43BC656D.2090103@computer.org> (raw)
In-Reply-To: <2898.1136389165@sss.pgh.pa.us>
References: <43BB0C5C.8020207@computer.org>
<27720.1136332812@sss.pgh.pa.us>
<43BBD4EC.5020207@computer.org>
<2898.1136389165@sss.pgh.pa.us>
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
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, tgl@sss.pgh.pa.us
Subject: Re: improving write performance for logging application
In-Reply-To: <43BC656D.2090103@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