pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Postgres vs. DSpam
3+ messages / 3 participants
[nested] [flat]
* Re: Postgres vs. DSpam
@ 2004-11-26 01:37 Andrew McMillan <andrew@catalyst.net.nz>
0 siblings, 2 replies; 3+ messages in thread
From: Andrew McMillan @ 2004-11-26 01:37 UTC (permalink / raw)
To: Evilio del Rio <edelrio@cmima.csic.es>; +Cc: dspam-users@networkdweebs.com; pgsql-performance
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!!
-------------------------------------------------------------------------
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Postgres vs. DSpam
@ 2004-11-26 03:25 Neil Conway <neilc@samurai.com>
parent: Andrew McMillan <andrew@catalyst.net.nz>
1 sibling, 0 replies; 3+ messages in thread
From: Neil Conway @ 2004-11-26 03:25 UTC (permalink / raw)
To: Andrew McMillan <andrew@catalyst.net.nz>; +Cc: Evilio del Rio <edelrio@cmima.csic.es>; dspam-users@networkdweebs.com; pgsql-performance
On Fri, 2004-11-26 at 14:37 +1300, Andrew McMillan wrote:
> 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.
Not necessarily: yes, you need a VACUUM to begin reusing the space
consumed by expired tuples, but that does not mean "tables will have a
lot of dead rows in them quite quickly". VACUUM does not block
concurrent database activity, so you can run it as frequently as you'd
like (and as your database workload requires). There is a tradeoff
between the space consumed by expired tuple versions and the I/O
required to do a VACUUM -- it's up to the PG admin to decide what the
right balance for their database is (pg_autovacuum et al. can help make
this decision).
> 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.
I doubt it.
-Neil
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Postgres vs. DSpam
@ 2004-11-29 21:50 Jim C. Nasby <decibel@decibel.org>
parent: Andrew McMillan <andrew@catalyst.net.nz>
1 sibling, 0 replies; 3+ messages in thread
From: Jim C. Nasby @ 2004-11-29 21:50 UTC (permalink / raw)
To: Andrew McMillan <andrew@catalyst.net.nz>; +Cc: Evilio del Rio <edelrio@cmima.csic.es>; dspam-users@networkdweebs.com; pgsql-performance
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?"
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-29 21:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-26 01:37 Re: Postgres vs. DSpam Andrew McMillan <andrew@catalyst.net.nz>
2004-11-26 03:25 ` Neil Conway <neilc@samurai.com>
2004-11-29 21:50 ` Jim C. Nasby <decibel@decibel.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox