0

I have a database where two users are connecting. Both are applications. Now, one of the application always log "Too many connections.." to mysql. So I increased max connections to higher value. After one day, max_connections is limit + 1, so I increased second time. After one day, max_connections is limit + 1. So I tried to set up max_connections to 220 and want to force that both applications has to split connections with setting max_user_connections to 100. But again: used connections is 221 now. In processlist just the two users making connection.

How can I setup mysql to reserve connections for one user?

I cannot use the 'every hour' parameters. It is important that both systems can connect everytime to the database. I am using suse 11 with mysql 5.5.

Regards, Wyphorn

Improve this question
3
  • There is something wrong with coding of the application. Take care of that. Commented May 6, 2017 at 22:11
  • Your application(s) isnt (arent) closing their connections to the server after theyre done. Make sure you end the connection when theyre done with the transactions or that they reuse them instead of creating new ones. Commented May 7, 2017 at 1:08
  • Yes, i think you both are right. But I have no influence to the applications and cannot change their database connection behaviour. But why MySQL accepts more than the 100 defined connections? Commented May 7, 2017 at 8:34

1 Answer 1

Reset to default
1

As per comments by Xaqron and Lou, the application is misbehaving. If you can't change that, adjust the MySQL wait_timeout parameter (in /etc/my.cnf or wherever your MySQL configuration file is stored) to a value (in seconds) that allows the application to operate whilst killing idle connections after an appropriate period. You'll need to determine what that value might be, but if you can experiment a little, you might want to start at 60 seconds and monitor the application behaviour. (You'll need to restart MySQL after making the change there.)

You can interactively make the change via a MySQL administrative connection by running set global wait_timeout=60 (or whether value you want), which will take effect immediately for new sessions; existing sessions will keep the wait_timeout value that was in place when they started.

Improve this answer
2
  • Ok, my security administrator found the answer. There was a wrong firewall setting so one application did never recieved an answer for successful query and did not closed the connection. So Paks answer was right too, force time out on mysql to close connections server side. Commented May 11, 2017 at 16:35
  • Thanks for letting us know that you got the problem resolved! Commented May 13, 2017 at 20:36

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.