PHP解决一个 MySQL 服务器进程 CPU 占用 100%解决一个 MySQL 服务器进程 CPU 占用 100%之技能笔记》[转]

釜底抽薪一个 MySQL 服务器进程 CPU 占用 100%缓解一个 MySQL 服务器进程 CPU
占用 100%的技能笔记》

2011-10-17 23:21

作者:liyihongcug

转载地址:http://bbs.chinaunix.net/archiver/tid-1823500.html

缓解一个 MySQL 服务器进程 CPU 占用 100%缓解一个 MySQL 服务器进程 CPU
占用 100%的技巧笔记》(
[url]http://www.xiaohui.com/weekly/20070307.htm\[/url\]
),谈到温馨当缓解一个所有 60 万长记下之 MySQL 数据库访问时,导致 MySQL
CPU 占用 100% 的经。在缓解问题形成优化(optimize)之后,我发觉 Discuz
论坛也在这题目,当时略提了瞬间:
[url]http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm\[/url\]
意识是主机运行了几乎单 Discuz 的论坛程序,
Discuz论坛的一点只表明也有着此题材。于是顺手一并缓解,cpu占用再度回落下来了。
  前几上,一各项情人通过就首小说找到了自家,说他固然运行最新的 discuz
版本,MySQL 占用 CPU
100%,导致系统假死,每日都要还开好几破,花了一个大抵月份之时一贯无缓解,希望自己帮一下。经过检查,他的之论坛最着重之几独表中,如今cdb_members 表,有记录 6.2 万;cdb_threads 表,有记录
11万;cdb_posts表,有记录 1740 万;所有数据表的记录加起来,超越 2000
万;数据库的分寸领先 1GB。经过半上之调节,总算完成了 discuz
论坛优化,于是将其解决因而记录在及时篇稿子
[url]http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm\[/url\]
中。

  二零零七年十一月自家发觉 discuz
论坛的数据库结构设计有一对大意,有过多查询子句之尺度较,都尚未建
Index 索引。当时本人所检查的坏数据表,记录才发生几千漫漫,由此对 CPU
负荷不死。现在以此数库表,上千万之记录检索,可以想像,假如数据表结构设计不标准,没有供索引,所耗费的年月是一个恐怖之数字。有关
MySQL
建立目录的严重性,可以瞻仰我之就篇稿子底部的申明:[url]http://www.xiaohui.com/weekly
/20070307.htm[/url]

  为了调节好,我打 dizcus 的官网下载了那新颖的 Dizcus! 5.5.0
论坛程序.

  我首先检查了 my.ini 的参数配置,一切正常。进入 MySQL 的一声令下执行,调用
show processlist 语句,查找负荷最重的 SQL 语句,结合 Discuz
论坛的源码,发现来以下语句导致 CPU 上升:

mysql> show processlist;
+—–+——+—————-+———+———+——+————+———
—————————————————————–+
| Id  | User | Host           | db      | Command | Time | State      |
Info
                                                                 |
+—–+——+—————-+———+———+——+————+———
—————————————————————–+
| 363 | root | localhost:1393 | history | Query   |    0 | statistics |
SELECT C
OUNT(*) FROM cdb_pms WHERE msgfromid=11212 AND folder=’outbox’ |
+—–+——+—————-+———+———+——+————+———

  检查 cdb_pms 表的结构:
mysql> show columns from cdb_pms;
+———–+————————+——+—–+———+—————-+
| Field     | Type                   | Null | Key | Default | Extra    
     |
+———–+————————+——+—–+———+—————-+
| pmid      | int(10) unsigned       | NO   | PRI | NULL    |
auto_increment |
| msgfrom   | varchar(15)            | NO   |     |         |          
     |
| msgfromid | mediumint(8) unsigned  | NO   | MUL | 0       |          
     |
| msgtoid   | mediumint(8) unsigned  | NO   | MUL | 0       |          
     |
| folder    | enum(‘inbox’,’outbox’) | NO   |     | inbox   |          
     |
| new       | tinyint(1)             | NO   |     | 0       |          
     |
| subject   | varchar(75)            | NO   |     |         |          
     |
| dateline  | int(10) unsigned       | NO   |     | 0       |          
     |
| message   | text                   | NO   |     |         |          
     |
| delstatus | tinyint(1) unsigned    | NO   |     | 0       |          
     |
+———–+————————+——+—–+———+—————-+
10 rows in set (0.00 sec)

  这长长的告词: WHERE msgfromid=11212 AND folder=’outbox’,我们看看,在
cdb_pms 表中,msgfromid 字段已经建立了目录,不过,folder
字段并从未。近来是发明已发生记录 7823
条。分明,这会针对查询造成一定影响。于是为其树立目录:
mysql> ALTER TABLE `cdb_pms` ADD INDEX ( `folder` );
Query OK, 7823 rows affected (1.05 sec)
Records: 7823  Duplicates: 0  Warnings: 0

  继续检查:
mysql> show processlist;

+——+——+—————-+———+———+——+————+——–

————–+
| Id   | User | Host           | db      | Command | Time | State      |
Info

              |

+——+——+—————-+———+———+——+————+——–

————–+
              |
| 1583 | root | localhost:2616 | history | Query   |    0 | statistics |
SELECT
t.tid, t.closed, f.*, ff.*  , f.fid AS fid
                        FROM cdb_threads t
                        INNER JOIN cdb_forums f |

+——+——+—————-+———+———+——+————+——–

————–+
1 rows in set (0.00 sec)

  这漫长 SQL 语句是对极要之数码表 cdb_threads 举办操作的,由于 show
processlist 没有拿及时漫长 SQL 语句全体显得了,经比 Discuz
论坛的源码,此SQL语句的原型位于 common.inc.php 的 Line 283,内容如下:
$query = $db->query(“SELECT t.tid,
t.closed,”.(defined(‘SQL_ADD_THREAD’) ?
    SQL_ADD_THREAD : ”).” f.*, ff.* $accessadd1 $modadd1, f.fid AS
fid
    FROM {$tablepre}threads t
    INNER JOIN {$tablepre}forums f ON f.fid=t.fid
    LEFT JOIN {$tablepre}forumfields ff ON ff.fid=f.fid $accessadd2
$modadd2
    WHERE t.tid=’$tid'”.($auditstatuson ? ” : ” AND
t.displayorder>=0″).” LIMIT 1″);

  经检查,数据表 cdb_threads, 并没有对准 displayorder
字段建立目录。在 discuz 论坛中,displayorder字段多次涉足了 Where
子词的于。于是为该确立目录:
mysql> ALTER TABLE `cdb_threads` ADD INDEX ( `displayorder` );
Query OK, 110330 rows affected (2.36 sec)
Records: 110330  Duplicates: 0  Warnings: 0

  这 cpu 已经轻微下跌了一致有些。

  继续检查,发现 上面就漫长 discuz 的 SQL
语句,也招致负荷增添,这漫漫告词位于 rss.php 程序中之第 142 行。

    $query = $db->query(“SELECT t.tid, t.readperm, t.price, t.author,
t.dateline, t.subject, p.message
    FROM {$tablepre}threads t
    LEFT JOIN {$tablepre}posts p ON p.tid=t.tid AND p.first=1
    WHERE t.fid=’$fid’ AND t.displayorder>=0
    ORDER BY t.dateline DESC LIMIT $num”);

  在这个 Order by 子句中,用到了 cdb_threads 表中的 dataline
字段。那多少个字段是为此来存储 unixtime
的年华戳,在漫天论坛程序中,大部分辰光数据的排序为是冲这么些字段,竟然没建立目录。于是加上:
mysql> ALTER TABLE `cdb_threads` ADD INDEX ( `dateline` );
Query OK, 110330 rows affected (12.27 sec)
Records: 110330  Duplicates: 0  Warnings: 0

  查找占用 CPU 高负茶的 SQL
语句,是同码劳动而又枯燥的从,需要同修一修破、分析。前面的劳作,都是仍此类推,经过检查,共查获有八处地方,需要充实索引,假诺您呢撞了
discuz 5.5.0 论坛导致 cpu 占用 100%
的景,可以一贯用下列语句复制过去,在 mysql 的吩咐行下执行即可:
ALTER TABLE `cdb_pms` ADD INDEX ( `folder` );
ALTER TABLE `cdb_threads` ADD INDEX ( `displayorder` );
ALTER TABLE `cdb_threads` ADD INDEX ( `dateline` );
ALTER TABLE `cdb_threads` ADD INDEX ( `closed` );
ALTER TABLE `cdb_threadsmod` ADD INDEX ( `dateline` );
ALTER TABLE `cdb_sessions` ADD INDEX ( `invisible` );
ALTER TABLE `cdb_forums` ADD INDEX ( `type` );
ALTER TABLE `cdb_forums` ADD INDEX ( `displayorder` );

  注意:“cdb_” 是 discuz 论坛的默认数据表前缀。假设您的表名前缀不是
“cdb_”,则该变更成为你对号入座的表名。例如:my_threads, my_pms 等等。

  完成这些构造的优化后,整个体系的 CPU 负荷在
10%~20%横震荡,问题解决。

  我老意外,设计数据库结构,是一个数据库开发人士的基础,discuz
论坛好歹也是一个前行了生六七年的论坛了,为什么数据库结构设计得这般不好?我惦念可能有如下三单由:

数据库开发人士设计时我的不经意
有意留下的弱项,当普通论坛没有达标数级的笔录时,不晤面感觉到到之题目,当数据量增大(例如千万级),此问题突现,以便对用户提供个**接过服务费.呵呵,估且以极可怜之黑心来怀疑此事,玩笑而都,不必当真。:) 
此外一个也许就是是用户的论坛是自低版本升级而来,程序升了层,但数据结构也许没有召开相应的翻新

附1: 补充笔记 2007-07-09

  前些天查网站日志的 reffer, 发现于 discuz
的官方论坛上,有人便以此文引起了片争执:
[url]http://www.discuz.net/thread-673887-1-1.html\[/url\]。discuz
的总指挥及大班有如下言论:

引用自 cnteacher:

相反,discuz 的优化措施以及数据库的目是按大论坛计划的。

TO 一楼:数据库结构的宏图依然按部就班程序用来拓展的,使用其他非Discuz!
标准版本以外的代码和程序,或者变更标准数量结构,均可能碰到不可预知的各类问题。

引用自 童虎:

你们可以省xxxxx,
xxxx之类的可比大型的网站,这种网站以dz论坛还无问题,表明dz标准程序是没有问题,出现楼主说的场合,多半属于服务器或者设置有插件造成的

  分明将题目推给插件的缘故是未正确的.举单简易的例子:在最新的 discuz
5.5.0 forumdisplay.php 第183 行,有如下语句:

$query = $db->query(“SELECT uid, groupid, username, invisible,
  lastactivity, action FROM {$tablepre}sessions
  WHERE $guestwhere fid=’$fid’ AND invisible=0″);

  这里的 invisible 并从未起目录。本文中起评论认为 session
表是内存表, 速度会老快。理论是这么。但是自己当 show processlist
中,观察到者立长长的语句占用了大气 CPU, 所以也以之并累加了
index。cdb_threads 中的 closed 等字段, 也频涉足 where 运算,
也没创立目录。这多少个运算的语句, 是 discuz 自己之程序中的。

附2: 补充笔记 2007-11-11
  自从这首笔记发布以来,在自我之及时首作品的评论、以及自身之交换音讯中,就平常收到众多底二种档次的褒贬以及邮件:一、许多技术人员批评自己胡扯、
Dizcus 论坛不需要开优化依旧无克混建索引的;二、许Dolly用Dizcus
的站长找我“冰天雪地裸体跪求”解决他们的 CPU 占用 100% 的问题。

  一、关于 MySQL 数据库优化技术达到之争议,我之理念更宣示如下:

术及之争论是可以加大了座谈的。而自我的水准为确实只有是半瓶子水,对数据库的理论知识也无非略知一二这么点,牛牛们的批评,我虚心接心,分外感谢。不过,评论里之批评不要上升至人身攻击,否则,我的地盘我作主,直接去。

数据库的优化,要干到的一切很多。关说理论是无因而的,得拄实际讲。一个相对级数据库的实例优化表达不了问题,两单相对级的数据库优化也许还表达不了问题,但本身深信,五个、多少个、五单连续可以申明问题之,--停止至
2007.11.09,我曾经协理对象优化了五独记录数超过 1000 万之 discuz
论坛了。我眷恋事实胜于雄辩:优化在此以前,cpu 都是 100%;优化后,cpu 降到
30%~40% 左右。没错,做 ADD INDEX 会增添数据库 INSERT/UPDATE
时之开,但转变忘了论坛最要害的操作,是 SELECT 查询。

  二、关于寻找我协驾驭决数据库优化的品以及邮件,答复如下:

数据库的优化,不同的本子有两样之实在情况,优化一个
database,短则三零星时,慢则半上同上。请大家了然这中年老男人养家的压力,我之精力有限,不可以一一帮到。
对于从未收入之私房网站,我可于礼拜一星期之空闲时间内拉。请先跟自我联络好。
对于生收益之网站,嗯嗯,自觉点,请带价格和自身互换,或者直接配备美女请我用,否则免谈。:)
请不要来信问“优化大家这论坛你而稍用?”这样没营养的语句,而是径直说“帮咱优化
XXXX 论坛, XXXX RMB
可以不?”,我以为适当就召开。我们都分外费力,我的日相当贵,你若本人要好报价,我心惊肉跳吓着你。
请通过 [url]http://www.xiaohui.com/support/\[/url\]
与己联络。不要当评价里养个 QQ 号然后而自我加以而,我未相会时时盯在评论看。

附3: 补充笔记 2007-11-17: 关于装有首页四格插件的 dz 论坛导致 MySQL 占用
大量CPU 的解析
  后天手机巴士的站长( [url]http://bbs.sj84.com\[/url\]
)找到自己,他的冲 Discuz 的论坛,也是 CPU 占用 100% 的题目,服务器从
Win 2003 换来 CentOS,内存 2G, CPU 1.86G, 数据:cdb_threads 4
万,cdb_posts 96 万,cdb_members 35
万,已经照自己上边作品所说的优化了索引。按说这些布局丰富运行论坛了,但问题直接得无顶解决。

  经过调试,将缓慢查询的结果 dump 到
/usr/local/mysql/var/localhost-slow.log,运行
/usr/local/mysql/bin/mysqldumpslow
/usr/local/mysql/var/localhost-slow.log 查看,结合 show processlist
命令,发现慢查询集中在下列语句:

SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE
t.fid<>’S’
AND f.fid=t.fid
AND f.fid NOT IN (N,N,N,N)
AND t.closed NOT LIKE ‘S’
AND t.replies !=N
AND t.displayorder>=N
ORDER BY t.views DESC LIMIT N, N
  但是搜索 Dizcus
论坛的源码,并无找到这行代码。怀疑是插件的由。经查阅,论坛装了首页四格的插件,这行语词位于
include/toplist.php 中:
仔细检查这行代码,发现是不少特性仍旧语法规范上的题材:

AND t.closed NOT LIKE ‘S’:t.closed 是数值字段,不应该据此 LIKE ‘S’
的形式参加于。 
ORDER BY t.views: t.views 在 dizcus 的老数据表中,是绝非做索引的。
SELECT t.*:
这种写法,是未为推举的。虽然只要摘某个表内的装有字段,最好是按实全体描绘出来,例如:select
t.aa, t.bb, t.cc, t.dd, …
WHERE t.fid <> ‘S’: t.fid 是数值类字段,不应有写成
字符相比的样式。这一个针对性能影响不死,是个编程规范的问题。
….

  toplist.php 的外三长长的 sql 语句,都在这多少个题目。即便如对他的 sql
语句去优化 MySQL 结构,会带动不良的产物;假若一贯改动他的 toplist.php
程序,倘若站长将来擢升 toplist.php
又害怕带来不兼容问题。于是我提出外干脆关门大吉首页四格插件。

  关闭首页四格插件之后,CPU 降到 18% 左右颠簸,表现非常好。

  倘使是本身来写首页四格的主次,我莫会师动用这种方案,我会合为此定时15秒钟要30分钟查询同一涂鸦数据库,将结果写副
TXT 文件要临时表,然后程序还从中读取,功用会高多。

  结论:

只要装了插件的论坛碰着 CPU 高负荷时,指出关闭插件再评估性能。
慎装第三着插件。没事不要乱插。:)

附4:补充笔记
2008-06-10:这首小说,首要的是分析过程,而休是举行更正的那段代码
  目前生几各种在评头论足着留言,以及为我 EMAIL,说到以自以文中给闹之 这8行
ALTER TABLE 代码,在他的起 CPU 100% 的 dz 论坛及,用了今后没听从。

  我之诠释如下:这段代码,不克担保在 dz
的拥有版本下通用。具体问题,要具体分析。那段代码,是自己于 Dizcus! 5.5.0
的版的大旨下展开分析得出的校正结果。其他的本子,不敢保证。

  这首著作的首要性,并无是作结果的即段代码,而是什么得出此结果的解析过程。知道了规律,你协调同好分析。

附5: 相关作品:
解决一个 MySQL 服务器进程 CPU 占用 100%底技能笔记

liyihongcug 发表于 2011-08-17 21:06

[i=s] 本帖最后由于 liyihongcug 于 2011-08-17 21:08 编辑 [/i]

[url]http://www.cnkuai.cn/news/20092/639.htm\[/url\]
[url]http://lgcpeter.iteye.com/blog/210461\[/url\]早上帮朋友一台服务器解决了
Mysql cpu 占用 100% 的题目,稍整理如下,希望对各位有助。

朋友主机(Windows 2003 + IIS + PHP + MYSQL )近日 MySQL 服务过程
(mysqld-nt.exe) CPU 占用率总为 100% 高居不产。此主机来10独左右的
database, 分别被十个网站调用。据朋友测试,导致 mysqld-nt.exe cpu
占用奇大的是网站A,一旦以 IIS 师长以此网站截止服务,CPU
占用就降下了。一启用,则登时提高。

MYSQL CPU 占用 100% 的缓解进程

前天早上精心检查了转。目前是网站的七日平均日 IP 为2000,PageView 为
3万横。网站A 用的 database 最近时有暴发39只表明,记录数 60.1万漫长,占空间
45MB。按此数,MySQL 无法占这么强的资源。

遂以服务器上运行命令,将 mysql 当前的环境变量输出到文件 output.txt:

d:\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_size=200M

下一场再一次开 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.date FROM _mydata AS t1 LEFT
JOIN _myuser AS t3 ON t1.userid=t3.userid LEFT JOIN _mydata_body AS
t2 ON t1.pid=t3.pid ORDER BY t1.pid LIMIT 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.userid=t3.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 t2 WHERE
t1.pid=t2.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占用再度回落下了。

至此,问题迎刃而解。

解决 MYSQL CPU 占用 100% 的经验统计

  1. 增加 tmp_table_size 值。mysql 的安排文件中,tmp_table_size
    的默认大小是 32M。假使同摆临时表超出该大小,MySQL发生一个 The table
    tbl_name is full 情势的错误,假如您做过多高级 GROUP BY 查询,扩大tmp_table_size 值。 这是 mysql 官方关于此选项的分解:

tmp_table_size

This 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.

  1. 对 WHERE, JOIN, MAX(), MIN(), ORDER BY
    等子句中的规范判断中之所以到之字段,应该按照其创建索引 INDEX。

目给用来很快搜索有以一个列上用同样仅定值的履。没有索引,MySQL不得不首先为率先漫长记下起先并然后读了所有表直到它寻找有相关的举行。表越老,花费时间越
多。假使表对于查询的排列有一个目录,MySQL能便捷到达一个岗位去寻找到数据文件的中档,没有必要考虑有数据。假如一个申明有1000履,这较顺序读取
至少快100加倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树被储存。

据悉 mysql 的开文档:

索引 index 用于:

   o 飞快搜索来很是一个WHERE子句的执行
   o 当执行统一(JOIN)时,从任何表检索行。
   o 对特定的索引列找有MAX()或MIN()值
   o 假设排序或分组在一个可用键的突出右边前缀上举办(例如,ORDER BY
key_part_1,key_part_2),排序或分组一个表明。倘若有键值部分跟随DESC,键以倒序被读取。
   o
在有些意况中,一个查询能给优化来探寻值,不用问数据文件。假若对一些表的备应用的排列是数字型的以做某些键的太左侧前缀,为了还快,值好从索引树被摸出来。

假定你闹下列SELECT语句:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

倘一个几近列索引存在于col1暨col2达,适当的履行可以从来叫取出。假诺分此外单行列索引存在于col1以及col2上,优化器试图透过决定什么人索引将找到更不见的行并来寻觅有重新具备限制性的目并且使该索引取行。

开发人士做 SQL 数据表设计的时候,一定假如通盘考虑清楚。

renxiao2003 发表于 2011-08-21 20:55

谢谢分享,我还无赶上了呀。

相关文章