High spam mail traffic -> max mysql connections

[Mark_NL]

Hey there,

I'm running into a problem and am looking for a good solution to fix this.
Eventually everybody running ispconfig3 will run into this problem!

After doing a clean Debian install and fresh ispconfig 3 installation (3.0.3.3)
i've added some domains, domains of which i know are attractive to send mail to.

The problem I'm running into is the MySQL connections.
All mails that get send to the server need a MySQL connection to check if it's black-,white-listed, filter rules, alias settings etc etc.

So what happens when your server gets hammered by spammers .. max MySQL connections .. and your sites will show blank pages, display MySQL errors etc .. I've set the max connections up to 250 now, and set the timeout for MySQL connections a lot lower (i think it was 120 or 180sec).
Doesn't work, I'm still running into max connections.

I'm using RBL (zen.spamhaus.org), but it looks like there are still connections being made, in a high tempo.

[till]

Please set max_connections and max_user_connections in mysql my.cnf file to 500 and restart mysqld. Setting just max_connections will not have an effect without setting higher max_user_connections as well. The recommended value that works fine on my systems is 500.

[Mark_NL]

Yes it does

max_connections = 250
max_user_connections = 0

A value of 0 means "no limit", so it'll go up to 250 if needed.

I will try 500, see how that goes, but still, maybe we should look for another way to prevent such an amount of connections being made to the MySQL server for checking some mail rules/filters. Since people will run websites on the servers as well, who need the connections.

Maybe limit the max concurrent connections postfix is allowed to make (so split up the user ispconfig to (f.e.) ispconfig_adm and ispconfig_mail. Set the mail user to only use X amount of connections, so the server admin nor the website users won't be affected when a server gets hammered by spammers.

[till]

Maybe limit the max concurrent connections postfix is allowed to make (so split up the user ispconfig to (f.e.) ispconfig_adm and ispconfig_mail. Set the mail user to only use X amount of connections, so the server admin nor the website users won't be affected when a server gets hammered by spammers.

Yes, that might be the best option.

I havent seen any negative effects with a high max connection numbers yet on a server. Mysql seems to handle that well and postfix keeps the connections open in sleep state but does not use them that much in comparison to e.g. websites that might have only few open connections but do many queries trough them.

[Mark_NL]

Assigning more connections you can run OOM when mysql gets a sudden increase in connections, I've seen it before on servers, they die, swap, whatever .. it's not a pretty picture

They stay in sleep state, true, but they aren't re-used (although, so it seems on my server), they need to time out before they are being dropped, so eventually you'll run out of connections.

I'll give the max500 a try and see how it goes, i'll also check and see if it's possible to limit the amount of hammering postfix does on the server.

[till]

You can try to use the postfix proxymap service to get better postfix mysql connection handling.

[kwickcut]

after my upgrade to 3.0.3.3 i have one site that is showing a blank index page. i assume that you are talking about editing

/etc/mysql/my.cnf

i did not see these settings in this file so i added

max_connections        = 500
max_user_connections        = 500

and then restarted mysql

/etc/init.d/mysql start

i checked the site and it is still white pages did i miss something?

i have 3 out of 4 sites running and they are all ok its just the one


kwick

[till]

The problem described in tnhis hread is about email settings, your website problem is not related to that. Please take a look into the error.log of the website, you will find the error message there. And plee make a new thread for that