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
...

--

[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.

[Gerald Galster]

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

[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

--

[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

[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

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

[Phil Stracchino]

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:   [phone]

[natan]

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
>
>

--

[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