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

[snowfly]

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:

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:

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.