Re: Performance considerations for very heavy INSERT traffic

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