Home / server / Questions / 848648
Accepted
Wyphorn
Asked: 2017-05-07 12:11:00 +0800 CST

mysql - max_connections and max_user_connections

  • 772

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

linux

1 Answers

  1. Best Answer

    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.

    • 1