MySQL CentOS7 – Managing Sleeping Queries

centos7linuxMySQL

We have 150 databases running on the server and sometimes it is causing "too many connections" issue and also there are lot of sleeping queries running on the MySQL process. The sleeping queries time is over 200. Please find the server my.cnf configuration below. I have increased the max_connections 500 to 750 but the connections sometimes increasing over 750. is it safe to increase max_connections to 1000 or any additional configuration need in the my.cnf? Can you please any one help me, if anything missing on the configurations.

[mysqld]
performance-schema=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
query_cache_type=1

slow_query_log=1
slow_query_log_file="/var/log/mysql/mysql_slow.log"
max_connections = 750
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=43000
bind-address=0.0.0.0
innodb_buffer_pool_size= 6G
wait_timeout = 300
key_buffer_size = 2G
read_buffer_size = 16M
bulk_insert_buffer_size = 512M
myisam_sort_buffer_size = 2M
sql_mode = 'NO_ENGINE_SUBSTITUTION'
query_cache_limit = 536870912
query_cache_size = 268435456

Threads Running On Mysql

mysql> show status like "%Threads%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 12    |
| Threads_connected      | 659   |
| Threads_created        | 38451 |
| Threads_running        | 1     |
+------------------------+-------+

htop results:

1  [|      1.3%]   11 [       0.0%]   21 [       0.0%]   31 [       0.0%]
2  [||     3.2%]   12 [||||  29.5%]   22 [       0.0%]   32 [       0.0%]
3  [       0.0%]   13 [       0.0%]   23 [|      0.6%]   33 [       0.0%]
4  [|||   21.3%]   14 [||||  26.1%]   24 [||     2.6%]   34 [       0.0%]
5  [|      0.6%]   15 [|      0.6%]   25 [||     1.9%]   35 [       0.0%]
6  [||||  26.3%]   16 [||     3.2%]   26 [||     1.3%]   36 [       0.0%]
7  [||     1.9%]   17 [||     4.5%]   27 [       0.0%]   37 [       0.0%]
8  [||     3.8%]   18 [||     5.8%]   28 [       0.0%]   38 [       0.0%]
9  [|      0.6%]   19 [       0.0%]   29 [       0.0%]   39 [       0.0%]
10 [||     4.5%]   20 [||     5.1%]   30 [       0.0%]   40 [       0.0%]
Mem[|||||||||||||||||||17.7G/62.7G]   Tasks: 132, 473 thr; 3 running
Swp[|                  89.0M/7.81G]   Load average: 1.03 0.97 0.90 
                                        Uptime: 22 days, 16:31:03 

 PID USER      PRI  NI  VIRT   RES   SHR S CPU% MEM%   TIME+  Command
14706 mysql      20   0 29.3G 13.7G 10816 S 12.2 21.9 37h23:37 /usr/sbin/mysqld

Database Connection Script (PHP):

<?php
class setting{  
    private $conn;
    private $dbs;   
    function __construct($cid){
        $client=$this->getclientbyid($cid);     
        $db=new db_bridge($client['website']);
        if(!$db->exist()){
            echo($client['website']." not exist in our DB");
            die;    
        }
        else{
            $db->setid();   
            $this->setdb($db->get());
        }   
   }
   function __destruct(){
    $this->dbs=null;
   }
   function setdb($db){
    $this->conn=$db;
    self::connect();    
   }
   function connect(){
        try{
        $this->dbs=new PDO("mysql:host=".$this->conn['host'].";dbname=".$this->conn['db'], $this->conn['duser'],$pass);
        }
        catch(Exception $e){
            echo "Unable to connect Database";
        }        
    }
    protected function query($query,$both=false){
        $result=$this->dbs->prepare($query);
        $result->execute();
        if(!$both){
            $rs=$result->fetch(PDO::FETCH_ASSOC);
        }
        else{
            $rs=$result->fetch(PDO::FETCH_BOTH);
        }
        return $rs;
    }
}

Best Answer

Instead of increasing max_connections, it would be better to

  • Have clients close connections when finished with them.
  • If using "connection pooling", don't set its number too high.
  • Fewer client connections allowed (eg, max children in Apache or other web server)
  • Decrease wait_timeout (so that the Sleeping connections will be automatically disconnected sooner)

The number of sleeping connections does not necessarily reflect anything critically wrong. The number of concurrently running threads (SHOW GLOBAL STATUS LIKE 'Threads_running') is more important. It should be no more than a few dozen. (The "1" you showed would be the SHOW command itself; nothing else was "running".)

max_connections of a few hundered is high enough for most systems. Please provide the info requested by Wilson; this may give further insight into what the real issues are.

Other issues...

query_cache_size = 268435456 is too high. The Query cache does not scale well; limit it to 50M.

In order to get much out of the slowlog, set long_query_time = 1.