ISPconfig 3.0.0.0.9 rc2 has been working fine for about two weeks (upgraded from 3.0.0.8), however since today I am getting mysql errors on all sites and the admin panel, reporting something like 'mysql error: too many connections'. I checked out the process list in phpmyadmin and I'm seeing something like this, which I assume can't be right. How can I fix this? FYI this is on Ubuntu Server 8.10. Code: Kill 3 ispconfig localhost dbispconfig Sleep 16 NULL Kill 21 ispconfig localhost dbispconfig Sleep 43 NULL Kill 311 ispconfig localhost dbispconfig Sleep 57 NULL Kill 337 ispconfig localhost dbispconfig Sleep 31 NULL Kill 531 ispconfig localhost dbispconfig Sleep 50 NULL Kill 743 ispconfig localhost dbispconfig Sleep 28 NULL Kill 1577 ispconfig localhost:44774 dbispconfig Sleep 1 NULL Kill 1578 ispconfig localhost:44775 dbispconfig Sleep 1 NULL Kill 1579 ispconfig localhost:44776 dbispconfig Sleep 1 NULL Kill 1585 ispconfig localhost:44780 dbispconfig Sleep 20 NULL Kill 1588 ispconfig localhost:44783 dbispconfig Sleep 53 NULL Kill 1589 ispconfig localhost:44784 dbispconfig Sleep 53 NULL Kill 1593 ispconfig localhost:44788 dbispconfig Sleep 19 NULL Kill 1598 ispconfig localhost:44792 dbispconfig Sleep 53 NULL Kill 1607 ispconfig localhost:44801 dbispconfig Sleep 0 NULL Kill 1866 ispconfig localhost:44932 dbispconfig Sleep 22 NULL Kill 1867 ispconfig localhost:44933 dbispconfig Sleep 22 NULL Kill 1868 ispconfig localhost:44934 dbispconfig Sleep 22 NULL Kill 1869 ispconfig localhost:44935 dbispconfig Sleep 22 NULL Kill 1889 ispconfig localhost:44958 dbispconfig Sleep 257 NULL Kill 1890 ispconfig localhost:44959 dbispconfig Sleep 256 NULL Kill 1891 ispconfig localhost:44960 dbispconfig Sleep 256 NULL Kill 1892 ispconfig localhost:44961 dbispconfig Sleep 256 NULL Kill 1923 ispconfig localhost:44977 dbispconfig Sleep 115 NULL Kill 1924 ispconfig localhost:44978 dbispconfig Sleep 115 NULL Kill 1925 ispconfig localhost:44979 dbispconfig Sleep 115 NULL Kill 1967 ispconfig localhost:45002 dbispconfig Sleep 55 NULL Kill 1968 ispconfig localhost:45003 dbispconfig Sleep 55 NULL Kill 1969 ispconfig localhost:45004 dbispconfig Sleep 55 NULL Kill 1977 ispconfig localhost:45009 dbispconfig Sleep 21 NULL Kill 1978 ispconfig localhost:45010 dbispconfig Sleep 117 NULL Kill 1979 ispconfig localhost:45011 dbispconfig Sleep 117 NULL Kill 1980 ispconfig localhost:45012 dbispconfig Sleep 117 NULL Kill 1981 ispconfig localhost:45013 dbispconfig Sleep 117 NULL Kill 1999 ispconfig localhost:45027 dbispconfig Sleep 115 NULL Kill 2003 ispconfig localhost:45030 dbispconfig Sleep 53 NULL Kill 2004 ispconfig localhost:45033 dbispconfig Sleep 39 NULL Kill 2012 ispconfig localhost:47919 dbispconfig Sleep 0 NULL Kill 2020 ispconfig localhost:47926 dbispconfig Sleep 7 NULL Kill 2021 ispconfig localhost:47927 dbispconfig Sleep 7 NULL Kill 2022 ispconfig localhost:47928 dbispconfig Sleep 7 NULL Kill 2045 ispconfig localhost:47938 dbispconfig Sleep 55 NULL Kill 2046 ispconfig localhost:47946 dbispconfig Sleep 54 NULL Kill 2048 ispconfig localhost:47967 dbispconfig Sleep 54 NULL Kill 2049 ispconfig localhost:47971 dbispconfig Sleep 53 NULL Kill 2050 ispconfig localhost:47972 dbispconfig Sleep 53 NULL Kill 2051 ispconfig localhost:47973 dbispconfig Sleep 53 NULL Kill 2052 ispconfig localhost:47974 dbispconfig Sleep 53 NULL Kill 2053 ispconfig localhost:47975 dbispconfig Sleep 53 NULL Kill 2054 ispconfig localhost:47976 dbispconfig Sleep 53 NULL Kill 2055 ispconfig localhost:47977 dbispconfig Sleep 53 NULL Kill 2056 ispconfig localhost:47978 dbispconfig Sleep 53 NULL Kill 2057 ispconfig localhost:47979 dbispconfig Sleep 20 NULL Kill 2062 ispconfig localhost:47980 dbispconfig Sleep 27 NULL Kill 2063 ispconfig localhost:47981 dbispconfig Sleep 27 NULL Kill 2064 ispconfig localhost:47982 dbispconfig Sleep 27 NULL Kill 2065 ispconfig localhost:47983 dbispconfig Sleep 27 NULL Kill 2066 ispconfig localhost:47984 dbispconfig Sleep 19 NULL Kill 2067 ispconfig localhost:47985 dbispconfig Sleep 19 NULL Kill 2068 ispconfig localhost:47986 dbispconfig Sleep 19 NULL Kill 2070 ispconfig localhost:47987 dbispconfig Sleep 43 NULL Kill 2071 ispconfig localhost:47988 dbispconfig Sleep 43 NULL Kill 2072 ispconfig localhost:47989 dbispconfig Sleep 43 NULL Kill 2073 ispconfig localhost:47990 dbispconfig Sleep 43 NULL Kill 2074 ispconfig localhost:47991 dbispconfig Sleep 20 NULL Kill 2075 ispconfig localhost:47992 dbispconfig Sleep 20 NULL Kill 2078 ispconfig localhost:47993 dbispconfig Sleep 7 NULL Kill 2083 ispconfig localhost:47995 dbispconfig Sleep 34 NULL Kill 2084 ispconfig localhost:47996 dbispconfig Sleep 34 NULL Kill 2085 ispconfig localhost:47997 dbispconfig Sleep 34 NULL Kill 2086 ispconfig localhost:47998 dbispconfig Sleep 34 NULL Kill 2087 ispconfig localhost:47999 dbispconfig Sleep 19 NULL Kill 2089 ispconfig localhost:48000 dbispconfig Sleep 34 NULL Kill 2090 ispconfig localhost:48001 dbispconfig Sleep 34 NULL Kill 2091 ispconfig localhost:48002 dbispconfig Sleep 34 NULL Kill 2092 ispconfig localhost:48003 dbispconfig Sleep 34 NULL Kill 2096 ispconfig localhost:48004 dbispconfig Sleep 25 NULL Kill 2097 ispconfig localhost:48005 dbispconfig Sleep 25 NULL Kill 2098 ispconfig localhost:48006 dbispconfig Sleep 24 NULL Kill 2099 ispconfig localhost:48007 dbispconfig Sleep 24 NULL Kill 2100 ispconfig localhost:48008 dbispconfig Sleep 24 NULL Kill 2101 ispconfig localhost:48009 dbispconfig Sleep 22 NULL Kill 2104 ispconfig localhost:48011 dbispconfig Sleep 19 NULL Kill 2112 ispconfig localhost:48018 dbispconfig Sleep 3 NULL Kill 2113 ispconfig localhost:48019 dbispconfig Sleep 3 NULL Kill 2114 ispconfig localhost:48020 dbispconfig Sleep 3 NULL Kill 2119 ispconfig localhost:48023 dbispconfig Sleep 0 NULL Kill 2120 ispconfig localhost:48024 dbispconfig Sleep 0 NULL Kill 2121 ispconfig localhost:48025 dbispconfig Sleep 0 NULL Kill 2122 ispconfig localhost:48026 dbispconfig Sleep 0 NULL
This is ok. the connections are from several services like postfix, pureftpd, courier, amavisd etc. which all connect with the ispconfig user to the mysql database. ISPconfig itself uses just one connetion when you are logged into the interface and another connection when the backend starts the processing. both connections are closed right after the page has been delivered or the data has been processed. If you reach the max connection limit of mysql, this happens most likely when a lot of spam comes in. You can set a higher connection limit in the mysql my.cnf file. Add or edit the following lines in the [mysqld] section of the file: max_connections=500 max_user_connections=500
Ah I see, I could have thought of that myself I've set the max connections higher and now everything's fine! Thanks!
Sorry to add to an ancient thread, but I have been experiencing this as well, and creating more connections does not make the problem go away (MySQL just has more available connections to put to sleep...). Sleeping threads allegedly do not consume much CPU, but they will take up memory, as well as prevent other processes to reuse connections. Thus, having a higher number of available connections will effectively help a bit (at the cost of using more memory), but it doesn't 'fix' all the problems. While it's really hard to track this down, I'm assuming that there are parts of the core ISPConfig package (not only the external apps such as Roundcube, etc.) which make persistent connections to MySQL and never close them, relying upon MySQL to do that (if and when needed). The problem is the timeout for that. Allegedly, the defaults are around 8 hours, which, on a busy server, can be way too long. You can change these defaults either on /etc/my.conf (or wherever your particular setup stores the global configuration for MySQL/MariaDB — under Ubuntu 20.04 with MariaDB 10.4.20, it's under /etc/mysql/mariadb.conf.d/50-server.cnf): Code: [mysqld] interactive_timeout = 180 wait_timeout = 180 ...or running the following commands inside a MySQL/MariaDB shell (takes effect immediately but will not survive a MySQL/MariaDB restart): Code: SET GLOBAL interactive_timeout = 180; SET GLOBAL wait_timeout = 180; The value is in seconds, so you can adjust according to your needs. Note that both must be set.
The MySQL connections that you see on your ISPConfig 3 system are not from ispconfig, they are from postfix, amavis, pure-ftpd and dovecot which all use a MySQL user named 'ispconfig' to connect to mysql. So the connections are not, as you assumed, from ispconfig core and ispconfig has no influence on when these connections get closed as the services I mentioned are reusing the connections to speed up your server.
Aye, my apologies, you're quite correct. In fact, it's rather simple to distinguish between both: 'real' ISPConfig3 connections will show up as localhost while all other connections from the many running services which also use the dbispconfig MySQL user will often have an additional port number, e.g. localhost:12345. If lsof is installed, with $ lsof -i :12345 it's easy to see which process (postfix, amavis, dovecot, roundcube, etc.) is currently logging in to MySQL and letting the connection go stale. These are frequent! However, ISPConfig3 itself also keeps a few sleeping threads around. I'd guess that, on my below-average-traffic server, it's about 50/50, i.e. half the sleeping threads are from ISPConfig3, the rest come from the many other applications using that MySQL account... Nevertheless, I'm quite pleased with the 'simple' solution of essentially reducing the sleep thread timeout from 8 hours to just 3 minutes! So far, I don't see any adverse effects, rather the contrary — the server has a much lower CPU usage and MySQL is taking about half of the (non-virtual) RAM it was taking. I suppose that I might now be able to reduce the total number of MySQL connections (perhaps even some of the many caches...) and therefore improve overall resource consumption even more. For now, I'm letting it run just with the considerably shorter timeout and see what happens; in a week or so, I will have gathered enough statistics to make a few tweaks and see if I can get a tiny little bit of extra performance...
