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. Greg Smith, the author of the above mentioned pgtune, is also the author of a really good book just published called PostgreSQL 9.0 High Performance published by Packt. https://www.packtpub.com/postgresql-9-0-high-performance/book It is available as a hardcover or as an eBook (standard PDF). There is a sample chapter from the book on database hardware available https://www.packtpub.com/sites/default/files/0301OS-Chapter-2-Database-Hardware.pdf If you setup a customer account with Packt and then login before purchasing the price is really discounted--I got this and another new e-book (PostgreSQL 9 Administration Cookbook) for about US$12. Standard disclaimer: I have no relation to any of these folks. Jeff ------------------------------------------------------------------------------ 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
