On 9/12/05, Brandon Black <[EMAIL PROTECTED]> wrote: > > I'm in the process of developing an application which uses PostgreSQL for > data storage. Our database traffic is very atypical, and as a result it has > been rather challenging to figure out how to best tune PostgreSQL on what > development hardware we have, as well as to figure out exactly what we > should be evaluating and eventually buying for production hardware.
A few suggestions... 1) Switch to COPY if you can, it's anywhere from 10-100x faster than INSERT, but it does not necessarily fit your idea of updating multiple tables. In that case, try and enlarge the transaction's scope and do multiple INSERTs in the same transaction. Perhaps batching once per second, or 5 seconds, and returning the aggregate result ot the clients. 2) Tune ext3. The default configuration wrecks high-write situations. Look into data=writeback for mounting, turning off atime (I hope you've done this already) updates, and also modifying the scheduler to the elevator model. This is poorly documented in Linux (like just about everything), but it's crtical. 3) Use 8.1 and strongly look at Bizgres. The data partitioning is critical. 4) Make sure you are not touching more data than you need, and don't have any extraneous indexes. Use the planner to make sure every index is used, as it substantially increases the write load. I've worked on a few similar applications, and this is a hard thing in any database, even Oracle. Chris -- | Christopher Petrilli | [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org