X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C471B52AE8 for ; Fri, 29 Jul 2005 08:34:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09875-09 for ; Fri, 29 Jul 2005 11:34:10 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.201]) by svr1.postgresql.org (Postfix) with ESMTP id 0B85852AC7 for ; Fri, 29 Jul 2005 08:34:09 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i4so602835wra for ; Fri, 29 Jul 2005 04:34:12 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type; b=HwQPltePmh/nNVIi8Y/4G6r60lbqBT9zm2tWczWW15id6nt1y7bXQcL7MFKz6uAZdOwiRWVmCFmKZnSx1FIu3ebfL2rFlNRzqSJA7XPqjgZljUAHUowbprkuopU5bY7NX+cDFC31MqyDtUKqGiU/PDczghxTCgoKVxqymUHTiZE= Received: by 10.54.57.62 with SMTP id f62mr1258405wra; Fri, 29 Jul 2005 04:34:12 -0700 (PDT) Received: by 10.54.129.3 with HTTP; Fri, 29 Jul 2005 04:34:12 -0700 (PDT) Message-ID: Date: Fri, 29 Jul 2005 12:34:12 +0100 From: Kailash Vyas Reply-To: Kailash Vyas To: pgsql-admin@postgresql.org Subject: postgres optimization Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5332_31842784.1122636852532" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.386 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200507/238 X-Sequence-Number: 18294 ------=_Part_5332_31842784.1122636852532 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi I need some information to optimize the postgres database server (7.3.10). I have 2 Gb RAM on the server. I have set the /proc/sys/kernel/shmmax and /proc/sys/kernel/shmall to=20 536870912 (512 Mb) should i set the shmmax and shmall to use 2 GB or it is better to keep it= =20 less. also how does it affect the performance and stability of system what would be the ideal shared buffer size and effective cache size to be= =20 set in conf file. currently i am using this expression to find out shared buffers parameter max connections =3D 128 250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or infinity shared buffers =3D 65277 (approx) also regarding the effective cache size how much should i set that to ? one of the article suggested this so which option would be more optimized "there is 1.5GB RAM in your machine, shared buffers are set to 32MB and=20 effective cache size is set to 800MB. So if a query needs 700MB of data set= ,=20 PostgreSQL would estimate that all the data required should be available in= =20 memory and would opt for more aggressive plan in terms of optimization,=20 involving heavier index usage and merge joins. But if effective cache is se= t=20 to only 200MB, the query planner is liable to opt for the more I/O efficien= t=20 sequential scan." thanks, kailash ------=_Part_5332_31842784.1122636852532 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi


I need some information to optimize the postgres database server (7.3.10).<= br> I have 2 Gb RAM on the server.

I have set the /proc/sys/kernel/shmmax and /proc/sys/kernel/shmall to = 536870912 (512 Mb)

should i set the shmmax and shmall to use 2 GB  or  it is better = to keep it less.
also how does it affect the performance and stability of system


what would be the ideal shared buffer size and effecti= ve cache size to be set in conf file.

currently i am using this expression to find out shared buffers parameter
max connections =3D 128

250kB + 8.2 kB * shared_buffers + 14.2 kB * = max_connections or infinity

shared buffers =3D 65277 (approx)

also regarding the effective cache size how much should i set that to ?

one of the article suggested this so which option would be more optimized
"there is 1.5GB RAM in your machine, shared buffers= =20 are set to 32MB and effective cache size is set to=20 800MB. So if a query needs 700MB of data set,=20 PostgreSQL would estimate that all the data required=20 should be available in memory and would opt for more=20 aggressive plan in terms of optimization, involving=20 heavier index usage and merge joins. But if effective=20 cache is set to only 200MB, the query planner is liable=20 to opt for the more I/O efficient sequential scan."


thanks,
kailash
------=_Part_5332_31842784.1122636852532--