From owner-freebsd-performance@FreeBSD.ORG Mon Aug 8 01:26:40 2005 Return-Path: X-Original-To: freebsd-performance@freebsd.org Delivered-To: freebsd-performance@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 5C9E716A421 for ; Mon, 8 Aug 2005 01:26:40 +0000 (GMT) (envelope-from mv@roq.com) Received: from vault.mel.jumbuck.com (ppp166-27.static.internode.on.net [150.101.166.27]) by mx1.FreeBSD.org (Postfix) with ESMTP id 749DF43D48 for ; Mon, 8 Aug 2005 01:26:39 +0000 (GMT) (envelope-from mv@roq.com) Received: from vault.mel.jumbuck.com (localhost [127.0.0.1]) by vault.mel.jumbuck.com (Postfix) with ESMTP id 9C72A8A02D; Mon, 8 Aug 2005 11:26:03 +1000 (EST) Received: from [192.168.46.52] (unknown [192.168.46.52]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by vault.mel.jumbuck.com (Postfix) with ESMTP id 8156F8A028; Mon, 8 Aug 2005 11:26:03 +1000 (EST) Message-ID: <42F6B4CD.5030703@roq.com> Date: Mon, 08 Aug 2005 11:26:37 +1000 From: Michael VInce User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US; rv:1.7.10) Gecko/20050802 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Claus Guttesen References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV using ClamSMTP Cc: freebsd-performance@freebsd.org Subject: Re: changing max_connections in postgresql on FreeBSD 5.4 X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 08 Aug 2005 01:26:40 -0000 Hi, I have done all my Postgres optimization configuration via sysctl or the postgresql.conf no kernel recompilation was performed. I did benchmarks at complete default FreeBSD / Postgres configuration and benchmarks after. I found raising the values to probably not much more then 1/4 of what they are now list below gave around 30% increase in performance. But increase the numbers after that did nothing. I still left them way past the mark anyway because this server has 4gigs of ram that would other wise go to waste. The Postgres DB setup here is only meant to handle a few connections at a time unlike yours. sysctl.conf kern.ipc.shmmax=1073741824 # x32 the default ( 8192 ) kern.ipc.shmall=262144 postgresql.conf # shared_buffers = 108000 32 x default = 884,736,000 bytes shared_buffers = 108000 # min 16, at least max_connections*2, 8KB each work_mem = 65536 # min 64, size in KB max_stack_depth = 4096 # min 100, size in KB max_fsm_pages = 40000 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 2000 # min 100, ~50 bytes each Also note that I have a MySQL server with a similar table setup and it performs a lot better. Claus Guttesen wrote: >Hi. > >I recently lowered max_connections from 1024 to 384 in >/usr/local/pgsql/data/postgresql.conf. The server is a quad opteron @ >2 GHz and 4 GB of RAM. > >This decreased the SIZE and RES values in top and it seems that the >current max_connections is more than adequate. To see how many >concurrent connections the db-server had I did a 'netstat -na|grep -i >establi|wc -l' which showed some 230 established connections during >peak. > >In order to use max_connections set to 1024 I had to raise the >following values in the kernel: > >options SHMMAXPGS=327680 >options SEMMNI=200 >options SEMMNS=1200 >options SEMUME=200 >options SEMMNU=600 > >The values were taken from the postgresql-ports-installation-notes and >were simply increased to fit the max_connections-parameter in >postgresql.conf. > >Will more RAM become available to postgresql if I change the >kernel-values mentioned above to half the size? > >The server does occasionally become loaded with load-averages around 6 >or 7 but usually stays below 4. Disk-io is not a problem, usually >remains below 1 MB/s, cpu-idle is approx. 5-10 % during peak and >around 80 % idle otherwise. > >regards >Claus >_______________________________________________ >freebsd-performance@freebsd.org mailing list >http://lists.freebsd.org/mailman/listinfo/freebsd-performance >To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.org" > >