From owner-freebsd-performance@FreeBSD.ORG Sun Jul 20 04:09:41 2003 Return-Path: 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 CFB0037B401; Sun, 20 Jul 2003 04:09:41 -0700 (PDT) Received: from perrin.int.nxad.com (internal.ext.nxad.com [69.1.70.251]) by mx1.FreeBSD.org (Postfix) with ESMTP id 3BBA644001; Sun, 20 Jul 2003 04:09:40 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 8BBBE20F00; Sun, 20 Jul 2003 04:09:39 -0700 (PDT) Date: Sun, 20 Jul 2003 04:09:39 -0700 From: Sean Chittenden To: Paul Pathiakis Message-ID: <20030720110939.GN24507@perrin.int.nxad.com> References: <200307191818.13516.paul@pathiakis.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200307191818.13516.paul@pathiakis.com> X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 3849 3760 1AFE 7B17 11A0 83A6 DD99 E31F BC84 B341 X-Web-Homepage: http://sean.chittenden.org/ User-Agent: Mutt/1.5.4i cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 20 Jul 2003 11:09:42 -0000 > FreeBSD 4.8 or 5.1? 4.8 is currently faster than 5.1. > OS configuration, any suggestions on SWAP layout, how much > swap, both drives? If this is a demo and you're looking for speed, SWAP isn't going to buy you anything. Have some swap that way idle bits can be paged out. As for your drives, use two drives for data (striped) and one drive for WAL logs. Put swap wherever, it won't matter. > Are there optimal parameters for sizing on the disk > throughput? Increase your file system cache. > That is, newfs parameters, i-node configuration, tunefs parameters, > async on the drives, etc? (consider nothing trivial, please feel > free to just lay into this like I'm a newbie - considering my > not-so-extensive experience with PostgreSQL) Read the /usr/local/pgsql/post-install-notes, for starters. > Should I keep softupdates on or off? This is a db and I am > constrained by data integrity issues, however, I'm looking for as > much speed as possible given the limited setup. Softupdates, on. > what is the best layout of OS, PostgreSQL, and PostgreSQL > logging on a 2 disk configuration? 3 disk configuration? See above. > Is there anything that I should put in the following files for > tuning: > > systrcl.conf (semaphore and shared memory configurations, > etc?) See the archives, there are bunches of recommendations for this. You might also want to check the performance@PostgreSQL.org list's archives too. > loader.conf (hw.ata.atapi_dma on or off? how do I make the > hardware scream as fast as possible at tuning level and then with > considerations to running PostgreSQL? Give as much too me as you > can. I'm the sysadmin and I love how easily supportable FreeBSD is > with regards to software, package, OS, maintenance. People are > vying for XP and Suse. However, I'll end up supporting this and I > know how easy FreeBSD is to support. > If there's additional reading, let me know. I'm more than > happy to follow links, read more about PostgreSQL, etc. Use the most recent version of PostgreSQL, 7.4-devel (most recent CVS snapshot if you can, it's in BETA and very stable, IMHO). Once you get your demo installed, read through: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html http://developer.postgresql.org/docs/postgres/performance-tips.html A fair number of the PgSQL experts recommend using FreeBSD's, actually: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#freebsd Make liberal use of EXPLAIN ANALYZE [query], VACUUM ANALYZE, and possibly CLUSTER. If you have any probs once you have something up and running, come back with details and we'll get your demo to fly if it isn't already (should be after reading the above links though). -sc -- Sean Chittenden