Multiple MySQL Queries - with Postfix-mysql, courier-authmysql, maildrop-mysql...

Discussion in 'HOWTO-Related Questions' started by snowfly, Jul 5, 2006.

  1. snowfly

    snowfly Member

    Hi,

    Ok I've been successfully running an email server for the past 2 years using the following:

    - Debian Sarge
    - 2.4.27 smp kernel
    - courier-pop, courier-imap, courier-authmysql
    - postfix-mysql, postfix-tls
    - maildrop, maildropmysql
    - spamassassin
    - clamav
    - amavisd

    Basically it handles multiple domains, for virtual users, virus/spam protection, etc.

    Now I've just been doing some cleaning up everything, and trying to get it run more efficiently, as I'm going to be setting up a new server soon.

    I was looking at the mysql.log (/var/mysql/mysql.log), that I had turned on, and interesting was seeing multiple queiries for every email that the server received.

    Here's an example, I was sending an email from an external address, to an address on the server:

    Code:
    060705 13:11:10     111 Query       select remote_address from email_aliases where email = 'xxxxx.co.nz'
                        112 Query       select maildir from email_addresses where email = 'xxxxx.co.nz'
                        113 Query       select transport from email_domains where domain = 'xxxxx.co.nz'
                        113 Query       select transport from email_domains where domain = 'co.nz'
                        113 Query       select transport from email_domains where domain = 'nz'
                        113 Query       select transport from email_domains where domain = 'xx@xxxxx.co.nz'
                        113 Query       select transport from email_domains where domain = 'xxxxx.co.nz'
                        113 Query       select transport from email_domains where domain = '.co.nz'
                        113 Query       select transport from email_domains where domain = '.nz'
                        114 Query       select remote_address from email_aliases where email = 'xx@xxxxx.co.nz'
                        114 Query       select remote_address from email_aliases where email = '@xxxxx.co.nz'
                        115 Query       select remote_address from email_aliases where email = 'xx@xxxxx.co.nz'
                        115 Query       select remote_address from email_aliases where email = '@xxxxx.co.nz'
    And another list of queries 1 second later:
    Code:
    060705 13:11:11     111 Query       select remote_address from email_aliases where email = 'xxxxx.co.nz'
                        112 Query       select maildir from email_addresses where email = 'xxxxx.co.nz'
                        113 Query       select transport from email_domains where domain = 'xxxxx.co.nz'
                        113 Query       select transport from email_domains where domain = 'co.nz'
                        113 Query       select transport from email_domains where domain = 'nz'
                        113 Query       select transport from email_domains where domain = 'xx@xxxxx.co.nz'
                        113 Query       select transport from email_domains where domain = 'xxxxx.co.nz'
                        113 Query       select transport from email_domains where domain = '.co.nz'
                        113 Query       select transport from email_domains where domain = '.nz'
                        118 Query       select remote_address from email_aliases where email = 'xx@xxxxxx.co.nz'
                        118 Query       select remote_address from email_aliases where email = '@xxxxx.co.nz'
                        115 Query       select remote_address from email_aliases where email = 'xx@xxxxxx.co.nz'
                        115 Query       select remote_address from email_aliases where email = '@xxxxxx.co.nz'
                        123 Connect     maildrop@localhost on
                        123 Init DB     xxxxx
                        123 Query       SELECT email, uidnumber, gidnumber, maildir, maildir, quota FROM email_addresses WHERE email = "m
    xx@xxxxx.co.nz" ""
                        123 Quit
    Now why would there be soo many queries, firstly checking the sender email address against the transport table, and then checking each part of the email (before the @, after the @, and the tld's).

    I don't think its maildrop, as there is only 1 mysql query from maildrop, the last one.

    So I'm assuming its something to do with postfix, as before those logs above its connecting as postfix: "Connect postfix@localhost on xxxxx".

    So has anyone seen this before? Multiple queries for just one incoming email?
    Meaning the mysql server is doing far more than it should have to.

    Courier-mysql seems to be ok, as I only get 1 query in the log when I check my email.

    Thanks, any help, advice, or comments would be greatly appreciated.

    Mike.
     
    Last edited: Oct 24, 2008
  2. osanet

    osanet New Member

    Hi

    I used too debian sarge and I have postfix+mysql+spamassassin,amavisd-new,clamav...

    I try running maildrop + mysql but it's not working
    How you compiled maildrop with mysql, from source?

    Can you write your configurations about maildropmysql and mai.cf,master.cf?

    Regards,
    osanet
     
  3. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    I think it's normal. Postfix has to do a lot of lookups to decide where to deliver the mail. As long as you don't see any errors in your mail log I wouldn't worry about it. :)
     
  4. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Can you open a new thread for this?
     
  5. snowfly

    snowfly Member

    Thanks. Well there are no errors, so guess I won't worry about it for now.
    Maybe if I get time I can look into it futher, as it would be nice to get postfix to cut down on mysql queries. Its doing on average 100 per minute with all the email its handling.
     

Share This Page