From owner-freebsd-database@FreeBSD.ORG Sat Jul 19 15:13:15 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 699A337B401; Sat, 19 Jul 2003 15:13:15 -0700 (PDT) Received: from remt25.cluster1.charter.net (remt25.cluster1.charter.net [209.225.8.35]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9C2E643F93; Sat, 19 Jul 2003 15:13:14 -0700 (PDT) (envelope-from paul@pathiakis.com) Received: from [66.189.13.10] (HELO pc2.pathiakis.com) by remt25.cluster1.charter.net (CommuniGate Pro SMTP 4.0.6) with ESMTP id 97167237; Sat, 19 Jul 2003 18:13:13 -0400 From: Paul Pathiakis To: freebsd-performance@freebsd.org, freebsd-database@freebsd.org Date: Sat, 19 Jul 2003 18:18:13 -0400 User-Agent: KMail/1.5.2 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200307191818.13516.paul@pathiakis.com> Subject: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 19 Jul 2003 22:13:15 -0000 Hi, I'm trying to setup a dedicated PostGreSQL Database server. First off, don't get me going on the inadequacies of the hardware setup, I am a sysadmin with a partial clue. :-) (The hardware was bought without any input from me for demo purposes.) It's an Intel board with twin 2.8 GHZ processors and Promise ATA Fastrack RAID built-in, 1 GB of RAM. Since this is just a throughput/response test against SuSe Linux Enterprise Server, Windows XP and FreeBSD, I'm going to break the mirror and try to do some intelligence about at least trying to log to a different drive. Anyhow, there are 2-3 drives in this box, no scsi, nothing. I want it to perform like no tomorrow on FreeBSD, not being a DB admin, I'm going to try to cover all the bases on this, so please let me know if I've missed something: First, this is a demo, I'm not worried about redundancy at this point. FreeBSD 4.8 or 5.1? OS configuration, any suggestions on SWAP layout, how much swap, both drives? Are there optimal parameters for sizing on the disk throughput? 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) 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. what is the best layout of OS, PostgreSQL, and PostgreSQL logging on a 2 disk configuration? 3 disk configuration? Is there anything that I should put in the following files for tuning: systrcl.conf (semaphore and shared memory configurations, etc?) 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. Thank you all! Paul Pathiakis From owner-freebsd-database@FreeBSD.ORG Sat Jul 19 15:51:33 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id EEC8B37B401; Sat, 19 Jul 2003 15:51:32 -0700 (PDT) Received: from misery.sdf.com (misery.sdf.com [207.200.153.226]) by mx1.FreeBSD.org (Postfix) with ESMTP id EB81943FB1; Sat, 19 Jul 2003 15:51:29 -0700 (PDT) (envelope-from tom@sdf.com) Received: from tom (helo=localhost) by misery.sdf.com with local-esmtp (Exim 2.12 #1) id 19dynS-0005PD-00; Sat, 19 Jul 2003 13:59:14 -0700 Date: Sat, 19 Jul 2003 13:59:12 -0700 (PDT) From: Tom Samplonius To: Paul Pathiakis In-Reply-To: <200307191818.13516.paul@pathiakis.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 19 Jul 2003 22:51:33 -0000 On Sat, 19 Jul 2003, Paul Pathiakis wrote: > Hi, > > I'm trying to setup a dedicated PostGreSQL Database server. First off, don't > get me going on the inadequacies of the hardware setup, I am a sysadmin with > a partial clue. :-) (The hardware was bought without any input from me for > demo purposes.) It's an Intel board with twin 2.8 GHZ processors and Promise > ATA Fastrack RAID built-in, 1 GB of RAM. Since this is just a > throughput/response test against SuSe Linux Enterprise Server, Windows XP and > FreeBSD, I'm going to break the mirror and try to do some intelligence about > at least trying to log to a different drive. Anyhow, there are 2-3 drives in > this box, no scsi, nothing. I want it to perform like no tomorrow on You don't really mention the nature of the test that you will running. Read intensive? Write intensive? 25/75 mix? Number of clients? > FreeBSD 4.8 or 5.1? Probably 5.1-RELEASE. > OS configuration, any suggestions on SWAP layout, how much swap, > both drives? As always, swap should be twice that of RAM. Ideally, depending on what the test consists of, you won't use swap at all. > Are there optimal parameters for sizing on the disk throughput? > 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) Well, it is database app, so very little on the filesystem is going to help. PostgreSQL will create a bunch of bunch of files, and randomly read and write into them. > 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, async off. Softupdates is just a better async. However, neither is going to help or hurt you much anyhow. Both softupdates and sync change how meta data is written to disk. Once the database files are created, meta data isn't much of any issue. > what is the best layout of OS, PostgreSQL, and PostgreSQL > logging on a 2 disk configuration? 3 disk configuration? Well, the database and the logs should be on separate devices. If the database can be broken up, do that too. I don't know that the OS files will be accessed much after boot, so that really isn't going to cause any significant load. > Is there anything that I should put in the following files for > tuning: > > systrcl.conf (semaphore and shared memory configurations, etc?) Well, you need to increase the semaphores and shared memory configuration limits to match whatever you plan to give to PostgreSQL. I don't know whether the test consists of. If the test database is large, you'll want a large shared memory area. But I find that many people do tests with tiny non-real-world databases. It is pointless to make the shared area larger than the database size! Plus, you need some memory available for client connections. Don't know how many clients you will have connecting. > loader.conf (hw.ata.atapi_dma on or off? how do I make the ... DMA should probably be on, as long as your disks can handle it. You will have to test with HyperThreading too. If the client count is large, HyperThreading might help. However, it might increase latency. No idea if you test measures latency! If the test is just raw throughput with lots of clients, HTT is probably going to help. However, if you never have more than 4 connected clients, probably not. > If there's additional reading, let me know. I'm more than happy > to follow links, read more about PostgreSQL, etc. > > Thank you all! > > Paul Pathiakis Tom From owner-freebsd-database@FreeBSD.ORG Sat Jul 19 20:58:37 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 1C0BA37B401 for ; Sat, 19 Jul 2003 20:58:37 -0700 (PDT) Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150]) by mx1.FreeBSD.org (Postfix) with ESMTP id 92E4043FBD for ; Sat, 19 Jul 2003 20:58:36 -0700 (PDT) (envelope-from dan@langille.org) Received: from wocker (wocker.unixathome.org [192.168.0.99]) by bast.unixathome.org (Postfix) with ESMTP id 091FB3D28 for ; Sat, 19 Jul 2003 23:58:36 -0400 (EDT) From: "Dan Langille" To: freebsd-database@freebsd.org Date: Sat, 19 Jul 2003 23:58:35 -0400 MIME-Version: 1.0 Message-ID: <3F19DB2B.8383.45E3377E@localhost> Priority: normal References: <200307191818.13516.paul@pathiakis.com> In-reply-to: X-mailer: Pegasus Mail for Windows (v4.02a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 20 Jul 2003 03:58:37 -0000 On 19 Jul 2003 at 13:59, Tom Samplonius wrote: > On Sat, 19 Jul 2003, Paul Pathiakis wrote: > > > FreeBSD 4.8 or 5.1? > > Probably 5.1-RELEASE. I would recommend 4.8 over 5.1. Especially if you intend this to be a production server. 5.1 is not ready for public consumption. -- Dan Langille : http://www.langille.org/