解决一个 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\]
中。

  2007年3月本身发现 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 论坛不欲开优化还是无克混建索引的;二、许多行使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

谢谢分享,我还没有碰面了什么。

网站地图xml地图