From owner-freebsd-stable@FreeBSD.ORG Tue Oct 24 18:48:17 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 85C8416A4C8 for ; Tue, 24 Oct 2006 18:48:17 +0000 (UTC) (envelope-from vivek@khera.org) Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id 1B29243D55 for ; Tue, 24 Oct 2006 18:48:16 +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 20FFFB821 for ; Tue, 24 Oct 2006 14:48:16 -0400 (EDT) Mime-Version: 1.0 (Apple Message framework v752.2) In-Reply-To: <20061024202408.U923@it.hackers> References: <453D49D2.1010705@rogers.com> <6DBE5906-CD84-44C5-AF40-FFCC78C7561E@khera.org> <20061024202408.U923@it.hackers> Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-4-872385275; protocol="application/pkcs7-signature" Message-Id: <6611C68B-1492-48A7-9425-3E23271CC940@khera.org> From: Vivek Khera Date: Tue, 24 Oct 2006 14:48:15 -0400 To: stable@freebsd.org X-Mailer: Apple Mail (2.752.2) X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: 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: Tue, 24 Oct 2006 18:48:17 -0000 --Apple-Mail-4-872385275 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed 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 Everything else is default. We run vacuum manually rather than using autovacuum for historical reasons. With upcoming 8.2 release I plan to experiment with higher shared_buffers settings. But for best help, pose your load details and hardware details and query info to the pgsql-performance list. Very smart folk there to help. --Apple-Mail-4-872385275--