On 9/12/05, PFC <lists@boutiquenumerique.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