X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id BBEDC9DC982 for ; Wed, 4 Jan 2006 10:08:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23905-02 for ; Wed, 4 Jan 2006 10:08:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mtiwmhc11.worldnet.att.net (mtiwmhc11.worldnet.att.net [204.127.131.115]) by postgresql.org (Postfix) with ESMTP id 6A3849DC83F for ; Wed, 4 Jan 2006 10:08:36 -0400 (AST) Received: from [10.10.10.3] (166.denver-06rh16rt-07rh15rt.co.dial-access.att.net[12.73.182.166]) by worldnet.att.net (mtiwmhc11) with ESMTP id <2006010414083811100n9bi6e>; Wed, 4 Jan 2006 14:08:39 +0000 Message-ID: <43BBD6E2.6080300@computer.org> Date: Wed, 04 Jan 2006 07:08:34 -0700 From: Steve Eckmann User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Steinar H. Gunderson" CC: pgsql-performance@postgresql.org Subject: Re: improving write performance for logging application References: <43BB0C5C.8020207@computer.org> <20060104000603.GA29469@uio.no> In-Reply-To: <20060104000603.GA29469@uio.no> Content-Type: multipart/alternative; boundary="------------070306010906040306040009" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.656 required=5 tests=[AWL=0.176, DNS_FROM_RFC_ABUSE=0.479, HTML_MESSAGE=0.001] X-Spam-Score: 0.656 X-Spam-Level: X-Archive-Number: 200601/10 X-Sequence-Number: 16488 This is a multi-part message in MIME format. --------------070306010906040306040009 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------070306010906040306040009 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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 --------------070306010906040306040009--