X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 286E4B42168; Sun, 6 Jul 2003 07:49:13 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 51140-02; Sun, 6 Jul 2003 04:49:02 -0300 (ADT) Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net [194.217.242.80]) by svr1.postgresql.org (Postfix) with ESMTP id C4CB6B42000; Sun, 6 Jul 2003 04:49:01 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2) id 19Z4Gb-0002mQ-0U; Sun, 06 Jul 2003 08:49:02 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 4EA7117079; Sun, 6 Jul 2003 08:49:00 +0100 (BST) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id B07BC16497; Sun, 6 Jul 2003 08:48:59 +0100 (BST) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: Martin Foster , PostgreSQL Novice , PostgreSQL Performance Subject: Re: [PERFORM] Extreme high load averages Date: Sun, 6 Jul 2003 08:49:00 +0100 User-Agent: KMail/1.4.3 References: <3F07ABA1.2070100@ethereal-realms.org> In-Reply-To: <3F07ABA1.2070100@ethereal-realms.org> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200307060849.00544.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/36 X-Sequence-Number: 7571 On Sunday 06 Jul 2003 5:54 am, Martin Foster wrote: > The only time that I have ever seen load averages of 30 or more under > OpenBSD is when one of my scripts goes wild. However, I can say that > I am also seeing these load averages under PostgreSQL 7.3.2 after a > migration to it from MySQL. [snip] > However, the system > handled 250 concurrent users without a singular problem, while under > Postgres with new scripts using functions, referential integrity, > transactions and lighter code, the system starts to buckle at even less > then 70 users. [snip] > PIII 1Ghz, 1GB > SDRAM, 2 IDE 20GB drives. > > I have changed settings to take advantage of the memory. So the > following settings are of interest: > shared_buffers =3D 16384 > wal_buffers =3D 256 > sort_mem =3D 16384 > vacuum_mem =3D 32768 You do know that sort_mem is in kB per sort (not per connection, but per so= rt=20 being done by a connection). That's 16MB per sort you've allowed in main=20 memory, or for 70 concurrent sorts up to 1.1GB of memory allocated to=20 sorting. You're not going into swap by any chance? Might want to try halving shared_buffers too and see what happens. I don't know the *BSDs myself, but do you have the equivalent of iostat/vms= tat=20 output you could get for us? Also a snapshot of "top" output? People are=20 going to want to see: - overall memory usage (free/buffers/cache/swap) - memory usage per process - disk activity (blocks in/out)