首页 随笔 乐走天涯 程序资料 评论中心 Tag 论坛 其他资源 搜索 消息中心 联系我 关于 RSS

解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记


日期: 2007-03-07 14:00 | 联系我 | 关注我: SteemIT, Twitter, Google+

 MYSQL CPU 占用 100% 的现象描述

  早上帮朋友一台服务器解决了 Mysql cpu 占用 100% 的问题。稍整理了一下,将经验记录在这篇文章里:《解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记》 https://www.xiaohui.com/weekly/20070307.htm

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

 MYSQL CPU 占用 100% 的解决过程

  全文记录: https://www.xiaohui.com/weekly/20070307.htm

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

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

d:webmysql> mysqld.exe --help >output.txt

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

d:webmysql> notepad c:windowsmy.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% 的 优化笔记 https://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm)

 解决 MYSQL CPU 占用 100% 的经验总结

   https://www.xiaohui.com/weekly/20070307.htm

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

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

    根据 mysql 的开发文档:

  4. 索引 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 数据表设计的时候,一定要通盘考虑清楚。

相关文章:千万级记录的 Discuz! 论坛导致 MySQL CPU 100% 的 优化笔记

标签: CPU 占用 | MySQL

 文章评论

第 1 楼  发表于 2007-03-07 12:41 | xjb 的所有评论
嘿嘿,xiaohui很少写技术文章,这样心得一定要纪录下来,“发表是最好的记忆”
回复于 2007-03-08 01:10:
呵呵,多谢支持!:) 看看能不能坚持写一些。:)

第 2 楼  发表于 2007-03-07 13:50 | nbvista 的所有评论
刚好应用下,感谢

第 3 楼  发表于 2007-03-08 12:23 | 水手 的所有评论
从没用过mysql在学校里都是用SQL Server
早听说mysql速度快,
回复于 2007-03-09 04:12:
下面是 MySQL 开发小组自己写的比较文章:MySQL 与 mSQL (MS SQL)的比较
因为没有线程创建开销、一个较小的语法分析器、较少功能和简单的安全性,mSQL应该在下列方面更快些:
  1. 执行重复的连接和断开的测试,在每个连接期间运行一个非常简单的查询。
  2. 有很少的列和键的插入很简单的表的INSERT操作。
  3. CREATE TABLE和DROP TABLE。
  4. 在不是一个索引的一些东西上SELECT。(一个表扫描是很容易的。)

因为这些操作是如此简单,当你有更高的启动开销时,很难在这些方面变得更好。在连接被建立以后,MySQL应该性能好一些。在另一方面,MySQL比mSQL(以及大多数其他的SQL实现)在下列方面更好些
  1. 复杂的SELECT操作。
  2. 检索较大的结果(MySQL有一个更好、更快并且更安全的协议)。
  3. 有变长字符串的表,因为MySQL有更有效的并可在VARCHAR列上索引。
  4. 有很多列的表的处理。
  5. 由长记录的表的处理。
  6. 有很多许多表达式的SELECT。
  7. 在大表上的SELECT。
  8. 同时处理很多连接。MySQL充分是完全多线程化的,每个连接有它自己的线程,这意味着没有线程必须等待另一个线程(除非一个线程正在修改一张表,另外的线程想要存取)在mSQL中,一旦一个连接被建立了,所有其它线程必须等到第一个线程完成,不管连接正在运行的查询是短的或是长的。当第一个连接终止时,下一个才能工作,而此时所有其它线程再次等待,等等。
  9. 联结。如果你改变一个SELECT中的表的顺序,mSQL可能变得异常地慢。在基准套件中,比MySQL要慢超过15000倍的时间。这是由于mSQL缺乏一个联结优化器以便以最佳的顺序排定表。然而,如果你把表按完全正确的顺序放在mSQL2中并且WHERE是很简单的并使用索引列,联结将相对快些!见10.8 使用你自己的基准。
  10. ORDER BY和GROUP BY。
  11. DISTINCT。
  12. 使用TEXT或BLOB列

第 4 楼  发表于 2007-03-17 18:48 | 水手 的所有评论
这相一对比就理论上知道了,但得实践做个东西来才有感觉。

第 5 楼  发表于 2007-07-02 10:08 | 游客 的所有评论
使用php程序而不用LAPM架构,非常另人费解

第 6 楼  发表于 2007-07-31 16:50 | 转走拉! 的所有评论
转走拉!

第 7 楼  发表于 2007-11-09 23:56 | 中草药 的所有评论
晕吧!
人家是缓存的,跟这个有屁事~~~

第 8 楼  发表于 2008-02-20 10:56 | pabu 的所有评论
您好,我单位网站也出现了这种情况,当mysql占cpu过大时,网站后台不能进入,在服务器上也无法进入mysql,要重启机器才行
调用 show processlist后发现经常出现这句:
select count<*> as num from phpcms_start_visitor where unix_timestamp <now<>>-unix_timestamp <ltime><=1
没有发现其他语句
麻烦您帮忙分析一下,现在每天都受其困扰,多谢啦
回复于 2008-02-20 11:51:
调一下这条语句:
show columns from phpcms_start_visitor; 
确认一下,看看 WHERE 子句中涉及的字段,是否有 INDEX KEY. 如果没有,建上。
如果仍无法解决,可以与我联系一下。

第 9 楼  发表于 2008-02-29 22:47 | lele 的所有评论
请教:mysql的替换功能可否使用通配符?
一般替换语句是:
UPDATE `supe_spacenews` SET `message` = replace (`message`,'abcdefg','higklmn')

可是如果想把【 】括号及里面内的内容都替换abdc该如何替换呢? 注:括号【 】里面是变量。

非常感谢!
回复于 2008-03-01 01:36:
MYSQL 的 REPLACE 函数的参数说明中,没有说明其可以使用通配符或者正则表达式,应该是不可以的。你可以自己做一下实验看看。:)

PHP 的 preg_replace 和 ereg_replace 是支持正则表达式的。

第 10 楼  发表于 2008-03-01 10:05 | lele 的所有评论
谢谢xiaohui的回复! 很喜欢这里,常来转转

第 11 楼  发表于 2008-07-16 17:36 | peterpantw 的所有评论
被空间商暂时停站,特来请教
Discuz 6.0

292733
peterpantw
localhost
peterpantw
Query
931
Locked
SELECT uid FROM cdb_sessions WHERE uid IN(55,250,5819,5,55,1948,2697,5819,375,250,2697,9446,55,5819,

292854
peterpantw
localhost
peterpantw
Query
932
Locked
SELECT pid FROM cdb_posts WHERE tid='13747' AND authorid='2697' LIMIT 1

292863
peterpantw
localhost
peterpantw
Query
932
Locked
SELECT pid FROM cdb_posts WHERE tid='13747' AND authorid='2697' LIMIT 1

293009
peterpantw
localhost
peterpantw
Query
1043
Locked
UPDATE cdb_sessions SET uid='4220', username='rainlily', groupid='23', styleid='11', invisible='0',

293019
peterpantw
localhost
peterpantw
Query
1018
Locked
UPDATE cdb_members SET posts=posts+('+1') , lastpost='1216193637' , extcredits2=extcredits2+6*1 WHER

293064
peterpantw
localhost
peterpantw
Query
1048
Locked
SELECT uid FROM cdb_sessions WHERE uid IN(5069,9594,5834,2416,5069,2416) AND invisible=0

293067
peterpantw
localhost
peterpantw
Query
1049
Locked
SELECT uid FROM cdb_sessions WHERE uid IN(2002,2002,2002) AND invisible=0



能不能给个方向去解决它?
麻烦了
回复于 2008-07-16 18:49:
最初步的办法, 查 SELECT 语句后面的 WHERE 子句操作涉及的字段的属性,用 show columns from table_name 查看. 如果没有索引,尝试加上.

第 12 楼  发表于 2010-07-02 11:10 | eric 的所有评论
说的很详细,谢谢

第 13 楼  发表于 2010-07-11 18:38 | 中文知识库 的所有评论
中文知识库
友情帮顶了

http://www.nooye.com

第 14 楼  发表于 2010-09-06 15:24 | e_polo 的所有评论
收获匪浅,刚刚遇到了这个问题,看了站长的心得,思路顿时清晰,问题顺利解决了,谢谢

第 15 楼  发表于 2010-09-13 04:18 | 站长工具 的所有评论
写得好

第 16 楼  发表于 2010-11-08 21:54 | 找潍坊信息网 的所有评论
你好,我的服务器也出现该状况,能帮我解决么?
是帝国的最新程序 6.5
可以QQ联系我,我付费,多少钱?你开

第 17 楼  发表于 2010-11-08 21:56 | 找潍坊信息网 的所有评论
我的QQ:421335102

第 18 楼  发表于 2010-11-20 23:47 | tmpp 的所有评论
我是个菜鸟,请问老大,如果为表建立索引,会不会影响原有程序和数据库的使用?
换言之,可以随便为一个程序的数据库中的表做索引吗?

第 19 楼  发表于 2010-11-23 23:04 | 美薇 的所有评论
看看是否有用到主键来提高效率

第 20 楼  发表于 2011-06-22 13:31 | 彭生 的所有评论
小辉你好,我网站现在遇到了这个问题,请与我联系一下,帮忙解决一下,灰常感谢,一定酬谢!!!
我的QQ: 28088668 请联系我哦!

第 21 楼  发表于 2011-10-27 19:58 | 沙发客 的所有评论
我的默认值竟然只有5M,我也改到200试试

第 22 楼  发表于 2012-03-09 13:33 | 黑妹 的所有评论
您好!我们网站现在就出现这种情况,可以跟您取得联系吗?谢谢!499627275我的QQ号,您方便的时候请联系我,好吗?期待您的佳音

第 23 楼  发表于 2012-05-10 08:26 | 罗莎 的所有评论
作者的很详细,感谢分享哦。。。。

第 24 楼  发表于 2012-11-13 11:23 | 圣骑士 的所有评论
飘过

第 25 楼  发表于 2012-12-24 19:50 | 闪光 的所有评论
我真也出现了这个情况,尤其是在晚上18点开始到次日,cpu几乎都是100% 查看进程是MYSQL 占用过多。

第 26 楼  发表于 2012-12-24 19:50 | 闪光 的所有评论
我真也出现了这个情况,尤其是在晚上18点开始到次日,cpu几乎都是100% 查看进程是MYSQL 占用过多。

我的qq:276592170

第 27 楼  发表于 2013-09-05 09:01 | jirofans 的所有评论
很好的技术文章,解决了我一大难题,谢谢!

第 28 楼  发表于 2015-05-27 18:58 | 长颈叔叔 的所有评论
小辉同学牛奔了,在搜索一个MySQL问题的时候,猪猪然都可以跑你网站来。

另外,顶部做的Call to Action很不错。
回复于 2015-06-04 12:13:
叔叔,我们不约 :D

第 29 楼  发表于 2018-04-28 10:27 | 阿伟 的所有评论
感谢,帮了我大忙了

共有评论 29 条, 显示 29 条。

发表你的评论
如果你想针对此文发表评论, 请填写下列表单:
姓名: * 必填 (Twitter 用户可输入以 @ 开头的用户名, Steemit 用户可输入 @@ 开头的用户名)
E-mail: 可选 (不会被公开。如果我回复了你的评论,你将会收到邮件通知)
网站 / Blog: 可选
反垃圾广告: 为了防止广告机器人自动发贴, 请计算下列表达式的值:
2 x 5 + 3 = * 必填
评论内容:
* 必填
你可以使用下列标签修饰文字:
[b] 文字 [/b]: 加粗文字
[quote] 文字 [/quote]: 引用文字

 
首页 随笔 乐走天涯 猎户星 Google Earth 程序资料 程序生活 评论 Tag 论坛 资源 搜索 联系 关于 隐私声明 版权声明 订阅邮件

程序员小辉 建站于 1997 ◇ 做一名最好的开发者是我不变的理想。
Copyright © XiaoHui.com; 保留所有权利。