From owner-freebsd-stable@FreeBSD.ORG Mon Nov 13 19:18:44 2006 Return-Path: X-Original-To: stable@freebsd.org Delivered-To: freebsd-stable@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0AE1616A403 for ; Mon, 13 Nov 2006 19:18:44 +0000 (UTC) (envelope-from vivek@khera.org) Received: from yertle.kcilink.com (yertle.kcilink.com [74.92.149.58]) by mx1.FreeBSD.org (Postfix) with ESMTP id 0FB2943E13 for ; Mon, 13 Nov 2006 19:16:59 +0000 (GMT) (envelope-from vivek@khera.org) Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103]) by yertle.kcilink.com (Postfix) with ESMTP id D0913B80F; Mon, 13 Nov 2006 14:16:54 -0500 (EST) In-Reply-To: <20061111022925.Q953@it.hackers> References: <453D49D2.1010705@rogers.com> <6DBE5906-CD84-44C5-AF40-FFCC78C7561E@khera.org> <20061024202408.U923@it.hackers> <6611C68B-1492-48A7-9425-3E23271CC940@khera.org> <20061111022925.Q953@it.hackers> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-9-454619936; protocol="application/pkcs7-signature" Message-Id: <92D6756F-12EA-43CF-95C3-33CB8A25FDC0@khera.org> From: Vivek Khera Date: Mon, 13 Nov 2006 14:16:53 -0500 To: Nguyen Tam Chinh X-Mailer: Apple Mail (2.752.2) X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: stable@freebsd.org Subject: Re: Running large DB's on FreeBSD X-BeenThere: freebsd-stable@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Production branch of FreeBSD source code List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 13 Nov 2006 19:18:44 -0000 --Apple-Mail-9-454619936 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed On Nov 10, 2006, at 6:35 PM, Nguyen Tam Chinh wrote: >> For the pg configuration, I use this on a 4Gb box: >> >> max_connections = 100 >> shared_buffers = 70000 # min 16 or >> max_connections*2, 8KB each >> work_mem = 262144 # min 64, size in KB > > Thank you very much. And how did you set the semaphore's > parameters? Do you have any trick or experience? I just think it's > just weird to inceremently increase ipc.shm* and ipc.sem* to get > the right values. The documentation of PostGreSQL gives us some > examples but without explanation how they found those values. The SEM parameters are the bare minimum. Pg uses a small number of semaphores, so unless you have a bazillion connections allowed, just use these settings: kern.ipc.semmsl=512 kern.ipc.semmap=256 kern.ipc.semmni=32 kern.ipc.semmns=512 Now, for the SHM usage, it is just arithmetic. You now how many buffers you're asking for, you know how big they are, and you just need to add some for overhead and you've got your number. If you want Pg to compute it for you, just read the error log when you fire up Pg with a small shm setting in the OS. --Apple-Mail-9-454619936--