Date: Wed, 4 Jan 2006 01:06:03 +0100
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
To: Steve Eckmann <eckmann@computer.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: improving write performance for logging application
Message-ID: <20060104000603.GA29469@uio.no>
Mail-Followup-To: Steve Eckmann <eckmann@computer.org>,
	pgsql-performance@postgresql.org
References: <43BB0C5C.8020207@computer.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <43BB0C5C.8020207@computer.org>
User-Agent: Mutt/1.5.11

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 */
-- 
Homepage: http://www.sesse.net/