The ispconfig user in mysql is connected in sleeping state to mysql server and east all connections. The source of connections is postfix as the mail server receives lot of spam. I set wait_timeout to kill old connection but than postfix says warning: mysql query failed: MySQL server has gone away warning: smtpd_client_event_limit_exceptions: mysql:/etc/postfix/mysql-virtual_forwardings.cf: table lookup problem warning: mysql:/etc/postfix/mysql-virtual_client.cf: table lookup problem Whenever I raise max_connection in mysql the ispconfig eats everything.
What have you set as max_connections and max_suer_connections in MySQL? Try to set both values to e.g. 1024 and restart mysql.
Then set it to 500. But what's more important is that you change max_user_connections too, as changing just max_connections has no effect without setting max_user_connections as well.
I set both to 500 and after few minutes I see Too many connections and lookup errors in mail.log. And lot of sleeping processes in mysql of the ISPConfig user.
If you truly are having mysql die because you run out of memory for the amount of mail you are receiving, you probably don't have enough memory in the system to begin with. What is the level of incoming spam mail (# messages / second) you're talking about? If too much incoming mail is indeed the source of your problem, you can try to cut that down before it ever hits mysql, eg. install postscreen (see https://git.ispconfig.org/ispconfig/ispconfig3/-/issues/4239 for example) should help. Also make sure you're on the latest ISPConfig version (more postfix msyql maps are proxied in recent config, which will reduce mysql connection use). Switching to rspamd from amavis would also help with mysql connections, though not overall memory use. I'd suspect you could have something more going on though. Mysql table corruption? Do you have amavis/spamassassin with bayes stored in sql? If so and autoexpire is on, that (ie. bayes token expiry) can run at inopportune times. But you might just need more memory and a little mysql tuning.
The problem was that whenever I raised the max_connections in mysql, the postfix was able to used them all. After few minutes, there was hundred of sleeping processes in mysql, all belonging to ISPConfig user. I decide to recreate the main.cf from the scratch, install postscreen and update ispconfig to latest version. Now I have just around 50 sleeping ISPConfig processes. I am fighting spam also with fail2ban, so the amount of parallel connection isn't so high. So guys, I thank you for you help, now the system is stable again. But the question, why I still see sleeping (very deep, hundred seconds) processes of ISPConfig user in mysql? MariaDB [(none)]> show processlist; +-------+-------------+-----------------+-------------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-------+-------------+-----------------+-------------+---------+------+--------------------------+------------------+----------+ | 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 14278 | ispconfig | localhost:55670 | dbispconfig | Sleep | 28 | | NULL | 0.000 | | 14305 | ispconfig | localhost:55692 | dbispconfig | Sleep | 33 | | NULL | 0.000 | | 14677 | ispconfig | localhost:55878 | dbispconfig | Sleep | 181 | | NULL | 0.000 | | 15730 | ispconfig | localhost:56348 | dbispconfig | Sleep | 327 | | NULL | 0.000 | | 15909 | ispconfig | localhost:56414 | dbispconfig | Sleep | 183 | | NULL | 0.000 | | 15982 | ispconfig | localhost:56478 | dbispconfig | Sleep | 189 | | NULL | 0.000 | | 16271 | ispconfig | localhost:56648 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16272 | ispconfig | localhost:56650 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16273 | ispconfig | localhost:56652 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16274 | ispconfig | localhost:56654 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16275 | ispconfig | localhost:56656 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16276 | ispconfig | localhost:56658 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16277 | ispconfig | localhost:56660 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16278 | ispconfig | localhost:56662 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16279 | ispconfig | localhost:56664 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16280 | ispconfig | localhost:56666 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16281 | ispconfig | localhost:56668 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16282 | ispconfig | localhost:56670 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16283 | ispconfig | localhost:56672 | dbispconfig | Sleep | 27 | | NULL | 0.000 | | 16294 | ispconfig | localhost:56676 | dbispconfig | Sleep | 33 | | NULL | 0.000 | | 16295 | ispconfig | localhost:56678 | dbispconfig | Sleep | 33 | | NULL | 0.000 | | 16296 | ispconfig | localhost:56680 | dbispconfig | Sleep | 33 | | NULL | 0.000 | | 16297 | ispconfig | localhost:56682 | dbispconfig | Sleep | 33 | | NULL | 0.000 | | 16298 | ispconfig | localhost:56684 | dbispconfig | Sleep | 33 | | NULL | 0.000 | | 16299 | ispconfig | localhost:56686 | dbispconfig | Sleep | 33 | | NULL | 0.000 | | 16300 | ispconfig | localhost | dbispconfig | Sleep | 1 | | NULL | 0.000 | | 16303 | ispconfig | localhost:56690 | dbispconfig | Sleep | 30 | | NULL | 0.000 | | 16304 | ispconfig | localhost:56692 | dbispconfig | Sleep | 30 | | NULL | 0.000 | | 16349 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 | +-------+-------------+-----------------+-------------+---------+------+--------------------------+------------------+----------+ 34 rows in set (0.000 sec)
The user 'ispconfig' is used by all services of your system, from postfix to dovecot, pure-ftpd etc. Many services like postfix keep the connection open to be able to re-use it to speed up your system, so it's perfectly fine to have lots of connections in a sleeping state.