CODEWITHSUNDEEP
phpmysqlcodeigniter
Linesofcode
Linesofcode

Reputation: 5903

MySQL: user has more than 'max_user_connections' active connections

I know this is well talked topic, but in my case is a little bit different. I have a SaaS service where each subscription has its own database.

By querying SHOW PROCESSLIST in my 25 databases, I get the same result in every one:

enter image description here

All databases seem to not have any connection whatsoever, yet I still receive the error:

User already has more than 'max_user_connections' active connections

I'm using a shared hosting CPanel and the limit max_connections is 150.

enter image description here

How is it possible that the max_connections has been reached when the processlist shows that nothing is being query? Plus, how do I reset the connections without the help of the hosting?

This is a PHP with CodeIgniter framework project.

Upvotes: 1

Views: 3778

Answers (1)

Linesofcode
Linesofcode

Reputation: 5903

Solved.

After contacting the hosting, they incremented the max_user_connections to 20, but the problem still persists. The project has been running for more than a year without a problem, so it was confusing to me.

I used this hook https://github.com/ozanmora/ci_log_query to print all the queries being done and it was clear that I was connecting to multiple models that weren't being used.

For example in all my classes (and controllers) I was loading all the models in the constructor and some of this models were only used in a certain function, therefore they were consuming resources without being used.

Example:

public function _construct()
{
    $this->load->model('a_model');
    $this->load->model('b_model');
    $this->load->model('c_model');
    $this->load->model('d_model');
    $this->load->model('e_model');
}

And I only call them in some functions, like:

public function test()
{
    $this->a_model->doSometing();
    $this->b_model->doSometing();
}

public function test2()
{
    $this->c_model->doSometing();
}

public function test3()
{
    $this->d_model->doSometing();
}

The solution to avoid loading unnecessary models/connections is to only load the models when needed. Although this was working previously without any problem, I end up fixing all my controllers/classes (which is quite a bit). The connections were drastically reduced and now its working.

public function _construct()
{

}

public function test()
{
    $this->load->model('a_model');
    $this->load->model('b_model');
    
    $this->a_model->doSometing();
    $this->b_model->doSometing();
}

public function test2()
{
    $this->load->model('c_model');

    $this->c_model->doSometing();
}

public function test3()
{
    $this->load->model('d_model');

    $this->d_model->doSometing();
}

Upvotes: 3

Related Questions