On Sat, Aug 09, 2008 at 03:36:07PM +0300, Ibrahim Harrani wrote: > Hello, > > I am running dspam with pgsql. > How can I calculate the best value for PgSQLConnectionCache > parameter? It is 10 by default. > and what can I do the increase performance of dspam_token_data table? > It is getting larger.. > > # SELECT count(*) from dspam_token_data ; > count > -------- > 852800 > (1 row) > > > Hi,
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. Second, you need to be running PostgreSQL 8.3. Third, you need to setup your tables/indexes to use a < 100% fill-factor, this will allow HOT updates to work. Fourth, cluster your token table on userid, this will localize your disk I/O and HOT will keep it localized. 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. 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 !DSPAM:1011,489daf71150922053712648!
