X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id D425BD1C92A for ; Mon, 21 Jul 2003 08:30:14 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 61332-08 for ; Mon, 21 Jul 2003 08:30:13 +0000 (GMT) Received: from mail.cict.nl (vnd-7521.mxs.adsl.euronet.nl [62.234.149.33]) by svr1.postgresql.org (Postfix) with ESMTP id 58945D1C929 for ; Mon, 21 Jul 2003 05:30:12 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 5.9.4) with SMTP id DUC73886 for ; Mon, 21 Jul 2003 10:30:06 +0200 Message-ID: <002a01c34f62$75c01de0$b696a8c0@APR> From: "Alexander Priem" To: Subject: Tuning PostgreSQL Date: Mon, 21 Jul 2003 10:31:19 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0027_01C34F73.39211A90" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_30_40, HTML_FONT_BIG, HTML_FONT_COLOR_BLUE, HTML_FONT_FACE_ODD X-Spam-Level: X-Archive-Number: 200307/321 X-Sequence-Number: 2614 This is a multi-part message in MIME format. ------=_NextPart_000_0027_01C34F73.39211A90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi guys, I am new to PostgreSQL and have done some "extensive" research already. If = you could give me some advice/confirmation, I would be really grateful. I am going to build a PostgreSQL database server for a client. This databas= e will contain many tables (over 100, maybe more), with some of them contai= ning over 1 million records pretty soon. This database will be accessed via= a desktop application (Windows) and a web application (PHP/Apache). There = may be over 100 people accessing the database at any given time (selecting,= inserting and updating), but I don't think it will be a lot more than 100 = at any given time. Most of the time, it will be less. What I am thinking about is buying a server with the following specificatio= ns: * 1 or 2 Intel Xeon processors (2.4 GHz). * 2 Gigabytes of RAM (DDR/ECC). * Three 36Gb SCSI160 disks (10.000rpm) in a RAID-5 config, giving 72Gb stor= age space (right?). The RAID-5 controller has a (hardware) cache of 128Mb. * 100Mbit ethernet. I will run RedHat Linux 9 (kernel 2.40) with PostgreSQL 7.3.3 on this serve= r. What would you think of this hardware config? Would it do? Of would 4Gb RAM= be a lot better? What do you think about the need for two Xeon procs? Finally, I have some questions about postgresql.conf (who doesnt?). After s= ome research, I think I will go for the following settings initially. Maybe= benchmarking will lead to other values, but I think these settings will be= a fine starting point : shared_buffers =3D 6000 (kernel.shmmax =3D 60000000) sort_mem =3D 4096 max_connections =3D 150 vacuum_mem =3D 65536 What do you think of these settings? Do you have any other hints for optimi= zing PostgreSQL Many many thanks in advance :) Kind regards, Alexander Priem CICT Solutions Email: ap@cict.nl Internet: www.cict.nl ------=_NextPart_000_0027_01C34F73.39211A90 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi guys,
 
I am new to PostgreSQL and have done some= =20 "extensive" research already. If you could give me some advice/confirmation= , I=20 would be really grateful.
 
I am going to build a PostgreSQL database = server=20 for a client. This database will contain many tables (over 100,=20 maybe more), with some of them containing over 1 million records prett= y=20 soon. This database will be accessed via a desktop application (Windows) an= d a=20 web application (PHP/Apache). There may be over 100 people accessing t= he=20 database at any given time (selecting, inserting and updating), b= ut I=20 don't think it will be a lot more than 100 at any given time. Most of the t= ime,=20 it will be less.
 
What I am thinking about is buying a serve= r with=20 the following specifications:
 
* 1 or 2 Intel Xeon processors (2.4=20 GHz).
* 2 Gigabytes of RAM (DDR/ECC).
* Three 36Gb SCSI160 disks (10.000rpm) in = a RAID-5=20 config, giving 72Gb storage space (right?). The RAID-5 controller has=20 a (hardware) cache of 128Mb.
* 100Mbit ethernet.
 
I will run RedHat Linux 9 (kernel 2.40) wi= th=20 PostgreSQL 7.3.3 on this server.
 
What would you think of this hardware conf= ig? Would=20 it do? Of would 4Gb RAM be a lot better? What do you think about the need f= or=20 two Xeon procs?
 
Finally, I have some questions about=20 postgresql.conf (who doesnt?). After some research, I think I will go for t= he=20 following settings initially. Maybe benchmarking will lead to other values,= but=20 I think these settings will be a fine starting point :
 
shared_buffers =3D 6000 (kernel.shmma= x =3D=20 60000000)
sort_mem =3D 4096
max_connections =3D 150
vacuum_mem =3D 65536
 
What do you think of these settings? Do yo= u have=20 any other hints for optimizing PostgreSQL
 
Many many thanks in advance  &nb= sp;=20 :)
 

Kind regards,

Alexander Priem
CICT Solutions
Email: ap@cict.nl
Internet: www.cict.nl
------=_NextPart_000_0027_01C34F73.39211A90--