Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Wei Weng <wweng@kencast.com>
Subject: Re: concurrent connections is worse than serialization?
Date: Wed, 14 Aug 2002 18:22:14 +0100
User-Agent: KMail/1.4.1
Cc: pgsql-sql@postgresql.org
References: <1029271170.16828.7.camel@Monet>
	<200208141549.57646.dev@archonet.com>
	<1029338971.21470.9.camel@Monet>
In-Reply-To: <1029338971.21470.9.camel@Monet>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200208141822.14573.dev@archonet.com>

On Wednesday 14 Aug 2002 4:29 pm, Wei Weng wrote:
> > [30 connections is much slower than 1 connection 30 times]
>
> Yeah, but the problem is, say I have 20 users using select on the
> database at the same time, and each select takes 10 seconds to finish. I
> really can't queue them up (or the last user will reall have to wait for
> a long time), can I?

No, and you shouldn't have to - Postgresql can handle hundreds of concurren=
t=20
connections if needs be.

> > If it's not CPU, is the system going into swap or are you seeing a lot =
of
> > disk activity?
>
> I did hear a lot of disk noise when I ran the test. How do I tell if the
> "system is going into swap"?

You'd expect disk activity to a degree, it's a database after all. To see h=
ow=20
much and what is happening try one of : vmstat, iostat, top. You should hav=
e=20
at least one of these on whatever system you are using.

> Is there any system settings I can/should change to make this a little
> faster?

There are a number of settings in postgresql.conf - see the online docs (I=
=20
think there's a tuning document on techdocs.postgresql.org). Bear in mind=
=20
that the default settings for postgresql are very small, so you might want =
to=20
look at the sort memory/shared memory settings. Increase values a bit at a=
=20
time and see where you get a good balance. See the list archives for lots o=
f=20
discussion of this.

> |ID    VARCHAR(40) | --> primary key
> |Name  VARCHAR(100)|
> And I did an INSERT INTO foo ('some-unique-guid-here', 'Test Name');

Try using the script multicli_ins.sh below - it creates 10 perl clients eac=
h=20
of which inserts 100 rows in separate transactions. This is about as=20
inefficient as you can get.

Have a play with different numbers of connections and see if there's a=20
pattern.

=3D> select max(ts) - min(ts) from foo;
    ?column?
-----------------
 00:00:06.448863

So - I can insert 1000 using 10 clients in under 6.5 seconds. That's on an=
=20
untuned system on a development server (400MHz, 256MB, IDE disk).

=3D=3D=3D table definition =3D=3D=3D

CREATE TABLE "foo" (
        "id" character varying(40),
        "name" character varying(100),
        "ts" timestamp with time zone DEFAULT now()
);
CREATE UNIQUE INDEX foo_key ON foo (id);

=3D=3D=3D Perl script: pg_ins_test.pl =3D=3D=3D

#!/usr/bin/perl -w
my $id =3D shift;
open CMD,"| psql -q";
my $key =3D "${id}AAA";
for (my $i=3D0; $i<100; $i++) {
    print CMD "INSERT INTO foo (id,name) VALUES ('$key','Test name');\n";
    $key++;
}
close CMD;
print "Done $id\n";

=3D=3D=3D Shellscript: multicli_ins.sh =3D=3D=3D

#!/bin/sh
psql -c 'DELETE FROM foo'
for i in A B C D E F G H I J
do
  ./pg_ins_test.pl $i 2>/dev/null &
done

=3D=3D=3D End shellscript =3D=3D=3D

- Richard Huxton