X-Original-To: pgsql-performance@postgresql.org Received: from perrin.int.nxad.com (unknown [69.1.70.251]) by postgresql.org (Postfix) with ESMTP id D724D474E42 for ; Sun, 16 Mar 2003 03:21:03 -0500 (EST) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 34DBC2107E; Sun, 16 Mar 2003 00:20:24 -0800 (PST) Date: Sun, 16 Mar 2003 00:20:24 -0800 From: Sean Chittenden To: Aaron Krowne Cc: pgsql-performance@postgresql.org Subject: Re: postgresql meltdown on PlanetMath.org Message-ID: <20030316082024.GA66903@perrin.int.nxad.com> References: <20030316060125.GD19570@vt.edu> <20030316061208.GA62529@perrin.int.nxad.com> <20030316080601.GF19570@vt.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030316080601.GF19570@vt.edu> User-Agent: Mutt/1.4i 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/ X-Archive-Number: 200303/105 X-Sequence-Number: 1403 > > > - Lastly, I tried reducing the shared memory max and limiting postgresql > > > to more conservative values, although still not to the out-of-box > > > values. Right now shared memory max on the system is 128mb, > > > postgres's shared buffers are at 64mb, sort_mem is at 16mb, and > > > effective cache size is at 10mb. > > *) You shouldn't have to do this either. > > Well, I've now been advised that the best way is all 3 that I have > tried (among aggressive buffering, moderate buffering, and default > conservative buffering). > > Perhaps you could explain to me why the system shouldn't be ok with > the moderate set of buffer sizes on a 512mb machine? I don't really > know enough about the internals of postgres to be doing anything but > voodoo when I change the values. Honestly? The defaults are small, but they're not small enough to give you the lousy performance you were describing. If your buffers are too high or there are enough things that are using up KVM/system memory... contention can cause thashing/swapping which it wasn't clear that you weren't having happen. Defaults shouldn't, under any non-embedded circumstance cause problems with machines >233Mhz, they're just too conservative to do any harm. :) > > *) Have you done a vacuum analyze? > > See previous message to list (summary: it worked this time, but > usually it does not help.) Hrmm... ENOTFREEBSD, eh? http://www.freebsd.org/cgi/cvsweb.cgi/ports/databases/postgresql7/files/502.pgsql?rev=1.5&content-type=text/x-cvsweb-markup You may want to setup a nightly vacuum/backup procedure. Palle Girgensohn has written a really nice and simple script that's been in use for ages on FreeBSD PostgreSQL installations for making sure that you don't have this problem. Actually, it'd be really cool to lobby to get this script added to the base PostgreSQL installation that way you wouldn't have this problem... it'd also dramatically increase the number of nightly backups performed for folks if a default script does this along with vacuuming. -sc -- Sean Chittenden