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 towait_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 theSHOW
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
.