From owner-freebsd-database@FreeBSD.ORG Sun Jul 20 04:09:41 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 20 Jul 2003 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-database@FreeBSD.ORG Sun Jul 20 09:42:44 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0D06637B404 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 D109E43F85 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 20 Jul 2003 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-database@FreeBSD.ORG Sun Jul 20 11:26:52 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 20 Jul 2003 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-database@FreeBSD.ORG Sun Jul 20 13:53:41 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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 cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 20 Jul 2003 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-database@FreeBSD.ORG Mon Jul 21 05:06:49 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 6A4B337B404 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 74EDD43FB1 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Mon Jul 21 12:26:46 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Mon Jul 21 12:26:50 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 104F337B404 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 80F7143FAF 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Mon Jul 21 12:45:21 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 7075E37B404 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 1402243F85 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Mon Jul 21 13:16:49 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Mon Jul 21 13:24:02 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 90E5A37B404 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 1988143F93 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Mon Jul 21 13:27:52 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0EC9C37B401; Mon, 21 Jul 2003 13:27:52 -0700 (PDT) Received: from mail.nsnpt.navy.mil (mail.nsnpt.navy.mil [205.70.65.3]) by mx1.FreeBSD.org (Postfix) with ESMTP id 33FF143F85; Mon, 21 Jul 2003 13:27:51 -0700 (PDT) (envelope-from HelpDesk@nsnpt.navy.mil) Received: by MAIL with Internet Mail Service (5.5.2653.19) id ; Mon, 21 Jul 2003 16:27:50 -0400 Message-ID: <549CB3C0EA63D711B58F00508B139A512B4BAE@MAIL> From: "Help Desk IT (NSNPT N6N)" To: "'Jim C. Nasby'" , Sean Chittenden Date: Mon, 21 Jul 2003 16:27:48 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="iso-8859-1" cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Jason Stone Subject: RE: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 21 Jul 2003 20:27:52 -0000 We have been getting several emails at this address and when I sent a message back to the unsubscribe email address at the bottom it told me that helpdesk@nsnpt.navy.mil is not part of your list. I don't know how we're getting this but can you please make sure we are taken off this list immediately. Sincerely, John Eastwood IT Dept. Naval Station Newport, RI -----Original Message----- From: Jim C. Nasby [mailto:jim@nasby.net] Sent: Monday, July 21, 2003 4:24 PM To: Sean Chittenden Cc: freebsd-database@freebsd.org; freebsd-performance@freebsd.org; Jason Stone Subject: Re: Tuning for PostGreSQL Database 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?" _______________________________________________ freebsd-database@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-database To unsubscribe, send any mail to "freebsd-database-unsubscribe@freebsd.org" From owner-freebsd-database@FreeBSD.ORG Mon Jul 21 20:35:44 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id BAD3737B404 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 D746643F85 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Tue Jul 22 08:37:49 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Tue Jul 22 08:59:03 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Tue Jul 22 09:02:30 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 8A41637B407; Tue, 22 Jul 2003 09:02:30 -0700 (PDT) Received: from mail.nsnpt.navy.mil (mail.nsnpt.navy.mil [205.70.65.3]) by mx1.FreeBSD.org (Postfix) with ESMTP id 668C043FB1; Tue, 22 Jul 2003 09:02:26 -0700 (PDT) (envelope-from HelpDesk@nsnpt.navy.mil) Received: by MAIL with Internet Mail Service (5.5.2653.19) id ; Tue, 22 Jul 2003 12:02:24 -0400 Message-ID: <549CB3C0EA63D711B58F00508B139A512B4BB0@MAIL> From: "Help Desk IT (NSNPT N6N)" To: 'Bosko Milekic' , Dan Langille Date: Tue, 22 Jul 2003 12:02:17 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="iso-8859-1" cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Jason Stone Subject: RE: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 22 Jul 2003 16:02:32 -0000 I had asked yesterday to stop receiving these messages because I followed the directions about removing our address from your list and your system tells me we're not part of it. Pleasew remove us as soon as possible. R/ John -----Original Message----- From: Bosko Milekic [mailto:bmilekic@technokratis.com] Sent: Tuesday, July 22, 2003 8:03 AM To: Dan Langille Cc: freebsd-database@freebsd.org; freebsd-performance@freebsd.org; Jason Stone Subject: Re: Tuning for PostGreSQL Database 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/ _______________________________________________ freebsd-database@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-database To unsubscribe, send any mail to "freebsd-database-unsubscribe@freebsd.org" From owner-freebsd-database@FreeBSD.ORG Tue Jul 22 09:06:58 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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 cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Jason Stone Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Tue Jul 22 09:23:29 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Tue Jul 22 09:31:58 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Tue Jul 22 11:34:55 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 32D0037B401 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 73E1843FAF 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/) cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Tue Jul 22 11:55:28 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 22 Jul 2003 18:55:29 -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-database@FreeBSD.ORG Tue Jul 22 12:19:41 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Tue Jul 22 13:56:51 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 9469237B404 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 53F1B43F93 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/) cc: freebsd-database@freebsd.org cc: freebsd-performance@freebsd.org cc: Paul Pathiakis Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 22 Jul 2003 20:56:52 -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-database@FreeBSD.ORG Tue Jul 22 23:23:07 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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: Paul Pathiakis Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Wed Jul 23 23:57:08 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Thu Jul 24 10:39:15 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id EA18F37B404 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 1781F43FDD 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 Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Fri Jul 25 00:15:27 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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 Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Fri Jul 25 01:55:58 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Fri Jul 25 09:26:07 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 1228637B404 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 C6CF843F85 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: Paul Pathiakis cc: Sean Chittenden cc: freebsd-performance@freebsd.org cc: Terry Lambert Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Fri Jul 25 10:06:17 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 9D72F37B401 for ; Fri, 25 Jul 2003 10:06:17 -0700 (PDT) Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150]) by mx1.FreeBSD.org (Postfix) with ESMTP id 137AA43F75 for ; Fri, 25 Jul 2003 10:06:17 -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 DF9833D28; Fri, 25 Jul 2003 13:06:15 -0400 (EDT) From: "Dan Langille" To: Christopher Weimann Date: Fri, 25 Jul 2003 13:06:15 -0400 MIME-Version: 1.0 Message-ID: <3F212B47.12980.6274A447@localhost> Priority: normal In-reply-to: <20030725162601.GA35378@smtp.k12us.com> References: 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 Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 25 Jul 2003 17:06:17 -0000 On 25 Jul 2003 at 12:26, Christopher Weimann wrote: > So I have conflicting documentation. > > I have machine with 4Gig of ram. What is the maximum > value of SHMMAX on FreeBSD? Many of the PostgreSQL team run FreeBSD. I think you should ask there. -- Dan Langille : http://www.langille.org/ From owner-freebsd-database@FreeBSD.ORG Fri Jul 25 11:25:59 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: 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-database@FreeBSD.ORG Fri Jul 25 21:22:24 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 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-database@FreeBSD.ORG Fri Jul 25 23:35:04 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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: Paul Pathiakis cc: Sean Chittenden cc: freebsd-performance@freebsd.org Subject: Re: Tuning for PostGreSQL Database X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 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-database@FreeBSD.ORG Fri Jul 25 23:48:38 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 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-database@FreeBSD.ORG Sat Jul 26 00:19:25 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 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-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 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