mysql 数据库占cpu到100%要如何解决 mysql 数据库占cpu到100%要如何解决 如何定位那些sql语句 建立索引呢。我不是太会,请详细说明一下解决过程!谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 show processlists 看一下。 先show full processlist; 看看是哪些用户哪些sql占用了100%的资源! 看慢查询 show processlist 看看哪些sql在运行 我怕慢查询影响cpu的性能。而且不能及时发现问题。 所以就没有开启。我用了show processlist 看了一下。以下是截图。请问要如何才能找到有问题的sql语句。我知道一个看不出问题。特地截取了两个图。 现在流量稍微下来了点点。稍微好点了。但是问题没有根本解决,等到流量一多,cpu还是被mysqld-nt.exe撑死了。 csdn论坛要如何添加截图附件呢。 刚刚使用, 不是很熟练。 以下是show processlist的内容。 mysql> show processlist;+--------+------+----------------+------+---------+------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+------+----------------+------+---------+------+-------+------------------+| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist |+--------+------+----------------+------+---------+------+-------+------------------+1 row in set (0.00 sec)mysql> show processlist;+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist || 595731 | root | localhost:4724 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order_status, SUM(og.goods_number) as go || 595734 | root | localhost:4727 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order_status, SUM(og.goods_number) as go |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)mysql> show processlist;+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist || 595740 | root | localhost:4733 | ecshopn | Query | 1 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order_status, SUM(og.goods_number) as go |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> show processlist;+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist || 595899 | root | localhost:4892 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order_status, SUM(og.goods_number) as go |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> show processlist;+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist || 595923 | root | localhost:4916 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order_status, SUM(og.goods_number) as go || 595924 | root | localhost:4917 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, g.goods_img, g.shop_price, oi.order_status, |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)mysql> show processlist;+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist || 595930 | root | localhost:4923 | ecshopn | Sleep | 0 | | NULL || 595931 | root | localhost:4924 | ecshopn | Query | 1 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order_status, SUM(og.goods_number) as go || 595933 | root | localhost:4926 | ecshopn | Query | 1 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order_status, SUM(og.goods_number) as go |+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+4 rows in set (0.00 sec)mysql> mysql字符集 数据库概论的多值依赖问题? 不同版本的mysql对内存 cpu 以及 单个的表的限制值 Run-Time Check Failure #2 - Stack around the variable 'mysql' was corrupted. 怎么优化这一句SQL 急急! 用mysql做的项目遇到的奇怪问题! 请教mysql的效率问题 mysql修改密码错误(在线等待) Mysql 用mysqldump备份全表时问题 select查询为什么可以无限叠加,什么原理? 用Mysql查询出A表中与掉B表相同的部分,并输出结果 mysql 流水号并发问题如何解决
先show full processlist; 看看是哪些用户哪些sql占用了100%的资源!
show processlist 看看哪些sql在运行
+--------+------+----------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+----------------+------+---------+------+-------+------------------+
| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist |
+--------+------+----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)mysql> show processlist;
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist
|
| 595731 | root | localhost:4724 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
_status, SUM(og.goods_number) as go |
| 595734 | root | localhost:4727 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
_status, SUM(og.goods_number) as go |
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
3 rows in set (0.00 sec)mysql> show processlist;
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist
|
| 595740 | root | localhost:4733 | ecshopn | Query | 1 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
_status, SUM(og.goods_number) as go |
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
2 rows in set (0.00 sec)mysql> show processlist;
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist
|
| 595899 | root | localhost:4892 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
_status, SUM(og.goods_number) as go |
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
2 rows in set (0.00 sec)mysql> show processlist;
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist
|
| 595923 | root | localhost:4916 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
_status, SUM(og.goods_number) as go |
| 595924 | root | localhost:4917 | ecshopn | Query | 0 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, g.goods_
img, g.shop_price, oi.order_status, |
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
3 rows in set (0.00 sec)mysql> show processlist;
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
| 595688 | root | localhost:4681 | NULL | Query | 0 | NULL | show processlist
|
| 595930 | root | localhost:4923 | ecshopn | Sleep | 0 | | NULL
|
| 595931 | root | localhost:4924 | ecshopn | Query | 1 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
_status, SUM(og.goods_number) as go |
| 595933 | root | localhost:4926 | ecshopn | Query | 1 | Copying to tmp table | select og.goods_id, og.goods_name, og.is_gift, g.cat_id, oi.order
_status, SUM(og.goods_number) as go |
+--------+------+----------------+---------+---------+------+----------------------+------------------------------------------------------------------
------------------------------------+
4 rows in set (0.00 sec)mysql>