CODEWITHSUNDEEP
mysql
justadev
justadev

Reputation: 1526

max_user_connections error meaning and fixing

I am getting this error from mysql: "Unable to connect to database [User xyzxyz already has more than 'max_user_connections' active connections"

My current max_user_connections=50, and I am running on a VPS

As a first step (I read somewhere that it should help) I created a new mysql user that will handle all the background operations which are the majority of the operations on my site. So now I have 2 users:

Still after that change users are reporting getting "xyzxyz_users already has more than 'max_user_connections' active connections"

This is a busy site that I running, but I wonder if I don't have a mistake in the code that is causing this.

I tried running "SHOW PROCESSLIST" and "SHOW FULL PROCESSLIST", but I only see 2 rows that are returned (one SLEEP and one QUERY of the the show processlist).

As far I know, I don't have any type of persistent mysql connections

Below is the code that I use the start the mysql connection at the head of each php file (using 'require')

if (!(isset($use_pdo))) {
  $conn_mls = mysql_connect($dbhost, $dbuser, $dbpass);
  mysql_select_db($dbname);
  mysql_query("SET NAMES utf8");

}else{
    $options = array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    );
    $dsn = "mysql:host=127.0.0.1;dbname=$dbname;charset=utf8";
    $conn = new PDO($dsn, $dbuser , $dbpass , $options);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

};

In some cases I use mysqli, I don't think I use persist connections there, I can post the code if required.

When does mysql create a new connection? If I have max_user_connections=50 does it mean that only 50 users can connect to the website at the same time? Do I need to manually close the connection in the code, or does mysql handles it itself?

Upvotes: 0

Views: 1047

Answers (1)

ron tornambe
ron tornambe

Reputation: 10780

I would suggest implementing persistent PDO connections everywhere (if you are able to budget the time) and increasing max-connections if need be. This will also improve application consistency and readability.

As the link I provided in my comment explains, PDO caches persistent connections:

"Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application."

I am not sure this will solve your connections problem, but it will improve performance and may have a positive effect on your connections issue depending on whether multiple users have the same credentials. Of course, if you are able to increase max-connections to support all concurrent users, then you can do so without implementing PDO Persistent connections.

Upvotes: 1

Related Questions