Re: improving write performance for logging application

[Steve Eckmann <eckmann@xxxxxxxxxxxx>]

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