CODEWITHSUNDEEP
mysqlsqldatabase
Yeison Cruz
Yeison Cruz

Reputation: 11

MYSQL, limit for max_user_connections

I am have a trouble with my database, I set up the max_user_connections to 800 and the issue persist, "User root already has more than 'max_user_connections' active connections" I would like to know if there is a limit for max_user_connections, Can I set 10000 and that is not going to break my database ? ¿ how can i know the limit ?

If I run SHOW PROCESSLIST and Get this, All is right ? http://prntscr.com/cc8bbv

Upvotes: 1

Views: 12408

Answers (2)

Naruto
Naruto

Reputation: 4329

To see what is the max connnections for a user use the vairable in information_schema for your mysql database to see global configuration for this.

SHOW VARIABLES LIKE "max_user_connections";

If it is zero, then it is no limit otherwise the limit is set to the one as response.

If you want to check for particular user, use this

SELECT max_user_connections FROM mysql.user WHERE user='db_user' AND host='localhost';

Now for your question what is the effect on increasing this, As per

http://www.electrictoolbox.com/update-max-connections-mysql/

Note that increasing the number of connections that can be made will increase the potential amount of RAM required for MySQL to run. Increase the max_connections setting with caution!

So here max_connections is total number of connections allowed from all users.

Also i would suggest to use connection pool so that the size of pool is fixed and it will be from that only and is not growing. Also make sure it is returned back to pool once the work is done.

Upvotes: 4

Luke
Luke

Reputation: 848

Too many active connections

The problem you are having is because you are not closing previous connections you have used to access the database. (And its also likely that you are running many separate queries that could all be compressed into a single query)

From looking at your error message, "User root" has more than the max available connections. There aren't 800 different people accessing the database, you are accessing the database 800 different times and not cleaning up afterwards.

Upvotes: 1

Related Questions