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 From owner-freebsd-performance@FreeBSD.ORG Sat Jul 19 15:13:15 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 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> X-Mailman-Approved-At: Sun, 20 Jul 2003 04:15:42 -0700 Subject: 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: 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-performance@FreeBSD.ORG Sat Jul 19 15:51:33 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 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 X-Mailman-Approved-At: Sun, 20 Jul 2003 04:15:42 -0700 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: 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-performance@FreeBSD.ORG Sat Jul 19 20:51: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 2229537B401 for ; Sat, 19 Jul 2003 20:51:41 -0700 (PDT) Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150]) by mx1.FreeBSD.org (Postfix) with ESMTP id 6B83E43F93 for ; Sat, 19 Jul 2003 20:51:40 -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 E28D73D28; Sat, 19 Jul 2003 23:51:38 -0400 (EDT) From: "Dan Langille" To: Tom Samplonius Date: Sat, 19 Jul 2003 23:51:38 -0400 MIME-Version: 1.0 Message-ID: <3F19D98A.26914.45DCDA15@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 X-Mailman-Approved-At: Sun, 20 Jul 2003 04:15:42 -0700 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 03:51:41 -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/ From owner-freebsd-performance@FreeBSD.ORG Sun Jul 20 04:25:51 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 9C93037B401 for ; Sun, 20 Jul 2003 04:25:51 -0700 (PDT) Received: from perrin.int.nxad.com (internal.ext.nxad.com [69.1.70.251]) by mx1.FreeBSD.org (Postfix) with ESMTP id 317C143FBF for ; Sun, 20 Jul 2003 04:25:51 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 3C19820F00; Sun, 20 Jul 2003 04:25:50 -0700 (PDT) Date: Sun, 20 Jul 2003 04:25:50 -0700 From: Sean Chittenden To: Dan Langille Message-ID: <20030720112550.GO24507@perrin.int.nxad.com> References: <200307191818.13516.paul@pathiakis.com> <3F19D98A.26914.45DCDA15@localhost> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3F19D98A.26914.45DCDA15@localhost> 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-performance@freebsd.org cc: Tom Samplonius 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:25:51 -0000 > > > 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. Public consumption, yes. Production consumption, no. 5.1 is still pretty crippled in its current state because it's not Giant free yet. Once Giant free, however, 5.X will be much closer to production ready and should be quicker as a result of the fine grained locking (or so everyone hopes). The simple locking mechanism in 4.x does have some advantages in cases and should be a consistent performer on UP machines and under most loads.... whether or not 5.x over takes 4.X in terms of speed, is the subject of great debate, but many are optimistic that it will be at some point, just not at the moment. 5.X, will however (and without much doubt), scale much better than 4.X on multiple processor machines, though I'm not sure where that stands at the moment in terms of being completed and should likely be directed to current@ or questions@ instead of here. -sc -- Sean Chittenden From owner-freebsd-performance@FreeBSD.ORG Sun Jul 20 09:42:44 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 0CB7C37B401 for ; Sun, 20 Jul 2003 09:42:44 -0700 (PDT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by mx1.FreeBSD.org (Postfix) with SMTP id D21BB43FAF for ; Sun, 20 Jul 2003 09:42:42 -0700 (PDT) (envelope-from decibel@decibel.org) Received: (qmail 15151 invoked by uid 1001); 20 Jul 2003 16:42:37 -0000 Date: Sun, 20 Jul 2003 11:42:37 -0500 From: "Jim C. Nasby" To: Sean Chittenden Message-ID: <20030720164237.GC55392@nasby.net> References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030720110939.GN24507@perrin.int.nxad.com> User-Agent: Mutt/1.4.1i X-Operating-System: FreeBSD 4.8-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis 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 16:42:44 -0000 On Sun, Jul 20, 2003 at 04:09:39AM -0700, Sean Chittenden wrote: > > Are there optimal parameters for sizing on the disk > > throughput? > > Increase your file system cache. How? I've been wondering if there's a way to do this, and all the settings I've played with seem to have no effect; it just sits at 199BUF according to top. -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" From owner-freebsd-performance@FreeBSD.ORG Sun Jul 20 11:26:52 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 E29D837B401; Sun, 20 Jul 2003 11:26:52 -0700 (PDT) Received: from misery.sdf.com (misery.sdf.com [207.200.153.226]) by mx1.FreeBSD.org (Postfix) with ESMTP id 3FB3C43F3F; Sun, 20 Jul 2003 11:26:51 -0700 (PDT) (envelope-from tom@sdf.com) Received: from tom (helo=localhost) by misery.sdf.com with local-esmtp (Exim 2.12 #1) id 19eH8m-00078M-00; Sun, 20 Jul 2003 09:34:28 -0700 Date: Sun, 20 Jul 2003 09:34:25 -0700 (PDT) From: Tom Samplonius To: Sean Chittenden In-Reply-To: <20030720110939.GN24507@perrin.int.nxad.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis 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 18:26:53 -0000 On Sun, 20 Jul 2003, Sean Chittenden wrote: > > FreeBSD 4.8 or 5.1? > > 4.8 is currently faster than 5.1. Probably not, especially for SMP. 4.8 uses a single giant lock on the kernel, and 5.1 has that mostly removed. Tom From owner-freebsd-performance@FreeBSD.ORG Sun Jul 20 13:53: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 7D13937B401; Sun, 20 Jul 2003 13:53: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 BC27843F85; Sun, 20 Jul 2003 13:53:40 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id B813920F00; Sun, 20 Jul 2003 13:53:39 -0700 (PDT) Date: Sun, 20 Jul 2003 13:53:39 -0700 From: Sean Chittenden To: "Jim C. Nasby" Message-ID: <20030720205339.GP24507@perrin.int.nxad.com> References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> <20030720164237.GC55392@nasby.net> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="0XhtP95kHFp3KGBe" Content-Disposition: inline In-Reply-To: <20030720164237.GC55392@nasby.net> 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 X-Mailman-Approved-At: Sun, 20 Jul 2003 14:18:08 -0700 cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis 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 20:53:41 -0000 --0XhtP95kHFp3KGBe Content-Type: multipart/mixed; boundary="S5HS5MvDw4DmbRmb" Content-Disposition: inline --S5HS5MvDw4DmbRmb Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable > > > Are there optimal parameters for sizing on the disk > > > throughput? > >=20 > > Increase your file system cache. > =20 > How? I've been wondering if there's a way to do this, and all the > settings I've played with seem to have no effect; it just sits at > 199BUF according to top. You might want to get in the habit of using sysctl for getting that kind of info. `sysctl -d vfs.bufspace` #!/bin/sh echo "Max Buffer space =3D=3D $((`sysctl -n vfs.maxbufspace` / 1024 / 1024)= )MB" The sysctl's that are going to be of most interest (in terms of monitoring) are: sysctl -a| grep bufspace However, none of those will let you adjust your buffer size. To do that, you want to change kern.nbuf in /boot/loader.conf (*grumble* Undocumented loader option). kern.maxbcache Limits the amount of KVM reserved for use by the buffer cache, specified in bytes. The default maximum is 200MB. This parameter is used to prevent the buffer cache from eating too much KVM in large-memory machine configuratio= ns. Only mess around with this parameter if you need to grea= tly extend the KVM reservation for other resources such as t= he swap zone or NMBCLUSTERS. Note that the NBUF parameter will override this limit. Modifies VM_BCACHE_SIZE_MAX. By default, the number of NBUF's on a system is auto sized and takes up to kern.maxbcache. There was a post by a guy to the performance@ list where he was trying to optimize a 4GB machine for squid and he'd set it kern.nbuf to 16384 with 512 KVA_PAGES. *shrug* YMMV with this guy's settings, but: http://lists.freebsd.org/pipermail/freebsd-performance/2003-June/000262.html This post in the developers handbook should also be of interest to most people playing with the NBUF setting: http://www.freebsd.org/doc/en_US.ISO8859-1/books/developers-handbook/x8987.= html I've attached an untested patch that lets you see what your kern.nbufs are via a sysctl. At the moment, determining what kern.nbuf is set to is a bit ellusive. If someone with src foo can bless this patch, I'll commit it. > Windows: "Where do you want to go today?" > Linux: "Where do you want to go tomorrow?" > FreeBSD: "Are you guys coming, or what?" Cool signature. -sc --=20 Sean Chittenden --S5HS5MvDw4DmbRmb Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=patch Content-Transfer-Encoding: quoted-printable Index: boot/common/help.common =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D RCS file: /home/ncvs/src/sys/boot/common/help.common,v retrieving revision 1.25 diff -u -r1.25 help.common --- boot/common/help.common 5 May 2003 07:33:12 -0000 1.25 +++ boot/common/help.common 20 Jul 2003 20:38:49 -0000 @@ -244,6 +244,11 @@ Set the number of sendfile buffers to be allocated. This overrides the value determined when the kernel was compiled. =20 + set kern.nbuf=3D NBUF + + Sets the amount of KVA the kernel can use to map filesystem + buffers for I/O. + set kern.vm.kmem.size=3D VM_KMEM_SIZE =20 Sets the size of kernel memory (bytes). This overrides Index: boot/common/loader.8 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D RCS file: /home/ncvs/src/sys/boot/common/loader.8,v retrieving revision 1.57 diff -u -r1.57 loader.8 --- boot/common/loader.8 29 Jun 2003 20:57:55 -0000 1.57 +++ boot/common/loader.8 20 Jul 2003 20:51:31 -0000 @@ -450,7 +450,7 @@ .Xr sendfile 2 buffers to be allocated. Overrides -.Dv NSFBUFS . +.Va NSFBUFS . .It Va kern.vm.kmem.size Sets the size of kernel memory (bytes). This overrides the value determined when the kernel was compiled. @@ -491,6 +491,13 @@ the NBUF parameter will override this limit. Modifies .Va VM_BCACHE_SIZE_MAX . +.It Va kern.nbuf +Sets the amount of KVA the system can use to map filesystem +buffers for I/O. This parameter is normally automatically +tuned by the kernel at startup and it is recommended that +this parameter be unused unless the administrator knows +exactly what adjusting this value influences. Overrides +.Va NBUF . .It Va machdep.disable_mtrrs Disable the use of i686 MTRRs (x86 only). .It Va net.inet.tcp.tcbhashsize Index: kern/kern_mib.c =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D RCS file: /home/ncvs/src/sys/kern/kern_mib.c,v retrieving revision 1.68 diff -u -r1.68 kern_mib.c --- kern/kern_mib.c 11 Jun 2003 00:56:56 -0000 1.68 +++ kern/kern_mib.c 20 Jul 2003 20:37:11 -0000 @@ -127,6 +127,9 @@ SYSCTL_INT(_kern, KERN_JOB_CONTROL, job_control, CTLFLAG_RD, 0, 1, "Whether job control is available"); =20 +SYSCTL_INT(_kern, KERN_NBUF, nbuf, CTLFLAG_RD, + &nbuf, 0, "Amount of KVA used to map filesystem buffers for I/O"); + #ifdef _POSIX_SAVED_IDS SYSCTL_INT(_kern, KERN_SAVED_IDS, saved_ids, CTLFLAG_RD, 0, 1, "Whether saved set-group/user ID is available"); Index: sys/sysctl.h =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D RCS file: /home/ncvs/src/sys/sys/sysctl.h,v retrieving revision 1.118 diff -u -r1.118 sysctl.h --- sys/sysctl.h 12 Jul 2003 02:00:16 -0000 1.118 +++ sys/sysctl.h 20 Jul 2003 20:26:20 -0000 @@ -355,6 +355,7 @@ #define KERN_LOGSIGEXIT 34 /* int: do we log sigexit procs? */ #define KERN_IOV_MAX 35 /* int: value of UIO_MAXIOV */ #define KERN_MAXID 36 /* number of valid kern ids */ +#define KERN_NBUFS 37 /* number of kernel buffers */ =20 #define CTL_KERN_NAMES { \ { 0, 0 }, \ --S5HS5MvDw4DmbRmb-- --0XhtP95kHFp3KGBe Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Comment: Sean Chittenden iD8DBQE/GwFT3ZnjH7yEs0ERAr2QAJ9R87B/qlix54Rw/7h+eYGIVv8XTgCcCQvx tleVmnzpS0zOIC4pmCCjLwc= =dIz2 -----END PGP SIGNATURE----- --0XhtP95kHFp3KGBe-- From owner-freebsd-performance@FreeBSD.ORG Sun Jul 20 16:45:57 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 BB8F337B401 for ; Sun, 20 Jul 2003 16:45:57 -0700 (PDT) Received: from misery.sdf.com (misery.sdf.com [207.200.153.226]) by mx1.FreeBSD.org (Postfix) with ESMTP id 1DFCE43F3F for ; Sun, 20 Jul 2003 16:45:55 -0700 (PDT) (envelope-from tom@sdf.com) Received: from tom (helo=localhost) by misery.sdf.com with local-esmtp (Exim 2.12 #1) id 19eM7h-00022j-00; Sun, 20 Jul 2003 14:53:41 -0700 Date: Sun, 20 Jul 2003 14:53:33 -0700 (PDT) From: Tom Samplonius To: Sean Chittenden In-Reply-To: <20030720112550.GO24507@perrin.int.nxad.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-performance@freebsd.org cc: Dan Langille 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 23:45:58 -0000 On Sun, 20 Jul 2003, Sean Chittenden 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. > > Public consumption, yes. Production consumption, no. > > 5.1 is still pretty crippled in its current state because it's not > Giant free yet. Once Giant free, however, 5.X will be much closer to > production ready and should be quicker as a result of the fine grained > locking (or so everyone hopes). The simple locking mechanism in 4.x Well, 5.1 is considerably less crippled by Giant than 4.8. Well, "crippled" is not a good description. "Impaired" is better. 5.1 SMP performance is less Giant impaired than 4.8. That's a good thing. > does have some advantages in cases and should be a consistent > performer on UP machines and under most loads.... whether or not 5.x Giant is not present in a UP kernel. At the rate things are going, in a couple of years, most people are going to using SMP, so UP is going to be less of an issue. > over takes 4.X in terms of speed, is the subject of great debate, but > many are optimistic that it will be at some point, just not at the > moment. 5.X, will however (and without much doubt), scale much better > than 4.X on multiple processor machines, though I'm not sure where > that stands at the moment in terms of being completed and should > likely be directed to current@ or questions@ instead of here. -sc Yes, 5.1 is better on multiple CPUs. So if 5.1 works for you, it is going to work faster than 4.8 on SMP. > -- > Sean Chittenden > > Tom From owner-freebsd-performance@FreeBSD.ORG Mon Jul 21 05:06:49 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 5313837B401 for ; Mon, 21 Jul 2003 05:06:49 -0700 (PDT) Received: from dfmm.org (walter.dfmm.org [209.151.233.240]) by mx1.FreeBSD.org (Postfix) with ESMTP id 74CF543FAF for ; Mon, 21 Jul 2003 05:06:48 -0700 (PDT) (envelope-from freebsd-performance@dfmm.org) Received: (qmail 76290 invoked by uid 1000); 21 Jul 2003 12:06:48 -0000 Received: from localhost (sendmail-bs@127.0.0.1) by localhost with SMTP; 21 Jul 2003 12:06:48 -0000 Date: Mon, 21 Jul 2003 05:06:47 -0700 (PDT) From: Jason Stone X-X-Sender: To: , In-Reply-To: Message-ID: <20030721043501.F14379-100000@walter> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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: Mon, 21 Jul 2003 12:06:49 -0000 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Sat, 19 Jul 2003, Tom Samplonius wrote: > Softupdates on, async off. Softupdates is just a better async. postgresql fsync's all its files before returning from a commit in order to ensure durability, right? Does softupdates interfere with the functioning of sync(2)/fsync(2)? On Sun, 20 Jul 2003, Sean Chittenden wrote: > > 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. > > Public consumption, yes. Production consumption, no. I recently decided to take up the issue of migrating towards 5.1 at my company. The way I dealt with it was to publish all of my services to 5.1 boxes and run regression tests. For me, 5.1 provided adequate performance, and since 5.1 is on the branch where all new dev work is going on and 4.x is going to be end-of-lifed in the not-too-distant future, I decided to go with 5.1. Personally, I would only go with 4.8 if testing showed 5.1 to be inadequate for your particular situation with your particular apps, hardware, load, etc. But you can only tell that by testing. -Jason -------------------------------------------------------------------------- Freud himself was a bit of a cold fish, and one cannot avoid the suspicion that he was insufficiently fondled when he was an infant. -- Ashley Montagu -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (FreeBSD) Comment: See https://private.idealab.com/public/jason/jason.gpg iD8DBQE/G9dYswXMWWtptckRAkrNAKD6NjeQW9kFS8J//fxec1qWVUVqaACgzTmM zeAG240i4H11u/yM/FI0E1w= =wFfZ -----END PGP SIGNATURE----- From owner-freebsd-performance@FreeBSD.ORG Mon Jul 21 05:16:17 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 8432237B401 for ; Mon, 21 Jul 2003 05:16:17 -0700 (PDT) Received: from mailout03.sul.t-online.com (mailout03.sul.t-online.com [194.25.134.81]) by mx1.FreeBSD.org (Postfix) with ESMTP id 1ABCA43F75 for ; Mon, 21 Jul 2003 05:16:16 -0700 (PDT) (envelope-from Alexander@Leidinger.net) Received: from fwd10.aul.t-online.de by mailout03.sul.t-online.com with smtp id 19eZa4-0000sI-0D; Mon, 21 Jul 2003 14:15:52 +0200 Received: from Andro-Beta.Leidinger.net (GzzvCyZerefJNz1HeoT4W2fLLD8lmJSbiCA1e31OS9-8Coq9ryuicH@[217.83.17.126]) by fmrl10.sul.t-online.com with esmtp id 19eZZs-0q8gxE0; Mon, 21 Jul 2003 14:15:40 +0200 Received: from Magelan.Leidinger.net (Magelan [192.168.1.1]) h6LCFd9P019942; Mon, 21 Jul 2003 14:15:39 +0200 (CEST) (envelope-from Alexander@Leidinger.net) Received: from Magelan.Leidinger.net (netchild@localhost [127.0.0.1]) by Magelan.Leidinger.net (8.12.9/8.12.9) with SMTP id h6LCFisO000920; Mon, 21 Jul 2003 14:15:44 +0200 (CEST) (envelope-from Alexander@Leidinger.net) Date: Mon, 21 Jul 2003 14:15:44 +0200 From: Alexander Leidinger To: Tom Samplonius Message-Id: <20030721141544.4ef4a0d6.Alexander@Leidinger.net> In-Reply-To: References: <20030720112550.GO24507@perrin.int.nxad.com> X-Mailer: Sylpheed version 0.9.3claws (GTK+ 1.2.10; i386-portbld-freebsd5.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Seen: false X-ID: GzzvCyZerefJNz1HeoT4W2fLLD8lmJSbiCA1e31OS9-8Coq9ryuicH@t-dialin.net 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: Mon, 21 Jul 2003 12:16:17 -0000 On Sun, 20 Jul 2003 14:53:33 -0700 (PDT) Tom Samplonius wrote: > Well, 5.1 is considerably less crippled by Giant than 4.8. Well, > "crippled" is not a good description. "Impaired" is better. 5.1 SMP > performance is less Giant impaired than 4.8. That's a good thing. The critical parts for the desired operations are still covered by the Giant lock (e.g. tcp stack, ata subsystem). > > over takes 4.X in terms of speed, is the subject of great debate, but > > many are optimistic that it will be at some point, just not at the > > moment. 5.X, will however (and without much doubt), scale much better > > than 4.X on multiple processor machines, though I'm not sure where > > that stands at the moment in terms of being completed and should > > likely be directed to current@ or questions@ instead of here. -sc > > Yes, 5.1 is better on multiple CPUs. So if 5.1 works for you, it is > going to work faster than 4.8 on SMP. Did you measured that, and if yes, which set of operations do you have banchmarked? On -current we still have reports that 5.1 is still not as fast as 4.8 (and nobody @FreeBSD.org is surprised, as the target is to first get a stable 5.x line and after that a fast&stable one). Bye, Alexander. -- Failure is not an option. It comes bundled with your Microsoft product. http://www.Leidinger.net Alexander @ Leidinger.net GPG fingerprint = C518 BC70 E67F 143F BE91 3365 79E2 9C60 B006 3FE7 From owner-freebsd-performance@FreeBSD.ORG Mon Jul 21 07:37:49 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 51A8A37B401; Mon, 21 Jul 2003 07:37:49 -0700 (PDT) Received: from probsd.org (rrcs-midsouth-24-172-108-90.biz.rr.com [24.172.108.90]) by mx1.FreeBSD.org (Postfix) with ESMTP id 3D4DA43F75; Mon, 21 Jul 2003 07:37:48 -0700 (PDT) (envelope-from xr@sh3lls.net) Received: from probsd.org (probsd.org [192.168.1.4]) by probsd.org (Postfix) with SMTP id 81AEB465AD; Mon, 21 Jul 2003 10:38:10 -0400 (EDT) Received: from 192.168.1.4 (SquirrelMail authenticated user ms) by probsd.org with HTTP; Mon, 21 Jul 2003 10:38:10 -0400 (EDT) Message-ID: <3792.192.168.1.4.1058798290.squirrel@probsd.org> Date: Mon, 21 Jul 2003 10:38:10 -0400 (EDT) From: "XR" To: freebsd-performance@freebsd.org User-Agent: SquirrelMail/1.4.0 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal cc: freebsd-questions@freebsd.org Subject: network performance testing 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: Mon, 21 Jul 2003 14:37:49 -0000 I'm considering purchasing hosting from a provider that says I will have a dedicated 10 Mbit connection with burst to 20 Mbit on a FreeBSD box I rebuilt to suit my needs.. The network is Multihomed with three other providers. However, I'm a skeptic. I went ahead and purchased for 1 month and now want to see if I am actually getting 10 Mbit uplink to the server. I'm looking at iperf to do this, but I also know taking ping/traceroute results from remote host to the server can calculate this, but I dont know the math. Would anyone using iperf guide me towards an example, or show me the math using ping/traceroute? Or, suggest running any other test? Thx XR From owner-freebsd-performance@FreeBSD.ORG Mon Jul 21 12:26:46 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 A7AAD37B401; Mon, 21 Jul 2003 12:26:46 -0700 (PDT) Received: from perrin.int.nxad.com (internal.ext.nxad.com [69.1.70.251]) by mx1.FreeBSD.org (Postfix) with ESMTP id 313F743FAF; Mon, 21 Jul 2003 12:26:46 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 2B91A210EF; Mon, 21 Jul 2003 12:26:45 -0700 (PDT) Date: Mon, 21 Jul 2003 12:26:45 -0700 From: Sean Chittenden To: Jason Stone Message-ID: <20030721192645.GB61464@perrin.int.nxad.com> References: <20030721043501.F14379-100000@walter> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030721043501.F14379-100000@walter> 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: Mon, 21 Jul 2003 19:26:47 -0000 > > Softupdates on, async off. Softupdates is just a better async. > > postgresql fsync's all its files before returning from a commit in > order to ensure durability, right? Does softupdates interfere with > the functioning of sync(2)/fsync(2)? It can, yes, but that's the risk of soft updates. From tuning(7): Softupdates drastically improves meta-data performance, mainly file creation and deletion. We recommend enabling softupdates on most file systems; however, there are two limitations to softupdates that you should be aware of when determining whether to use it on a file system. First, softupdates guarantees file system consistency in the case of a crash but could very easily be several seconds (even a minute!) behind on pending write to the physical disk. If you crash you may lose more work than otherwise. Secondly, softupdates delays the freeing of file system blocks. If you have a file system (such as the root file system) which is close to full, doing a major update of it, e.g. ``make installworld'', can run it out of space and cause the update to fail. For this reason, softupdates will not be enabled on the root file system during a typical install. > > > 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. > > > > Public consumption, yes. Production consumption, no. > > I recently decided to take up the issue of migrating towards 5.1 at > my company. The way I dealt with it was to publish all of my > services to 5.1 boxes and run regression tests. For me, 5.1 > provided adequate performance, and since 5.1 is on the branch where > all new dev work is going on and 4.x is going to be end-of-lifed in > the not-too-distant future, I decided to go with 5.1. > > Personally, I would only go with 4.8 if testing showed 5.1 to be > inadequate for your particular situation with your particular apps, > hardware, load, etc. But you can only tell that by testing. Glad to hear that 5.1 is getting used in the work place. Were there any places where your testing showed huge gaps in performance? -sc -- Sean Chittenden From owner-freebsd-performance@FreeBSD.ORG Mon Jul 21 12:26:50 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 E93AE37B401 for ; Mon, 21 Jul 2003 12:26:49 -0700 (PDT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by mx1.FreeBSD.org (Postfix) with SMTP id 7FADB43F85 for ; Mon, 21 Jul 2003 12:26:48 -0700 (PDT) (envelope-from decibel@decibel.org) Received: (qmail 77454 invoked by uid 1001); 21 Jul 2003 19:26:43 -0000 Date: Mon, 21 Jul 2003 14:26:43 -0500 From: "Jim C. Nasby" To: Jason Stone Message-ID: <20030721192643.GD55392@nasby.net> References: <20030721043501.F14379-100000@walter> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030721043501.F14379-100000@walter> User-Agent: Mutt/1.4.1i X-Operating-System: FreeBSD 4.8-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net 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: Mon, 21 Jul 2003 19:26:50 -0000 On Mon, Jul 21, 2003 at 05:06:47AM -0700, Jason Stone wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > On Sat, 19 Jul 2003, Tom Samplonius wrote: > > Softupdates on, async off. Softupdates is just a better async. > > postgresql fsync's all its files before returning from a commit in order > to ensure durability, right? Does softupdates interfere with the > functioning of sync(2)/fsync(2)? No, afaik it only fsync's the write-ahead-logs. -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" From owner-freebsd-performance@FreeBSD.ORG Mon Jul 21 12:45:21 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 5ED3037B401 for ; Mon, 21 Jul 2003 12:45:21 -0700 (PDT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by mx1.FreeBSD.org (Postfix) with SMTP id 14F0F43F93 for ; Mon, 21 Jul 2003 12:45:20 -0700 (PDT) (envelope-from decibel@decibel.org) Received: (qmail 78470 invoked by uid 1001); 21 Jul 2003 19:45:19 -0000 Date: Mon, 21 Jul 2003 14:45:19 -0500 From: "Jim C. Nasby" To: Sean Chittenden Message-ID: <20030721194519.GE55392@nasby.net> References: <20030721043501.F14379-100000@walter> <20030721192645.GB61464@perrin.int.nxad.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030721192645.GB61464@perrin.int.nxad.com> User-Agent: Mutt/1.4.1i X-Operating-System: FreeBSD 4.8-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Jason Stone 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: Mon, 21 Jul 2003 19:45:21 -0000 On Mon, Jul 21, 2003 at 12:26:45PM -0700, Sean Chittenden wrote: > > > Softupdates on, async off. Softupdates is just a better async. > > > > postgresql fsync's all its files before returning from a commit in > > order to ensure durability, right? Does softupdates interfere with > > the functioning of sync(2)/fsync(2)? > > It can, yes, but that's the risk of soft updates. From tuning(7): > > Softupdates drastically improves meta-data performance, mainly > file creation and deletion. We recommend enabling softupdates on > most file systems; however, there are two limitations to > softupdates that you should be aware of when determining whether > to use it on a file system. First, softupdates guarantees file > system consistency in the case of a crash but could very easily > be several seconds (even a minute!) behind on pending write to > the physical disk. If you crash you may lose more work than > otherwise. Secondly, softupdates delays the freeing of file > system blocks. If you have a file system (such as the root file > system) which is close to full, doing a major update of it, > e.g. ``make installworld'', can run it out of space and cause the > update to fail. For this reason, softupdates will not be enabled > on the root file system during a typical install. So are you saying that softupdates on whatever partition contains the database transaction logs is dangerous? Luckily, the metadata for these files is changed very infrequently (normally), so this shouldn't prove to be much of a performance impact. -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" From owner-freebsd-performance@FreeBSD.ORG Mon Jul 21 13:16:49 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 322B837B401; Mon, 21 Jul 2003 13:16:49 -0700 (PDT) Received: from perrin.int.nxad.com (internal.ext.nxad.com [69.1.70.251]) by mx1.FreeBSD.org (Postfix) with ESMTP id 7439543F85; Mon, 21 Jul 2003 13:16:46 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 8EB99210FF; Mon, 21 Jul 2003 13:16:45 -0700 (PDT) Date: Mon, 21 Jul 2003 13:16:45 -0700 From: Sean Chittenden To: "Jim C. Nasby" Message-ID: <20030721201645.GD61464@perrin.int.nxad.com> References: <20030721043501.F14379-100000@walter> <20030721192645.GB61464@perrin.int.nxad.com> <20030721194519.GE55392@nasby.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030721194519.GE55392@nasby.net> 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 cc: Jason Stone 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: Mon, 21 Jul 2003 20:16:49 -0000 > > > > Softupdates on, async off. Softupdates is just a better > > > > async. > > > > > > postgresql fsync's all its files before returning from a commit > > > in order to ensure durability, right? Does softupdates > > > interfere with the functioning of sync(2)/fsync(2)? > > > > It can, yes, but that's the risk of soft updates. From tuning(7): > > > > Softupdates drastically improves meta-data performance, > > mainly file creation and deletion. We recommend enabling > > softupdates on most file systems; however, there are two > > limitations to softupdates that you should be aware of when > > determining whether to use it on a file system. First, > > softupdates guarantees file system consistency in the case of > > a crash but could very easily be several seconds (even a > > minute!) behind on pending write to the physical disk. If > > you crash you may lose more work than otherwise. Secondly, > > softupdates delays the freeing of file system blocks. If you > > have a file system (such as the root file system) which is > > close to full, doing a major update of it, e.g. ``make > > installworld'', can run it out of space and cause the update > > to fail. For this reason, softupdates will not be enabled on > > the root file system during a typical install. > > So are you saying that softupdates on whatever partition contains > the database transaction logs is dangerous? Well, think about it. What happens when you pull the plug while the OS is sitting on a pile of data that it's waiting to flush to disk? The problem isn't so much with the WAL logs, so much as it is with the actual files that contain the databases/table's data. Any kind of volatle backed buffer increases the danger of data loss. Good solution for these concerns? Get a UPS. > Luckily, the metadata for these files is changed very infrequently > (normally), so this shouldn't prove to be much of a performance > impact. Correct. -sc -- Sean Chittenden From owner-freebsd-performance@FreeBSD.ORG Mon Jul 21 13:24:02 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 74AD037B401 for ; Mon, 21 Jul 2003 13:24:02 -0700 (PDT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by mx1.FreeBSD.org (Postfix) with SMTP id 1874843F85 for ; Mon, 21 Jul 2003 13:24:01 -0700 (PDT) (envelope-from decibel@decibel.org) Received: (qmail 79827 invoked by uid 1001); 21 Jul 2003 20:24:00 -0000 Date: Mon, 21 Jul 2003 15:24:00 -0500 From: "Jim C. Nasby" To: Sean Chittenden Message-ID: <20030721202400.GG55392@nasby.net> References: <20030721043501.F14379-100000@walter> <20030721192645.GB61464@perrin.int.nxad.com> <20030721194519.GE55392@nasby.net> <20030721201645.GD61464@perrin.int.nxad.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030721201645.GD61464@perrin.int.nxad.com> User-Agent: Mutt/1.4.1i X-Operating-System: FreeBSD 4.8-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Jason Stone 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: Mon, 21 Jul 2003 20:24:02 -0000 On Mon, Jul 21, 2003 at 01:16:45PM -0700, Sean Chittenden wrote: > > > > > Softupdates on, async off. Softupdates is just a better > > > > > async. > > > > > > > > postgresql fsync's all its files before returning from a commit > > > > in order to ensure durability, right? Does softupdates > > > > interfere with the functioning of sync(2)/fsync(2)? > > > > > > It can, yes, but that's the risk of soft updates. From tuning(7): > > > > > > Softupdates drastically improves meta-data performance, > > > mainly file creation and deletion. We recommend enabling > > > softupdates on most file systems; however, there are two > > > limitations to softupdates that you should be aware of when > > > determining whether to use it on a file system. First, > > > softupdates guarantees file system consistency in the case of > > > a crash but could very easily be several seconds (even a > > > minute!) behind on pending write to the physical disk. If > > > you crash you may lose more work than otherwise. Secondly, > > > softupdates delays the freeing of file system blocks. If you > > > have a file system (such as the root file system) which is > > > close to full, doing a major update of it, e.g. ``make > > > installworld'', can run it out of space and cause the update > > > to fail. For this reason, softupdates will not be enabled on > > > the root file system during a typical install. > > > > So are you saying that softupdates on whatever partition contains > > the database transaction logs is dangerous? > > Well, think about it. What happens when you pull the plug while the > OS is sitting on a pile of data that it's waiting to flush to disk? > The problem isn't so much with the WAL logs, so much as it is with the > actual files that contain the databases/table's data. Any kind of > volatle backed buffer increases the danger of data loss. If you pull the plug you're going to lose in-flight transactions no matter what (or at least you better). Once you commit, it should hit disk, at least in the WAL. Shouldn't sync/fsync ensure this even with softupdates? Of course the *real* issue is ensuring that no matter what, the WAL hits the disk before the main table data does, so that you can do proper recovery (though I'm not as clear on how this works with MVCC). If the issue is only a matter of delayed writes across the board, I don't see that it should really matter... it's just as if the plug got pulled a little earlier. -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" From owner-freebsd-performance@FreeBSD.ORG Mon Jul 21 20:35:44 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 B58CC37B401 for ; Mon, 21 Jul 2003 20:35:44 -0700 (PDT) Received: from dfmm.org (walter.dfmm.org [209.151.233.240]) by mx1.FreeBSD.org (Postfix) with ESMTP id D727643F3F for ; Mon, 21 Jul 2003 20:35:43 -0700 (PDT) (envelope-from jason@shalott.net) Received: (qmail 22353 invoked by uid 1000); 22 Jul 2003 03:35:43 -0000 Received: from localhost (sendmail-bs@127.0.0.1) by localhost with SMTP; 22 Jul 2003 03:35:43 -0000 Date: Mon, 21 Jul 2003 20:35:43 -0700 (PDT) From: Jason Stone X-X-Sender: To: , In-Reply-To: <20030721192645.GB61464@perrin.int.nxad.com> Message-ID: <20030721202338.C14379-100000@walter> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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: Tue, 22 Jul 2003 03:35:45 -0000 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > > > Softupdates on, async off. Softupdates is just a better async. > > > > postgresql fsync's all its files before returning from a commit in > > order to ensure durability, right? Does softupdates interfere with > > the functioning of sync(2)/fsync(2)? > > It can, yes, but that's the risk of soft updates. From tuning(7): Yes, I know - tuning(7) warns that writes may be delayed. But (data) writes are always delayed some, and fsync(2) doesn't say anything about a situation where fsync() would return success and the data would not actually be written out to disk. I feel like this is an extremely important point. If softupdates changes the semantics of sync(2)/fsync(2), then it absolutely has to be off for a postgresql server because postgresql counts on fsync in order to make its durability guarantees. Additionally, the manpages for sync/fsync should be updated, as I'm sure there are lots of other consumers of those calls, and, "these calls cause your dirty buffers to be flushed to disk... except if there's a global filesystem flag set, in which case they do absolutely nothing," is a pretty big omission, especially since softupdates is the default these days. -Jason -------------------------------------------------------------------------- Freud himself was a bit of a cold fish, and one cannot avoid the suspicion that he was insufficiently fondled when he was an infant. -- Ashley Montagu -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (FreeBSD) Comment: See https://private.idealab.com/public/jason/jason.gpg iD8DBQE/HLEPswXMWWtptckRArsqAKC17wHyGmPKM5PJDbBFZxqZCWmtQQCfWZ5v bKgHPa0QH7afT49aBZ7q8SA= =WhuE -----END PGP SIGNATURE----- From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 03:12:25 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 805C037B401 for ; Tue, 22 Jul 2003 03:12:25 -0700 (PDT) Received: from mailout10.sul.t-online.com (mailout10.sul.t-online.com [194.25.134.21]) by mx1.FreeBSD.org (Postfix) with ESMTP id 7204D43FA3 for ; Tue, 22 Jul 2003 03:12:24 -0700 (PDT) (envelope-from Alexander@Leidinger.net) Received: from fwd00.aul.t-online.de by mailout10.sul.t-online.com with smtp id 19eu87-0004m2-06; Tue, 22 Jul 2003 12:12:23 +0200 Received: from Andro-Beta.Leidinger.net (rCLbumZ1gevg1eIzFEXQ7CTmV4DRBor-h6PqCp51l5NTzkDdT88MQp@[217.229.208.90]) by fmrl00.sul.t-online.com with esmtp id 19eu7x-1BvxHk0; Tue, 22 Jul 2003 12:12:13 +0200 Received: from Magelan.Leidinger.net (Magelan [192.168.1.1]) h6MACB9P023963 for ; Tue, 22 Jul 2003 12:12:11 +0200 (CEST) (envelope-from Alexander@Leidinger.net) Received: from Magelan.Leidinger.net (netchild@localhost [127.0.0.1]) by Magelan.Leidinger.net (8.12.9/8.12.9) with SMTP id h6MACBo7000902 for ; Tue, 22 Jul 2003 12:12:11 +0200 (CEST) (envelope-from Alexander@Leidinger.net) Date: Tue, 22 Jul 2003 12:12:11 +0200 From: Alexander Leidinger To: freebsd-performance@freebsd.org Message-Id: <20030722121211.27684f49.Alexander@Leidinger.net> In-Reply-To: <20030721201645.GD61464@perrin.int.nxad.com> References: <20030721043501.F14379-100000@walter> <20030721192645.GB61464@perrin.int.nxad.com> <20030721194519.GE55392@nasby.net> <20030721201645.GD61464@perrin.int.nxad.com> X-Mailer: Sylpheed version 0.9.3claws (GTK+ 1.2.10; i386-portbld-freebsd5.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Seen: false X-ID: rCLbumZ1gevg1eIzFEXQ7CTmV4DRBor-h6PqCp51l5NTzkDdT88MQp@t-dialin.net 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: Tue, 22 Jul 2003 10:12:25 -0000 On Mon, 21 Jul 2003 13:16:45 -0700 Sean Chittenden wrote: > Well, think about it. What happens when you pull the plug while the > OS is sitting on a pile of data that it's waiting to flush to disk? > The problem isn't so much with the WAL logs, so much as it is with the > actual files that contain the databases/table's data. Any kind of > volatle backed buffer increases the danger of data loss. > > Good solution for these concerns? Get a UPS. Thats not only a good solution, it's the only solution for mainstream PC hardware... Bye, Alexander. -- Where do you think you're going today? http://www.Leidinger.net Alexander @ Leidinger.net GPG fingerprint = C518 BC70 E67F 143F BE91 3365 79E2 9C60 B006 3FE7 From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 03:29:47 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 164DD37B401 for ; Tue, 22 Jul 2003 03:29:47 -0700 (PDT) Received: from mailout06.sul.t-online.com (mailout06.sul.t-online.com [194.25.134.19]) by mx1.FreeBSD.org (Postfix) with ESMTP id 8E26E43F93 for ; Tue, 22 Jul 2003 03:29:45 -0700 (PDT) (envelope-from Alexander@Leidinger.net) Received: from fwd05.aul.t-online.de by mailout06.sul.t-online.com with smtp id 19euOu-000683-07; Tue, 22 Jul 2003 12:29:44 +0200 Received: from Andro-Beta.Leidinger.net (ZefmecZv8e9VReZGzDwXmfpRaMEnWjyBZTdULBJcAWviZ3FkcTpUk7@[217.229.208.90]) by fmrl05.sul.t-online.com with esmtp id 19euOf-1aPJ9k0; Tue, 22 Jul 2003 12:29:29 +0200 Received: from Magelan.Leidinger.net (Magelan [192.168.1.1]) h6MATS9P023996 for ; Tue, 22 Jul 2003 12:29:28 +0200 (CEST) (envelope-from Alexander@Leidinger.net) Received: from Magelan.Leidinger.net (netchild@localhost [127.0.0.1]) by Magelan.Leidinger.net (8.12.9/8.12.9) with SMTP id h6MATSo7000935 for ; Tue, 22 Jul 2003 12:29:28 +0200 (CEST) (envelope-from Alexander@Leidinger.net) Date: Tue, 22 Jul 2003 12:29:27 +0200 From: Alexander Leidinger To: freebsd-performance@freebsd.org Message-Id: <20030722122927.6ccfe953.Alexander@Leidinger.net> In-Reply-To: <20030721202400.GG55392@nasby.net> References: <20030721043501.F14379-100000@walter> <20030721192645.GB61464@perrin.int.nxad.com> <20030721194519.GE55392@nasby.net> <20030721201645.GD61464@perrin.int.nxad.com> <20030721202400.GG55392@nasby.net> X-Mailer: Sylpheed version 0.9.3claws (GTK+ 1.2.10; i386-portbld-freebsd5.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Seen: false X-ID: ZefmecZv8e9VReZGzDwXmfpRaMEnWjyBZTdULBJcAWviZ3FkcTpUk7@t-dialin.net 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: Tue, 22 Jul 2003 10:29:47 -0000 On Mon, 21 Jul 2003 15:24:00 -0500 "Jim C. Nasby" wrote: > Of course the *real* issue is ensuring that no matter what, the WAL hits > the disk before the main table data does, so that you can do proper > recovery (though I'm not as clear on how this works with MVCC). If the > issue is only a matter of delayed writes across the board, I don't see > that it should really matter... it's just as if the plug got pulled a > little earlier. SoftUpdates affects metadata updates. Simplified explanation: if you don't use SU and delete more than one file in a directory, the changed directory information gets written to disk after every deleted file, with SU enabled it just writes the directory information after the last deleted file (actually SU doesn't know what the last file is, so it uses some heuristics, but for this example you can ignore this fact) to disk, every change in between will happen in memory only. The same applies to the metadata of files (access time, modification time, ...). So if you replace a file with SU enabled you may get either the old one or the new one, but no inconsistent state in between (this is possible without SU). Without SU the window of the race condition is smaller, but it's still there. I'm not aware of a statistical measurement of the difference, but I think the probability of "the plug gets pulled in the wrong moment" is the same (Murphy's law applied to this: the probability is "1" ;-) ). Bye, Alexander. -- The best things in life are free, but the expensive ones are still worth a look. http://www.Leidinger.net Alexander @ Leidinger.net GPG fingerprint = C518 BC70 E67F 143F BE91 3365 79E2 9C60 B006 3FE7 From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 08:37:49 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 D187A37B40D; Tue, 22 Jul 2003 08:37:49 -0700 (PDT) Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4330C43F85; Tue, 22 Jul 2003 08:37:49 -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 EE7203D29; Tue, 22 Jul 2003 11:37:44 -0400 (EDT) From: "Dan Langille" To: Jason Stone Date: Tue, 22 Jul 2003 11:37:44 -0400 MIME-Version: 1.0 Message-ID: <3F1D2208.2124.52B03E6A@localhost> Priority: normal References: <20030721192645.GB61464@perrin.int.nxad.com> In-reply-to: <20030721202338.C14379-100000@walter> X-mailer: Pegasus Mail for Windows (v4.02a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body 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: Tue, 22 Jul 2003 15:37:50 -0000 On 21 Jul 2003 at 20:35, Jason Stone wrote: > I feel like this is an extremely important point. If softupdates changes > the semantics of sync(2)/fsync(2), then it absolutely has to be off for a > postgresql server because postgresql counts on fsync in order to make its > durability guarantees. If this means all FreeBSD-PostgreSQL users need to change their setup, we need to do something ASAP. I'd first recommend getting the confirmation from the PostgreSQL team, then adjusting the PostgreSQL documenation and the FreeBSD port. Does anyone feel this is urgent enough that they'll do something about it? -- Dan Langille : http://www.langille.org/ From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 08:59:03 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 4165237B401; Tue, 22 Jul 2003 08:59:03 -0700 (PDT) Received: from godel.mtl.distributel.net (nat.MTL.distributel.NET [66.38.181.24]) by mx1.FreeBSD.org (Postfix) with ESMTP id 8B17C43F75; Tue, 22 Jul 2003 08:59:02 -0700 (PDT) (envelope-from bmilekic@technokratis.com) Received: from godel.mtl.distributel.net (localhost [127.0.0.1]) h6MC37EH059476; Tue, 22 Jul 2003 12:03:07 GMT (envelope-from bmilekic@technokratis.com) Received: (from bmilekic@localhost) by godel.mtl.distributel.net (8.12.9/8.12.9/Submit) id h6MC37lw059475; Tue, 22 Jul 2003 12:03:07 GMT X-Authentication-Warning: godel.mtl.distributel.net: bmilekic set sender to bmilekic@technokratis.com using -f Date: Tue, 22 Jul 2003 12:03:07 +0000 From: Bosko Milekic To: Dan Langille Message-ID: <20030722120307.GA59423@technokratis.com> References: <20030721192645.GB61464@perrin.int.nxad.com> <3F1D2208.2124.52B03E6A@localhost> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3F1D2208.2124.52B03E6A@localhost> User-Agent: Mutt/1.4.1i cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Jason Stone 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: Tue, 22 Jul 2003 15:59:03 -0000 On Tue, Jul 22, 2003 at 11:37:44AM -0400, Dan Langille wrote: > On 21 Jul 2003 at 20:35, Jason Stone wrote: > > > I feel like this is an extremely important point. If softupdates changes > > the semantics of sync(2)/fsync(2), then it absolutely has to be off for a > > postgresql server because postgresql counts on fsync in order to make its > > durability guarantees. > > If this means all FreeBSD-PostgreSQL users need to change their > setup, we need to do something ASAP. I'd first recommend getting the > confirmation from the PostgreSQL team, then adjusting the PostgreSQL > documenation and the FreeBSD port. > > Does anyone feel this is urgent enough that they'll do something > about it? > -- > Dan Langille : http://www.langille.org/ You sound like you know exactly what's involved. :-) -- Bosko Milekic * bmilekic@technokratis.com * bmilekic@FreeBSD.org TECHNOkRATIS Consulting Services * http://www.technokratis.com/ From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 09:23:29 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 9DC8537B401; Tue, 22 Jul 2003 09:23:29 -0700 (PDT) Received: from misery.sdf.com (misery.sdf.com [207.200.153.226]) by mx1.FreeBSD.org (Postfix) with ESMTP id CA15643FAF; Tue, 22 Jul 2003 09:23:27 -0700 (PDT) (envelope-from tom@sdf.com) Received: from tom (helo=localhost) by misery.sdf.com with local-esmtp (Exim 2.12 #1) id 19eyAn-0005Kv-00; Tue, 22 Jul 2003 07:31:25 -0700 Date: Tue, 22 Jul 2003 07:31:23 -0700 (PDT) From: Tom Samplonius To: Sean Chittenden In-Reply-To: <20030721192645.GB61464@perrin.int.nxad.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Jason Stone 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: Tue, 22 Jul 2003 16:23:29 -0000 On Mon, 21 Jul 2003, Sean Chittenden wrote: > > > Softupdates on, async off. Softupdates is just a better async. > > > > postgresql fsync's all its files before returning from a commit in > > order to ensure durability, right? Does softupdates interfere with > > the functioning of sync(2)/fsync(2)? > > It can, yes, but that's the risk of soft updates. From tuning(7): No it can't. Regardless of how softupdates re-orders filesystem activity, fsync flushes all dirty buffers. Tom From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 09:31:58 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 02E1437B401; Tue, 22 Jul 2003 09:31:58 -0700 (PDT) Received: from mta4.rcsntx.swbell.net (mta4.rcsntx.swbell.net [151.164.30.28]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4FBD043F3F; Tue, 22 Jul 2003 09:31:57 -0700 (PDT) (envelope-from mbsd@pacbell.net) Received: from atlas (adsl-64-160-45-246.dsl.snfc21.pacbell.net [64.160.45.246])h6MGVqvi006749; Tue, 22 Jul 2003 11:31:52 -0500 (CDT) Date: Tue, 22 Jul 2003 09:31:51 -0700 (PDT) From: =?ISO-8859-1?Q?Mikko_Ty=F6l=E4j=E4rvi?= X-X-Sender: mikko@atlas.home To: Dan Langille In-Reply-To: <3F1D2208.2124.52B03E6A@localhost> Message-ID: <20030722092917.L18121@atlas.home> References: <20030721192645.GB61464@perrin.int.nxad.com> <3F1D2208.2124.52B03E6A@localhost> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Jason Stone 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: Tue, 22 Jul 2003 16:31:58 -0000 On Tue, 22 Jul 2003, Dan Langille wrote: > On 21 Jul 2003 at 20:35, Jason Stone wrote: > > > I feel like this is an extremely important point. If softupdates changes > > the semantics of sync(2)/fsync(2), then it absolutely has to be off for a > > postgresql server because postgresql counts on fsync in order to make its > > durability guarantees. > > If this means all FreeBSD-PostgreSQL users need to change their > setup, we need to do something ASAP. I'd first recommend getting the > confirmation from the PostgreSQL team, then adjusting the PostgreSQL > documenation and the FreeBSD port. > > Does anyone feel this is urgent enough that they'll do something > about it? I think you can relax. Check the mailing lists, or google a bit and you'll find similar discussions. For example: In short: fsync() works with soft-updates. Too many things would break otherwise. $.02, /Mikko From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 09:49:12 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 5B57637B40B for ; Tue, 22 Jul 2003 09:49:12 -0700 (PDT) Received: from svaha.com (svaha.com [64.46.156.67]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5E93043F75 for ; Tue, 22 Jul 2003 09:49:11 -0700 (PDT) (envelope-from meconlen@obfuscated.net) Received: from obfuscated.net (internal.neutelligent.com [64.156.25.4]) (AUTH: LOGIN meconlen, TLS: TLSv1/SSLv3,256bits,AES256-SHA) by svaha.com with esmtp; Tue, 22 Jul 2003 12:49:10 -0400 Message-ID: <3F1D6B04.4010704@obfuscated.net> Date: Tue, 22 Jul 2003 12:49:08 -0400 From: Michael Conlen User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: freebsd-performance@freebsd.org Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: sbwait state for loaded Apache server 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: Tue, 22 Jul 2003 16:49:12 -0000 I'm working with an Apache webserver running 1400 apache processes and the system pusing somewhere in the area of 50-60Mbit/sec sustained. The system seems to top out around 60Mbit/sec and I see some minor degradation of server response times. The server response times are generally very very stable otherwise. Most of the apache processes are in the sbwait state. I've got 4Gig of memory, so I can play with some of the values (nmbclusters has been turned up and I never see delayed or dropped requests for mbufs). I don't see in my old Design & Implementation of 4.4BSD (Red Book?) much about the state, and I don't a copy of TCP/IP Illustrated 2 handy these days, but if memory serves sbwait is waiting on a socket buffer resource. My guess is that these are processes waiting on the send buffer to drain. $ netstat -an | egrep '[0-9] 3[0-9]{4}' | wc -l 297 seems to indicate that I've got a lot of processes waiting to drain. Looking at the actual output it shows most of these are ESTABLISHED. So my thought is by increasing the send queue size I could reduce this. I've got a pretty good idea on the size of the files being sent and my thoughts were to increase the send-q size to where Apache can write() the file and go to the keep alive state quickly instead of waiting. So the questions are Would this affect actual network performance Would this reduce load on the machine (a handy thing to do, but secondary) given c = number of connections and q = queue adjustment and s = size of mbuf do I just need to make sure I have (c*q)/s buffers available, and any fudge? How do I know when I need to increase the overall system buffer size beyond 200 MB? -- Michael Conlen From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 11:05:56 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 EA27E37B401 for ; Tue, 22 Jul 2003 11:05:56 -0700 (PDT) Received: from 156.Red-80-35-166.pooles.rima-tde.net (156.Red-80-35-166.pooles.rima-tde.net [80.35.166.156]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2D37543F85 for ; Tue, 22 Jul 2003 11:05:56 -0700 (PDT) (envelope-from sjmudd@pobox.com) Received: by unicorn.wl0.org (Postfix, from userid 1001) id 485113158; Tue, 22 Jul 2003 20:05:53 +0200 (CEST) To: freebsd-performance@freebsd.org References: <20030721043501.F14379-100000@walter> <20030721192643.GD55392@nasby.net> From: Simon J Mudd Date: 22 Jul 2003 20:05:53 +0200 In-Reply-To: <20030721192643.GD55392@nasby.net> Message-ID: <86znj6l9mm.fsf@unicorn.wl0.org> Lines: 29 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii 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: Tue, 22 Jul 2003 18:05:57 -0000 jim@nasby.net ("Jim C. Nasby") writes: > > On Sat, 19 Jul 2003, Tom Samplonius wrote: > > > Softupdates on, async off. Softupdates is just a better async. > > > > postgresql fsync's all its files before returning from a commit in order > > to ensure durability, right? Does softupdates interfere with the > > functioning of sync(2)/fsync(2)? > > No, afaik it only fsync's the write-ahead-logs. Someone else mentioned Qmail. I use Postfix and I know the author relies on fsync semantics before confirming the acceptance of email. I'm sure Qmail (and sendmail, ...) does the same. They should do - the RFC requires this (that mail can't be lost once it is accepted for delivery). Therefore I think we are confusing 2 different issues. The effect of soft-updates (which tries to reorder writes to ensure FS recover is easier) and sync/fsync which only returns when the FS or file have been flushed to disk. I would guess that PostGreSQL uses fsync() and softupdates do not effect the fsync() semantics. Or am I missing something? Simon From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 11:46:12 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 C82EF37B401 for ; Tue, 22 Jul 2003 11:46:12 -0700 (PDT) Received: from clavin.cluepon.com (clavin.cluepon.com [64.154.215.6]) by mx1.FreeBSD.org (Postfix) with ESMTP id 35E4C43F85 for ; Tue, 22 Jul 2003 11:46:10 -0700 (PDT) (envelope-from lamont@cluepon.com) Received: from lamont by clavin.cluepon.com with local (Exim 3.03 #1) id 19f29H-000NHV-00; Tue, 22 Jul 2003 11:46:07 -0700 Date: Tue, 22 Jul 2003 11:46:07 -0700 From: Lamont Lucas To: Michael Conlen Message-ID: <20030722184607.GI499@clavin.cluepon.com> References: <3F1D6B04.4010704@obfuscated.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3F1D6B04.4010704@obfuscated.net> Organization: Cluepon Consulting, Inc. User-Agent: Mutt/1.5.4i cc: freebsd-performance@freebsd.org Subject: Re: sbwait state for loaded Apache server 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: Tue, 22 Jul 2003 18:46:13 -0000 On Tue, Jul 22, 2003 at 12:49:08PM -0400, Michael Conlen wrote: > I'm working with an Apache webserver running 1400 apache processes and > the system pusing somewhere in the area of 50-60Mbit/sec sustained. The > system seems to top out around 60Mbit/sec and I see some minor > degradation of server response times. I did a lan only test of an apache install on a stock 4.8-stable box recently. With no extra tuning being done to the network stack or similar, I maxed out at about 60Mbit/sec originally, but after recompiling the kernel with: options ACCEPT_FILTER_DATA options ACCEPT_FILTER_HTTP options NMBCLUSTERS=32768 and recompiling apache with HARD_SERVER_LIMIT=2048, SO_ACCEPTFILTER and BUFFERED_LOGS, I pushed that to 83Mbit/s and an average of 1310 requests per second (using our actual website data and replaying a day's worth of logs in fast forward) (oh, and apache 1.3.27) The machine in question is a dual cpu 2.4ghz SuperMicro 6013P-8 with 2 gigs of ram. I turned hyperthreading off earlier but when I get around to doing real tuning in earnest I'll experiment with it off and on. Oh, and when you make /usr/ports/www/apache13, set the env variable WITH_APACHE_PERF_TUNING and a higher hard server limit. So my highly unscientific advice to you is to check out the ACCEPT_FILTER options and recompile apache to take advantage of them. -- - Lamont "I am not an atomic playboy." From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 11:55:28 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 8FE3737B401; Tue, 22 Jul 2003 11:55:28 -0700 (PDT) Received: from perrin.int.nxad.com (internal.ext.nxad.com [69.1.70.251]) by mx1.FreeBSD.org (Postfix) with ESMTP id 0E01943FA3; Tue, 22 Jul 2003 11:55:28 -0700 (PDT) (envelope-from sean@nxad.com) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 81EAC21216; Tue, 22 Jul 2003 11:55:27 -0700 (PDT) Date: Tue, 22 Jul 2003 11:55:27 -0700 From: Sean Chittenden To: Christopher Weimann Message-ID: <20030722185527.GU64860@perrin.int.nxad.com> References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> <20030720164237.GC55392@nasby.net> <20030720205339.GP24507@perrin.int.nxad.com> <20030722143449.B10666@smtp.k12us.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030722143449.B10666@smtp.k12us.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 cc: Paul Pathiakis 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: Tue, 22 Jul 2003 18:55:28 -0000 > > You might want to get in the habit of using sysctl for getting > > that kind of info. `sysctl -d vfs.bufspace` > > I'm confused. Everything I have ever read about FreeBSD indicates > that it uses all free ram for the disk cache. >From loader(8): kern.maxbcache Limits the amount of KVM reserved for use by the buffer cache, specified in bytes. The default maximum is 200MB. This parameter is used to prevent the buffer cache from eating too much KVM in large-memory machine configurations. Only mess around with this parameter if you need to greatly extend the KVM reservation for other resources such as the swap zone or NMBCLUSTERS. Note that the NBUF parameter will override this limit. Modifies VM_BCACHE_SIZE_MAX. -sc -- Sean Chittenden From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 12:19: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 CDEC037B401; Tue, 22 Jul 2003 12:19:41 -0700 (PDT) Received: from svaha.com (svaha.com [64.46.156.67]) by mx1.FreeBSD.org (Postfix) with ESMTP id B5F6143F3F; Tue, 22 Jul 2003 12:19:40 -0700 (PDT) (envelope-from meconlen@obfuscated.net) Received: from obfuscated.net (internal.neutelligent.com [64.156.25.4]) (AUTH: LOGIN meconlen, TLS: TLSv1/SSLv3,256bits,AES256-SHA) by svaha.com with esmtp; Tue, 22 Jul 2003 15:19:38 -0400 Message-ID: <3F1D8E45.7090101@obfuscated.net> Date: Tue, 22 Jul 2003 15:19:33 -0400 From: Michael Conlen User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Sean Chittenden References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> <20030720164237.GC55392@nasby.net> <20030720205339.GP24507@perrin.int.nxad.com> <20030722143449.B10666@smtp.k12us.com> <20030722185527.GU64860@perrin.int.nxad.com> In-Reply-To: <20030722185527.GU64860@perrin.int.nxad.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Christopher Weimann cc: Paul Pathiakis 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: Tue, 22 Jul 2003 19:19:42 -0000 Sean Chittenden wrote: >>>You might want to get in the habit of using sysctl for getting >>>that kind of info. `sysctl -d vfs.bufspace` >>> >>> >>I'm confused. Everything I have ever read about FreeBSD indicates >>that it uses all free ram for the disk cache. >> >> > >>From loader(8): > > kern.maxbcache > Limits the amount of KVM reserved for use by the buffer > cache, specified in bytes. The default maximum is 200MB. > This parameter is used to prevent the buffer cache from > eating too much KVM in large-memory machine configurations. > Only mess around with this parameter if you need to greatly > extend the KVM reservation for other resources such as the > swap zone or NMBCLUSTERS. Note that the NBUF parameter > will override this limit. Modifies VM_BCACHE_SIZE_MAX. > >-sc > > Aren't we talking about two different things here, the buffer cache and the inactive pages? -- Michael Conlen From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 09:06:58 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 25BCC37B401; Tue, 22 Jul 2003 09:06:58 -0700 (PDT) Received: from search.sparks.net (search.sparks.net [207.5.180.136]) by mx1.FreeBSD.org (Postfix) with ESMTP id 74E2D43FB1; Tue, 22 Jul 2003 09:06:57 -0700 (PDT) (envelope-from dmiller@miningworks.com) Received: by search.sparks.net (Postfix, from userid 100) id E0D95A902; Tue, 22 Jul 2003 12:06:56 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by search.sparks.net (Postfix) with ESMTP id D74A2A901; Tue, 22 Jul 2003 12:06:56 -0400 (EDT) Date: Tue, 22 Jul 2003 12:06:56 -0400 (EDT) From: David Miller X-Sender: dmiller@search.sparks.net To: Dan Langille In-Reply-To: <3F1D2208.2124.52B03E6A@localhost> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Sender: dmiller@miningworks.com X-Mailman-Approved-At: Tue, 22 Jul 2003 16:49:49 -0700 cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Jason Stone 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: Tue, 22 Jul 2003 16:06:58 -0000 On Tue, 22 Jul 2003, Dan Langille wrote: > On 21 Jul 2003 at 20:35, Jason Stone wrote: > > > I feel like this is an extremely important point. If softupdates changes > > the semantics of sync(2)/fsync(2), then it absolutely has to be off for a > > postgresql server because postgresql counts on fsync in order to make its > > durability guarantees. > > If this means all FreeBSD-PostgreSQL users need to change their > setup, we need to do something ASAP. I'd first recommend getting the > confirmation from the PostgreSQL team, then adjusting the PostgreSQL > documenation and the FreeBSD port. If softupdates breaks the semantics of sync/fsync it affects a lot more than postgres, it affects every application that manages data on its own. Every other database that doesn't do raw IO would seem to be affected, for example. Before we go off half-cocked, can anyone confirm that softupdates actually does this - reorders/delays sync/fsync - on writes within a file? I had the impression it worked on meta data about files (directory structures) rather than the data within the file itself. --- David From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 11:34:55 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 37D3537B405 for ; Tue, 22 Jul 2003 11:34:55 -0700 (PDT) Received: from smtp.k12us.com (smtp.k12us.com [65.112.222.15]) by mx1.FreeBSD.org (Postfix) with SMTP id 736F743F85 for ; Tue, 22 Jul 2003 11:34:53 -0700 (PDT) (envelope-from cweimann@k12hq.com) Received: (qmail 54906 invoked by uid 1001); 22 Jul 2003 18:34:50 -0000 Date: Tue, 22 Jul 2003 14:34:49 -0400 From: Christopher Weimann To: Sean Chittenden Message-ID: <20030722143449.B10666@smtp.k12us.com> References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> <20030720164237.GC55392@nasby.net> <20030720205339.GP24507@perrin.int.nxad.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <20030720205339.GP24507@perrin.int.nxad.com>; from seanc@FreeBSD.org on Sun, Jul 20, 2003 at 01:53:39PM -0700 X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) X-Mailman-Approved-At: Tue, 22 Jul 2003 16:49:49 -0700 cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis cc: "Jim C. Nasby" 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: Tue, 22 Jul 2003 18:34:55 -0000 On Sun 07/20/2003-01:53:39PM -0700, Sean Chittenden wrote: > > You might want to get in the habit of using sysctl for getting that > kind of info. `sysctl -d vfs.bufspace` > I'm confused. Everything I have ever read about FreeBSD indicates that it uses all free ram for the disk cache. From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 13:56:51 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 8A85D37B401 for ; Tue, 22 Jul 2003 13:56:51 -0700 (PDT) Received: from smtp.k12us.com (smtp.k12us.com [65.112.222.15]) by mx1.FreeBSD.org (Postfix) with SMTP id 53D6243F85 for ; Tue, 22 Jul 2003 13:56:49 -0700 (PDT) (envelope-from cweimann@k12hq.com) Received: (qmail 65524 invoked by uid 1001); 22 Jul 2003 20:56:47 -0000 Date: Tue, 22 Jul 2003 16:56:47 -0400 From: Christopher Weimann To: Michael Conlen Message-ID: <20030722165647.A48502@smtp.k12us.com> References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> <20030720164237.GC55392@nasby.net> <20030720205339.GP24507@perrin.int.nxad.com> <20030722143449.B10666@smtp.k12us.com> <20030722185527.GU64860@perrin.int.nxad.com> <3F1D8E45.7090101@obfuscated.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <3F1D8E45.7090101@obfuscated.net>; from meconlen@obfuscated.net on Tue, Jul 22, 2003 at 03:19:33PM -0400 X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) X-Mailman-Approved-At: Tue, 22 Jul 2003 16:49:49 -0700 cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis 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: Tue, 22 Jul 2003 20:56:51 -0000 On Tue 07/22/2003-03:19:33PM -0400, Michael Conlen wrote: > > >>From loader(8): > > > > kern.maxbcache > > Limits the amount of KVM reserved for use by the buffer > > cache, specified in bytes. The default maximum is 200MB. > > This parameter is used to prevent the buffer cache from > > eating too much KVM in large-memory machine configurations. > > Only mess around with this parameter if you need to greatly > > extend the KVM reservation for other resources such as the > > swap zone or NMBCLUSTERS. Note that the NBUF parameter > > will override this limit. Modifies VM_BCACHE_SIZE_MAX. > > > > Aren't we talking about two different things here, the buffer cache and > the inactive pages? > I think this article clarifies it somewhat but it is a bit old... http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=utf-8&selm=199907290910.CAA06665%40implode.root.com From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 23:04:43 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 5D3E337B401 for ; Tue, 22 Jul 2003 23:04:43 -0700 (PDT) Received: from bluejay.mail.pas.earthlink.net (bluejay.mail.pas.earthlink.net [207.217.120.218]) by mx1.FreeBSD.org (Postfix) with ESMTP id B13EA43F3F for ; Tue, 22 Jul 2003 23:04:42 -0700 (PDT) (envelope-from tlambert2@mindspring.com) Received: from user-2ivfkao.dialup.mindspring.com ([165.247.209.88] helo=mindspring.com) by bluejay.mail.pas.earthlink.net with asmtp (SSLv3:RC4-MD5:128) (Exim 3.33 #1) id 19fCjk-00009i-00; Tue, 22 Jul 2003 23:04:29 -0700 Message-ID: <3F1E252B.7F1F676@mindspring.com> Date: Tue, 22 Jul 2003 23:03:23 -0700 From: Terry Lambert X-Mailer: Mozilla 4.79 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: Michael Conlen References: <3F1D6B04.4010704@obfuscated.net> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-ELNK-Trace: b1a02af9316fbb217a47c185c03b154d40683398e744b8a472385ceb6388863aa9e9d9511088b69c350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c cc: freebsd-performance@freebsd.org Subject: Re: sbwait state for loaded Apache server 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: Wed, 23 Jul 2003 06:04:43 -0000 Michael Conlen wrote: > I'm working with an Apache webserver running 1400 apache processes and > the system pusing somewhere in the area of 50-60Mbit/sec sustained. The > system seems to top out around 60Mbit/sec and I see some minor > degradation of server response times. The server response times are > generally very very stable otherwise. Most of the apache processes are > in the sbwait state. I've got 4Gig of memory, so I can play with some of > the values (nmbclusters has been turned up and I never see delayed or > dropped requests for mbufs). > > I don't see in my old Design & Implementation of 4.4BSD (Red Book?) much > about the state, and I don't a copy of TCP/IP Illustrated 2 handy these > days, but if memory serves sbwait is waiting on a socket buffer > resource. My guess is that these are processes waiting on the send > buffer to drain. > > $ netstat -an | egrep '[0-9] 3[0-9]{4}' | wc -l > 297 > > seems to indicate that I've got a lot of processes waiting to drain. > Looking at the actual output it shows most of these are ESTABLISHED. cd /usr/src/sys/kern grep sbwait The sleep call is the sbwait() function in uipc_socket2.c. It's called: o On the send buffer from sendfile() o On the send buffer from sosend() o On the receive buffer from soreceive() There's also a commented out call on the receive buffer in unp_gc(), which you can ignore, since it only deals with rights issues in the uipc cases related to AF_UNIX (UNIX domain sockets). The receive() case can probably be ignored, too, since it deals with blocking reads on sockets with no data present, and Apache generally doesn't do this. So you are spending your time waiting for the send buffers to drain on an sosend() or a sendfile(). Basically, this probably means that you have a client on a slow link talking to your server on a fast link, or you have a client that is intentionally attempting to DOS you by sending a request and not keeping up the TCP/IP conversation, or you are running Microsoft's WAST HTTP benchmark program, and you really don't understand how it works, or you are running a Web Avalanche(tm) box against your server, or you have legitimate client traffic, but the client has dropped off the net. If I had to guess, I'd say "slow clients". > So my thought is by increasing the send queue size I could reduce this. > I've got a pretty good idea on the size of the files being sent and my > thoughts were to increase the send-q size to where Apache can write() > the file and go to the keep alive state quickly instead of waiting. This would most likely be an incredibly bad idea, since you will be more likely to go to FIN-WAIT-2 state, instead, and if you did go into an application level KeepAlive in Apache, you aren't going to be sending any KeepAlive messages that are going anywhere until the rest of the data has drained. Further, you really want to delay closes until the client has done the close (1-2 seconds after you would have closed the socket) so that the client goes into FIN-WAIT-2 instead of you going into that state (servers don't want to be the ones to close the connection, because the FIN-WAIT-1 -> FIN-WAIT-2 transition doesn't get reversed ...even though you could technically pretend you never got the second FIN and solicit either an RST or another FIN... or no response, after a couple of which you could safely drop the connection. > So the questions are > > Would this affect actual network performance Yes. It would negatively impact the overall total number of connections you could handle without running out of RAM, and it would penalize faster connections (people who get what they want and get the heck off your server, thus reducing your load) in favor of people with slower connections (people who get on your server and stay there forever, consuming your resources). > Would this reduce load on the machine (a handy thing to do, but secondary) > given c = number of connections and q = queue adjustment and s = size of > mbuf do I just need to make sure I have (c*q)/s buffers available, and > any fudge? Not really. All it would do is move your Apache processes into trying to poll clients who aren't going to answer, trying to ask them if they are still there and still need the connection. Or it will drop you into FIN-WAIT-2, and if the client drops off the net, or is trying to DOS your server, leave it in that state for about 4 hours, chewing up those resources (the default for the timer that the TCP/IP standard doesn't permit people to implement to reap hanging FIN-WAIT-2's, but which people implement anyway). Much better to hack the stack to pretend it didn't get the second FIN at this point, send a FIN/ACK, and then only keep the connection around if you get a FIN back. > How do I know when I need to increase the overall system buffer size > beyond 200 MB? That's a hard one to answer. The general answer is "When a overall system buffer size less tan or equal to 200 MB is constraining my ability to service connections". -- Terry From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 23:15:14 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 9483337B401 for ; Tue, 22 Jul 2003 23:15:14 -0700 (PDT) Received: from bluejay.mail.pas.earthlink.net (bluejay.mail.pas.earthlink.net [207.217.120.218]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5618F43F93 for ; Tue, 22 Jul 2003 23:15:13 -0700 (PDT) (envelope-from tlambert2@mindspring.com) Received: from user-2ivfkao.dialup.mindspring.com ([165.247.209.88] helo=mindspring.com) by bluejay.mail.pas.earthlink.net with asmtp (SSLv3:RC4-MD5:128) (Exim 3.33 #1) id 19fCu7-0001Jp-00; Tue, 22 Jul 2003 23:15:12 -0700 Message-ID: <3F1E27AD.F7405D10@mindspring.com> Date: Tue, 22 Jul 2003 23:14:05 -0700 From: Terry Lambert X-Mailer: Mozilla 4.79 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: Simon J Mudd References: <20030721192643.GD55392@nasby.net> <86znj6l9mm.fsf@unicorn.wl0.org> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-ELNK-Trace: b1a02af9316fbb217a47c185c03b154d40683398e744b8a472385ceb6388863a831eab35a3a09d88350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c 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: Wed, 23 Jul 2003 06:15:14 -0000 Simon J Mudd wrote: > Someone else mentioned Qmail. I use Postfix and I know the author > relies on fsync semantics before confirming the acceptance of email. > I'm sure Qmail (and sendmail, ...) does the same. They should do - > the RFC requires this (that mail can't be lost once it is accepted for > delivery). It's OK t fsync() the data file before sending back the "250 OK"; RFC 821 (and now 2821) require this behavior: the data much be committed to stable storage before the "250 OK" response is sent (unless you are HotMail, of course ;^)). The problem with qmail is that it assumes that it needs to sync the directory because it thinks the underlying FS implemention violates the POSIX standard with regard to "SHALL be updated" instead of "SHALL be marked for update" semantics on directory metadata. So qmail does an extra fsync() on something that doesn't need to be fsync()'ed. This makes qmail unnecessarily slow, in order to make it safe for people to run nominally POSIX code on non-POSIX compliant systems. For POSIX systems, qmail should turn this behaviour off. Unfortunately, there are a number of systems which define POSIX manifest constants, thus lying about whether or not they are really POSIX compliant systems. -- Terry From owner-freebsd-performance@FreeBSD.ORG Tue Jul 22 23:23:07 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 7BC8637B401; Tue, 22 Jul 2003 23:23:07 -0700 (PDT) Received: from bluejay.mail.pas.earthlink.net (bluejay.mail.pas.earthlink.net [207.217.120.218]) by mx1.FreeBSD.org (Postfix) with ESMTP id BA7BB43F3F; Tue, 22 Jul 2003 23:23:06 -0700 (PDT) (envelope-from tlambert2@mindspring.com) Received: from user-2ivfkao.dialup.mindspring.com ([165.247.209.88] helo=mindspring.com) by bluejay.mail.pas.earthlink.net with asmtp (SSLv3:RC4-MD5:128) (Exim 3.33 #1) id 19fD1i-0001ya-00; Tue, 22 Jul 2003 23:23:03 -0700 Message-ID: <3F1E297E.70962D97@mindspring.com> Date: Tue, 22 Jul 2003 23:21:50 -0700 From: Terry Lambert X-Mailer: Mozilla 4.79 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: Christopher Weimann References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> <20030720164237.GC55392@nasby.net> <20030722143449.B10666@smtp.k12us.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-ELNK-Trace: b1a02af9316fbb217a47c185c03b154d40683398e744b8a45cc5ac0d552399a086f2d627dc4ddc34350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c cc: Sean Chittenden cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: "Jim C. Nasby" cc: Paul Pathiakis 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: Wed, 23 Jul 2003 06:23:07 -0000 Christopher Weimann wrote: > On Sun 07/20/2003-01:53:39PM -0700, Sean Chittenden wrote: > > You might want to get in the habit of using sysctl for getting that > > kind of info. `sysctl -d vfs.bufspace` > > I'm confused. Everything I have ever read about FreeBSD indicates > that it uses all free ram for the disk cache. FreeBSD uses all unwired free memory for caching. Wired free memory has, by definition, been committed to a type-stable zone, and is therefore unavailable for reallocation, since it may have been (probably *has* been) fragmented into sub-page-size fragments that cannot be recoelesced into pages, even if they are all free, so that they can be given back to the system. There are some types of kernel allocations that are recoverable (e.g. buffer cache entries/VM), but most allocations use the zone allocator, and once it owns a page's soul, that page belongs to it forever. You should read Matt Dillon's excellent VM articles in the Blue Prints column at http://www.daemonnews.org/ for more information. -- Terry From owner-freebsd-performance@FreeBSD.ORG Wed Jul 23 23:57:08 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 8300737B401; Wed, 23 Jul 2003 23:57:08 -0700 (PDT) Received: from stork.mail.pas.earthlink.net (stork.mail.pas.earthlink.net [207.217.120.188]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9923D43F75; Wed, 23 Jul 2003 23:57:07 -0700 (PDT) (envelope-from tlambert2@mindspring.com) Received: from user-2ivfnj3.dialup.mindspring.com ([165.247.222.99] helo=mindspring.com) by stork.mail.pas.earthlink.net with asmtp (SSLv3:RC4-MD5:128) (Exim 3.33 #1) id 19fa1e-00012v-00; Wed, 23 Jul 2003 23:56:31 -0700 Message-ID: <3F1F82DD.966C7F2F@mindspring.com> Date: Wed, 23 Jul 2003 23:55:25 -0700 From: Terry Lambert X-Mailer: Mozilla 4.79 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: "Jim C. Nasby" References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> <20030720164237.GC55392@nasby.net> <20030722143449.B10666@smtp.k12us.com> <3F1E297E.70962D97@mindspring.com> <20030723144700.GL55392@nasby.net> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-ELNK-Trace: b1a02af9316fbb217a47c185c03b154d40683398e744b8a44ca18453e58061dd37fa2b3b25b9f726350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c cc: Sean Chittenden cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Christopher Weimann cc: Paul Pathiakis 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: Thu, 24 Jul 2003 06:57:08 -0000 "Jim C. Nasby" wrote: [ ... quote of me and quote of Matt Dillon's "Blue Prints" article ... ] > The question I have is: can pages in the inactive queue be used as disk > cache? The answer is "yes, they can be reactivated and written to before they are flushed if soft updates is enabled" and "yes, they can be reactivated and read (but not written) to before they are flushed if soft updates is not enabled". In general, this only happens for data pages, which is to say, the pages containing user file data. Pages containing FS metadata are specifically considered as "write through" or "virtually write through". It doesn't happen for data pages, if they are explicitly fsync'ed to ensure write ordering is guaranteed. Metadata pages will be marked as "busy" by the system until they are written out in dependency order, once a write is started on the page in question. Effectively, they are "read-only", and reads do not stall, but new writes stall, until the write completes. This only happens *after* the write hits the block I/O subsystem. In reality, the pages are treated as copy-on-write, with a blocking semantic to ensure metadata serialization (e.g. if there was a bwrite in progress and a bdwrite was requested, it could go through, but another bdwrite would be blocked until the first finished. IF there are multiple operations in progress in the same page, AND there are no dependencies between the operations, AND soft updates is enabled, AND the write has been paced on the soft updates clock wheel to be written AND the wheel has not progressed to the point where the write has actually been taken off the wheel and scheduled in the I/O subsystem, THEN the write may be scheduled to occur simultaneously, IF there are no intermediate dependent writes that need to take place. In other words, if the dependency is "soft", then it can gather any modifications to a single page together, and save I/O operations (or in the case of create-write-delete for a shortlived intermediate file, it can avoid the writes altogether. All this boils down to one thing: in the normal case, metadata write ordering is implicitly guaranteed in all cases where it is not specifically declined at the time the FS is mounted (via the "async" option, the "noatime" option, etc.), all of which are disabled by default. > Or maybe a better question would be: what does each memory catagory in > top mean? > Mem: 365M Active, 1400M Inact, 168M Wired, 76M Cache, 199M Buf, 3008K Free Depends on the version of "top" you are running. The statistics we keep are in the "struct vmmeter" in the file /usr/src/sys/sys/vmmeter.h. The meaning of these statistics varies slightly, over time, so that's not fixed either (but I've seen more changes in "top" than FreeBSD). The place to look for their meanings is first in the source code for the version of "top" you are running, to see what fields they are using and how/if they are combining them mathematically, and then second, in the code that updates the variables you are interested in (usually meaning code that lives in /usr/src/sys/vm/*.c). Honestly, if you aren't able to dig the information out, you are not likely to be able to understand the answer the way it was intended to be understood, if someone comes right out and tells you. Kirk McKusick is rumored to be working on a FreeBSD Internals book, but we are going on 3 years for that rumor. I started one, and I updated it several times in the process, but, frankly, FreeBSD will not stand still long enough for a single person to document it well, and I discontinued work on it at about the 4.6-RELEASE level. IMO, writing a good book takes at least 2080 hours on the part of the author(s), which is equivalent to a full time job for a year, and it also takes a willingness on the part o technical reviewer(s) to spend a lot of time on the review process, in order for the book to be any good (e.g. I spent probably 200 total hours in the review process on Uresh Vahalia's "UNIX Internals: The New Frontiers" for Prentice Hall's technical editor on that project). > Is there anywhere that clearly defines what each queue is, and how it's > used? The source code for a particular version tag does, for a version built from that particular version tag, and probably only that version. -- Terry From owner-freebsd-performance@FreeBSD.ORG Thu Jul 24 10:39:14 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 CF5E437B401 for ; Thu, 24 Jul 2003 10:39:14 -0700 (PDT) Received: from smtp.k12us.com (smtp.k12us.com [65.112.222.15]) by mx1.FreeBSD.org (Postfix) with SMTP id 499E243FDF for ; Thu, 24 Jul 2003 10:39:12 -0700 (PDT) (envelope-from csw@k12hq.com) Received: (qmail 39300 invoked by uid 1001); 24 Jul 2003 17:39:10 -0000 Date: Thu, 24 Jul 2003 13:39:10 -0400 From: Christopher Weimann To: Terry Lambert Message-ID: <20030724173910.GA9364@smtp.k12us.com> References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> <20030720164237.GC55392@nasby.net> <20030722143449.B10666@smtp.k12us.com> <3F1E297E.70962D97@mindspring.com> <20030723144700.GL55392@nasby.net> <3F1F82DD.966C7F2F@mindspring.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3F1F82DD.966C7F2F@mindspring.com> User-Agent: Mutt/1.5.4i X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) cc: Sean Chittenden cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis cc: "Jim C. Nasby" 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: Thu, 24 Jul 2003 17:39:15 -0000 On Wed 07/23/2003-11:55:25PM -0700, Terry Lambert wrote: > > > The question I have is: can pages in the inactive queue be used as disk > > cache? > > The answer is "yes, they can be reactivated and written to before > they are flushed if soft updates is enabled" and "yes, they can be > reactivated and read (but not written) to before they are flushed > if soft updates is not enabled". > > In general, this only happens for data pages, which is to say, the > pages containing user file data. Pages containing FS metadata are > specifically considered as "write through" or "virtually write > through". > > It doesn't happen for data pages, if they are explicitly fsync'ed > to ensure write ordering is guaranteed. > So if PostGreSQL uses fsync when writing ( which I think is only true on the write-ahead logs at this point ) that data will NOT wind up in the cache. Anything that PostGreSQL reads should wind up in the cache? #uname -r 4.7-RELEASE-p3 # top -b | head -5 last pid: 58622; load averages: 1.96, 1.67, 1.47 up 2+12:59:15 13:31:39 130 processes: 6 running, 124 sleeping Mem: 348M Active, 2628M Inact, 438M Wired, 155M Cache, 380M Buf, 76M Free Swap: 4096M Total, 28K Used, 4096M Free The 2628M Inact is likley to be acting as cache? Basically I should stop worrying about this :) From owner-freebsd-performance@FreeBSD.ORG Thu Jul 24 15:56:47 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 8E6AC37B401 for ; Thu, 24 Jul 2003 15:56:47 -0700 (PDT) Received: from silver.he.iki.fi (silver.he.iki.fi [193.64.42.241]) by mx1.FreeBSD.org (Postfix) with ESMTP id 11B4543F85 for ; Thu, 24 Jul 2003 15:56:46 -0700 (PDT) (envelope-from pete@he.iki.fi) Received: from PETEX31 (h81.vuokselantie10.fi [193.64.42.129]) by silver.he.iki.fi (8.12.9/8.11.4) with SMTP id h6OMuhsL014276 for ; Fri, 25 Jul 2003 01:56:43 +0300 (EEST) (envelope-from pete@he.iki.fi) Message-ID: <00d001c35236$d69cfcb0$812a40c1@PETEX31> From: "Petri Helenius" To: Date: Fri, 25 Jul 2003 01:56:37 +0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Subject: cpu performance counters 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: Thu, 24 Jul 2003 22:56:47 -0000 Does FreeBSD allow access to CPU-specific performance counters for Intel and AMD CPUs? The ones which can be used to optimize pipeline and branch prediction things, etc. Pete From owner-freebsd-performance@FreeBSD.ORG Fri Jul 25 00:15:27 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 B366C37B401; Fri, 25 Jul 2003 00:15:27 -0700 (PDT) Received: from puffin.mail.pas.earthlink.net (puffin.mail.pas.earthlink.net [207.217.120.139]) by mx1.FreeBSD.org (Postfix) with ESMTP id EADB143FAF; Fri, 25 Jul 2003 00:15:26 -0700 (PDT) (envelope-from tlambert2@mindspring.com) Received: from user-2ivfi6b.dialup.mindspring.com ([165.247.200.203] helo=mindspring.com) by puffin.mail.pas.earthlink.net with asmtp (SSLv3:RC4-MD5:128) (Exim 3.33 #1) id 19fwmu-00062u-00; Fri, 25 Jul 2003 00:14:49 -0700 Message-ID: <3F20D8A8.6FA1C790@mindspring.com> Date: Fri, 25 Jul 2003 00:13:44 -0700 From: Terry Lambert X-Mailer: Mozilla 4.79 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: Christopher Weimann References: <200307191818.13516.paul@pathiakis.com> <20030720110939.GN24507@perrin.int.nxad.com> <20030720164237.GC55392@nasby.net> <20030722143449.B10666@smtp.k12us.com> <3F1E297E.70962D97@mindspring.com> <20030723144700.GL55392@nasby.net> <3F1F82DD.966C7F2F@mindspring.com> <20030724173910.GA9364@smtp.k12us.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-ELNK-Trace: b1a02af9316fbb217a47c185c03b154d40683398e744b8a4c4d372233ee63315d328c02106f30f8e93caf27dac41a8fd350badd9bab72f9c350badd9bab72f9c cc: Sean Chittenden cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis cc: "Jim C. Nasby" 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: Fri, 25 Jul 2003 07:15:28 -0000 Christopher Weimann wrote: > So if PostGreSQL uses fsync when writing ( which I think is only true > on the write-ahead logs at this point ) that data will NOT wind up in > the cache. Anything that PostGreSQL reads should wind up in the cache? No; fsync'ed data pages are in the cache as clean pages containing data, until they are inactivated, or until you madvise them away, etc.. One common practice is to madvise(DONTNEED), which basically moves the pages to the other end of the LRU, so that they will be stolen and given back to the system first, rather than putting them on the LRU in access order, which means other pages would be at risk first. Probably reading the decriptions in the madvise man page would be a useful exercise for you, since it talks about the states that the vmobject_t's and pages in them can be in at any given time, with specific regard to FreeBSD. As I said before, there's really no good documentation, except the source code, and to undestand what top's numbers means, you have to understand how top gets those numbers in the first place, then you have to reverse the math to get the statictics values it's using and their effect on the values displayed by top, and then you have to look at the VM code that changes each of those statistics (in the "vmmeter" structure) to see what conditions cause a state change on a page. Short of writing better documentation, that's about all I can tell you about it without defacto writing better documentation. The problem with writing better documentation is that then some idiot will understand the code, and, understanding it, either fix or try to fix any shortcomings described in the documentation, and fail to update the documentation in the process, at which point the documentation will be near-instantly out of date. It's like comparing Linux and FreeBSD: as soon as you do a valid comparison which honestly shows a difference between the two, an army of people who don't value accurate documentation rush out to correct any discrepancies in performance, and then a week after you've published your comparison, someone's trying to blow up your car for publishing "lies" about their favorite OS ("That's not true! You're a disreputable scoundrel!"). > last pid: 58622; load averages: 1.96, 1.67, 1.47 up 2+12:59:15 13:31:39 > 130 processes: 6 running, 124 sleeping > > Mem: 348M Active, 2628M Inact, 438M Wired, 155M Cache, 380M Buf, 76M Free > Swap: 4096M Total, 28K Used, 4096M Free > > The 2628M Inact is likley to be acting as cache? > > Basically I should stop worrying about this :) Or go read the top sources and the VM code, yes. -- Terry From owner-freebsd-performance@FreeBSD.ORG Fri Jul 25 01:55:58 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 8F30537B401; Fri, 25 Jul 2003 01:55:58 -0700 (PDT) Received: from misery.sdf.com (misery.sdf.com [207.200.153.226]) by mx1.FreeBSD.org (Postfix) with ESMTP id 400F043F3F; Fri, 25 Jul 2003 01:55:56 -0700 (PDT) (envelope-from tom@sdf.com) Received: from tom (helo=localhost) by misery.sdf.com with local-esmtp (Exim 2.12 #1) id 19fwc6-0004gx-00; Fri, 25 Jul 2003 00:03:38 -0700 Date: Fri, 25 Jul 2003 00:03:32 -0700 (PDT) From: Tom Samplonius To: Christopher Weimann In-Reply-To: <20030724173910.GA9364@smtp.k12us.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: Sean Chittenden cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Terry Lambert cc: Paul Pathiakis 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: Fri, 25 Jul 2003 08:55:59 -0000 On Thu, 24 Jul 2003, Christopher Weimann wrote: ... > So if PostGreSQL uses fsync when writing ( which I think is only true > on the write-ahead logs at this point ) that data will NOT wind up in > the cache. Anything that PostGreSQL reads should wind up in the cache? > > #uname -r > 4.7-RELEASE-p3 > > # top -b | head -5 > last pid: 58622; load averages: 1.96, 1.67, 1.47 up 2+12:59:15 13:31:39 > 130 processes: 6 running, 124 sleeping > > Mem: 348M Active, 2628M Inact, 438M Wired, 155M Cache, 380M Buf, 76M Free > Swap: 4096M Total, 28K Used, 4096M Free > > The 2628M Inact is likley to be acting as cache? > > Basically I should stop worrying about this :) Maybe you should continue to worry. PostgreSQL isn't MySQL (or a typical server application). It reads all database pages into its shared memory area. It is wasteful for the DBMS and the OS to both cache this data. You'll want the PostgreSQL shared memory size to be around 75% the size of RAM (on a dedicated DBMS server). In fact, many commercial DBMS systems will use raw writes to bypass the OS cache! Tom From owner-freebsd-performance@FreeBSD.ORG Fri Jul 25 09:26:07 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 0BB6C37B401 for ; Fri, 25 Jul 2003 09:26:07 -0700 (PDT) Received: from smtp.k12us.com (smtp.k12us.com [65.112.222.15]) by mx1.FreeBSD.org (Postfix) with SMTP id C738B43FA3 for ; Fri, 25 Jul 2003 09:26:04 -0700 (PDT) (envelope-from cweimann@k12hq.com) Received: (qmail 35443 invoked by uid 1001); 25 Jul 2003 16:26:01 -0000 Date: Fri, 25 Jul 2003 12:26:01 -0400 From: Christopher Weimann To: Tom Samplonius Message-ID: <20030725162601.GA35378@smtp.k12us.com> References: <20030724173910.GA9364@smtp.k12us.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) cc: freebsd-database@freebsd.org cc: Christopher Weimann cc: Paul Pathiakis cc: Sean Chittenden cc: freebsd-performance@freebsd.org cc: Terry Lambert 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: Fri, 25 Jul 2003 16:26:07 -0000 On Fri 07/25/2003-12:03:32AM -0700, Tom Samplonius wrote: > > Maybe you should continue to worry. PostgreSQL isn't MySQL (or a > typical server application). It reads all database pages into its shared > memory area. It is wasteful for the DBMS and the OS to both cache this > data. You'll want the PostgreSQL shared memory size to be around 75% the > size of RAM (on a dedicated DBMS server). In fact, many commercial DBMS > systems will use raw writes to bypass the OS cache! > I was concerned about the disk cache because of this link http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#shbuf which says... PostgreSQL counts a lot on the OS to cache data files and hence does not bother with duplicating its file caching effort. The shared buffers parameter assumes that OS is going to cache a lot of files and hence it is generally very low compared with system RAM. Even for a dataset in excess of 20GB, a setting of 128MB may be too much, if you have only 1GB RAM and an aggressive-at-caching OS like Linux. But now that I have looked a bit more I see that this link http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node6.html which says... Ideally, the POSTGRESQL shared buffer cache will be: * Large enough to hold most commonly-accessed tables * Small enough to avoid swap pagein activity So I have conflicting documentation. I have machine with 4Gig of ram. What is the maximum value of SHMMAX on FreeBSD? From owner-freebsd-performance@FreeBSD.ORG Fri Jul 25 11:25:59 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 A835F37B401; Fri, 25 Jul 2003 11:25:59 -0700 (PDT) Received: from svaha.com (svaha.com [64.46.156.67]) by mx1.FreeBSD.org (Postfix) with ESMTP id A95D143F3F; Fri, 25 Jul 2003 11:25:58 -0700 (PDT) (envelope-from meconlen@obfuscated.net) Received: from presa (24161248hfc18.tampabay.rr.com [24.161.248.18]) (AUTH: LOGIN meconlen) by svaha.com with esmtp; Fri, 25 Jul 2003 14:25:57 -0400 From: "Michael E. Conlen" To: "Christopher Weimann" Date: Fri, 25 Jul 2003 14:25:57 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) In-Reply-To: <20030725162601.GA35378@smtp.k12us.com> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 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: Fri, 25 Jul 2003 18:26:00 -0000 Usually a database server will have better algorithms to cache information than the OS, as the OS handles the general case, and the server can handle the specific case. In this case, I'd look to the Postgress people to see if they know which is better, but there's also this tidbit found on the page under the first link "There is one way to decide what is best for you. Set a high value of this parameter and run the database for typical usage. Watch usage of shared memory using ipcs or similar tools. A recommended figure would be between 1.2 to 2 times peak shared memory usage. " Give it a run and see. -- Michael Conlen -----Original Message----- I was concerned about the disk cache because of this link http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#shbuf which says... PostgreSQL counts a lot on the OS to cache data files and hence does not bother with duplicating its file caching effort. The shared buffers parameter assumes that OS is going to cache a lot of files and hence it is generally very low compared with system RAM. Even for a dataset in excess of 20GB, a setting of 128MB may be too much, if you have only 1GB RAM and an aggressive-at-caching OS like Linux. But now that I have looked a bit more I see that this link http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node6.html which says... Ideally, the POSTGRESQL shared buffer cache will be: * Large enough to hold most commonly-accessed tables * Small enough to avoid swap pagein activity So I have conflicting documentation. I have machine with 4Gig of ram. What is the maximum value of SHMMAX on FreeBSD? From owner-freebsd-performance@FreeBSD.ORG Fri Jul 25 21:22:24 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 5967B37B401; Fri, 25 Jul 2003 21:22:24 -0700 (PDT) Received: from misery.sdf.com (misery.sdf.com [207.200.153.226]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9581C43F3F; Fri, 25 Jul 2003 21:22:22 -0700 (PDT) (envelope-from tom@sdf.com) Received: from tom (helo=localhost) by misery.sdf.com with local-esmtp (Exim 2.12 #1) id 19gEol-0003pK-00; Fri, 25 Jul 2003 19:29:55 -0700 Date: Fri, 25 Jul 2003 19:29:50 -0700 (PDT) From: Tom Samplonius To: "Michael E. Conlen" In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Christopher Weimann 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: Sat, 26 Jul 2003 04:22:24 -0000 On Fri, 25 Jul 2003, Michael E. Conlen wrote: > I was concerned about the disk cache because of this link > > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#shbuf > > which says... > But now that I have looked a bit more I see that this link > > http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node6.html ... I would go with the information from the PostgreSQL site. It is at least reviewed by the developers (if not written by them). Tom From owner-freebsd-performance@FreeBSD.ORG Fri Jul 25 23:35:04 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 0A56737B401; Fri, 25 Jul 2003 23:35:04 -0700 (PDT) Received: from stork.mail.pas.earthlink.net (stork.mail.pas.earthlink.net [207.217.120.188]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5561F43F75; Fri, 25 Jul 2003 23:35:01 -0700 (PDT) (envelope-from tlambert2@mindspring.com) Received: from user-2ivfj2b.dialup.mindspring.com ([165.247.204.75] helo=mindspring.com) by stork.mail.pas.earthlink.net with asmtp (SSLv3:RC4-MD5:128) (Exim 3.33 #1) id 19gIdg-0007k3-00; Fri, 25 Jul 2003 23:34:44 -0700 Message-ID: <3F2220C7.F0179FC4@mindspring.com> Date: Fri, 25 Jul 2003 23:33:43 -0700 From: Terry Lambert X-Mailer: Mozilla 4.79 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: Christopher Weimann References: <20030724173910.GA9364@smtp.k12us.com> <20030725162601.GA35378@smtp.k12us.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-ELNK-Trace: b1a02af9316fbb217a47c185c03b154d40683398e744b8a4c10426f27d3e889ed0aa6d67ef163ea2667c3043c0873f7e350badd9bab72f9c350badd9bab72f9c cc: freebsd-database@freebsd.org cc: Christopher Weimann cc: Paul Pathiakis cc: Sean Chittenden cc: freebsd-performance@freebsd.org cc: Tom Samplonius 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: Sat, 26 Jul 2003 06:35:04 -0000 Christopher Weimann wrote: > So I have conflicting documentation. > > I have machine with 4Gig of ram. What is the maximum > value of SHMMAX on FreeBSD? Ideally, you would use memory mapped files for this, and not System V shared memory, so that the OS could implement swapping policies as it saw fit, and could actually swap the data, if nevcessary, instead of it sucking up huge amounts of wired memory. In any case, POSIX deprecated SysV shared memory years ago, and recommends that all code utilize shm_open instead, these days, if you insist on using an interface that sucks up huge amounts of wired pages and KVA space. -- Terry From owner-freebsd-performance@FreeBSD.ORG Fri Jul 25 23:48:38 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 AC66237B401; Fri, 25 Jul 2003 23:48:38 -0700 (PDT) Received: from misery.sdf.com (misery.sdf.com [207.200.153.226]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5795C43F93; Fri, 25 Jul 2003 23:48:36 -0700 (PDT) (envelope-from tom@sdf.com) Received: from tom (helo=localhost) by misery.sdf.com with local-esmtp (Exim 2.12 #1) id 19gH6M-0005iR-00; Fri, 25 Jul 2003 21:56:14 -0700 Date: Fri, 25 Jul 2003 21:56:08 -0700 (PDT) From: Tom Samplonius To: Terry Lambert In-Reply-To: <3F2220C7.F0179FC4@mindspring.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: Sean Chittenden cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Christopher Weimann cc: Paul Pathiakis 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: Sat, 26 Jul 2003 06:48:39 -0000 On Fri, 25 Jul 2003, Terry Lambert wrote: > Christopher Weimann wrote: > > So I have conflicting documentation. > > > > I have machine with 4Gig of ram. What is the maximum > > value of SHMMAX on FreeBSD? > > Ideally, you would use memory mapped files for this, and not System V > shared memory, so that the OS could implement swapping policies as it > saw fit, and could actually swap the data, if nevcessary, instead of > it sucking up huge amounts of wired memory. PostgreSQL is from the good old days of RDMSes when the they would System V shared memory for everything, and store databases on raw devices in an effort to utilize as little of the OS as possible, in effort to be fast and reliable. But it does give PostgreSQL the advantage of working with large tables and databases. Mmapping a file over 4GB in size would likely exhaust the VM on a x86. Or, is it possible to map 4+GB with PAE? > In any case, POSIX deprecated SysV shared memory years ago, and > recommends that all code utilize shm_open instead, these days, if > you insist on using an interface that sucks up huge amounts of > wired pages and KVA space. > > -- Terry > > Tom From owner-freebsd-performance@FreeBSD.ORG Sat Jul 26 00:19:25 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 E08A737B401; Sat, 26 Jul 2003 00:19:25 -0700 (PDT) Received: from stork.mail.pas.earthlink.net (stork.mail.pas.earthlink.net [207.217.120.188]) by mx1.FreeBSD.org (Postfix) with ESMTP id CB16543F85; Sat, 26 Jul 2003 00:19:24 -0700 (PDT) (envelope-from tlambert2@mindspring.com) Received: from user-2ivfj2b.dialup.mindspring.com ([165.247.204.75] helo=mindspring.com) by stork.mail.pas.earthlink.net with asmtp (SSLv3:RC4-MD5:128) (Exim 3.33 #1) id 19gJKi-0004SK-00; Sat, 26 Jul 2003 00:19:13 -0700 Message-ID: <3F222B36.CFAD4391@mindspring.com> Date: Sat, 26 Jul 2003 00:18:15 -0700 From: Terry Lambert X-Mailer: Mozilla 4.79 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: Tom Samplonius References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-ELNK-Trace: b1a02af9316fbb217a47c185c03b154d40683398e744b8a468c5c12d8272b9e579ad62b00e49fc6ba2d4e88014a4647c350badd9bab72f9c350badd9bab72f9c cc: Sean Chittenden cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Christopher Weimann cc: Paul Pathiakis 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: Sat, 26 Jul 2003 07:19:26 -0000 Tom Samplonius wrote: > On Fri, 25 Jul 2003, Terry Lambert wrote: > > Ideally, you would use memory mapped files for this, and not System V > > shared memory, so that the OS could implement swapping policies as it > > saw fit, and could actually swap the data, if nevcessary, instead of > > it sucking up huge amounts of wired memory. > > PostgreSQL is from the good old days of RDMSes when the they would > System V shared memory for everything, and store databases on raw devices > in an effort to utilize as little of the OS as possible, in effort to be > fast and reliable. > > But it does give PostgreSQL the advantage of working with large tables > and databases. Mmapping a file over 4GB in size would likely exhaust the > VM on a x86. Or, is it possible to map 4+GB with PAE? It's not possible. PAE only provides the ability to utilize a larger-than-4G amount of RAM consecutively in different processes. It doesn't buy you the ability to have 4+GB System V shared memory segments, either. The limitation is based on pointer size in user space, which is, in turn, based on the size of the hardware registers. PAE is basically good for allowing you to utilize large amounts of RAM for seperate process insteances and/or RAM disks (not even cache), and is most often used merely to run a lot of processes without getting bottlenecked by disk I/O doing swapping. To get better than that, you need 64 bit pointers, which means a 64 bit architecture. System V shared memory segments are basically *subtracted* from your available memory. Memory mappes files don't benefit you (or hurt you) in terms of available address space for your process. But they *are* able to be swapped, and as long as you use madvise() and/or can live with "LRU" as your paging policy, they *will* give you more physical RAM to play with for other things, if it gets down to it, than using System V shared memory will. Maybe you could reimplement the code to allocate pageable RAM for shared memory segments; I don't really see how, without an alomost total rewrite of the System V shared memory code, though. -- Terry From owner-freebsd-performance@FreeBSD.ORG Sat Jul 26 02:32:16 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 A689737B404 for ; Sat, 26 Jul 2003 02:32:16 -0700 (PDT) Received: from mailav.tor1.inquent.com (mail.inquent.com [216.208.117.106]) by mx1.FreeBSD.org (Postfix) with ESMTP id 60A4143F75 for ; Sat, 26 Jul 2003 02:32:15 -0700 (PDT) (envelope-from rbt@rbt.ca) Received: from localhost (localhost [127.0.0.1]) by mailav.tor1.inquent.com (Postfix) with ESMTP id E80AE1024D; Sat, 26 Jul 2003 05:25:06 -0400 (EDT) Received: from [192.168.1.200] (dyn-133-81.tor.dsl.tht.net [134.22.133.81]) by mailav.tor1.inquent.com (Postfix) with ESMTP id 5156610248; Sat, 26 Jul 2003 05:25:04 -0400 (EDT) From: Rod Taylor To: Tom Samplonius In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-afgb1KEF0SzM9Gw+oOUg" Message-Id: <1059211976.78488.45.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 26 Jul 2003 05:32:57 -0400 X-Virus-Scanned: by AMaViS perl-11 cc: freebsd-performance@freebsd.org cc: "Michael E. Conlen" cc: Christopher Weimann 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: Sat, 26 Jul 2003 09:32:17 -0000 --=-afgb1KEF0SzM9Gw+oOUg Content-Type: text/plain Content-Transfer-Encoding: quoted-printable > > But now that I have looked a bit more I see that this link > >=20 > > http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node6.html > ... >=20 > I would go with the information from the PostgreSQL site. It is at > least reviewed by the developers (if not written by them). In this case Bruce is a developer, but the advice in his book is rather old (much has changed in PostgreSQL over the last couple of years). Short answer is that you need to benchmark your specific situation. The more writes you have, the lower you will want the shared buffers (PostgreSQL isn't particularly efficient with buffers in this manner).=20 Between 4000 and 8000 is usually a good number for a busy primarily write based db. In regards to read performance, higher numbers can help but each step up will help less than the previous. If all of a sudden things get busy and some other elements (sorts, aggregate hash, ...) something is going to swap if you have the buffer cache set too large. Once the system starts swapping it will be difficult for it to catch back up again. 75% of RAM is generally too high. It leaves little space for several large queries to do their work in RAM (remember, buffer cache won't automatically resize itself when space becomes short). For a system running one or two report style (large amounts of data involved, aggregates all over the place, etc.) queries it may be ideal. Most suggestions put buffer cache around 25% of the ram size. It's a safe number that comes close enough to peaking in read performance without too much of a hit to write performance. Tom Lane still gives the advice that you not go over a 10k due to the write performance hit and the lack of solid proof that read performance is improved by larger values. All of the above assumes a recent version of PostgreSQL and a modern operating system. 7.4 will probably (not yet benchmarked) require a slightly buffer_cache setting than 7.3 due to increased ram usage for the work involved (speed increase) and indexes do not grow unbounded, as such take less space. Please join pgsql-performance@postgresql.org to continue the debate. Nearly every PostgreSQL DBA and developer has a different opinion about exact values, but they're usually within the same ballpark. Of course, the real fun is the the sweet spot changes with your data (distribution and quantity), query load, hardware and operating system.=20 Even if you find the best value for todays load, you will quickly find it no longer to be true -- which is another reason to err on the conservative side. --=-afgb1KEF0SzM9Gw+oOUg Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA/IkrI6DETLow6vwwRAuy/AJ9MM+KorCK6c/vAnDJyskQI1kFJ7QCggVLB RcT+YilJslWIDvu5gScR3uI= =VbkS -----END PGP SIGNATURE----- --=-afgb1KEF0SzM9Gw+oOUg--