From: Christopher Petrilli <petrilli@gmail.com>
To: blblack@gmail.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance considerations for very heavy INSERT traffic
Date: Mon, 12 Sep 2005 23:39:14 -0400
Message-ID: <59d991c40509122039773cac17@mail.gmail.com> (raw)
In-Reply-To: <84621a6050912140464c380c3@mail.gmail.com>
References: <84621a6050912140464c380c3@mail.gmail.com>
On 9/12/05, Brandon Black <blblack@gmail.com> 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
| petrilli@gmail.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: petrilli@gmail.com, blblack@gmail.com
Subject: Re: Performance considerations for very heavy INSERT traffic
In-Reply-To: <59d991c40509122039773cac17@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox