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 concurrent
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 how
much and what is happening try one of : vmstat, iostat, top. You should have
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
think there's a tuning document on techdocs.postgresql.org). Bear in mind
that the default settings for postgresql are very small, so you might want to
look at the sort memory/shared memory settings. Increase values a bit at a
time and see where you get a good balance. See the list archives for lots of
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 each
of which inserts 100 rows in separate transactions. This is about as
inefficient as you can get.
Have a play with different numbers of connections and see if there's a
pattern.
=> 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
untuned system on a development server (400MHz, 256MB, IDE disk).
=== table definition ===
CREATE TABLE "foo" ( "id" character varying(40), "name" character varying(100), "ts" timestamp with
timezone DEFAULT now()
);
CREATE UNIQUE INDEX foo_key ON foo (id);
=== Perl script: pg_ins_test.pl ===
#!/usr/bin/perl -w
my $id = shift;
open CMD,"| psql -q";
my $key = "${id}AAA";
for (my $i=0; $i<100; $i++) { print CMD "INSERT INTO foo (id,name) VALUES ('$key','Test name');\n"; $key++;
}
close CMD;
print "Done $id\n";
=== Shellscript: multicli_ins.sh ===
#!/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
=== End shellscript ===
- Richard Huxton
