From owner-freebsd-stable@FreeBSD.ORG Fri Nov 10 23:35:29 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 D945C16A403 for ; Fri, 10 Nov 2006 23:35:29 +0000 (UTC) (envelope-from chinhngt@sectorb.msk.ru) Received: from hq.sectorb.msk.ru (petaflop.b.gz.ru [194.88.210.5]) by mx1.FreeBSD.org (Postfix) with ESMTP id 216A943D55 for ; Fri, 10 Nov 2006 23:35:28 +0000 (GMT) (envelope-from chinhngt@sectorb.msk.ru) Received: from it.local (it.local [172.16.20.8]) by hq.sectorb.msk.ru (Postfix) with ESMTP id 03CA11CC8; Sat, 11 Nov 2006 02:35:26 +0300 (MSK) Date: Sat, 11 Nov 2006 02:35:23 +0300 (MSK) From: Nguyen Tam Chinh X-X-Sender: chinhngt@it.hackers To: Vivek Khera In-Reply-To: <6611C68B-1492-48A7-9425-3E23271CC940@khera.org> Message-ID: <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> X-Operating-System: FreeBSD 6-STABLE Keywords: 216091683 MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed 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: Fri, 10 Nov 2006 23:35:29 -0000 On Tue, 24 Oct 2006, Vivek Khera wrote: > > On Oct 24, 2006, at 12:27 PM, Nguyen Tam Chinh wrote: > >>> The size of your DB is not all that large. There are people running >>> terabyte DB's under postgres. Our big DB is around 60Gb with hundreds of >>> millions of rows spread across dozens of tables which are regularly joined >>> with each other for reports. It is pounded on 24x7 with lots and lots of >>> inserts, updates, and selects going on all the time. >>> >> >> Could you share with us your servers' hardware specifics and configuration >> (tuning) of PostgreSQL? >> This would help many in making decision. > > My current favorites are the SunFire X4100 from Sun with an Adaptec 2230SLP > dual channel U320 RAID card and a 14+ disk array. These are incredibly > stable. The disk arrays I have right now are from Dell, and I would not > recommend them. I don't think they're totally U320 compliant as some drives > occasionally come up at U160 speed. The Adaptec card is the *only* dual > channel U320 SCSI card availble in low-profile size; unfortunately LSI > doesn't make a low-profile version of the 320-2X card... > > I have one box with 4Gb which is good for our smaller databases, and one > which we are upgrading from 4Gb to 8Gb next week due to the high load it has. > > I use 1 disk from each SCSI channel to make a mirrored RAID volume for boot + > OS + postgres transaction log, and the remaining disks in RAID10 with the > disks on each mirror pair coming from opposite SCSI channels. > > 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 > maintenance_work_mem = 524288 # min 1024, size in KB > max_fsm_pages = 1800000 # min max_fsm_relations*16, 6 bytes > each > vacuum_cost_delay = 25 # 0-1000 milliseconds > checkpoint_segments = 256 > checkpoint_timeout = 900 > effective_cache_size = 27462 # `sysctl -n vfs.hibufspace` / 8192 > (BLKSZ) > random_page_cost = 2 > log_min_error_statement = error > 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. ----- With best regards, | The Power to Serve Nguyen Tam Chinh | http://www.FreeBSD.org Loc: sp.cs.msu.su |