X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 98FD5D1C9FA for ; Wed, 1 Oct 2003 03:16:04 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 18255-06 for ; Wed, 1 Oct 2003 00:15:17 -0300 (ADT) Received: from www.postgresql.com (unknown [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id 903AFD1C9F7 for ; Tue, 30 Sep 2003 23:22:05 -0300 (ADT) Received: from boats_exch01.boats.com (h209-17-182-106.gtconnect.net [209.17.182.106]) by www.postgresql.com (Postfix) with ESMTP id 0FA24CF510A for ; Tue, 30 Sep 2003 17:17:43 -0300 (ADT) Received: by BOATS_EXCH01 with Internet Mail Service (5.5.2653.19) id ; Tue, 30 Sep 2003 13:16:17 -0700 Received: from GRIFFITHS2 ([192.168.12.195]) by boats_exch01.boats.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id TTY64KDL; Tue, 30 Sep 2003 13:16:15 -0700 From: David Griffiths To: pgsql-performance@postgresql.org Message-ID: <058d01c38791$0d0a91b0$6501a8c0@griffiths2> Subject: Tuning/performance issue.... Date: Tue, 30 Sep 2003 13:25:50 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0586_01C38756.5DD938C0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4922.1500 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4925.2800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_30_40, RCVD_IN_OSIRUSOFT_COM X-Spam-Level: X-Archive-Number: 200310/3 X-Sequence-Number: 3751 This is a multi-part message in MIME format. ------=_NextPart_000_0586_01C38756.5DD938C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable And finally, =20 Here's the contents of the postgresql.conf file (I've been playing with these setting the last couple of days, and using the guide @ http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html to make sure I didn't have it mis-tuned): =20 tcpip_socket =3D true max_connections =3D 500 # We will need quite a few connections; currently only one connection to database, however port =3D 5432 shared_buffers =3D 5000 # I've tried 5000 to 80,000 with no apparent difference wal_buffers =3D 16 sort_mem =3D 256 # decreased this due to the large # of connectiosn effective_cache_size =3D 50000 # read that this can improve performance; hasn't done anything. =20 The machine is a dual-Pentium 3 933mhz, with 2 gigabytes of RAM and a 3Ware RAID-5 card. =20 As a reference, our production Oracle database (exactly the same hardware, but RAID-mirroring) with way more load can handle the query in 1-2 seconds. I have MySQL 4.0.14 with InnoDB on the same machine (shutdown when I am testing Postgres, and visa versa) and it does the query in 0.20 seconds. =20 Thanks for any insight. David. ------=_NextPart_000_0586_01C38756.5DD938C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
And finally,
 
Here's the contents of the postgresql.conf= file=20 (I've been playing with these setting the last couple of days, and using th= e=20 guide @ http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.= html to=20 make sure I didn't have it mis-tuned):
 
tcpip_socket =3D true
max_connections =3D 500        # We = will need=20 quite a few connections; currently only one connection to database,=20 however
port =3D 5432
shared_buffers =3D=20 5000           # I've tri= ed=20 5000 to 80,000 with no apparent difference
wal_buffers =3D 16
sort_mem =3D=20 256            =    =20     # decreased this due to the large # of=20 connectiosn
effecti= ve_cache_size=20 =3D 50000 # read that this can impr= ove=20 performance; hasn't done anything.
 
The machine is a dual-Pentium 3 933mhz, wi= th 2=20 gigabytes of RAM and a 3Ware RAID-5 card.
 
As a reference, our production Oracle data= base=20 (exactly the same hardware, but RAID-mirroring) with way more load can hand= le=20 the query in 1-2 seconds. I have MySQL 4.0.14 with InnoDB on the same machi= ne=20 (shutdown when I am testing Postgres, and visa versa) and it does the query= in=20 0.20 seconds.
 
Thanks for any insight.
David.
------=_NextPart_000_0586_01C38756.5DD938C0--