Better understanding the max_user_connections count in MySQL

0
  • If on a page I have connection and query more than one database will count as two connections
  • And if it's the same database but has multiple queries (select) will count as multiple active connections?
  • Why do not show proccesslist become active (sleep)? all the connections stay in sleep for an equal period of time? I have the impression that they are different times, and why do they end up as sleep?
  • asked by anonymous 13.08.2018 / 21:40

    1 answer

    1

    I'll simply enumerate:

  • Although the name max_user_connections is not related to users, PHP is the "client-side" of mysql, so every connection to the APIs is as follows:

    • mysqli_connect
    • new PDO

    It's that they count towards this number, of course this I'm not talking about persistent connections (that's another subject)

  •   

    If on a page I have connection and query more than one database will count as two connections

    No, as I said in the first, the connection is related from the point that actually connects to the APIs, at the time of executing the querys the connection is already established, of course there are connection loss situations which you can treat and try to reconnect, for example:

    mysqli::ping() is an example function that can be used to check the status of a connection already done before executing a query, because if the page has a long script there is a possibility of the connection getting lost by things like server-side connection instability that communicates with the mysql server, with this you could create a recursive function if I lost the "signal" I would try to execute the query again, or I could check up the error of the query with mysqli::$errno (I do not know the number of the connection error, I'll edit this part of the answer soon) / p>

  •   

    Why do not the show proccesslist become active (sleep)? all the connections stay in sleep for an equal period of time? I have the impression that they are different times, and why do they end up as sleep?

    Connections sleep are the connections that are open, but are not actually running, I'm not sure, but I believe that even when executing mysqli::close() (or equivalent APIs) maybe the connection is not completely terminated, I think this may be PHP's main program, even after your script has closed the connection (I'm not sure, it might actually close and wait for it to close), I'll confirm this later, but only to get back to focus, if a PHP page makes a connection to the database, but you do not execute anything, nor a query if you most likely will be a sleep in the list, I give you the suggestion to maybe implement your own script, which only connects if I run a query at least, I did this, decreased 30% of unnecessary connections I had, a pretty simple example:

    class Banco
    {
         private $conexao;
         private $host;
         private $user;
         private $pass;
         private $db;
    
         private function conectar()
         {
               //Previne conectar duas vezes
               if ($this->conexao) return true;
    
               $this->conexao = new mysqli($this->host, $this->user, $this->pass, $this->db);
    
               //Retorna true em caso de conexão bem sucedida, false caso contrário
               return !!$this->conexao;
         }
    
         // salva as variaveis para a conexão
         public function __construct($host, $user, $pass, $db)
         {
              $this->host = $host;
              $this->user = $user;
              $this->pass = $pass;
              $this->db   = $db;
         }
    
         // só fecha a conexão se existe uma conexão aberta
         public function close()
         {
              if ($this->conexao) $this->conexao->close();
         }
    
         // Qualquer método que usar do mysqli será executado a partir daqui e será aplicando no $this->conexao
         public function __call($metodo, $argumentos)
         {
              if ($this->conectar()) {
                   return call_user_func_array($metodo, $argumentos);
              }
    
              //Retorna
              return false;
         }
    
         // Qualquer proriedade/variavel do mysqli que quiser acessar será a partir daqui e será aplicando no $this->conexao->$propriedade, retornando somente o valor
         public function __get($propriedade) {
             return $this->conexao->$propriedade;
         }
    }
    

    In use I would look like this:

    $banco = new Banco('1.1.1.1', 'foo', 'senha', 'banco');
    
    //O banco só conecta neste momento
    $banco->query('SELECT ...');
    

    That is, if you run new Banco on a page, but there are no querys, then it will not connect, which will help save the server a lot. Understand that this code is just an example, you can do it in a number of ways, and I'm not saying that the above form is ideal, it's just an illustrative example to understand

  • 13.08.2018 / 22:21