<b>办理一个MySQL服务器进程CPU占用100%的技术笔记</b>[MySQL防范]

MYSQL CPU 占用 100% 的现象描写
　　早上帮朋友一台服务器办理了 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% 的经验总结

增添 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.

对 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 数据表计划的时刻,一定要通盘考虑清楚. 　　以上是“<b>办理一个MySQL服务器进程CPU占用100%的技术笔记</b>[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：