#37 merge SQL queries for alias domains

open
nobody
None
5
2013-12-01
2008-05-26
No

From POSTFIX_CONF.txt:

virtual_alias_maps =
proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf,
proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_maps.cf,
proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_catchall_maps.cf

virtual_mailbox_maps =
proxy:mysql:/etc/postfix/sql/mysql_virtual_mailbox_maps.cf,
proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_mailbox_maps.cf

This means MySQL is called twice for mailboxes and three times for aliases (except if the first try returns a result, of course).

If possible, we should merge the queries so that alias and mailbox queries always only need one map.

SELECT ... UNION SELECT ... UNION SELECT ...
doesn't look very nice, but it has a better performance than doing two or three separate queries.

Or is there a special reason why we should do separate queries?

Discussion

  • Guido Boehm

    Guido Boehm - 2008-06-08

    Logged In: YES
    user_id=142401
    Originator: NO

    hi christian,

    after re-thinking the way postfix does lookups it appears to me it already does run 2 queries per item in 'virtual_alias_maps':

    - a first try for exact matches (looking up the key 'bob@example.com')
    - if the first try didn't give any results a second try for catchall (looking up the key '@example.com')

    based on this assumption i created the following query which should be able to replace the 3 virtual_alias_maps-queries in the current setup:

    SELECT alias.goto
    FROM alias
    LEFT JOIN alias_domain ON alias_domain.alias_domain = '%d'
    WHERE alias.active = 1
    AND (
    alias.address='%s'
    OR
    (alias_domain.alias_domain='%d' AND alias.address=CONCAT('%u','@',alias_domain.target_domain))
    )

    although i did run a set of test-queries against my own database which looked promising i don't have the time to extensivly test it inside my postfix-setup right now. i'll try to do this next week and keep you updated.

    thanks for your patience, lenix

     
  • Christian Boltz

    Christian Boltz - 2008-06-08
    • milestone: --> SVN_(please_specify_revision!)
     
  • Christian Boltz

    Christian Boltz - 2008-06-08

    Logged In: YES
    user_id=593261
    Originator: YES

    To make things more interesting: We still allow to have separate aliases on a domain that is defined as alias domain. This is not really bad (and might even be useful in some cases), but it might lead to interesting ;-) setups.

    For example, there could be the following setup:

    alias domain: foo.de -> bar.de
    alias: user@bar.de -> user-bar@example.com
    alias: user@foo.de -> user-foo@example.com

    In this case, I would expect that mails sent to user@foo.de are forwarded to user-foo@, not user-bar@. With the separate queries, it should work - but I'm not sure about the merged one.

    Please add such a setup to your testcase ;-)

    If it turns out that merging the queries is too complicated, creating a function inside MySQL could be an option for those who want better performance. Users who don't want to do this (or can't because they still use MySQL 4) could still use the separated queries.

     
  • Guido Boehm

    Guido Boehm - 2008-06-08

    Logged In: YES
    user_id=142401
    Originator: NO

    with the query i mentioned your testcase would result in 2 rows being returned.
    at least for me the first result was always the one where
    alias.address='%s'
    matched, so one could just append "LIMIT 1", but i'm not sure how consistent
    this is, maybe there's some mysql-professional who could tell?

    // lenix

     
  • Guido Boehm

    Guido Boehm - 2008-06-08

    Logged In: YES
    user_id=142401
    Originator: NO

    with the query i mentioned your testcase would result in 2 rows being returned.
    at least for me the first result was always the one where
    alias.address='%s'
    matched, so one could just append "LIMIT 1", but i'm not sure how consistent
    this is, maybe there's some mysql-professional who could tell?

    // lenix

     
  • Christian Boltz

    Christian Boltz - 2008-06-09

    Logged In: YES
    user_id=593261
    Originator: YES

    I'd say I know MySQL quite good, but don't consider myself to be an expert ;-)

    I would not rely on the sort order and do a LIMIT 1. Unless we specify an ORDER BY, the results are probably sorted by creation time, which is as acceptable as ORDER BY random()...

    In theory we could use something like
    SELECT goto, 1 as rank FROM alias UNION SELECT ... AS goto, 2 as rank ORDER BY rank LIMIT 1
    but it has some disadvantage:
    * It will return an additional column to postfix, and I have no idea how postfix behaves in this case. (Additionally the query above is untested, but I hope it should work this way.)
    * We have a feature request to split the goto field to one address per row, which means there could be multiple results (I hope the requester tested the postfix behaviour, I didn't ;-) - LIMIT 1 would be deadly after this change.

    I'm afraid the only clean solution is doing this inside MySQL. This reduces the traffic between Postfix and MySQL to a single query. For some example SQL code, see
    https://listi.jpberlin.de/pipermail/postfixbuch-users/2007-November/039627.html

    People who use older MySQL versions (MySQL 4 and older) could still use separate queries.

     
  • Guido Boehm

    Guido Boehm - 2008-06-10

    Logged In: YES
    user_id=142401
    Originator: NO

    hi christian,

    the following modified query would ensure that we always get the desired row:

    SELECT alias.goto
    FROM alias
    LEFT JOIN alias_domain ON alias_domain.alias_domain = '%d' AND alias.domain = alias_domain.target_domain
    WHERE alias.active = 1
    AND (
    alias.address='%s'
    OR
    (alias_domain.alias_domain='%d' AND alias.address=CONCAT('%u','@',alias_domain.target_domain))
    )
    ORDER BY alias_domain.alias_domain ASC
    LIMIT 1

    extending the join-condition with
    "AND alias.domain = alias_domain.target_domain"
    will return a NULL-value for the "alias_domain.alias_domain" column
    in a row matched by the condition "alias.address='%s'".

    to explain this with your example (user@foo.de -> user-foo@example.com):

    there would be one row with
    alias.address='user@foo.de',
    alias.domain='foo.de'
    alias_domain.alias_domain=NULL
    alias_domain.target_domain=NULL

    for this row alias_domain.alias_domain='foo.de' would be matched
    by the first ON-condition, but alias_domain.target_domain='bar.de' is
    != alias.domain, so no join and therefor NULL-values in alias_domain.*

    next to this there would be another row with
    alias.address='user@bar.de'
    alias.domain='bar.de'
    alias_domain.alias_domain='foo.de'
    alias_domain.target_domain='bar.de'

    since the NULL-value orders in front of 'foo.de', "LIMIT 1" will return
    only the first row, which is the one we're looking for.

    i already studied the feature-request you mentioned, but as long as wietse/
    the postfix-docs don't recommend to return multiple rows for alias-lookups i
    would personally advice against doing so. of course you're right that this
    query wouldn't work with such a configuration.

    i'll have a look at "virtual_mailbox_maps" and do some testing inside
    postfix the next days :-)

    sorry for posting the last message twice btw.

    // lenix

     
  • Guido Boehm

    Guido Boehm - 2008-06-10

    Logged In: YES
    user_id=142401
    Originator: NO

    hi christian,

    i did some testing with the new query inside postfix and had to do
    some further modifications.

    http://www.postfix.org/mysql_table.5.html states that
    %u When the input key is an address of the form
    user@domain, %u is replaced by the SQL
    quoted local part of the address. Other-
    wise, %u is replaced by the entire search
    string. If the localpart is empty, the
    query is suppressed and returns no results.

    that's why i had to get "%u" and "%d' manually using
    SUBSTR_INDEX(), else postfix would suppress the catchall-lookup
    (with the empty %u part)

    next to this there's a note that
    This parameter is available with Postfix 2.2. In
    prior releases the SQL query was built from the
    separate parameters: select_field, table,
    where_field and additional_conditions. The mapping
    from the old parameters to the equivalent query is:
    so our current setup is >= postfix 2.2 compatible.

    the final query installed on my system now looks like this:

    SELECT alias.goto
    FROM alias
    LEFT JOIN alias_domain ON
    alias_domain.alias_domain = SUBSTRING_INDEX( '%s', '@', -1 )
    AND alias.domain = alias_domain.target_domain
    WHERE alias.active =1
    AND ( alias.address = '%s'
    OR ( alias_domain.alias_domain = SUBSTRING_INDEX( '%s', '@', -1 )
    AND alias.address = CONCAT( SUBSTRING_INDEX( '%s', '@', 1 ), '@', alias_domain.target_domain )
    )
    )
    ORDER BY alias_domain.alias_domain ASC
    LIMIT 1

    // lenix

     

Log in to post a comment.