mysql线程和连接数查询

查看mysql使用的线程模式

show variables like ‘%thread_handling%’;
+—————–+—————————+
| Variable_name | Value |
+—————–+—————————+
| thread_handling | one-thread-per-connection |
+—————–+—————————+

查看最大连接数

show variables like ‘%max_connections%’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 1024 |
+—————–+——-+

设置最大连接数

set global max_connections=1024

查看当前连接情况

show status like ‘Threads%’;

| Threads_cached | 2 | 缓存的当前没在用的线程数
| Threads_connected | 6 | 打开的连接数，跟show processlist看到数量相同
| Threads_created | 1119 | 创建过的总线程数
| Threads_running | 1 | 当前正在处理sql的连接数

查看详细的连接信息

show processlist;
| 1249 | root | localhost:51297 | DB1 | Sleep | 16952 | | NULL |
| 1250 | root | localhost:51334 | DB1 | Sleep | 7593 | | NULL |
| 1257 | root | localhost:53919 | DB2 | Sleep | 3397 | | NULL |
| 1259 | root | localhost:53980 | DB3 | Sleep | 626 | | NULL |
| 1260 | root | localhost:53981 | DB4 | Sleep | 553 | | NULL |
| 1264 | root | localhost | NULL | Query | 0 | NULL | show processlist

按Ip查看连接数

select SUBSTRING_INDEX(host,’:’,1) as ip , count() from information_schema.processlist group by ip order by count() desc;
+———–+———-+
| ip | count(*) |
+———–+———-+
| localhost | 6 |