Re: Many SQL Lookups on outbounding mails
clunk.wercl...@wibblywobblyteapot.co.uk (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-1 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
Re: Many SQL Lookups on outbounding mails
Clunk == Clunk Werclick clunk.wercl...@wibblywobblyteapot.co.uk 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 cl...@jhcloos.com OpenPGP: 1024D/ED7DAEA6
Re: Many SQL Lookups on outbounding mails
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.
Re: Many SQL Lookups on outbounding mails
2009/7/23 Clunk Werclick clunk.wercl...@wibblywobblyteapot.co.uk: 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. :)
Re: Many SQL Lookups on outbounding mails
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
Re: Many SQL Lookups on outbounding mails
On Thu, 2009-07-23 at 18:47 +1000, Barney Desmond wrote:
From:
Barney Desmond
barneydesm...@gmail.com
To:
postfix users list
postfix-users@postfix.org
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 clunk.wercl...@wibblywobblyteapot.co.uk:
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: user...@yahoo.co.uk
rcpt to: t...@destination.co.uk
generated a lot of lookups for the 'mail from' command ?
803 Connect postfi...@localhost on mndb
803 Query SELECT transport FROM transport WHERE destination='*'
803 Query SELECT transport FROM transport WHERE destination='*'
804 Connect postfi...@localhost on mndb
804 Query SELECT virtual_user_email FROM recipients WHERE
alias='yahoo.co.uk'
805 Connect postfi...@localhost on mndb
805 Query SELECT domain FROM domains WHERE domain='yahoo.co.uk'
AND local=1
806 Connect postfi...@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='user...@yahoo.co.uk'
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='t...@destination.co.uk'
803 Query SELECT transport FROM transport WHERE
destination='destination.co.uk'
807 Connect postfi...@localhost on mndb
807 Query SELECT virtual_user_email FROM recipients WHERE
alias='t...@destination.co.uk'
807 Query SELECT virtual_user_email FROM recipients WHERE
alias='@destination.co.uk'
808 Connect postfi...@localhost on mndb
808 Query SELECT virtual_user_email FROM recipients WHERE
alias='t...@destination.co.uk'
808 Query SELECT virtual_user_email FROM recipients WHERE
alias='@destination.co.uk'
808 Query SELECT virtual_user_email FROM recipients WHERE
alias='postmas...@destination.co.uk'
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='postmas...@destination.co.uk'
803 Query SELECT transport FROM transport WHERE
destination='destination.co.uk'
I
Re: Many SQL Lookups on outbounding mails
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.
Re: Many SQL Lookups on outbounding mails
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
Re: Many SQL Lookups on outbounding mails
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.
Re: Many SQL Lookups on outbounding mails
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
Re: Many SQL Lookups on outbounding mails
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 u...@some.foo.example.com would trigger the following with a check_sender_access table: u...@some.foo.example.com 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.
Re: Many SQL Lookups on outbounding mails
2009/7/22 Clunk Werclick clunk.wercl...@wibblywobblyteapot.co.uk: 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.
Re: Many SQL Lookups on outbounding mails
On Wed, 2009-07-22 at 20:31 +1000, Barney Desmond wrote: 2009/7/22 Clunk Werclick clunk.wercl...@wibblywobblyteapot.co.uk: 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_usreject_rbl_client zen.spamhaus.orgpermit 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.
Re: Many SQL Lookups on outbounding mails
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
Re: Many SQL Lookups on outbounding mails
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.
Re: Many SQL Lookups on outbounding mails
2009/7/23 Clunk Werclick clunk.wercl...@wibblywobblyteapot.co.uk: 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)
Re: Many SQL Lookups on outbounding mails
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
Re: Many SQL Lookups on outbounding mails
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.
Re: Many SQL Lookups on outbounding mails
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 u...@some.foo.example.com would trigger the following with a check_sender_access table: u...@some.foo.example.com some.foo.example.com foo.example.com example.com com -- Noel Jones
