深圳哪家公司做网站好,成功的软文营销案例,小程序在哪个网站做,wordpress文章页全白MYSQL CPU 100%实例详解2011/7/2 11:19:19作者#xff1a;xy我要评论(0)自己的一台主机(Windows 2003 IIS PHP MYSQL )近来 MySQL 服务进程 (mysqld-nt.exe) CPU 占用率总为 100% 高居不下。此主机有10个左右的 database, 分别给十个网站调用。据朋友测试#xff0c;导致 …MYSQL CPU 100%实例详解2011/7/2 11:19:19作者xy我要评论(0)自己的一台主机(Windows 2003 IIS PHP MYSQL )近来 MySQL 服务进程 (mysqld-nt.exe) CPU 占用率总为 100% 高居不下。此主机有10个左右的 database, 分别给十个网站调用。据朋友测试导致 mysqld-nt.exe cpu 占用奇高的是网站A一旦在 IIS 中将此网站停止服务CPU 占用就降下来了。一启用则马上上升。MYSQL CPU 占用 100% 的解决过程今天早上仔细检查了一下。目前此网站的七日平均日 IP 为2000PageView 为 3万左右。网站A 用的 database 目前有39个表记录数 60.1万条占空间 45MB。按这个数据MySQL 不可能占用这么高的资源。于是在服务器上运行命令将 mysql 当前的环境变量输出到文件 output.txtd:\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_size200M然后重启 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.dateFROM _mydata AS t1LEFT JOIN _myuser AS t3 ON t1.useridt3.useridLEFT JOIN _mydata_body AS t2 ON t1.pidt3.pidORDER BY t1.pidLIMIT 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.useridt3.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 t2WHERE t1.pidt2.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_sizeThis 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 col1val1 AND col2val2;如果一个多列索引存在于col1和col2上适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。开发人员做 SQL 数据表设计的时候一定要通盘考虑清楚。阅读本文后您有什么感想? 已有0人给出评价! 0囧0恶心0期待0难过0不错0关注
