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