无心宠物导致MYSQLD-NT占用率100%的问题

haskey · 发表于 2009-1-1 12:36:53

本帖最后由 haskey 于 2009-1-1 12:40 编辑

自建主机
IBMX3850 两颗Xeon5080 16G内存
WindowsServer2003, IIS6,MySQL5.0.51b
UCCenter1.5+Discuz6.1(20081117)+UCHome1.5
全站ReWrite

注册人数5000，日访问量不大，每天800~1600贴之间的发帖
突然几日CPU占用率100%,使用者是mySQLd-nt.exe

用Show Process 得到如下信息：

-+------------------------------------------------------------------------------
------------------------+
| 3793217 | root | localhost:3631 | NULL | Query | 0 | NULL
| show processlist
|
| 3793519 | root | localhost:3941 | discuz | Query | 0 | removing tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782165) or mapi
d=0 order by rand() lim |
| 3793762 | root | localhost:4199 | discuz | Query | 0 | freeing items
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782196) or mapi
d=0 order by rand() lim |
| 3793785 | root | localhost:4223 | discuz | Query | 0 | Creating tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782198) or mapi
d=0 order by rand() lim |
| 3793820 | root | localhost:4261 | discuz | Query | 0 | freeing items
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782202) or mapi
d=0 order by rand() lim |
| 3793824 | root | localhost:4265 | discuz | Query | 0 | freeing items
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782203) or mapi
d=0 order by rand() lim |
| 3793831 | root | localhost:4272 | discuz | Query | 0 | removing tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782204) or mapi
d=0 order by rand() lim |
| 3793835 | root | localhost:4276 | discuz | Query | 0 | Creating tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782205) or mapi
d=0 order by rand() lim |
| 3793842 | root | localhost:4283 | discuz | Query | 0 | Creating tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782205) or mapi
d=0 order by rand() lim |
| 3793843 | root | localhost:4284 | discuz | Query | 0 | Creating tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782205) or mapi
d=0 order by rand() lim |
| 3793850 | root | localhost:4292 | discuz | Query | 0 | Creating tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782207) or mapi
d=0 order by rand() lim |
| 3793858 | root | localhost:4300 | discuz | Query | 0 | removing tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782208) or mapi
d=0 order by rand() lim |
| 3793868 | root | localhost:4310 | discuz | Query | 0 | removing tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782209) or mapi
d=0 order by rand() lim |
| 3793873 | root | localhost:4315 | discuz | Query | 0 | Creating tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782210) or mapi
d=0 order by rand() lim |
| 3793974 | root | localhost:4421 | discuz | Query | 0 | Creating tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782224) or mapi
d=0 order by rand() lim |
| 3793988 | root | localhost:4436 | discuz | Query | 0 | freeing items
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782226) or mapi
d=0 order by rand() lim |
| 3793989 | root | localhost:4437 | discuz | Query | 0 | Creating tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782226) or mapi
d=0 order by rand() lim |
| 3793994 | root | localhost:4442 | discuz | Query | 0 | Creating tmp table
| select * from cdb_wxnpcdata where (mapid='47' and pktime<1230782227) or mapi
d=0 order by rand() lim |
+---------+------+----------------+--------+---------+------+-------------------
-+------------------------------------------------------------------------------
------------------------+
18 rows in set (0.02 sec)

复制代码

将无心宠物关闭后，再看CPU占用率回到了正常的5%以下了。

当时宠物在线人数不大，也就10来个人，却能让mySQL服务这么忙？

我是新手，对于数据库了解不多，所以请各位前辈帮忙解释一下出现此问题的原因并且给一点解决建议。

谢谢了。

rqzh · 发表于 2009-1-3 12:24:00

无心宠物和论坛股市对服务器CPU要求很大。。
很占资源。。。
如果不是必要，个人PC不要装以上两种插件。。。
我之前也有这种情况。。。

domin · 发表于 2009-1-3 15:28:35

同意楼上的说法. 很多插件在编写过程中都不考虑是否会占用太多资源.

Tino · 发表于 2009-1-13 17:40:22

宠物挂机导致的，开启2秒刷新限制即可

haskey · 发表于 2009-1-19 11:59:01

宠物挂机导致的，开启2秒刷新限制即可
Tino 发表于 2009-1-13 17:40

果然如此！

原先没有限制，有人用外挂Application点游戏。一秒钟会产生多次攻击和刷新操作。

现在设置成1秒1次的限制，这类问题再没出现过。

我就纳闷了，我Discuz!论坛的三百万条数据的查询复制整理操作都只让CPU占用率不到10%。
他一个小小的无心宠物就能让mySQL这么忙？

女施主请留步 · 发表于 2009-5-12 00:21:56

果然如此！

原先没有限制，有人用外挂Application点游戏。一秒钟会产生多次攻击和刷新操作。

现在设置成1秒1次的限制，这类问题再没出现过。

我就纳闷了，我Discuz!论坛的三百万条数据的查询复制整理 ...
haskey 发表于 2009-1-19 11:59

问下楼主我也碰到了这样的问题，这个刷新限制具体是哪里改呢？

zjstandup · 发表于 2009-7-12 16:21:54

是这样么?是不是开心宠物的flash占cpu?

jwhna · 发表于 2009-8-4 22:20:25

请问一下在哪里设置??能指点一句吗?

		自动登录	找回密码
密码			立即注册