![Mysql线程消耗了整个cpu](https://linux22.com/image/586993/Mysql%E7%BA%BF%E7%A8%8B%E6%B6%88%E8%80%97%E4%BA%86%E6%95%B4%E4%B8%AAcpu.png)
我的 MySQL 服务器出了点问题。某个 mysql 线程在几个小时内耗尽了整个处理器。终止进程肯定有帮助,但如何才能跟踪代码是否在内部运行?
我目前的顶部:
PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ IO Command
1353 mysql 20 0 340M 70004 7652 S 31.0 1.1 1h34:28 0 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket
4344 mysql 20 0 340M 70004 7652 S 3.0 1.1 5:17.75 0 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket
5870 mysql 20 0 340M 70004 7652 S 2.0 1.1 1:13.46 0 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket
mysql> SHOW PROCESSLIST;
+------+-------+-----------+---------+---------+------+--------------+---------------
| Id | User | Host | db | Command | Time | State | Info
+------+-------+-----------+---------+---------+------+--------------+----------------
| 8731 | sites | localhost | mywebsite | Sleep | 2520 | | NULL
| 8734 | sites | localhost | mywebsite | Sleep | 2516 | | NULL
| 8737 | sites | localhost | mywebsite | Sleep | 2508 | | NULL
| 8741 | sites | localhost | mywebsite | Sleep | 2502 | | NULL
...
| 9848 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST
| 9952 | sites | localhost | mywebsite | Sleep | 2 | | NULL
| 9953 | sites | localhost | mywebsite | Query | 2 | Sending data | SELECT user_info.name, |
+------+-------+-----------+---------+---------+------+--------------+---------------------------
150 rows in set (0.00 sec)
好吧,在终止该进程之后(它已经占用了整个 CPU),输出发生了变化(10 分钟后仍然没有空进程):
mysql> SHOW PROCESSLIST;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+-------+------------------+
| 952 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+-----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
答案1
休眠的 MySQL 进程确实会消耗 CPU。
150 个休眠查询很多。您有数百个(或更多)并发连接吗?如果没有,这可能是首先要考虑的事情。
在您的 Web 应用程序中,请确保在完成查询后关闭 MySQL 连接。PHP 中的 mysql_close(),但实现基于您当前的设置。