DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=qrIMWRRyTBuD8tiTMXokI66WgxAvTr+sQwa+3aZjTQ3dGFw2SflcKUBK7x9VdLWlWOupbZVXICj2ezgOgm8qy6cBL5AR1hTgav8sBgf2cLdWfGngCFxkmjHF5JVAXBUqbI0yYcNrsjD5QgPVFHHXTOceuPmcQrsDW3RVLD97Nnw=
Message-ID: <59d991c40509122039773cac17@mail.gmail.com>
Date: Mon, 12 Sep 2005 23:39:14 -0400
From: Christopher Petrilli <petrilli@gmail.com>
Reply-To: petrilli@gmail.com
To: blblack@gmail.com
Subject: Re: Performance considerations for very heavy INSERT traffic
Cc: pgsql-performance@postgresql.org
In-Reply-To: <84621a6050912140464c380c3@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <84621a6050912140464c380c3@mail.gmail.com>

On 9/12/05, Brandon Black <blblack@gmail.com> wrote:
>=20
>  I'm in the process of developing an application which uses PostgreSQL fo=
r
> 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=3Dwriteback 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=20

--=20
| Christopher Petrilli
| petrilli@gmail.com