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> pl/pgsql 语法问题 我这个为什么建表不能成功? mysql的添加并取出问题 用什么命令能够让root被指定一个ip(如192。168。1。1 这个机器能访问 ) 求postgresql的一个命令 求助c#如何连接postgreSQL数据库,帮找错误 紧急求助PHP+mysql问题 mysql4.013的数据导入导出怎么搞?(急) 查询如何显示在MSFlexGrid网格控件中的问题。救急! SqlServer中的时间戳在MySql中如何等价 用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>