X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D2961D8C36 for ; Mon, 12 Sep 2005 19:02:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27278-03 for ; Mon, 12 Sep 2005 22:02:27 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.194]) by svr1.postgresql.org (Postfix) with ESMTP id 4356ED8C53 for ; Mon, 12 Sep 2005 19:02:27 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 36so2315677wra for ; Mon, 12 Sep 2005 15:02:30 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:references; b=nzU+k1g53iFyoKAPWWYfI7EoebevJ+94BroLiFw7HJjLJrqGfqQ0DcxUjWoQzTl29U6Boz5IO3pgeMkVMFE0J6WuAunGR7Z9otQnZYBMpmeeKXSir2NkXUgCrjTJYgCh6VE31cCVXoMZhIMQGhfSOmdEH2oPdQeJgCf/LM8UOR4= Received: by 10.54.57.23 with SMTP id f23mr288wra; Mon, 12 Sep 2005 15:02:30 -0700 (PDT) Received: by 10.54.158.11 with HTTP; Mon, 12 Sep 2005 15:02:30 -0700 (PDT) Message-ID: <84621a60509121502603bd56c@mail.gmail.com> Date: Mon, 12 Sep 2005 17:02:30 -0500 From: Brandon Black Reply-To: blblack@gmail.com To: PFC , pgsql-performance@postgresql.org Subject: Re: Performance considerations for very heavy INSERT traffic In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11494_29519225.1126562550353" References: <84621a6050912140464c380c3@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.137 required=5 tests=[AWL=0.026, HTML_40_50=0.086, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/150 X-Sequence-Number: 14458 ------=_Part_11494_29519225.1126562550353 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 9/12/05, PFC wrote: >=20 >=20 >=20 > > I know I haven't provided a whole lot of application-level detail here, >=20 > You did ! >=20 > What about : >=20 > - using COPY instead of INSERT ? > (should be easy to do from the aggregators) Possibly, although it would kill the current design of returning the=20 database transaction status for a single client packet back to the client o= n=20 transaction success/failure. The aggregator could put several clients' data= =20 into a series of delayed multi-row copy statements. - using Bizgres ? > (which was designed for your use case) I only briefly scanned their "About" page, but they didn't sound=20 particularly suited to my case at the time (it sounded kinds buzzwordy=20 actually, which I suppose is great for business apps people :) ). We're mor= e=20 of a sciency shop. I'll go look at the project in more detail tonight in=20 light of your recommendation. - splitting the xlog and the data on distinct physical drives or arrays That would almost definitely help, I haven't tried it yet. Speaking of the= =20 xlog, anyone know anything specific about the WAL tuning parameters for=20 heavy concurrent write traffic? What little I could dig up on WAL tuning wa= s=20 contradictory, and testing some random changes to the parameters hasn't bee= n=20 very conclusive yet. I would imagine the WAL buffers stuff could potentiall= y=20 have a large effect for us. - benchmarking something else than ext3 > (xfs ? reiser3 ?) >=20 We've had bad experiences under extreme and/or strange workloads with XFS= =20 here in general, although this is the first major postgresql project - the= =20 rest were with other applications writing to XFS. Bad experiences like XFS= =20 filesystems "detecting internal inconsistencies" at runtime and unmounting= =20 themselves from within the kernel module (much to the dismay of application= s=20 with open files on the filesystem), on machines with validated good=20 hardware. It has made me leary of using anything other than ext3 for fear o= f=20 stability problems. Reiser3 might be worth taking a look at though. Thanks for the ideas, -- Brandon ------=_Part_11494_29519225.1126562550353 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 9/12/05, PFC <lists@bo= utiquenumerique.com> wrote:


> I know I haven't provided a whole lot of application-level det= ail here,

        You did !<= br>
        What about :

=         - using COPY instead of INS= ERT ?
           =      (should be easy to do from the aggregators)

Possibly, although it would kill the current design of returning the database transaction status for a single client packet back to the client on transaction success/failure.   The aggregator could put several clients' data into a series of delayed multi-row copy statements.

 &= nbsp;      - using Bizgres ?
  &= nbsp;           &nbs= p; (which was designed for your use case)

I only briefly scanned their "About" page, but they didn't sound particularly suited to my case at the time (it sounded kinds buzzwordy actually, which I suppose is great for business apps people :) ).  We're more of a sciency shop.  I'll go look at the project in more detail tonight in light of your recommendation.

 &= nbsp;      - splitting the xlog and the data = on distinct physical drives or arrays

That would almost definitely help, I haven't tried it yet.  Speaking of the xlog, anyone know anything specific about the WAL tuning parameters for heavy concurrent write traffic?  What little I could dig up on WAL tuning was contradictory, and testing some random changes to the parameters hasn't been very conclusive yet.  I would imagine the WAL buffers stuff could potentially have a large effect for us.

 &= nbsp;      - benchmarking something else than= ext3
           =      (xfs ? reiser3 ?)

We've had bad experiences under extreme and/or strange workloads with XFS here in general, although this is the first major postgresql project - the rest were with other applications writing to XFS.  Bad experiences like XFS filesystems "detecting internal inconsistencies" at runtime and unmounting themselves from within the kernel module (much to the dismay of applications with open files on the filesystem), on machines with validated good hardware.  It has made me leary of using anything other than ext3 for fear of stability problems.  Reiser3 might be worth taking a look at though.

Thanks for the ideas,
-- Brandon
------=_Part_11494_29519225.1126562550353--