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=q2Ey2siL1V8GOLhFbg1BCPXEr7q/aHbEcICp04AcHljiDDlal3mqqL4gZy2GWCDqOv1Yj6YI8A4E37BKuz/3UkID0M2djHuelCbN6bb3x1BaOVqJglqZ8hR+uJ81s9Kg0XERJqMLZkoh0wj2SBQssYEtFCQcZv6mOJvWcv2cLys=
Message-ID: <758d5e7f0507191319636afaa3@mail.gmail.com>
Date: Tue, 19 Jul 2005 22:19:03 +0200
From: Dawid Kuroczko <qnex42@gmail.com>
Reply-To: Dawid Kuroczko <qnex42@gmail.com>
To: Oliver Crosby <ryusei@gmail.com>
Subject: Re: Looking for tips
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	jd@commandprompt.com, pgsql-performance@postgresql.org
In-Reply-To: <1efd553a0507191308219a098b@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <s2dd0700.023@gwmta.wicourts.gov>
	<1efd553a0507191308219a098b@mail.gmail.com>

On 7/19/05, Oliver Crosby <ryusei@gmail.com> wrote:
> > We had low resource utilization and poor throughput on inserts of
> > thousands of rows within a single database transaction.  There were a
> > lot of configuration parameters we changed, but the one which helped th=
e
> > most was wal_buffers -- we wound up setting it to 1000.  This may be
> > higher than it needs to be, but when we got to something which ran well=
,
> > we stopped tinkering.  The default value clearly caused a bottleneck.
>=20
> I just tried wal_buffers =3D 1000, sort_mem at 10% and
> effective_cache_size at 75%.
> The performance refuses to budge.. I guess that's as good as it'll go?

If it is possible try:
1) wrapping many inserts into one transaction
(BEGIN;INSERT;INSERT;...INSERT;COMMIT;).  As PostgreSQL will need to
handle less transactions per second (each your insert is a transaction), it
may work faster.

2) If you can do 1, you could go further and use a COPY command which is
the fastest way to bulk-load a database.

Sometimes I insert data info temporary table, and then do:
INSERT INTO sometable SELECT * FROM tmp_table;
(but I do it when I want to do some select, updates, etc on
the data before "commiting" them to main table; dropping
temporary table is much cheaper than vacuuming many-a-row
table).

  Regards,
     Dawid

PS: Where can I find benchmarks comparing PHP vs Perl vs Python in
terms of speed of executing prepared statements?