Many SQL Lookups on outbounding mails

classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

Many SQL Lookups on outbounding mails

Clunk Werclick
Hello.

Postfix is new to me and I have spent many hours of reading and testing.
I do not have much experience to look at things and say they are normal or not.

My Postfix hosts some virtual domains locally, and it also relays some
others to another Postfix. It looks up virtual domains, relay domains
and trasport information from mysql. It is working well, thank you.

One thing I notice in my SQL logs. When I send a mail to an outside
domain - yahoo for example - It makes lots of lookups and they are
confusing me.

First it checks to see if the domain I am sending mail to is hosted locally;
SELECT domain FROM virtual_domains WHERE domain='yahoo.co.uk'
That I understand and it makes sense.

Next it checks to see if the domain I am sending to is a relay domain;
SELECT domain FROM virtual_domains WHERE domain='yahoo.co.uk' AND local=0
That I understand and it makes sense.

Then I get very confused as it seems to break the TLD apart and ask if it is a relay domain for each part;
SELECT domain FROM virtual_domains WHERE domain='.co.uk' AND local=0
and again;
SELECT domain FROM virtual_domains WHERE domain='.uk' AND local=0
This is confusing me and I would like to ask if this is normal? I think it may be, but it did not do this when it checked locally hosted domains.

Next it checks a transport map, which I can understand
SELECT transport FROM virtual_domains WHERE domain='[hidden email]'

But again it breaks each part down.
SELECT transport FROM virtual_domains WHERE domain='yahoo.co.uk'
SELECT transport FROM virtual_domains WHERE domain='.co.uk'
SELECT transport FROM virtual_domains WHERE domain='.uk'

Please may I ask someone to reassure me this is doing the thing that is right.
It seems lots of lookups per message and I'm not sure that mysql will not crash like this
\\\   ///
 {.} {.}
    ..
     O

--
C Werclick .Lot
Technical incompetent
Loyal Order Of The Teapot.

This e-mail and its attachments is intended only to be used as an e-mail
and an attachment. Any use of it for other purposes other than as an
e-mail and an attachment will not be covered by any warranty that may or
may not form part of this e-mail and attachment.

Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Noel Jones-2
Clunk Werclick wrote:
> Hello.
>
> Postfix is new to me and I have spent many hours of reading and testing.
> I do not have much experience to look at things and say they are normal or not.
> ...
> Please may I ask someone to reassure me this is doing the thing that is right.
> It seems lots of lookups per message and I'm not sure that mysql will not crash like this

Yes, normal.  Please see
http://www.postfix.org/postconf.5.html#parent_domain_matches_subdomains

Also see:
http://www.postfix.org/proxymap.8.html

   -- Noel Jones

Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Clunk Werclick
On Tue, 2009-07-21 at 10:39 -0500, Noel Jones wrote:

> Clunk Werclick wrote:
> > Hello.
> >
> > Postfix is new to me and I have spent many hours of reading and testing.
> > I do not have much experience to look at things and say they are normal or not.
> > ...
> > Please may I ask someone to reassure me this is doing the thing that is right.
> > It seems lots of lookups per message and I'm not sure that mysql will not crash like this
>
> Yes, normal.  Please see
> http://www.postfix.org/postconf.5.html#parent_domain_matches_subdomains
:: parent_domain_matches_subdomains =
debug_peer_list,fast_flush_domains,mynetworks,permit_mx_backup_networks,qmqpd_authorized_clients,smtpd_access_maps

Not making sense to me.... ?
>
> Also see:
> http://www.postfix.org/proxymap.8.html
>
>    -- Noel Jones
>
To do - thank you.
--
-----------------------------------------------------------
C Werclick .Lot
Technical incompetent
Loyal Order Of The Teapot.

This e-mail and its attachments is intended only to be used as an e-mail
and an attachment. Any use of it for other purposes other than as an
e-mail and an attachment will not be covered by any warranty that may or
may not form part of this e-mail and attachment.



Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Noel Jones-2
Clunk Werclick wrote:

> On Tue, 2009-07-21 at 10:39 -0500, Noel Jones wrote:
>> Clunk Werclick wrote:
>>> Hello.
>>>
>>> Postfix is new to me and I have spent many hours of reading and testing.
>>> I do not have much experience to look at things and say they are normal or not.
>>> ...
>>> Please may I ask someone to reassure me this is doing the thing that is right.
>>> It seems lots of lookups per message and I'm not sure that mysql will not crash like this
>> Yes, normal.  Please see
>> http://www.postfix.org/postconf.5.html#parent_domain_matches_subdomains
> :: parent_domain_matches_subdomains =
> debug_peer_list,fast_flush_domains,mynetworks,permit_mx_backup_networks,qmqpd_authorized_clients,smtpd_access_maps
>
> Not making sense to me.... ?

When the table type is listed in
parent_domain_matches_subdomains, postfix looks up each
subdomain to see if it's listed.  See the documented "Search
Order" in the man page for the specific feature; access,
transport, etc.

So for example [hidden email] would trigger the
following with a check_sender_access table:
[hidden email]
some.foo.example.com
foo.example.com
example.com
com

   -- Noel Jones
Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Clunk Werclick
On Tue, 2009-07-21 at 12:34 -0500, Noel Jones wrote:

> Clunk Werclick wrote:
> > On Tue, 2009-07-21 at 10:39 -0500, Noel Jones wrote:
> >> Clunk Werclick wrote:
> >>> Hello.
> >>>
> >>> Postfix is new to me and I have spent many hours of reading and testing.
> >>> I do not have much experience to look at things and say they are normal or not.
> >>> ...
> >>> Please may I ask someone to reassure me this is doing the thing that is right.
> >>> It seems lots of lookups per message and I'm not sure that mysql will not crash like this
> >> Yes, normal.  Please see
> >> http://www.postfix.org/postconf.5.html#parent_domain_matches_subdomains
> > :: parent_domain_matches_subdomains =
> > debug_peer_list,fast_flush_domains,mynetworks,permit_mx_backup_networks,qmqpd_authorized_clients,smtpd_access_maps
> >
> > Not making sense to me.... ?
>
> When the table type is listed in
> parent_domain_matches_subdomains, postfix looks up each
> subdomain to see if it's listed.  See the documented "Search
> Order" in the man page for the specific feature; access,
> transport, etc.
>
> So for example [hidden email] would trigger the
> following with a check_sender_access table:
> [hidden email]
> some.foo.example.com
> foo.example.com
> example.com
> com
>
>    -- Noel Jones
What I am not understanding is this is my list:

debug_peer_list,fast_flush_domains,mynetworks,permit_mx_backup_networks,qmqpd_authorized_clients,smtpd_access_maps

I don't understand which 'table type' is in charge of virtual and relay.
It is perhaps not very clear?

--
-----------------------------------------------------------
C Werclick .Lot
Technical incompetent
Loyal Order Of The Teapot.

This e-mail and its attachments is intended only to be used as an e-mail
and an attachment. Any use of it for other purposes other than as an
e-mail and an attachment will not be covered by any warranty that may or
may not form part of this e-mail and attachment.



Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Barney Desmond
2009/7/22 Clunk Werclick <[hidden email]>:
> What I am not understanding is this is my list:
>
> debug_peer_list,fast_flush_domains,mynetworks,permit_mx_backup_networks,qmqpd_authorized_clients,smtpd_access_maps
>
> I don't understand which 'table type' is in charge of virtual and relay.
> It is perhaps not very clear?

It just means that when one of these features is used, it will test
parent domains. Seeing as you haven't shown us the output of `postconf
-n`, we can only guess. I'm going to guess that it's most likely using
mynetworks and smtpd_access_maps.

>> >>> Please may I ask someone to reassure me this is doing the thing that is right.

As Noel said, you should rest assured that postfix is doing exactly
the checks it needs to implement the functionality as documented.

>> >>> It seems lots of lookups per message and I'm not sure that mysql will not crash like this

Who's to say what "a lot" of lookups are? Why do you think mysql will
Just Crash? You're far better off looking at the general load and
responsiveness of your server than checking how many queries mysql is
doing.
Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Clunk Werclick
On Wed, 2009-07-22 at 20:31 +1000, Barney Desmond wrote:

> 2009/7/22 Clunk Werclick <[hidden email]>:
> > What I am not understanding is this is my list:
> >
> > debug_peer_list,fast_flush_domains,mynetworks,permit_mx_backup_networks,qmqpd_authorized_clients,smtpd_access_maps
> >
> > I don't understand which 'table type' is in charge of virtual and relay.
> > It is perhaps not very clear?
>
> It just means that when one of these features is used, it will test
> parent domains. Seeing as you haven't shown us the output of `postconf
> -n`, we can only guess. I'm going to guess that it's most likely using
> mynetworks and smtpd_access_maps.
>
> >> >>> Please may I ask someone to reassure me this is doing the thing that is right.
>
> As Noel said, you should rest assured that postfix is doing exactly
> the checks it needs to implement the functionality as documented.
>
> >> >>> It seems lots of lookups per message and I'm not sure that mysql will not crash like this
>
> Who's to say what "a lot" of lookups are? Why do you think mysql will
> Just Crash? You're far better off looking at the general load and
> responsiveness of your server than checking how many queries mysql is
> doing.
I think perhaps 4-12 queries per message is not optimal?
If server handle 50,000 a day X 12 that is quite a lot? I don't think
it is going to get may fields returned for .co.uk .uk in my database?

I stress much that this is not Postfix, it is my silly configuration of
Postfix. Am learning as I go along so plenty of things wrong probably:

This is output;

postconf -n
alias_database = hash:/etc/postfix/aliases
alias_maps = hash:/etc/postfix/aliases
anvil_rate_time_unit = 60s
body_checks = regexp:/etc/postfix/maps/body_checks
broken_sasl_auth_clients = yes
config_directory = /etc/postfix
disable_vrfy_command = yes
header_checks = regexp:/etc/postfix/maps/header_checks
mail_name = testbox
milter_default_action = accept
mime_header_checks = regexp:/etc/postfix/maps/mime_header_checks
mydestination = testbox localhost
mydomain = wibblywobblyteapot.co.uk
myhostname = testbox.wibblywobblyteapot.co.uk
mynetworks = 127.0.0.0/8
myorigin = $mydomain
queue_directory = /home/mail/email
rbl_reply_maps = hash:/etc/postfix/maps/rbl_reply
relay_domains = mysql:/etc/postfix/mysql/relay_domains.cf
smtpd_banner = $myhostname ESMTP Hello Dolly
smtpd_client_connection_count_limit = 3
smtpd_client_connection_rate_limit = 3
smtpd_client_event_limit_exceptions = 212.202.241.232
smtpd_delay_reject = yes
smtpd_error_sleep_time = 3s
smtpd_hard_error_limit = 10
smtpd_helo_required = yes
smtpd_helo_restrictions = permit_mynetworks permit
smtpd_junk_command_limit = 2
smtpd_milters = unix:/home/mail/email/private/clamav-milter,
unix:/home/mail/email/private/samilter
smtpd_recipient_restrictions = permit_mynetworks
permit_sasl_authenticated
reject_unauth_destinationcheck_recipient_access
hash:/etc/postfix/maps/recipient_checks
reject_unknown_reverse_client_hostname check_sender_access
hash:/etc/postfix/maps/no_from_us        reject_rbl_client
zen.spamhaus.org        permit
smtpd_restriction_classes = LOG
smtpd_sasl_auth_enable = yes
smtpd_sasl_path = private/auth
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = dovecot
smtpd_sender_restrictions = permit_mynetworks
permit_sasl_authenticated
smtpd_soft_error_limit = 5
smtpd_timeout = 30
transport_maps = mysql:/etc/postfix/mysql/transport.cf
unknown_client_reject_code = 550
virtual_alias_maps = mysql:/etc/postfix/mysql/virtual_alias_maps.cf
virtual_gid_maps = static:5000
virtual_mailbox_base = /home/mail/mailbox
virtual_mailbox_domains = mysql:/etc/postfix/mysql/virtual_domains.cf
virtual_mailbox_maps =
mysql:/etc/postfix/mysql/virtual_mailbox_recipients.cf
virtual_uid_maps = static:5000

--
-----------------------------------------------------------
C Werclick .Lot
Technical incompetent
Loyal Order Of The Teapot.

This e-mail and its attachments is intended only to be used as an e-mail
and an attachment. Any use of it for other purposes other than as an
e-mail and an attachment will not be covered by any warranty that may or
may not form part of this e-mail and attachment.



Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Noel Jones-2
Clunk Werclick wrote:
> I think perhaps 4-12 queries per message is not optimal?
> If server handle 50,000 a day X 12 that is quite a lot? I don't think
> it is going to get may fields returned for .co.uk .uk in my database?
>

Postfix does the lookups required to route your mail properly.

> I stress much that this is not Postfix, it is my silly configuration of
> Postfix. Am learning as I go along so plenty of things wrong probably:
>
> This is output;
>
> postconf -n
> relay_domains = mysql:/etc/postfix/mysql/relay_domains.cf

Unless relay_domains changes frequently, better to keep it in
a hash table.  Or just set it explicitly empty if you don't
have any relay_domains.

If you do have some relay_domains, you should also use
relay_recipient_maps to define which recipients are valid.
Failure to do this will eventually get you blacklisted for
backscatter.

> smtpd_client_connection_count_limit = 3
> smtpd_client_connection_rate_limit = 3

That's terribly low.  The anvil limit settings are intended to
prevent gross abuse and must not be used for traffic shaping.

> transport_maps = mysql:/etc/postfix/mysql/transport.cf

better to keep transport_maps in a hash: table unless it
changes frequently.

> virtual_mailbox_domains = mysql:/etc/postfix/mysql/virtual_domains.cf

better to keep virtual_mailbox_domains in a hash table unless
it changes frequently.

For the tables that I suggest you keep in a hash, if you want
to still store the data in mysql you can automate a daily dump
to a hash file for postfix to use.


   -- Noel Jones
Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Clunk Werclick
On Wed, 2009-07-22 at 11:04 -0500, Noel Jones wrote:
> Clunk Werclick wrote:
> > I think perhaps 4-12 queries per message is not optimal?
> > If server handle 50,000 a day X 12 that is quite a lot? I don't think
> > it is going to get may fields returned for .co.uk .uk in my database?
> >
>
> Postfix does the lookups required to route your mail properly.
It is a bit silly to do this for .co.uk then .uk yes?

>
> > I stress much that this is not Postfix, it is my silly configuration of
> > Postfix. Am learning as I go along so plenty of things wrong probably:
> >
> > This is output;
> >
> > postconf -n
> > relay_domains = mysql:/etc/postfix/mysql/relay_domains.cf
>
> Unless relay_domains changes frequently, better to keep it in
> a hash table.  Or just set it explicitly empty if you don't
> have any relay_domains.

They change frequently that is why I have a database back end.

> > transport_maps = mysql:/etc/postfix/mysql/transport.cf
>
> better to keep transport_maps in a hash: table unless it
> changes frequently.
>
> > virtual_mailbox_domains = mysql:/etc/postfix/mysql/virtual_domains.cf
>
> better to keep virtual_mailbox_domains in a hash table unless
> it changes frequently.

They change frequently that is why I have a database back end.
>
> For the tables that I suggest you keep in a hash, if you want
> to still store the data in mysql you can automate a daily dump
> to a hash file for postfix to use.

This seems to be a bit silly, that is what the database is for, but
thank you for your advice. I may have to do this to stop this DoS type
of hammering for silly lookups. Thank you anyhow.

>
>
>    -- Noel Jones
--
-----------------------------------------------------------
C Werclick .Lot
Technical incompetent
Loyal Order Of The Teapot.

This e-mail and its attachments is intended only to be used as an e-mail
and an attachment. Any use of it for other purposes other than as an
e-mail and an attachment will not be covered by any warranty that may or
may not form part of this e-mail and attachment.



Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Barney Desmond
2009/7/23 Clunk Werclick <[hidden email]>:
> On Wed, 2009-07-22 at 11:04 -0500, Noel Jones wrote:
>> Clunk Werclick wrote:
>> > I think perhaps 4-12 queries per message is not optimal?
>> > If server handle 50,000 a day X 12 that is quite a lot? I don't think
>> > it is going to get may fields returned for .co.uk .uk in my database?

It was a rhetorical question. :)

>> Postfix does the lookups required to route your mail properly.
> It is a bit silly to do this for .co.uk then .uk yes?

Not necessarily, it's doing what it's been configured to do. It just
so happens that the configuration is a bit "legacy" - as documented,
it's a backwards compatibility feature. You can turn it off, though it
*may* cause behaviour to change in ways you don't expect.

>> For the tables that I suggest you keep in a hash, if you want
>> to still store the data in mysql you can automate a daily dump
>> to a hash file for postfix to use.
>
> This seems to be a bit silly, that is what the database is for, but
> thank you for your advice. I may have to do this to stop this DoS type
> of hammering for silly lookups. Thank you anyhow.

You need to ask yourself if this is a real problem, or something
you're just imagining. Mysql generally works fine, 50,000 messages a
day at 12 queries each, equates to several queries per second. This is
an "easy" load. If you're concerned, then disable the parent domain
searching as mentioned before. If you're worried about mysql's
stability then you probably shouldn't be using it. Using a database as
a table backend carries its own share of risks and failure cases. I
notice in your postconf output that you're not using proxymap with
mysql. This is generally recommended:
http://www.postfix.org/MYSQL_README.html (notes on client connections)
http://www.postfix.org/proxymap.8.html (specific proxy:mysql example)
Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Clunk Werclick
On Thu, 2009-07-23 at 13:50 +1000, Barney Desmond wrote:
> You need to ask yourself if this is a real problem, or something
> you're just imagining. Mysql generally works fine, 50,000 messages a
> day at 12 queries each, equates to several queries per second. This is
> an "easy" load.
That is a comfort to know. My main concern was this hammering was not
optimal, but it is welcome to make as many queries as it likes if it
does not crash the database server. Perhaps Postgresql would be a bit
more manly ? but slower ?
> If you're concerned, then disable the parent domain
> searching as mentioned before.
Forgive my sincere stupidness, but I did not see where it said 'do this
to disable parent domain searching'. I would like to do this and see if
it makes a difference. What do I need to take out/add to do this ?
>  If you're worried about mysql's
> stability then you probably shouldn't be using it. Using a database as
> a table backend carries its own share of risks and failure cases.
It is not ideal to use it but it makes it easy to write web front ends
for management. I could script the generation of index postmaps from the
database but will this scale well? How big can the postmaps be before it
gets a little crazy? 100 lines? 1000 lines? 10,000 lines? 100,000 lines?
I cannot find any figures to say at which point it is best to cross
over ? This would be very useful and help me make an informed choice.
>  I
> notice in your postconf output that you're not using proxymap with
> mysql. This is generally recommended:
> http://www.postfix.org/MYSQL_README.html (notes on client connections)
Thank you. I have looked at this and taken your notes on board.
> http://www.postfix.org/proxymap.8.html (specific proxy:mysql example)
And this also. I don't think there is any major benefit being sold to me
here for using a proxy map and I am wondering if this will introduce a
small amount of latency perhaps? But I wont kick the gifted horse and I
will try this today - thank you Sir.
--
-----------------------------------------------------------
C Werclick .Lot
Technical incompetent
Loyal Order Of The Teapot.

This e-mail and its attachments is intended only to be used as an e-mail
and an attachment. Any use of it for other purposes other than as an
e-mail and an attachment will not be covered by any warranty that may or
may not form part of this e-mail and attachment.



Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Barney Desmond
2009/7/23 Clunk Werclick <[hidden email]>:
> That is a comfort to know. My main concern was this hammering was not
> optimal, but it is welcome to make as many queries as it likes if it
> does not crash the database server. Perhaps Postgresql would be a bit
> more manly ? but slower ?

Realistically you shouldn't notice a difference, but every system will
be different, and between those two it'll depend somewhat on tuning as
well.

> to disable parent domain searching'. I would like to do this and see if
> it makes a difference. What do I need to take out/add to do this ?

You can do this in main.cf, I believe you just set an empty value.
http://www.postfix.org/postconf.5.html#parent_domain_matches_subdomains

Something like:

parent_domain_matches_subdomains =

> It is not ideal to use it but it makes it easy to write web front ends
> for management. I could script the generation of index postmaps from the
> database but will this scale well? How big can the postmaps be before it
> gets a little crazy? 100 lines? 1000 lines? 10,000 lines? 100,000 lines?
> I cannot find any figures to say at which point it is best to cross
> over ? This would be very useful and help me make an informed choice.

Anecdotally, hash-based tables are "very fast". CDB tables are said to
perform well at least up to a million entries.
http://www.postfix.org/DATABASE_README.html#types

> And this also. I don't think there is any major benefit being sold to me
> here for using a proxy map and I am wondering if this will introduce a
> small amount of latency perhaps?

It's true that there's some latency involved, but the benefit it gives
is that it consolidates the number of open connections to the
database. This is a fairly common problem on setups with mysql when
the system gets busy - it will open a lot of separate connections, and
this can hit the configured limits, which causes things to fail
(postfix can't connect to the database, so lookups fail).

There are some (specific) lookup types that can't take proper
advantage of proxymap, so they get handled correctly and silently
(bypassing the proxymap service). There was some discussion about this
a little while ago, but the outcome was that the potential performance
gain of not using proxymap when it's not needed was low, so you might
as well always use it. Of course, the best way to know is to try it
for yourself. :)
Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Thomas Gelf-2
In reply to this post by Clunk Werclick
Clunk Werclick wrote:
> On Thu, 2009-07-23 at 13:50 +1000, Barney Desmond wrote:
>> You need to ask yourself if this is a real problem, or something
>> you're just imagining. Mysql generally works fine, 50,000 messages a
>> day at 12 queries each, equates to several queries per second. This is
>> an "easy" load.
> That is a comfort to know. My main concern was this hammering was not
> optimal, but it is welcome to make as many queries as it likes if it
> does not crash the database server. Perhaps Postgresql would be a bit
> more manly ? but slower ?

You'll probably not note a difference. I guess MySQL will allow you to
connnect() faster if using a local socket. However you should always use
proxy_read_maps - so connect()-times are not so relevant.

I gave a quick look at the server statistics of our MySQL instance
providing Postix and Amavis config (not used as Amavis storage etc, its
only purpose is providing "configuration"): DB uptime 250 days with an
average of 300 queries per second (our reports are showing peeks of
slightly more than 6 million delivery attempts a day).

We are using multiple servers, but that's mostly as of disaster recovery
and failover reasons - you could handle similar traffic also on a single
host (using recent server hardware).

A certain percentage of queries could of course be avoided if Postfix
where optimized for DB usage. As we know it isn't - this design choice
however keeps it flexible and simple.

Best regards,
Thomas Gelf

Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Clunk Werclick
In reply to this post by Barney Desmond
On Thu, 2009-07-23 at 18:47 +1000, Barney Desmond wrote:

>                              From:
> Barney Desmond
> <[hidden email]>
>                                To:
> postfix users list
> <[hidden email]>
>                           Subject:
> Re: Many SQL Lookups on outbounding
> mails
>                              Date:
> Thu, 23 Jul 2009 18:47:54 +1000
> (09:47 BST)
>
>
> 2009/7/23 Clunk Werclick <[hidden email]>:
> > That is a comfort to know. My main concern was this hammering was
> not
> > optimal, but it is welcome to make as many queries as it likes if it
> > does not crash the database server. Perhaps Postgresql would be a
> bit
> > more manly ? but slower ?
>
> Realistically you shouldn't notice a difference, but every system will
> be different, and between those two it'll depend somewhat on tuning as
> well.
>
> > to disable parent domain searching'. I would like to do this and see
> if
> > it makes a difference. What do I need to take out/add to do this ?
>
> You can do this in main.cf, I believe you just set an empty value.
> http://www.postfix.org/postconf.5.html#parent_domain_matches_subdomains
>
> Something like:
>
> parent_domain_matches_subdomains =
OK, done and reload but I'm still finding this (see below). What is
weird is the lookups it does based on the mail_from command. It's
recursing those in the database for a reason I am not sure I fully
understand? I understand why it do this for rcpt_to, makes full sense to
me, but for the sender also?
>
>
> Anecdotally, hash-based tables are "very fast". CDB tables are said to
> perform well at least up to a million entries.
> http://www.postfix.org/DATABASE_README.html#types
That scales well and I will re-engineer what I am doing to take
advantage of that. There seems little useful point to use SQL for
anything other than as a holding container that is used to generate maps
if these figures are correct. {in my context at least}
>
{trim}
> a little while ago, but the outcome was that the potential performance
> gain of not using proxymap when it's not needed was low, so you might
> as well always use it.
This is in my to be doing list and I thank you Sir.

After making single change suggestted and reload
A single test message by telnet where;

mail from: [hidden email]
rcpt to: [hidden email]

generated a lot of lookups for the 'mail from' command ?
803 Connect     postfixro@localhost on mndb
803 Query       SELECT transport FROM transport WHERE destination='*'
803 Query       SELECT transport FROM transport WHERE destination='*'

804 Connect     postfixro@localhost on mndb
804 Query       SELECT virtual_user_email FROM recipients WHERE
alias='yahoo.co.uk'
805 Connect     postfixro@localhost on mndb
805 Query       SELECT domain FROM domains WHERE domain='yahoo.co.uk'
AND local=1
806 Connect     postfixro@localhost on mndb
806 Query       SELECT domain FROM domains WHERE domain='yahoo.co.uk'
AND local=0
806 Query       SELECT domain FROM domains WHERE domain='.co.uk' AND
local=0
806 Query       SELECT domain FROM domains WHERE domain='.uk' AND
local=0
803 Query       SELECT transport FROM transport WHERE
destination='[hidden email]'
803 Query       SELECT transport FROM transport WHERE
destination='yahoo.co.uk'
803 Query       SELECT transport FROM transport WHERE
destination='.co.uk'
803 Query       SELECT transport FROM transport WHERE destination='.uk'

090723 10:18:07    804 Query       SELECT virtual_user_email FROM
recipients WHERE alias='destination.co.uk'
805 Query       SELECT domain FROM domains WHERE
domain='destination.co.uk' AND local=1
806 Query       SELECT domain FROM domains WHERE
domain='destination.co.uk' AND local=0
803 Query       SELECT transport FROM transport WHERE
destination='[hidden email]'
803 Query       SELECT transport FROM transport WHERE
destination='destination.co.uk'

807 Connect     postfixro@localhost on mndb
807 Query       SELECT virtual_user_email FROM recipients WHERE
alias='[hidden email]'
807 Query       SELECT virtual_user_email FROM recipients WHERE
alias='@destination.co.uk'

808 Connect     postfixro@localhost on mndb
808 Query       SELECT virtual_user_email FROM recipients WHERE
alias='[hidden email]'
808 Query       SELECT virtual_user_email FROM recipients WHERE
alias='@destination.co.uk'
808 Query       SELECT virtual_user_email FROM recipients WHERE
alias='[hidden email]'
808 Query       SELECT virtual_user_email FROM recipients WHERE
alias='@destination.co.uk'
090723 10:18:21    804 Query       SELECT virtual_user_email FROM
recipients WHERE alias='destination.co.uk'
805 Query       SELECT domain FROM domains WHERE
domain='destination.co.uk' AND local=1
806 Query       SELECT domain FROM domains WHERE
domain='destination.co.uk' AND local=0
803 Query       SELECT transport FROM transport WHERE
destination='[hidden email]'
803 Query       SELECT transport FROM transport WHERE
destination='destination.co.uk'

I switch to maps soon and this will not concern me, but I guess it will
still do this with maps and I wonder why ? I would like to get it to a
'Lean mean e-mail machine' if I may -..- {}Long term hope when I learn
more things with Postfix{}

--
-----------------------------------------------------------
C Werclick .Lot
Technical incompetent
Loyal Order Of The Teapot.

This e-mail and its attachments is intended only to be used as an e-mail
and an attachment. Any use of it for other purposes other than as an
e-mail and an attachment will not be covered by any warranty that may or
may not form part of this e-mail and attachment.



Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Clunk Werclick
In reply to this post by Thomas Gelf-2
On Thu, 2009-07-23 at 11:24 +0200, Thomas Gelf wrote:

> Clunk Werclick wrote:
> > On Thu, 2009-07-23 at 13:50 +1000, Barney Desmond wrote:
> >> You need to ask yourself if this is a real problem, or something
> >> you're just imagining. Mysql generally works fine, 50,000 messages a
> >> day at 12 queries each, equates to several queries per second. This is
> >> an "easy" load.
> > That is a comfort to know. My main concern was this hammering was not
> > optimal, but it is welcome to make as many queries as it likes if it
> > does not crash the database server. Perhaps Postgresql would be a bit
> > more manly ? but slower ?
>
> You'll probably not note a difference. I guess MySQL will allow you to
> connnect() faster if using a local socket. However you should always use
> proxy_read_maps - so connect()-times are not so relevant.
>
> I gave a quick look at the server statistics of our MySQL instance
> providing Postix and Amavis config (not used as Amavis storage etc, its
> only purpose is providing "configuration"): DB uptime 250 days with an
> average of 300 queries per second (our reports are showing peeks of
> slightly more than 6 million delivery attempts a day).
>
That is very reassuring Thomas, thank you.

Now I don't know if I should stay with SQL or drop to maps ? It is
easier to configure with SQL from a web based front end - but to get SQL
to dump to flat files and Postmap is also only a few Perl lines. What is
a fool to do ? :-#

> We are using multiple servers, but that's mostly as of disaster recovery
> and failover reasons - you could handle similar traffic also on a single
> host (using recent server hardware).
>
> A certain percentage of queries could of course be avoided if Postfix
> where optimized for DB usage. As we know it isn't - this design choice
> however keeps it flexible and simple.
>
> Best regards,
> Thomas Gelf

--
-----------------------------------------------------------
C Werclick .Lot
Technical incompetent
Loyal Order Of The Teapot.

This e-mail and its attachments is intended only to be used as an e-mail
and an attachment. Any use of it for other purposes other than as an
e-mail and an attachment will not be covered by any warranty that may or
may not form part of this e-mail and attachment.



Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Thomas Gelf-2
Clunk Werclick wrote:
> That is very reassuring Thomas, thank you.
>
> Now I don't know if I should stay with SQL or drop to maps ? It is
> easier to configure with SQL from a web based front end - but to get SQL
> to dump to flat files and Postmap is also only a few Perl lines. What is
> a fool to do ? :-#

If you're comfortable with SQL: stay with SQL. Load should absolutely
not be an issue with your estimated traffic - and even if I could tell
some scary anecdotes regarding MySQL: it is pretty stable. Please also
note that all my Postfix instances are using TCP, not local sockets. And
it still performs very well!

"Dump to flat files" is an option, but I don't see any reason why you
should do so: it just adds one more layer of complexity to your system.
If you're writing an SQL frontend you have all config right there in
realtime, are not forced to reflect about possible locking issues (what
happens if you run your "recreate-flat-files"-script simultaneously more
than once etc) - and if you add another Postfix host in the future all
you need to do is providing it some credentials to connect to your DB.

Regards,
Thomas

Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Clunk Werclick
On Thu, 2009-07-23 at 11:57 +0200, Thomas Gelf wrote:

> Clunk Werclick wrote:
> > That is very reassuring Thomas, thank you.
> >
> > Now I don't know if I should stay with SQL or drop to maps ? It is
> > easier to configure with SQL from a web based front end - but to get SQL
> > to dump to flat files and Postmap is also only a few Perl lines. What is
> > a fool to do ? :-#
>
> If you're comfortable with SQL: stay with SQL. Load should absolutely
> not be an issue with your estimated traffic - and even if I could tell
> some scary anecdotes regarding MySQL: it is pretty stable. Please also
> note that all my Postfix instances are using TCP, not local sockets. And
> it still performs very well!
>
> "Dump to flat files" is an option, but I don't see any reason why you
> should do so: it just adds one more layer of complexity to your system.
> If you're writing an SQL frontend you have all config right there in
> realtime, are not forced to reflect about possible locking issues (what
> happens if you run your "recreate-flat-files"-script simultaneously more
> than once etc) - and if you add another Postfix host in the future all
> you need to do is providing it some credentials to connect to your DB.
>
> Regards,
> Thomas
Thank you Thomas. I stick with Mysql and worry if I ever have to set up
a server so big it fails. If that happens I have lots of £$£ and pay
someone else to do it whilst I sit on beach sipping wine.

I have now got proxy working on the maps too, so that is off my to be do
list.

Now I fight the recipient verification process for many many domains
hosted on one Postfix - but that is a new adventure.
--
-----------------------------------------------------------
C Werclick .Lot
Technical incompetent
Loyal Order Of The Teapot.

This e-mail and its attachments is intended only to be used as an e-mail
and an attachment. Any use of it for other purposes other than as an
e-mail and an attachment will not be covered by any warranty that may or
may not form part of this e-mail and attachment.



Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Thomas Gelf-2
Clunk Werclick wrote:
> Thank you Thomas. I stick with Mysql and worry if I ever have to set up
> a server so big it fails. If that happens I have lots of £$£ and pay
> someone else to do it whilst I sit on beach sipping wine.

Once that happens: let me know! I'll join you at the beach and configure
your servers remotely. Of course it's up to you to pay for drinks and
UTMS traffic ;-)

> I have now got proxy working on the maps too, so that is off my to be do
> list.

Congratulations!

> Now I fight the recipient verification process for many many domains
> hosted on one Postfix - but that is a new adventure.

Have fun ;-)

Cheers,
Thomas

Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

James Cloos-9
In reply to this post by Clunk Werclick
>>>>> "Clunk" == Clunk Werclick <[hidden email]> writes:

Clunk> I think perhaps 4-12 queries per message is not optimal?

Use proxymap(8) to reduce the number of queries made to the backend
database.  It is just as fast in my experience as a hash table.

Basically, everywhere you have mysql:... make it proxy:mysql:....

Read the proxymap(8) man page for full details.

-JimC
--
James Cloos <[hidden email]>         OpenPGP: 1024D/ED7DAEA6
Reply | Threaded
Open this post in threaded view
|

Re: Many SQL Lookups on outbounding mails

Simon J Mudd
In reply to this post by Clunk Werclick
[hidden email] (Clunk Werclick) writes:

> > >> >>> It seems lots of lookups per message and I'm not sure that mysql will not crash like this
> >
> > Who's to say what "a lot" of lookups are? Why do you think mysql will
> > Just Crash? You're far better off looking at the general load and
> > responsiveness of your server than checking how many queries mysql is
> > doing.
> I think perhaps 4-12 queries per message is not optimal?
> If server handle 50,000 a day X 12 that is quite a lot? I don't think
> it is going to get may fields returned for .co.uk .uk in my database?

A suitably configured mysql backend should easily be able to respond
with 5000-10000 queries per second.  You'll be delivering a lot of
mail when you get to that volume. Perhaps you need to look at your
MySQL configuration in this case?

Simon