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))'.
')';
$sql_select_white_black_list =
'SELECT wb '.
'FROM amavis_wblist '.
'WHERE recipient_id=? '.
'AND addr IN (%k)';
The DB layout is like this: one account can have multiple usernames
(aliases), one hosting can have multiple domains, one hosting can have
multiple accounts.
that means, that we don't have a email address field. so we have to
merge (concat) domainname and username field to check the %k strings.
we also need per recipient email address and domain lookup, not just
email address. because we also do something like email proxy, where we
don't know the usernames, just the domains for a customer.
the query takes about 8 seconds with 3500 usernames and 400 domains.
is there a way to change the query like this (separate variables in
the query for domain and username)?
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 username='<<<<username>>>>' and
domainname='<<<<domain>>>>') OR ((is_transport=1 OR is_mxbackup=1) AND
domainname='<<<<domain>>>>'));
this query is executed in ~0.04 sec... what would be okay...
thanks for any ideas...
thanks and best regards,
Marco
------------------------------------------------------------------------------
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
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
