不断锁表，卡死MYsql，情况非常严重。已经出现多个相同案例！！！

2016-07-07 15:32 来源：www.chinab4c.com 作者：ecshop专家

首先看看相同的案例和解说。

请版主看看：http://www.aslibra.com/blog/read.php?1043


另一相同案例：http://bbs.ecshop.com/thread-54969-1-1.html

a、ECshop版本：2.5.1
MySQL版本：5.0.37
PHP版本：5.2.3
IIS或Apache版本：Microsoft-IIS/6.0
b、出现问题描述：MYSQL被EC拖到占用CPU100%。
c、出现问题前做的修改是：只有修改模板

产品200，ip1000。

现在情况是只开了ECshop，整个mysql就卡死。CPU100%！！！


EVshop一直锁死mysql。
重装ECshop，恢复数据后，也是一样。

用了另一台服务器来测试，也是一样。
应该是ECshop的数据出了很大的问题！



之前一直正常使用，突然就出现这样的问题了！！

非常郁闷啊！什么回事呢？

谢谢

回答：
看看最耗时的sql查询是什么

需要你打开慢查询检查您的慢查询语句

929 | yddgweb| localhost:1669 | yddgweb| Query|78 | Locked
| SELECT COUNT(distinct(a0.goods_id)) FROM`yddgweb`.`ecs_goods` AS g, `yddgw
eb`.`ecs_goods_attr` AS|
|933 | yddgweb| localhost:1673 | yddgweb| Query|76 | Locked
| SELECT COUNT(distinct(a0.goods_id)) FROM`yddgweb`.`ecs_goods` AS g, `yddgw
eb`.`ecs_goods_attr` AS|
|934 | yddgweb| localhost:1674 | yddgweb| Query|76 | Locked
| SELECT COUNT(distinct(a0.goods_id)) FROM`yddgweb`.`ecs_goods` AS g, `yddgw
eb`.`ecs_goods_attr` AS|
|936 | yddgweb| localhost:1676 | yddgweb| Query|75 | Locked
| SELECT COUNT(distinct(a0.goods_id)) FROM`yddgweb`.`ecs_goods` AS g, `yddgw
eb`.`ecs_goods_attr` AS|

。。。。。。。
是说slow_query你发一堆锁表有啥用
表不锁你也不会提问了

谢谢你的帮忙。

但我不知道怎样开慢查询。你可以告诉我一下嘛？

非常感谢。

my.ini
设置slow_query
搜索一下吧 很多详细教程

你好。

在my.ini 中，找不到slow_query参数的？

先搜索下 看看在提问

我也是这样的问题,MYSQL直接死亡

慢查询，记录运行大于1s的记录：






# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 0Rows_examined: 0
SET character_set_connection=utf8, character_set_results=utf8, character_set_client=binary;
# Time: 080614 16:10:53
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 1Rows_examined: 0
SELECT b.brand_id, b.brand_name, COUNT(g.goods_id) AS goods_numFROM `yddgweb`.`ecs_goods` AS gLEFT JOIN `yddgweb`.`ecs_brand` AS b ON g.brand_id=b.brand_idWHERE g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 AND g.brand_id > 0 AND g.goods_idIN ('12')GROUP BY g.brand_id;
# Time: 080614 16:10:57
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 5Lock_time: 0Rows_sent: 0Rows_examined: 0
INSERT INTO `yddgweb`.`ecs_sessions` (sesskey, expiry, ip, data) VALUES ('37d944d5a6c50cd1756ec485ff5af6bf', '1213431022', '64.1.215.164', 'a:0:{}');
# User@Host: sq_marco608[sq_marco608] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 0Rows_examined: 0
SET sql_mode='';
# Time: 080614 16:10:58
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 0Rows_examined: 0
SET sql_mode='';
# Time: 080614 16:10:59
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 1Rows_examined: 0
SELECT c.cat_id, c.cat_name, COUNT(g.goods_id) AS goods_numFROM `yddgweb`.`ecs_goods` AS gLEFT JOIN `yddgweb`.`ecs_category` AS c ON c.cat_id = g.cat_idWHERE g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0AND g.goods_idIN ('12')GROUP BY g.cat_id;
# Time: 080614 16:11:03

# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 0Rows_examined: 0
SET sql_mode='';
# Time: 080614 16:22:03
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 0Rows_examined: 0
SET sql_mode='';
# Time: 080614 16:22:05
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 0Rows_examined: 0
SET sql_mode='';
# Time: 080614 16:22:06

# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 5Lock_time: 0Rows_sent: 1Rows_examined: 0
use yddgweb;
SELECT data, expiry FROM `yddgweb`.`ecs_sessions` WHERE sesskey = '63c75bcf6bc2fa744cc21889dffb4c09';
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 5Lock_time: 0Rows_sent: 1Rows_examined: 0
SELECT data, expiry FROM `yddgweb`.`ecs_sessions` WHERE sesskey = 'd16868f1e6a8bd8a834e1ab3683ad515';
# Time: 080614 16:22:12
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 0Rows_examined: 0
INSERT INTO `yddgweb`.`ecs_sessions` (sesskey, expiry, ip, data) VALUES ('4e8e4d9bceab59a84ea9835dc974cd7e', '1213431697', '64.1.215.164', 'a:0:{}');
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 3Rows_examined: 3
DESC `yddgweb`.`ecs_searchengine`;
# Time: 080614 16:22:13

# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 149Rows_examined: 149
use yddgweb;
SELECT id, value FROM `yddgweb`.`ecs_shop_config`;
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 1Rows_examined: 0
SELECT data, expiry FROM `yddgweb`.`ecs_sessions_data` WHERE sesskey = '63c75bcf6bc2fa744cc21889dffb4c09';
# Time: 080614 16:22:18
# User@Host: yddgweb[yddgweb] @ localhost [127.0.0.1]
# Query_time: 6Lock_time: 0Rows_sent: 1Rows_examined: 0
SELECT cat_name FROM `yddgweb`.`ecs_goods_type` WHERE cat_id = '1';
# Time: 080614 16:22:19