MySQL服务器进程CPU占用100%的解决方法

朋友主机(Windows 2003 + IIS + PHP + MYSQL )近来 MySQL 服务进程 (mysqld-nt.exe) CPU 占用率总为 100% 高居不下。此主机有10个左右的 database, 分别给十个网站调用。据朋友测试,导致 mysqld-nt.exe cpu 占用奇高的是网站A,一旦在 IIS 中将此网站停止服务,CPU 占用就降下来了。一启用,则马上上升。

 MYSQL CPU 占用 100% 的解决过程
今天早上仔细检查了一下。目前此网站的七日平均日 IP 为2000,PageView 为 3万左右。网站A 用的 database 目前有39个表,记录数 60.1万条,占空间 45MB。按这个数据,MySQL 不可能占用这么高的资源。

  于是在服务器上运行命令,将 mysql 当前的环境变量输出到文件 output.txt:

d:\web\mysql> mysqld.exe --help >output.txt

  发现 tmp_table_size 的值是默认的 32M,于是修改 My.ini, 将 tmp_table_size 赋值到 200M:

d:\web\mysql> notepad c:\windows\my.ini
[mysqld]
tmp_table_size=200M

  然后重启 MySQL 服务。CPU 占用有轻微下降,以前的CPU 占用波形图是 100% 一根直线,现在则在 97%~100%之间起伏。这表明调整 tmp_table_size 参数MYSQL 性能提升有改善作用。但问题还没有完全解决。

  于是进入 mysql 的 shell 命令行,调用 show processlist, 查看当前 mysql 使用频繁的 sql 语句:

mysql> show processlist;

  反复调用此命令,发现网站 A 的两个 SQL 语句经常在 process list 中出现,其语法如下:

SELECT t1.pid, t2.userid, t3.count, t1.date
FROM _mydata AS t1
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid
ORDER BY t1.pid
LIMIT 0,15

  调用 show columns 检查这三个表的结构 :

mysql> show columns from _myuser;
mysql> show columns from _mydata;
mysql> show columns from _mydata_body;

  终于发现了问题所在:_mydata 表,只根据 pid 建立了一个 primary key,但并没有为 userid 建立索引。而在这个 SQL 语句的第一个 LEFT JOIN ON 子句中:

LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid

  _mydata 的 userid 被参与了条件比较运算。于是我为给 _mydata 表根据字段 userid 建立了一个索引:

mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )

  建立此索引之后,CPU 马上降到了 80% 左右。看到找到了问题所在,于是检查另一个反复出现在 show processlist 中的 sql 语句:

SELECT COUNT(*)
FROM _mydata AS t1, _mydata_key AS t2
WHERE t1.pid=t2.pid and t2.keywords = '孔雀'

  经检查 _mydata_key 表的结构,发现它只为 pid 建了了 primary key, 没有为 keywords 建立 index。_mydata_key 目前有 33 万条记录,在没有索引的情况下对33万条记录进行文本检索匹配,不耗费大量的 cpu 时间才怪。看来就是针对这个表的检索出问题了。于是同样为 _mydata_key 表根据字段 keywords 加上索引:

mysql> ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )

  建立此索引之后,CPU立刻降了下来,在 50%~70%之间震荡。

  再次调用 show prosslist,网站A 的sql 调用就很少出现在结果列表中了。但发现此主机运行了几个 Discuz 的论坛程序, Discuz 论坛的好几个表也存在着这个问题。于是顺手一并解决,cpu占用再次降下来了。(2007.07.09 附注:关于 discuz 论坛的具体优化过程,我后来另写了一篇文章,详见:千万级记录的 Discuz! 论坛导致 MySQL CPU 100% 的 优化笔记 http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm)
解决 MYSQL CPU 占用 100% 的经验总结

  1. 增加 tmp_table_size 值。mysql 的配置文件中,tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增加 tmp_table_size 值。 这是 mysql 官方关于此选项的解释:
    tmp_table_size

    This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.


  2. 对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引 INDEX。索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。

    根据 mysql 的开发文档:

    索引 index 用于

    • 快速找出匹配一个WHERE子句的行
    • 当执行联结(JOIN)时,从其他表检索行。
    • 对特定的索引列找出MAX()或MIN()值
    • 如果排序或分组在一个可用键的最左面前缀上进行(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有键值部分跟随DESC,键以倒序被读取。
    • 在一些情况中,一个查询能被优化来检索值,不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀,为了更快,值可以从索引树被检索出来。

      假定你发出下列SELECT语句:

      mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
      如果一个多列索引存在于col1和col2上,适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上,优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。

开发人员做 SQL 数据表设计的时候,一定要通盘考虑清楚。

时间: 2010-12-29

Mysql CPU占用高的问题解决方法小结

通过以前对mysql的操作经验,先将mysql的配置问题排除了,查看msyql是否运行正常,通过查看mysql data目录里面的*.err文件(将扩展名改为.txt)记事本查看即可.如果过大不建议用记事本了,容易死掉,可以用editplus等工具 简单的分为下面几个步骤来解决这个问题: 1.mysql运行正常,也有可能是同步设置问题导致 2.如果mysql运行正常,那就是php的一些sql语句导致问题发现,用root用户进入mysql管理mysql -u root -p输入密码mysql:sh

Mysql占用过高CPU时的优化手段(必看)

Mysql占用CPU过高的时候,该从哪些方面下手进行优化? 占用CPU过高,可以做如下考虑: 1)一般来讲,排除高并发的因素,还是要找到导致你CPU过高的哪几条在执行的SQL,show processlist语句,查找负荷最重的SQL语句,优化该SQL,比如适当建立某字段的索引: 2)打开慢查询日志,将那些执行时间过长且占用资源过多的SQL拿来进行explain分析,导致CPU过高,多数是GroupBy.OrderBy排序问题所导致,然后慢慢进行优化改进.比如优化insert语句.优化group

mysql占用CPU过高的解决办法(添加索引)

下面是MYSQL占用CPU高处理的一个例子,希望对遇到类似问题的朋友们有点启发.一般来说MYQL占用CPU高,多半是数据库查询代码问题,查询数据库过多.所以一方面要精简代码,另一方面最好对频繁使用的代码设置索引. 今天早上起来 机器报警 一查负载一直都在4以上 top了一下 发现 mysql 稳居 第一 而且相当稳定 我擦 重启一下mysql不行 mysql> show processlist;一下 发现xxx网站有两条 查询语句 一直 在列,我擦 该站 也就30多万条记录 量也不大 不可能是机

PyTorch 随机数生成占用 CPU 过高的解决方法

PyTorch 随机数生成占用 CPU 过高的问题 今天在使用 pytorch 的过程中,发现 CPU 占用率过高.经过检查,发现是因为先在 CPU 中生成了随机数,然后再调用.to(device)传到 GPU,这样导致效率变得很低,并且CPU 和 GPU 都被消耗. 查阅PyTorch文档后发现,torch.randn(shape, out)可以直接在GPU中生成随机数,只要shape是tensor.cuda.Tensor类型即可.这样,就可以避免在 CPU 中生成过大的矩阵,而 shape

w3wp.exe占用cpu过高的解决方法第1/2页

iisapp.vbs:IIS 应用程序查询脚本 报告为特定的应用程序池提供服务且当前正在运行 w3pwp.exe 进程的进程标识符 (PID). 语法 iisapp [a/ AppPoolName | /p AppPoolID] 参数 /a AppPoolName 指定特定应用程序池的名称.(可选项) /p AppPoolID 按 ID 号指定应用程序池.(可选项) 注释 如果未指定应用程序池名或 ID,则 iisapp 列出所有运行应用程序. 仅当使用 /s 时,才可使用 /u 和 /p 命令

php-fpm 占用CPU过高,100%的解决方法

话说最近配置的LNMP还算稳定,正在暗自窃喜,但是从昨晚开始,就发现服务器的CPU占用过高,甚至到了100%.我的内存是1G的,正常情况下占用率应该在5%以下,最多不超10%. 阿里云最近的监控显示: 使用top命令查看,发现 php-fpm 占用内存过高,非常不正常: 我按照<Nginx使用的php-fpm的两种进程管理方式及优化>这篇文章,配置 php-fpm 进程数如下: 重启 php-fpm 后,还是没有彻底解决问题,依旧会出现占用 99以上,不知道哪位朋友知道如何分析和解决呢?小弟求

mysql运行net start mysql报服务名无效的解决办法

运行net start mysql报服务名无效的解决办法,供大家参考,具体内容如下 1. 症状 以前电脑上安装了 MySQL,今天在电脑上运行,发现没有 MySQL 服务了 C:UsersAdministrator>net start mysql 服务名无效. 请键入 NET HELPMSG 2185 以获得更多的帮助 2. 解决办法 输入 mysqld.exe -install MySQL 安装在:D:\wamp\bin\mysql\mysql5.1.53 以上就是本文的全部内容,希望对大家的

MySQL Daemon failed to start错误解决办法

MySQL Daemon failed to start错误解决办法 前两天我们发现发布好的网站不可以进行注册,登陆这些活动,但是访问页面是正常的.于是开始对问题进行排查,首先我们重启了jenkins,但是每次重启都有错误,于是我们只能重启服务器,重启服务器需要重新启动的服务很多,其中一个就是mysql. 首先我尝试用命令:service mysql start 来启动服务,但是提示: MySQL Daemon failed to start 一开始出现这个问题我很方,然后开始查,说什么的都有,

mysql 服务意外停止1067错误解决办法小结

事件类型: 错误 事件来源: Service Control Manager 事件种类: 无 事件 ID: 7034 日期: 2012-11-1 事件: 9:25:52 用户: N/A 计算机: SVCTAG-HZWGF3X 描述: 服务 MySQL 意外停止.这发生了 1 次. 有关更多信息,请参阅在 http://go.microsoft.com/fwlink/events.asp 的帮助和支持中心. 再看应用程序错误,看出 发现有这么个提示 D:xxxxmysqld: Shutdown c

spoolsv.exe占用cpu 99%的完美解决方法

spoolsv.exe占用cpu 99%的解决方法  spoolsv.exe占cpu 99%的解决方法 解决方法其实很简单: 就是清空 C:\WINDOWS\system32\spool\PRINTERS 目录下所有的文件: 前提是:你已经使用了杀毒软件排除了病毒和已经使用防间谍软件排除了恶意软件. 相关问题的Microsoft官方文档: Windows 后台打印程序没有删除打印作业后台文件导致的打印程序可能会反复地尝试对该打印作业进行后台处理解决方案 为避免发生此问题,请不要在打印后台文件位于

MySQL ERROR 1045 (28000) 错误的解决办法

错误现象: ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)  ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: YES) windows下,以上两个错误的解决方法,本人亲测有效,现分享解决方案如下: 1.找到配置文件my.ini  ,然后将其打开,可以选择用记事本打开. 2

关于mysql innodb count(*)速度慢的解决办法

innodb引擎在统计方面和myisam是不同的,Myisam内置了一个计数器,所以在使用 select count(*) from table 的时候,直接可以从计数器中取出数据.而innodb必须全表扫描一次方能得到总的数量.要初步解决这个问题,需要做不同于myisam的一些工作: 1.使用第二索引(一般不使用主键索引),并且添加where条件,如: 复制代码 代码如下: select count(*) from product where comp_id>=0 ; show index f