MySQL：如何治理连接数 ?


#MySQL 笔记

如何发现连接数过多的 IP

注意要使用高权限的管理员账号，否则统计会不全。

总连接数：

select count(*) from information_schema.processlist;

哪些IP连接数过多？

select * from (
    select substring_index(host,':',1) as ip , count(*) as connection_count from information_schema.processlist group by ip
) t order by t.connection_count desc

哪些IP连接某个库的连接数过多？

select * from (
    select substring_index(host,':',1) as ip, db, count(*) as connection_count from information_schema.processlist group by ip, db
) t order by t.connection_count desc

示例

mysql root@127.0.0.1:test> select count(*) from information_schema.processlist;
+----------+
| count(*) |
+----------+
| 1        |
+----------+
1 row in set
Time: 0.031s

mysql root@127.0.0.1:test> SELECT * FROM (
                        -> select substring_index(host,':',1) as ip , count(*) as connection_co
                        -> unt from information_schema.processlist group by ip
                        -> ) t order by t.connection_count desc
+-----------+------------------+
| ip        | connection_count |
+-----------+------------------+
| localhost | 1                |
+-----------+------------------+
1 row in set
Time: 0.015s

mysql root@127.0.0.1:test> SELECT * FROM (
                        -> select substring_index(host,':',1) as ip, db, count(*) as connection
                        -> _count from information_schema.processlist group by ip, db
                        -> ) t order by t.connection_count desc
+-----------+------+------------------+
| ip        | db   | connection_count |
+-----------+------+------------------+
| localhost | test | 1                |
+-----------+------+------------------+
1 row in set
Time: 0.009s

应用层如何限制

1、使用连接池

2、连接池最大连接数不宜过多。连接数并不是越多越好，可参考这篇讨论 数据库链接池终于搞对了，这次直接从100ms优化到3ms！ 。



( 本文完 )