Better understanding max_user_connections count in Mysql

Question

1. If on a page I have connection and query to more than one database will count as two connections
2. And if it is the same database but has multiple queries (select) it will count as several active connections?
3. Why does show proccesslist become active (Sleep)? all connections stay in Sleep for the same period of time? I have the impression that they are different times, and because they end up as Sleep?

Answer 1

I will simply enumerate:

1. Despite the name the max_user_connections not related to users, PHP is the "client-side" of mysql, so each connection with the Apis as:

   • mysqli_connect
   • new PDO

   It is that count for this number, of course that I am not talking about persistent connections (that is another subject)

2. If on a page I have connection and query to 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 with the Apis, at the time of executing the querys the connection is already established, of course there are situations of loss of connection which you can treat and try to reconnect, for example:

   mysqli::ping() is an example of a function that can be used to check the state of a connection already made before executing a query, because if the page has a long script there is a possibility that the connection has been lost by things like server side connection instability that communicates with the mysql server, with this you could create a recursive function that if you missed the "signal" would try to run the query again, or could even check the query error with mysqli::$errno (at the moment do not know what the number of connection error lost, I will edit this part of the reply soon)

3. Why does show proccesslist become active (Sleep)? all connections stay in Sleep for the same period of time? I have the impression that they are different times, and because 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 even when running mysqli::close() (or Apis equivalent) maybe the connection is not fully completed, who solves this I think maybe is the main PHP program, even after your script has already closed the connection (I’m not sure, it might actually close and wait to close), I will confirm this later, but just to get back to focus, if a PHP page makes a connection to the database, but you don’t run anything, nor a query if you want it very likely it will be a Sleep in the list, I give you the suggestion to maybe implement your own script, which only connects if you run a query at least, I did this, decreased 30% of unnecessary connections I had, a very 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 it 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 execute new Banco on a page, but there are no querys, so it will not connect, which will help save the server a lot. Understand that this code is just an example, you can do this in countless ways, and I’m not saying that the above form is ideal, it’s just an illustrative example to understand