MySQL连接优化

查询连接数

SHOW VARIABLES LIKE 'max_connections';  -- 144
SHOW VARIABLES LIKE 'max_user_connections';  -- 0
SHOW GLOBAL STATUS LIKE 'Threads_connected';  -- 85
SHOW GLOBAL STATUS LIKE 'Max_used_connections'; -- 147
SHOW GLOBAL STATUS LIKE 'Connection_errors_max_connections'; -- 636
SHOW GLOBAL STATUS LIKE 'Aborted_connects'; -- 6893

谁/从哪来/占了多少

SELECT USER, HOST, COUNT(*) AS cnt
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY cnt DESC;

列出长时间 SLEEP 或执行很慢的连接

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE (COMMAND='Sleep' AND TIME > 60) OR TIME > 30
ORDER BY TIME DESC;

列出查询慢 sleep超过1000s的

SELECT CONCAT('KILL CONNECTION ', ID, ';') AS kill_stmt
FROM information_schema.PROCESSLIST
WHERE COMMAND='Sleep' AND TIME > 1000;

优化项 5分钟无活动就断开

SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;