Re: improving write performance for logging application

From: Steinar H. Gunderson
Subject: Re: improving write performance for logging application
Date: ,
Msg-id: 20060104000603.GA29469@uio.no
(view: Whole thread, Raw)
In response to: improving write performance for logging application  (Steve Eckmann)
Responses: Re: improving write performance for logging application  (Steve Eckmann)
List: pgsql-performance

Tree view

improving write performance for logging application  (Steve Eckmann, )
 Re: improving write performance for logging application  (Tom Lane, )
  Re: improving write performance for logging application  (dlang, )
   Re: improving write performance for logging application  (Steve Eckmann, )
  Re: improving write performance for logging application  (Steve Eckmann, )
   Re: improving write performance for logging application  (Tom Lane, )
    Re: improving write performance for logging application  (Steve Eckmann, )
 Re: improving write performance for logging application  ("Steinar H. Gunderson", )
  Re: improving write performance for logging application  (Steve Eckmann, )
   Re: improving write performance for logging application  (Kelly Burkhart, )
    Re: improving write performance for logging application  (Steve Eckmann, )
 Re: improving write performance for logging application  (Ian Westmacott, )
  Re: improving write performance for logging application  (Steve Eckmann, )
  Re: improving write performance for logging  (Ron, )
   Re: improving write performance for logging  (Ian Westmacott, )
    Re: improving write performance for logging  ("Jim C. Nasby", )
     Re: improving write performance for logging  (Ian Westmacott, )
      Re: improving write performance for logging  ("Jim C. Nasby", )
       Re: improving write performance for logging  (Ian Westmacott, )
        Re: improving write performance for logging  (Tom Lane, )
      Re: improving write performance for logging  (Michael Stone, )

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/


pgsql-performance by date:

From: "Virag Saksena"
Date:
Subject: Avoiding cartesian product
From: Ian Westmacott
Date:
Subject: Re: improving write performance for logging application