X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F18F23A51FB for ; Mon, 29 Nov 2004 21:51:07 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93609-01 for ; Mon, 29 Nov 2004 21:50:56 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by svr1.postgresql.org (Postfix) with ESMTP id 754A23A51E1 for ; Mon, 29 Nov 2004 21:50:57 +0000 (GMT) Received: by flake.decibel.org (Postfix, from userid 1001) id A29931C8FD; Mon, 29 Nov 2004 15:50:56 -0600 (CST) Date: Mon, 29 Nov 2004 15:50:56 -0600 From: "Jim C. Nasby" To: Andrew McMillan Cc: Evilio del Rio , dspam-users@networkdweebs.com, pgsql-performance@postgresql.org Subject: Re: Postgres vs. DSpam Message-ID: <20041129215056.GZ41545@decibel.org> References: <1101302058.338.74.camel@haddock.cmima.csic.es> <1101433032.16058.57.camel@lamb.mcmillan.net.nz> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1101433032.16058.57.camel@lamb.mcmillan.net.nz> X-Operating-System: FreeBSD 4.10-RELEASE-p3 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/417 X-Sequence-Number: 9371 FWIW, those queries won't be able to use an index. A better WHERE clause would be: AND last_hit < CURRENT_DATE - 60 On Fri, Nov 26, 2004 at 02:37:12PM +1300, Andrew McMillan wrote: > On Wed, 2004-11-24 at 14:14 +0100, Evilio del Rio wrote: > > Hi, > > > > I have installed the dspam filter > > (http://www.nuclearelephant.com/projects/dspam) on our mail server > > (RedHat 7.3 Linux with sendmail 8.13 and procmail). I have ~300 users > > with a quite low traffic of 4000 messages/day. So it's a quite common > > platform/environment, nothing spectacular. > > I am using DSpam with PostgreSQL here. I have a daily job that cleans > the DSpam database up, as follows: > > DELETE FROM dspam_token_data > WHERE (innocent_hits*2) + spam_hits < 5 > AND CURRENT_DATE - last_hit > 60; > > DELETE FROM dspam_token_data > WHERE innocent_hits = 1 > AND CURRENT_DATE - last_hit > 30; > > DELETE FROM dspam_token_data > WHERE CURRENT_DATE - last_hit > 180; > > DELETE FROM dspam_signature_data > WHERE CURRENT_DATE - created_on > 14; > > VACUUM dspam_token_data; > > VACUUM dspam_signature_data; > > > > I also occasionally do a "VACUUM FULL ANALYZE;" on the database as well. > > > In all honesty though, I think that MySQL is better suited to DSpam than > PostgreSQL is. > > > > Please, could anyone explain me this difference? > > Is Postgres that bad? > > Is MySQL that good? > > Am I the only one to have observed this behavior? > > I believe that what DSpam does that is not well-catered for in the way > PostgreSQL operates, is that it does very frequent updates to rows in > (eventually) quite large tables. In PostgreSQL the UPDATE will result > internally in a new record being written, with the old record being > marked as deleted. That old record won't be re-used until after a > VACUUM has run, and this means that the on-disk tables will have a lot > of dead rows in them quite quickly. > > The reason that PostgreSQL operates this way, is a direct result of the > way transactional support is implemented, and it may well change in a > version or two. It's got better over the last few versions, with things > like pg_autovacuum, but that approach still doesn't suit some types of > database updating. > > Cheers, > Andrew. > ------------------------------------------------------------------------- > Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St > DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 > These PRESERVES should be FORCE-FED to PENTAGON OFFICIALS!! > ------------------------------------------------------------------------- > -- Jim C. Nasby, Database Consultant decibel@decibel.org 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?"