On Fri, Nov 19, 2010 at 10:21:53AM -0700, Jeff Ross wrote: > On 11/18/10 23:01, Julien Vehent wrote: > > On 11/18/2010 05:32 PM, Jeff Ross wrote: > >> On 11/18/10 01:18, Julien Vehent wrote: > >>> Hi all, > >>> > >>> I've been using SPBH and Postgresql and my setup is rather slow. It > >>> seems that postgresql is eating most of the CPU time of the machine, > >>> even so I don't receive more than 300 messages a day on that setup. > >>> > >>> I check the logs of postgres and I have files filled with lines like this > >>> > >>> ---- > >>> 2010-11-14 07:42:25 CET STATEMENT: PREPARE dspam_update_plan (bigint) > >>> AS UPDATE dspam_token_data SET > >>> last_hit=CURRENT_DATE,innocent_hits=innocent_hits+1 WHERE uid=1 AND > >>> token=$1;PREPARE dspam_insert_plan (bigint,int,int) AS INSERT INTO > >>> dspam_token_data (uid,token,spam_hits,innocent_hits,last_hit) VALUES > >>> (1,$1,$2,$3,CURRENT_DATE); > >>> > >>> 2010-11-14 07:42:25 CET ERROR: duplicate key value violates unique > >>> constraint "dspam_token_data_uid_key" > >>> ---- > >> > >> I'm not running 3.9.1 but I do get some similar errors. It appears to me > >> that dspam first tries to INSERT and counts on the constraint to throw > >> an error if the INSERT will duplicate an existing record. It then runs > >> an UPDATE instead. Is the log snip above follewed by an UPDATE statement > >> that succeeds? > >> > > > > Does that mean I can safely ignore it ? > > > > Yes. > > >>> What is this ? Could it cause a performance problem ? > >>> > >>> Also, I use a basic Debian setup for postgres, is there any optimization > >>> I can do to the postgres conf to speed things up ? > >>> > >>> > >>> Thanks, > >>> > >>> Julien > >> > >> Postgresql is by default installed with a minimal configuration. There > >> are tuning guides available but the handiest is called pgtune. > >> > >> http://pgfoundry.org/projects/pgtune/ > >> > >> Hope that helps! > >> > >> Jeff Ross > > > > Interesting, I will give it a shot. > > I increased my shared memory to 256MB, seems to be better now, but more > > tuning/testing is definitely needed (something I would probably add to > > the doc when I publish it). > > So if you guys have experience tuning pg for dspam, I'm interested. > > > > Following the pgtune suggestions will get you a lot of boost with the > hardware you've got. I run dspam (3.8.0) on three mail servers but I > only use PostgreSQL as the backend for one, the most recent installed. > I'm getting ready to upgrade all three to the latest dspam and I'll > switch the other two to postgres then. Prior to 3.8.0, the postgres > backend just wasn't very well implemented. I'm looking forward to > seeing how the latest does. >
Here are a couple of other things that you can do to boost your performance. First, create your dspam_token_data with a fill factor less than 100%. This will enable HOT updates which will preserve the locality of reference for your selects and updates. Otherwise, the table I/O will become more and more random over time. Second, once you have trained your system appropriately, cluster the dspam_token_table on the primary key index. This will place all of a user's tokens close to each other which will result in fewer I/O's needed to find the proper tokens. Cheers, Ken ------------------------------------------------------------------------------ Beautiful is writing same markup. Internet Explorer 9 supports standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3. Spend less time writing and rewriting code and more time creating great experiences on the web. Be a part of the beta today http://p.sf.net/sfu/msIE9-sfdev2dev _______________________________________________ Dspam-user mailing list Dspam-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspam-user
