Hi Kenneth,
Thanks for your prompt reply.
My answers are inline
> We have been running DSPAM since the 3.2 days and have wanted to use
> PostgreSQL instead of MySQL for many reasons. Both DSPAM and PostgreSQL
> had performance issues that needed to be addressed for this combination
> to work well in a high load environment. First, you need to be running
> the latest version of DSPAM, in particular the PG driver should define
> two functions for lookup_token, one with 1 arg and a second with 2 args.
I am using cvs version.
> Second, you need to be running PostgreSQL 8.3.
# SELECT version();
version
-------------------------------------------------------------------------------------------------
PostgreSQL 8.3.0 on amd64-portbld-freebsd6.3, compiled by GCC cc
(GCC) 3.4.6 [FreeBSD] 20060305
(1 row)
Third, you need to
> setup your tables/indexes to use a < 100% fill-factor, this will allow
> HOT updates to work.
# ALTER TABLE dspam_token_data SET ( FILLFACTOR = 100 );
Fourth, cluster your token table on userid, this
> will localize your disk I/O and HOT will keep it localized.
# CLUSTER dspam_token_data USING dspam_token_data_uid_key ;
I think I should CLUSTER the table regularly right? like following:
=# CLUSTER dspam_token_data ;
Fifth and
> last, you need to remove any index on the fields that are to be updated
> in the token table. A typical on is on the spam count + not spam count
> to help expire tokends. If an index exists, then HOT will not be able
> to function.
Fifth is not clear?
# \d dspam_token_data
Table "public.dspam_token_data"
Column | Type | Modifiers
---------------+----------+-----------
uid | smallint |
token | bigint |
spam_hits | integer |
innocent_hits | integer |
last_hit | date |
Indexes:
"dspam_token_data_uid_key" UNIQUE, btree (uid, token) CLUSTER
"id_token_data_sumhits" btree ((spam_hits + innocent_hits))
Are you talking about "id_token_data_sumhits" index on the table?
Should I drop it?
> Please let me know if you have any questions. At the very least, you
> can perform a CLUSTER of the token table against the id column and
> your performance should improve due to better I/O localization.
>
> Cheers,
> Ken
>
Best Regards
!DSPAM:1011,489db589150921184912892!
