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 8A5E4529C0 for ; Fri, 29 Jul 2005 10:24:21 -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 39895-03 for ; Fri, 29 Jul 2005 13:24:12 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.192]) by svr1.postgresql.org (Postfix) with ESMTP id 4C324529A2 for ; Fri, 29 Jul 2005 10:24:11 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i4so619098wra for ; Fri, 29 Jul 2005 06:24:15 -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:cc:mime-version:content-type; b=cxy6qlU8/mk1sYpUgFFea9GoRwjVv9MBPeBPIqUQ1kmLGdSsi1xKl4efOjuXHtRA3tvLsZjk2R3FZbbRiUxjSE8+pY1IOj7NRE6F+Lwr4PMbZfzPxZbJA8WEreHi4uQfiK2sLngbJIeU69H21IOrh9EgbqE6LgyTMJyGevq/Wqc= Received: by 10.54.45.14 with SMTP id s14mr1316102wrs; Fri, 29 Jul 2005 06:24:15 -0700 (PDT) Received: by 10.54.129.3 with HTTP; Fri, 29 Jul 2005 06:24:15 -0700 (PDT) Message-ID: Date: Fri, 29 Jul 2005 14:24:15 +0100 From: Kailash Vyas Reply-To: Kailash Vyas To: pgsql-admin@postgresql.org Subject: postgres optimization Cc: kailash.vyas@gmail.com Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5568_903818.1122643455862" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.41 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200507/240 X-Sequence-Number: 18296 ------=_Part_5568_903818.1122643455862 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi i am optmizing postgres database and need some help on it. I currently have a server with 2 Gb RAM. I have setup the /proc/sys/kernel/shmall and /proc/sys/kernel/shmmax to=20 536870912 i.e 512 Mb. should i change it to 2 Gb and how will it affect the perfomance and will= =20 there be any downsides to it or is it better to keep it less. I then changed the shared buffers in config file according to this=20 expression specifies in postgres manual max connections=3D128 250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or infinity shared buffer=3D65277 what value should i specify for Effective Cache Size. I came across this=20 article but it is slightly confusing. "Say 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_5568_903818.1122643455862 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi

i am optmizing postgres database and need some help on it.
I currently have a server with 2 Gb RAM.

I have setup the /proc/sys/kernel/shmall and /proc/sys/kernel/shmmax to 536= 870912 i.e 512 Mb.
should i change it to 2 Gb and how will it affect the perfomance and will there be any downsides to it or is it better to keep it less.

I then changed the shared buffers in config file according to this expressi= on specifies in postgres manual

max connections=3D128
250kB + 8.2 kB * shared_buffers + 14.2 kB * = max_connections or infinity


shared buffer=3D65277

what value should i specify for Effective Cache Size. I= came across this article but it is slightly confusing.

"Say 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_5568_903818.1122643455862--