On 09/02/2010 09:34 AM, Marco Fretz wrote:
> Hi,
>
> We build a clustered mail system with postfix, amavisd, clamd, SA,
> dovecot, mysql, etc. 2 Servers are handling incoming mail (MX records
> pointing to us) and 2 are handling outgoing mail (relaying). mysql
> database is stored on 2 master-master replicated servers.
>
> so far, everything is working well. but we have problems with the
> sql_lookup from amavisd-new that we need to lookup per recipient
> whitelisting.
>
> $sql_select_policy = 'select amavis_policy.*,amavis_recipients.id as id '.
> 'FROM (hostings,domains,amavis_recipients,products,amavis_policy)
> LEFT JOIN (users,accounts) '.
> 'ON accounts.hosting_id=hostings.id AND users.account_id=accounts.id
> '.
> 'WHERE (amavis_recipients.hosting_id = hostings.id OR
> amavis_recipients.account_id = accounts.id) '.
> 'AND domains.hosting_id=hostings.id '.
> 'AND hostings.product_id = products.id '.
> 'AND hostings.amavis_policy_id = amavis_policy.id '.
> 'AND ('.
> ' (is_mailbox=1 AND concat(username,\'@\',domainname) IN (%k))
> '.
> ' OR ((is_transport=1 OR is_mxbackup=1) AND
> concat(\'@\',domainname) IN (%k))'.
> ')';
1. Why are you joining products?
2. Create indices on any column appearing in the query with a lot of
records or that is used in a join. Dump the database to your workstation
and play around there.
------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:
Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
AMaViS-user mailing list
[email protected]
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
