1、登陆mysql,可以通过 show_processlist 命令来查看当前的所有连接状态
mysql> show full processlist;
+------+------+----------------------+----------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+----------------------+----------+---------+------+----------+-----------------------+
| 5467 | root | 1.1.1.1:39610 | datasafe | Sleep | 7868 | | NULL |
| 5468 | root | 1.1.1.1:39740 | datasafe | Sleep | 7610 | | NULL |
| 5469 | root | 1.1.1.1:39808 | datasafe | Sleep | 7518 | | NULL |
| 5470 | root | 1.1.1.1:39830 | datasafe | Sleep | 7506 | | NULL |
| 5471 | root | 1.1.1.1:39832 | datasafe | Sleep | 7506 | | NULL |
| 5472 | root | 1.1.1.1:39834 | datasafe | Sleep | 7506 | | NULL |
| 5473 | root | 1.1.1.1:39836 | datasafe | Sleep | 7503 | | NULL |
| 5474 | root | 1.1.1.1:39838 | datasafe | Sleep | 7503 | | NULL |
| 5475 | root | 1.1.1.1:39840 | datasafe | Sleep | 7503 | | NULL |
| 5476 | root | 1.1.1.1:39842 | datasafe | Sleep | 7503 | | NULL |
| 5477 | root | 1.1.1.1:39844 | datasafe | Sleep | 7503 | | NULL |
| 5478 | root | 1.1.1.1:39846 | datasafe | Sleep | 7503 | | NULL |
| 5479 | root | 1.1.1.1:39848 | datasafe | Sleep | 7503 | | NULL |
| 5480 | root | 1.1.1.1:39850 | datasafe | Sleep | 7503 | | NULL |
| 5481 | root | 1.1.1.1:39852 | datasafe | Sleep | 7503 | | NULL |
| 5482 | root | 1.1.1.1:39854 | datasafe | Sleep | 7503 | | NULL |
| 5483 | root | 1.1.1.1:39856 | datasafe | Sleep | 7503 | | NULL |
| 5484 | root | 1.1.1.1:39858 | datasafe | Sleep | 7501 | | NULL |
| 5485 | root | 1.1.1.1:39912 | datasafe | Sleep | 7487 | | NULL |
| 5486 | root | 1.1.1.1:39928 | datasafe | Sleep | 7483 | | NULL |
| 5487 | root | 1.1.1.1:39948 | datasafe | Sleep | 7477 | | NULL |
| 5488 | root | 1.1.1.1:39952 | datasafe | Sleep | 7477 | | NULL |
| 5489 | root | 1.1.1.1:39954 | datasafe | Sleep | 7477 | | NULL |
| 5490 | root | 1.1.1.1:39956 | datasafe | Sleep | 7475 | | NULL |
| 5491 | root | 1.1.1.1:39958 | datasafe | Sleep | 7475 | | NULL |
| 5492 | root | 1.1.1.1:39960 | datasafe | Sleep | 7475 | | NULL |
| 5493 | root | 1.1.1.1:39962 | datasafe | Sleep | 7475 | | NULL |
| 5494 | root | 1.1.1.1:39964 | datasafe | Sleep | 7475 | | NULL |
| 5495 | root | 1.1.1.1:39966 | datasafe | Sleep | 7475 | | NULL |
| 5496 | root | 1.1.1.1:39968 | datasafe | Sleep | 7475 | | NULL |
| 5497 | root | 1.1.1.1:39970 | datasafe | Sleep | 7470 | | NULL |
| 5498 | root | 1.1.1.1:39972 | datasafe | Sleep | 7470 | | NULL |
| 5499 | root | 1.1.1.1:39974 | datasafe | Sleep | 7470 | | NULL |
| 5500 | root | 1.1.1.1:39976 | datasafe | Sleep | 7470 | | NULL |
| 5501 | root | 1.1.1.1:39978 | datasafe | Sleep | 7470 | | NULL |
| 5502 | root | 1.1.1.1:39980 | datasafe | Sleep | 7470 | | NULL |
| 5503 | root | 1.1.1.1:39982 | datasafe | Sleep | 7470 | | NULL |
| 5504 | root | 1.1.1.1:39984 | datasafe | Sleep | 7470 | | NULL |
| 5505 | root | 1.1.1.1:39986 | datasafe | Sleep | 7470 | | NULL |
| 5506 | root | 1.1.1.1:40008 | datasafe | Sleep | 7441 | | NULL |
| 5507 | root | 1.1.1.1:40010 | datasafe | Sleep | 7441 | | NULL |
| 5508 | root | 1.1.1.1:40012 | datasafe | Sleep | 7441 | | NULL |
| 5509 | root | 1.1.1.1:40056 | datasafe | Sleep | 7409 | | NULL |
| 5510 | root | 1.1.1.1:40094 | datasafe | Sleep | 7342 | | NULL |
| 5511 | root | 1.1.1.1:40096 | datasafe | Sleep | 7342 | | NULL |
| 5512 | root | 1.1.1.1:40140 | datasafe | Sleep | 7277 | | NULL |
| 5513 | root | 1.1.1.1:40180 | datasafe | Sleep | 7248 | | NULL |
| 5514 | root | 1.1.1.1:40182 | datasafe | Sleep | 7244 | | NULL |
| 5515 | root | 1.1.1.1:40214 | datasafe | Sleep | 7209 | | NULL |
| 5516 | root | 1.1.1.1:40292 | datasafe | Sleep | 7115 | | NULL |
| 5517 | root | 1.1.1.1:40350 | datasafe | Sleep | 7051 | | NULL |
| 5518 | root | 1.1.1.1:40352 | datasafe | Sleep | 7051 | | NULL |
| 5519 | root | 1.1.1.1:40378 | datasafe | Sleep | 7034 | | NULL |
| 5520 | root | 1.1.1.1:40388 | datasafe | Sleep | 7017 | | NULL |
| 5521 | root | 1.1.1.1:40390 | datasafe | Sleep | 7012 | | NULL |
| 5522 | root | 1.1.1.1:40424 | datasafe | Sleep | 6944 | | NULL |
| 5523 | root | 1.1.1.1:40430 | datasafe | Sleep | 6823 | | NULL |
| 5524 | root | 1.1.1.1:40442 | datasafe | Sleep | 6525 | | NULL |
| 5525 | root | 1.1.1.1:40452 | datasafe | Sleep | 6324 | | NULL |
| 5526 | root | 1.1.1.1:40454 | datasafe | Sleep | 6323 | | NULL |
| 5527 | root | 1.1.1.1:40464 | datasafe | Sleep | 6074 | | NULL |
| 5530 | root | 1.1.1.1:40484 | datasafe | Sleep | 5742 | | NULL |
| 5531 | root | 1.1.1.1:40486 | datasafe | Sleep | 5739 | | NULL |
| 5532 | root | 1.1.1.1:40488 | datasafe | Sleep | 5738 | | NULL |
| 5533 | root | 1.1.1.1:40490 | datasafe | Sleep | 5734 | | NULL |
| 5535 | root | 1.1.1.1:40494 | datasafe | Sleep | 5732 | | NULL |
| 5536 | root | 1.1.1.1:40498 | datasafe | Sleep | 5730 | | NULL |
| 5538 | root | 1.1.1.1:40502 | datasafe | Sleep | 5724 | | NULL |
| 5539 | root | 1.1.1.1:40504 | datasafe | Sleep | 5723 | | NULL |
| 5540 | root | 1.1.1.1:40506 | datasafe | Sleep | 5720 | | NULL |
| 5541 | root | 1.1.1.1:40508 | datasafe | Sleep | 5717 | | NULL |
| 5543 | root | 1.1.1.1:40540 | datasafe | Sleep | 5708 | | NULL |
| 5544 | root | 1.1.1.1:40542 | datasafe | Sleep | 5708 | | NULL |
| 5545 | root | 1.1.1.1:40544 | datasafe | Sleep | 5707 | | NULL |
| 5546 | root | 1.1.1.1:40546 | datasafe | Sleep | 5707 | | NULL |
| 5547 | root | 1.1.1.1:40548 | datasafe | Sleep | 5707 | | NULL |
| 5548 | root | 1.1.1.1:40550 | datasafe | Sleep | 5707 | | NULL |
| 5549 | root | 1.1.1.1:40552 | datasafe | Sleep | 5706 | | NULL |
| 5550 | root | 1.1.1.1:40554 | datasafe | Sleep | 5706 | | NULL |
| 5551 | root | 1.1.1.1:40556 | datasafe | Sleep | 5706 | | NULL |
| 5552 | root | 1.1.1.1:40558 | datasafe | Sleep | 5705 | | NULL |
| 5553 | root | 1.1.1.1:40560 | datasafe | Sleep | 5702 | | NULL |
| 5554 | root | 1.1.1.1:40562 | datasafe | Sleep | 5702 | | NULL |
| 5555 | root | 1.1.1.1:40564 | datasafe | Sleep | 5697 | | NULL |
| 5556 | root | 1.1.1.1:40566 | datasafe | Sleep | 5695 | | NULL |
| 5557 | root | 1.1.1.1:40568 | datasafe | Sleep | 5695 | | NULL |
| 5558 | root | 1.1.1.1:40570 | datasafe | Sleep | 5691 | | NULL |
| 5559 | root | 1.1.1.1:40572 | datasafe | Sleep | 5686 | | NULL |
| 5560 | root | 1.1.1.1:40574 | datasafe | Sleep | 5684 | | NULL |
| 5561 | root | 1.1.1.1:40576 | datasafe | Sleep | 5683 | | NULL |
| 5562 | root | 1.1.1.1:40614 | datasafe | Sleep | 5675 | | NULL |
| 5563 | root | 1.1.1.1:40616 | datasafe | Sleep | 5675 | | NULL |
| 5564 | root | 1.1.1.1:40618 | datasafe | Sleep | 5673 | | NULL |
| 5565 | root | 1.1.1.1:40620 | datasafe | Sleep | 5672 | | NULL |
| 5566 | root | 1.1.1.1:40622 | datasafe | Sleep | 5672 | | NULL |
| 5567 | root | 1.1.1.1:40626 | datasafe | Sleep | 5661 | | NULL |
| 5568 | root | 1.1.1.1:40646 | datasafe | Sleep | 5642 | | NULL |
| 5569 | root | 1.1.1.1:40648 | datasafe | Sleep | 5641 | | NULL |
| 5570 | root | 1.1.1.1:40668 | datasafe | Sleep | 5575 | | NULL |
| 5571 | root | 1.1.1.1:40670 | datasafe | Sleep | 5575 | | NULL |
| 5572 | root | 1.1.1.1:40674 | datasafe | Sleep | 5489 | | NULL |
| 5573 | root | 1.1.1.1:40678 | datasafe | Sleep | 5459 | | NULL |
| 5574 | root | 1.1.1.1:40726 | datasafe | Sleep | 5411 | | NULL |
| 5575 | root | 1.1.1.1:40766 | datasafe | Sleep | 5314 | | NULL |
| 5576 | root | 1.1.1.1:40768 | datasafe | Sleep | 5305 | | NULL |
| 5577 | root | 1.1.1.1:40812 | datasafe | Sleep | 5249 | | NULL |
| 5578 | root | 1.1.1.1:40814 | datasafe | Sleep | 5243 | | NULL |
| 5579 | root | 1.1.1.1:40816 | datasafe | Sleep | 5243 | | NULL |
| 5580 | root | 1.1.1.1:40838 | datasafe | Sleep | 5213 | | NULL |
| 5581 | root | 1.1.1.1:40906 | datasafe | Sleep | 5080 | | NULL |
| 5582 | root | 1.1.1.1:40936 | datasafe | Sleep | 5017 | | NULL |
| 5583 | root | 1.1.1.1:40992 | datasafe | Sleep | 4790 | | NULL |
| 5584 | root | 1.1.1.1:41302 | datasafe | Sleep | 4297 | | NULL |
| 5585 | root | 1.1.1.1:41488 | datasafe | Sleep | 4030 | | NULL |
| 5586 | root | 1.1.1.1:41502 | datasafe | Sleep | 3999 | | NULL |
| 5587 | root | 1.1.1.1:41526 | datasafe | Sleep | 3980 | | NULL |
| 5588 | root | 1.1.1.1:41546 | datasafe | Sleep | 3969 | | NULL |
| 5589 | root | 1.1.1.1:41548 | datasafe | Sleep | 3969 | | NULL |
| 5590 | root | 1.1.1.1:41550 | datasafe | Sleep | 3969 | | NULL |
| 5591 | root | 1.1.1.1:41576 | datasafe | Sleep | 3951 | | NULL |
| 5592 | root | 1.1.1.1:41618 | datasafe | Sleep | 3932 | | NULL |
| 5593 | root | 1.1.1.1:41640 | datasafe | Sleep | 3920 | | NULL |
| 5594 | root | 1.1.1.1:42128 | datasafe | Sleep | 3181 | | NULL |
| 5595 | root | 1.1.1.1:42164 | datasafe | Sleep | 3147 | | NULL |
| 5596 | root | 1.1.1.1:42166 | datasafe | Sleep | 3147 | | NULL |
| 5597 | root | 1.1.1.1:42168 | datasafe | Sleep | 3147 | | NULL |
| 5598 | root | 1.1.1.1:42170 | datasafe | Sleep | 3147 | | NULL |
| 5599 | root | 1.1.1.1:42172 | datasafe | Sleep | 3147 | | NULL |
| 5600 | root | 1.1.1.1:42174 | datasafe | Sleep | 3147 | | NULL |
| 5601 | root | 1.1.1.1:42176 | datasafe | Sleep | 3147 | | NULL |
| 5602 | root | 1.1.1.1:42178 | datasafe | Sleep | 3147 | | NULL |
| 5603 | root | 1.1.1.1:42180 | datasafe | Sleep | 3147 | | NULL |
| 5604 | root | 1.1.1.1:42182 | datasafe | Sleep | 3147 | | NULL |
| 5605 | root | 1.1.1.1:42184 | datasafe | Sleep | 3147 | | NULL |
| 5606 | root | 1.1.1.1:42186 | datasafe | Sleep | 3147 | | NULL |
| 5607 | root | 1.1.1.1:42188 | datasafe | Sleep | 3147 | | NULL |
| 5608 | root | 1.1.1.1:42190 | datasafe | Sleep | 3147 | | NULL |
| 5609 | root | 1.1.1.1:42192 | datasafe | Sleep | 3147 | | NULL |
| 5610 | root | 1.1.1.1:42194 | datasafe | Sleep | 3147 | | NULL |
| 5611 | root | 1.1.1.1:42196 | datasafe | Sleep | 3147 | | NULL |
| 5612 | root | 1.1.1.1:42300 | datasafe | Sleep | 2917 | | NULL |
| 5613 | root | 1.1.1.1:42372 | datasafe | Sleep | 2788 | | NULL |
| 5614 | root | 1.1.1.1:42418 | datasafe | Sleep | 2723 | | NULL |
| 5617 | root | 1.1.1.1:42508 | datasafe | Sleep | 2594 | | NULL |
| 5619 | root | 1.1.1.1:42646 | datasafe | Sleep | 2426 | | NULL |
| 5620 | root | 1.1.1.1:42682 | datasafe | Sleep | 2398 | | NULL |
| 5621 | root | 1.1.1.1:42822 | datasafe | Sleep | 2201 | | NULL |
| 5624 | root | 1.1.1.1:43026 | datasafe | Sleep | 1969 | | NULL |
| 5625 | root | 1.1.1.1:43028 | datasafe | Sleep | 1969 | | NULL |
| 5628 | root | 1.1.1.1:43348 | datasafe | Sleep | 1543 | | NULL |
| 5629 | root | 1.1.1.1:43350 | datasafe | Sleep | 1543 | | NULL |
| 5630 | root | 1.1.1.1:43352 | datasafe | Sleep | 1541 | | NULL |
| 5631 | root | 1.1.1.1:43354 | datasafe | Sleep | 1541 | | NULL |
| 5632 | root | 1.1.1.1:43356 | datasafe | Sleep | 1541 | | NULL |
| 5633 | root | 1.1.1.1:43358 | datasafe | Sleep | 1541 | | NULL |
| 5634 | root | 1.1.1.1:43360 | datasafe | Sleep | 1541 | | NULL |
| 5644 | root | 1.1.1.1:43558 | datasafe | Sleep | 1380 | | NULL |
| 5645 | root | 1.1.1.1:43560 | datasafe | Sleep | 1380 | | NULL +------+------+----------------------+----------+---------+------+----------+-----------------------
162 rows in set (0.00 sec)
可以发现, Sleep 的连接占了绝大多数。
2、分别执行命令:
show global variables like'%wait_timeout';
set global wait_timeout=250;
MySQL 数据库有一个属性 wait_timeout 就是 sleep 连接最大存活时间,默认是 28800 s,换算成小时就是 8 小时,我的天呐!这也太长了!严重影响性能。相当于今天上班以来所有建立过而未关闭的连接都不会被清理。
我们将他修改成一个合适的值,这里我改成了 250s。当然也可以在配置文件中修改,添加 wait_timeout = 250。这个值可以根据项目的需要进行修改,以 s 为单位。我在这里结合 navicat 的超时请求机制配置了 240s。
mysql> show global variables like'%wait_timeout';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 31536000 |
| wait_timeout | 28800 |
+--------------------------+----------+
3 rows in set (0.01 sec)
mysql> set global wait_timeout=250;
Query OK, 0 rows affected (0.00 sec)
【备用】
查看当前 Mysql 最大连接数量是多少:
show variables like'%max_connections%';
查看从这次 mysql 服务启动到现在,同一时刻并行连接数的最大值:
show status like'Max_used_connections';
也可以以下操作,但是不会自动释放连接数
1、修改最大连接数
cd /usr/local/mysql/
vim my.cnf
max_connections =200(通常,mysql的最大连接数默认是100, 最大可以达到16384)
2、重启mysql
/etc/init.d/mysqld restart