Rok,
> Finally figured out what was the problem - penpals. Apparently that part
> of the code isn't optimised for my kind of usage usage (pgsql backend,
> table partitions, ...), I'm not quite sure why, but after disabling
> penpals on all the gateways (~8 server) the load on the SQL server
> dropped from 6 to 3 and there were no errors ever since... For now it'll
> be fine, guess we'll have to do the db redesign sometime soon :S
Btw, which version of PostgreSQL was that?
Check your %sql_clause settings, the current default is:
'sel_penpals' => # no message-id references list
"SELECT msgs.time_num, msgs.mail_id, subject".
" FROM msgs JOIN msgrcpt USING (partition_tag,mail_id)".
" WHERE sid=? AND rid=? AND msgs.content!='V' AND ds='P'".
" ORDER BY msgs.time_num DESC", # LIMIT 1
'sel_penpals_msgid' => # with a nonempty list of message-id references
"SELECT msgs.time_num, msgs.mail_id, subject, message_id, rid".
" FROM msgs JOIN msgrcpt USING (partition_tag,mail_id)".
" WHERE sid=? AND msgs.content!='V' AND ds='P' AND message_id IN (%m)".
" AND rid!=sid".
" ORDER BY rid=? DESC, msgs.time_num DESC", # LIMIT 1
The $sql_clause{sel_penpals used} used to have a coalesce() function in
the expression, which had painful effects on query times. It is gone now
in 2.7.0-pre (not sure exactly in which pre-release I dropped it).
You could try manually analyzing how a penpals query is evaluated
by SQL, which may shed some light. Are you using weekly partition tags?
Perhaps adding some index may help.
Mark
------------------------------------------------------------------------------
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
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
Please visit http://www.ijs.si/software/amavisd/ regularly
For administrativa requests please send email to rainer at openantivirus dot
org
