hi, can anyone tell me the difference between the Max Open connections value (default is 300) that we can set from the system console and the value we get via the “show variables like “max_connections”;” command from within the mysql console (default is 150)?. We have a self-hosted setup
Hi @amir.jawaid and welcome to the Mattermost forums!
SqlSettings.MaxOpenConns variable in your Mattermost server’s
config.json file is telling the server how many connections it should be able to open to the database at maximum. The value you can see when running the query on your database server is the maximum possible connections your database server is configured to accept, so if the connections in the Mattermost server are higher than the ones from your database server, you might run into a situation where the Mattermost server tries to open a connection to the database and fails, because the MySQL server refuses the connection with an error message about having reached the maximum possible connections.
You should either lower the
SqlSettings.MaxOpenConns setting in your Mattermost server to a value below the MySQL servers setting, or you should increase the possible connections on your MySQL server. For the latter, please be aware that allowing more connections will also increase memory usage and other system resources on your MySQL server, so I’d suggest to check how many connections have been made at maximum during the last server restart, which should give you an idea of what to set this value to.
You can use the following query to see the maximum used connection count since the last server startup (15 on my demo system):
mysql> show status like "Max_used_connections"; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 15 | +----------------------+-------+ 1 row in set (0.00 sec)
thank you for the superb detailed reply. I truly appreciate this. The too many open connections problem started when we recently upgraded to Mattermost Version: 7.7.1 / Database Schema Version: 100
Upon checking the values once we started to see “too many connections” error, the SqlSettings.MaxOpenConns was set to 300 while the value with mysql was 150. We made both values to be same at 600, is having the same value OK?, perhaps we set it too high but since we did that, we did not face any issue
Btw the current max_used_connections value i see in our mysql is 140
the same value is OK as long as you‘re not reaching it. In theory, there are connection pool recreations on the client side which might cause short overusage and there are superuser reserved connections on the MySQL server which limit the number of available connections. Check the historically max used connections again in a week or two and if it‘s still around 150, you should be good to go with the values you had (and probably could even lower them both to 300).
thanks again for the detailed response
You’re welcome - I’m marking this topic as resolved now!