From: Steve Eckmann <eckmann@computer.org>
To: Steinar H. Gunderson <sgunderson@bigfoot.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: improving write performance for logging application
Date: Wed, 04 Jan 2006 07:08:34 -0700
Message-ID: <43BBD6E2.6080300@computer.org> (raw)
In-Reply-To: <20060104000603.GA29469@uio.no>
References: <43BB0C5C.8020207@computer.org>
<20060104000603.GA29469@uio.no>
Steinar H. Gunderson wrote:
>On Tue, Jan 03, 2006 at 04:44:28PM -0700, Steve Eckmann wrote:
>
>
>>Are there general guidelines for tuning the PostgreSQL server for this kind
>>of application? The suggestions I've found include disabling fsync (done),
>>
>>
>
>Are you sure you really want this? The results could be catastrophic in case
>of a crash.
>
>
>
>>On the client side I've found only two suggestions: disable autocommit and
>>use COPY instead of INSERT. I think I've effectively disabled autocommit by
>>batching up to several hundred INSERT commands in each PQexec() call, and
>>it isn’t clear that COPY is worth the effort in our application.
>>
>>
>
>I'm a bit confused here: How can you batch multiple INSERTs into large
>statements for MySQL, but not batch multiple INSERTs into COPY statements for
>PostgreSQL?
>
>Anyhow, putting it all inside one transaction (or a few) is likely to help
>quite a lot, but of course less when you have fsync=false. Bunding multiple
>statements in each PQexec() call won't really give you that; you'll have to
>tell the database so explicitly.
>
>/* Steinar */
>
>
Thanks, Steinar. I don't think we would really run with fsync off, but I
need to document the performance tradeoffs. You're right that my
explanation was confusing; probably because I'm confused about how to
use COPY! I could batch multiple INSERTS using COPY statements, I just
don't see how to do it without adding another process to read from
STDIN, since the application that is currently the database client is
constructing rows on the fly. I would need to get those rows into some
process's STDIN stream or into a server-side file before COPY could be
used, right?
You're comment about bundling multiple statements in each PQexec() call
seems to disagree with a statement in 27.3.1 that I interpret as saying
each PQexec() call corresponds to a single transaction. Are you sure my
interpretation is wrong?
Regards, 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, sgunderson@bigfoot.com
Subject: Re: improving write performance for logging application
In-Reply-To: <43BBD6E2.6080300@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