ipconfig mysql user is using all connections - postfix problem?

Discussion in 'Installation/Configuration' started by psvec, Mar 19, 2021.

  1. psvec

    psvec New Member

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

    till Super Moderator Staff Member ISPConfig Developer

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

    psvec New Member

    If I set max_connections to 1024 I will run out of memory. Mysql consumption will be too high.
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

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

    psvec New Member

    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.
     
  6. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

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

    psvec New Member

    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)
     
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    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.
     

Share This Page