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 73F78474E53 for ; Mon, 17 Mar 2003 01:10:17 -0500 (EST) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 431AF20F01; Sun, 16 Mar 2003 22:10:11 -0800 (PST) Date: Sun, 16 Mar 2003 22:10:11 -0800 From: Sean Chittenden To: Logan Bowers Cc: pgsql-performance@postgresql.org Subject: Re: postgresql meltdown on PlanetMath.org Message-ID: <20030317061011.GH23355@perrin.int.nxad.com> References: <20030316060125.GD19570@vt.edu> <3E741B93.1090203@joeconway.com> <20030316075206.GE19570@vt.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: 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/112 X-Sequence-Number: 1410 > I don't know what your definition of "high" is, but I do find that > turnover can degrade performance over time. Perhaps one of the devs > can enlighten me, but I have a database that turns over ~100,000 > rows/day that does appear to slowly get worse. The updates are done > in batches and I "VACUUM" and "VACUUM ANALYZE" after each batch > (three/day) but I found that over time simple queries would start to > hit the disk more and more. Creeping index syndrome. Tom recently fixed this in HEAD. Try the latest copy from the repo and see if this solves your problems. > A "select count(*) FROM tblwordidx" initially took about 1 second to > return a count of 2 million but after a few months it took several > minutes of really hard HDD grinding. That's because there are dead entries in the index that weren't being reused or cleaned up. As I said, this has been fixed. -sc PS It's good to see you around again. :) -- Sean Chittenden