Mysql too many conntions 问题解决以及优化

to many connections

• 报错信息

Too many connections

• 原因

连接数超过mysql设置的最大线程数
    1.一般是连接泄露导致
    2.也可能是mysql max_connections 设置过小导致

• 解决

手动断开连接

step 1  查看最大的链接
    SELECT substring_index(host, ':',1) AS host_name,state,count(*) FROM information_schema.processlist GROUP BY state, host_name order by host_name;
step 2 查看进程
    show processlist ;
    Id          user    host：port      command  status Time info
    15289454,apiserver,10.244.0.21:32918,apiserver,Sleep,29,"",
    15290359,apiserver,10.244.0.21:44534,apiserver,Sleep,29,"",
    15291995,apiserver,10.244.0.21:35212,apiserver,Sleep,29,"",
    15365612,apiserver,10.244.0.162:44682,apiserver,Sleep,5,"",
    22508742,apiserver,10.244.0.196:32842,apiserver,Sleep,33,"",
    22508744,apiserver,10.244.0.189:52898,apiserver,Sleep,33,"",

step 3 kill 
    kill processid

设置最大连接数

查看设置的最大连接数
show variables like "max_connections";

查看连接失效时间
show global variables like 'wait_timeout' ;

查看当前连接数
show status like 'Threads%';

设置最大连接（暂时）
set global max_connections=500;

设置最大连接数（永久）修改mysql配置文件
max_connections=500

排查连接泄露

根据用户筛选
select USER , count(*) from information_schema.processlist group by USER;

根据主机名
SELECT substring_index(host, ':',1) AS host_name,state,count(*) FROM information_schema.processlist GROUP BY state, host_name order by host_name;

• 优化,合理的根据服务器配置设置连接数

max_connections:最大连接数（151 默认值）

max_user_connections:用户最大连接数
wait_timeout 连接失效时间

而一个连接会消耗大约2M左右的内存
wait_timeout的值如果设置的比较小，连接会不停的创建和销毁，这样会浪费一定的IO资源