DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=tgUC+f/aA4TSK54n3cm13WMgMmVTL0xVliRwQBJiaxvNiuplY7D+RpoD3Pa7TMXSra3PK829hxhdeI1UyvVwSPcsbBMBfaLKWIhAfar0o1mHrmDhQxucq9HDPNNfWg/6IcXS5CS1TeUps4BdbdaCpu8PRxG7xL2P6DZ5rQ6esN0=
Message-ID: <b41c75520510311102t31a6eal@mail.gmail.com>
Date: Mon, 31 Oct 2005 20:02:26 +0100
From: Claus Guttesen <kometen@gmail.com>
To: Kelly Burkhart <kelly@tradebotsystems.com>
Subject: Re: 8.x index insert performance
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1130778725.7026.33.camel@krb06.tradebot.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <1130778725.7026.33.camel@krb06.tradebot.com>

> We are running some performance tests in which we are attempting to
> insert about 100,000,000 rows in a database at a sustained rate.  About
> 50M rows in, our performance drops dramatically.
>
> This test is with data that we believe to be close to what we will
> encounter in production.  However in tests with purely generated,
> sequential data, we did not notice this slowdown.  I'm trying to figure
> out what patterns in the "real" data may be causing us problems.
>
> I have log,data and indexes on separate LUNs on an EMC SAN.  Prior to
> slowdown, each partition is writing at a consistent rate.  Index
> partition is reading at a much lower rate.  At the time of slowdown,
> index partition read rate increases, all write rates decrease.  CPU
> utilization drops.

I'm doing some test-inserts (albeit with much fewer records) into
8.0.4 (on FreeBSD 6.0 RC1) and the import-time decreased three-fold
when I increased the below mentioned values:

shared_buffers =3D 8192
commit_delay =3D 100000
commit_siblings =3D 1000

When I increased shared_buffers the kernel needed minor tweaking.

regards
Claus