postfix with mysql - too many connections

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

postfix with mysql - too many connections

natan
Hi
I use postfix-3.1.15 and mysql (domain mp, policyd, whitelist) - this is
vary have high-volume server

I read e-mail from this group near "Feb 07, 2016; 5:44pmRe: postfix with
mysql - too many connections" about changing proxy:mysql to sharing
connections
http://postfix.1071664.n5.nabble.com/postfix-with-mysql-too-many-connections-td81836.html

Can anyone give me an example of such configuration: if i have
virtual_mailbox_domains = proxy:mysql:/etc/postfix/map.sql

and
smtpd_recipient_restrictions =
..
check_recipient_access
memcache:/etc/postfix/memcache_recipient_whitelist_cache.cf
...

--

Reply | Threaded
Open this post in threaded view
|

Re: postfix with mysql - too many connections

Matus UHLAR - fantomas
On 04.12.20 15:13, natan wrote:

>I use postfix-3.1.15 and mysql (domain mp, policyd, whitelist) - this is
>vary have high-volume server
>
>I read e-mail from this group near "Feb 07, 2016; 5:44pmRe: postfix with
>mysql - too many connections" about changing proxy:mysql to sharing
>connections
>http://postfix.1071664.n5.nabble.com/postfix-with-mysql-too-many-connections-td81836.html
>
>Can anyone give me an example of such configuration: if i have
>virtual_mailbox_domains = proxy:mysql:/etc/postfix/map.sql
>
>and
>smtpd_recipient_restrictions =
>..
>check_recipient_access
>memcache:/etc/postfix/memcache_recipient_whitelist_cache.cf

you need to increate maximum connections available to mysql server.
parameter max_connections on mysql conig.


--
Matus UHLAR - fantomas, [hidden email] ; http://www.fantomas.sk/
Warning: I wish NOT to receive e-mail advertising to this address.
Varovanie: na tuto adresu chcem NEDOSTAVAT akukolvek reklamnu postu.
You have the right to remain silent. Anything you say will be misquoted,
then used against you.
Reply | Threaded
Open this post in threaded view
|

Re: postfix with mysql - too many connections

Gerald Galster
In reply to this post by natan

I use postfix-3.1.15 and mysql (domain mp, policyd, whitelist) - this is
vary have high-volume server

I read e-mail from this group near "Feb 07, 2016; 5:44pmRe: postfix with
mysql - too many connections" about changing proxy:mysql to sharing
connections
http://postfix.1071664.n5.nabble.com/postfix-with-mysql-too-many-connections-td81836.html

Can anyone give me an example of such configuration: if i have


I gave you hint's on this very list two days ago ...

Best regards
Gerald


check_recipient_access mysql:/etc/postfix/mysql_whitelist_recipient.cf
...
"Dec  2 13:51:09 mail4 postfix/smtpd[21777]: warning: connect to mysql
server 127.0.0.1: Too many connections

try http://www.postfix.org/proxymap.8.html

check_recipient_access proxy:mysql:/etc/postfix/mysql_whitelist_recipient.cf

proxy_read_maps = ... proxy:mysql:/etc/postfix/mysql_whitelist_recipient.cf
Reply | Threaded
Open this post in threaded view
|

Re: postfix with mysql - too many connections

natan

I gave you hint's on this very list two days ago ...

Best regards
Gerald


Yes but I have proxy_read_maps in main.cf:

proxy_read_maps = $canonical_maps $lmtp_generic_maps $local_recipient_maps $mydestination $mynetworks $recipient_bcc_maps $recipient_canonical_maps $relay_domains $relay_recipient_maps $relocated_maps $sender_bcc_maps $sender_canonical_maps $smtp_generic_maps $smtpd_sender_login_maps $transport_maps $virtual_alias_domains $virtual_alias_maps $virtual_mailbox_domains $virtual_mailbox_maps $smtpd_sender_restrictions $sender_dependent_relayhost_maps

And use:
virtual_mailbox_domains = proxy:mysql:/etc/postfix/map.sql

In http://postfix.1071664.n5.nabble.com/postfix-with-mysql-too-many-connections-td81836.html

Mr Wietse say:
"> As workaround I used now proxymap and the problems are solved:

This is not a workaround. Sharing connections is the recommended
configuration.

> But I think it would be a better way, if postfix could close the mysql
> connections immediately after receiving the data and not hold opened
> until the mail is fully delivered.

Doing a connect+close for every query would reduce performance on a
high-volume server."

In my postfix proxymap may not be working very well:
warning: virtual_mailbox_domains: proxy:mysql:/etc/postfix/map.sql: table lookup problem
warning: memcache:/etc/postfix/memcache_recipient_whitelist_cache.cf: table lookup problem

because in mysql i see many hanging processes


check_recipient_access mysql:/etc/postfix/mysql_whitelist_recipient.cf
...
"Dec  2 13:51:09 mail4 postfix/smtpd[21777]: warning: connect to mysql
server 127.0.0.1: Too many connections

try http://www.postfix.org/proxymap.8.html

check_recipient_access proxy:mysql:/etc/postfix/mysql_whitelist_recipient.cf

proxy_read_maps = ... proxy:mysql:/etc/postfix/mysql_whitelist_recipient.cf



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

Re: postfix with mysql - too many connections

Gerald Galster

>> I gave you hint's on this very list two days ago ...
>>
>> Best regards
>> Gerald
>>
>>
> Yes but I have proxy_read_maps in main.cf:
>
> proxy_read_maps = $canonical_maps $lmtp_generic_maps $local_recipient_maps $mydestination $mynetworks $recipient_bcc_maps $recipient_canonical_maps $relay_domains $relay_recipient_maps $relocated_maps $sender_bcc_maps $sender_canonical_maps $smtp_generic_maps $smtpd_sender_login_maps $transport_maps $virtual_alias_domains $virtual_alias_maps $virtual_mailbox_domains $virtual_mailbox_maps $smtpd_sender_restrictions     $sender_dependent_relayhost_maps

proxy_read_maps = ... proxy:mysql:/etc/postfix/mysql_whitelist_recipient.cf

in your case means:

proxy_read_maps = $canonical_maps $lmtp_generic_maps $local_recipient_maps $mydestination $mynetworks $recipient_bcc_maps $recipient_canonical_maps $relay_domains $relay_recipient_maps $relocated_maps $sender_bcc_maps $sender_canonical_maps $smtp_generic_maps $smtpd_sender_login_maps $transport_maps $virtual_alias_domains $virtual_alias_maps $virtual_mailbox_domains $virtual_mailbox_maps $smtpd_sender_restrictions     $sender_dependent_relayhost_maps proxy:mysql:/etc/postfix/mysql_whitelist_recipient.cf

                                 ^^^^^^^^^^^^

Add proxy:mysql:/etc/postfix/mysql_whitelist_recipient.cf to your proxy_read_maps, restart postfix and see if your problem is gone.

This proxy mechanism does work very well with high performance servers.

Best regards
Gerald
Reply | Threaded
Open this post in threaded view
|

Re: postfix with mysql - too many connections

Gerald Galster
In reply to this post by natan
> In my postfix proxymap may not be working very well:
> warning: virtual_mailbox_domains: proxy:mysql:/etc/postfix/map.sql: table lookup problem
> warning: memcache:/etc/postfix/memcache_recipient_whitelist_cache.cf: table lookup problem
>
> because in mysql i see many hanging processes

Does the query from /etc/postfix/map.sql hang when you try it manually in mysql monitor?

If that table is long, is there an index on the columns you search for?

Do those queries hit mysql slow log?

You could enable mysql general log and see what query postfix sends and try that manually (EXPLAIN SELECT ...).

How many entries does "show processlist" in mysql show? Are these all from postfix? What state are they in?
How high is your configured mysql connection limit?

Best regards
Gerald


Reply | Threaded
Open this post in threaded view
|

Re: postfix with mysql - too many connections

Phil Stracchino
In reply to this post by natan
On 12/4/20 9:13 AM, natan wrote:

> Hi
> I use postfix-3.1.15 and mysql (domain mp, policyd, whitelist) - this is
> vary have high-volume server
>
> I read e-mail from this group near "Feb 07, 2016; 5:44pmRe: postfix with
> mysql - too many connections" about changing proxy:mysql to sharing
> connections
> http://postfix.1071664.n5.nabble.com/postfix-with-mysql-too-many-connections-td81836.html
>
> Can anyone give me an example of such configuration: if i have
> virtual_mailbox_domains = proxy:mysql:/etc/postfix/map.sql



This might be a silly question, but:  Have you actually tuned and
configured MySQL?  Or are you just using it straight out of the box as
about 90% of people do?


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: postfix with mysql - too many connections

natan
In reply to this post by Gerald Galster
On 04.12.2020 16:11, Gerald Galster wrote:
>> In my postfix proxymap may not be working very well:
>> warning: virtual_mailbox_domains: proxy:mysql:/etc/postfix/map.sql: table lookup problem
>> warning: memcache:/etc/postfix/memcache_recipient_whitelist_cache.cf: table lookup problem
>>
>> because in mysql i see many hanging processes

I have a 15489 domain - slow log is empty (via >3)
This is simply query like
query=SELECT domain FROM domain WHERE domain='%s'

Now I change memche map to proxy:mysql and probably problem solved
In db I have ~200 - all from postfix

> Does the query from /etc/postfix/map.sql hang when you try it manually in mysql monitor?
>
> If that table is long, is there an index on the columns you search for?
>
> Do those queries hit mysql slow log?
>
> You could enable mysql general log and see what query postfix sends and try that manually (EXPLAIN SELECT ...).
>
> How many entries does "show processlist" in mysql show? Are these all from postfix? What state are they in?
> How high is your configured mysql connection limit?
>
> Best regards
> Gerald
>
>

--

Reply | Threaded
Open this post in threaded view
|

Re: postfix with mysql - too many connections

Gerald Galster

>>> In my postfix proxymap may not be working very well:
>>> warning: virtual_mailbox_domains: proxy:mysql:/etc/postfix/map.sql: table lookup problem
>>> warning: memcache:/etc/postfix/memcache_recipient_whitelist_cache.cf: table lookup problem
>>>
>>> because in mysql i see many hanging processes
>
> I have a 15489 domain - slow log is empty (via >3)
> This is simply query like
> query=SELECT domain FROM domain WHERE domain='%s'

Then there should be an INDEX on the domain column, but even without
it would not decrease performance severely with just 15k rows as
they are in mysql/innodb page cache when queried constantly.

> Now I change memche map to proxy:mysql and probably problem solved
> In db I have ~200 - all from postfix

Just remember to put proxy:mysql: everywhere you use mysql lookups
and add those to proxy_read_maps or you might still be running
into mysql connection limit.

Best regards
Gerald