Skip site navigation (1) Skip section navigation (2)

Re: Performance considerations for very heavy INSERT traffic

From: Brandon Black <blblack(at)gmail(dot)com>
To: PFC <lists(at)boutiquenumerique(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance considerations for very heavy INSERT traffic
Date: 2005-09-12 22:02:30
Message-ID: 84621a60509121502603bd56c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 9/12/05, PFC <lists(at)boutiquenumerique(dot)com> wrote:
> 
> 
> 
> > I know I haven't provided a whole lot of application-level detail here,
> 
> You did !
> 
> What about :
> 
> - using COPY instead of INSERT ?
> (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.

- using Bizgres ?
> (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.

- 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.

- 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

In response to

Responses

pgsql-performance by date

Next:From: Qingqing ZhouDate: 2005-09-13 00:37:19
Subject: Re: Performance considerations for very heavy INSERT traffic
Previous:From: Alex TurnerDate: 2005-09-12 21:45:21
Subject: Re: Performance considerations for very heavy INSERT traffic

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group