实战:Discuz导致MySQL CPU 100%占用优化-服务器专区

实战:Discuz导致MySQL CPU 100%占用优化

作者：InfoQ 陈俊编辑：李博（泡泡） 2007-07-16 09:19

    【IT168 专稿】前不久，我写过一篇文章《实战笔记MYSQL CPU 占用 100% 的解决过程》( 点击)，谈到自己在解决一个拥有 60 万条记录的 MySQL 数据库访问时，导致 MySQL CPU 占用 100% 的经过。在解决问题完成优化(optimize)之后，我发现 Discuz 论坛也存在这个问题，当时稍微提了一下：

    发现此主机运行了几个 Discuz 的论坛程序， Discuz论坛的好几个表也存在着这个问题。于是顺手一并解决，cpu占用再次降下来了。

    前几天，一位朋友通过这篇文章找到了我，说他就是运行最新的 discuz 6.0 版本，MySQL 占用 CPU 100%，导致系统假死，每天都要重启好几次，花了一个多月的时间一直没有解决，希望我帮忙一下。经过检查，他的这个论坛最重要的几个表中，目前 cdb_members 表，有记录 6.2 万；cdb_threads 表，有记录 11万；cdb_posts表，有记录 1740 万；所有数据表的记录加起来，超过 2000 万；数据库的大小超过 1GB。经过半天的调试，总算完成了 discuz 论坛优化，于是将其解决经过记录在本篇文章中，供各位参考。

    2007年3月我发现 discuz 论坛的数据库结构设计有一些疏忽，有许多查询子句的条件比较，都没有建立 Index 索引。当时我所检查的那个数据表，记录只有几千条，因此对 CPU 负荷不大。现在这个数据库表，上千万的记录检索，可以想象，如果数据表结构设计不规范，没有提供索引，所耗费的时间是一个恐怖的数字。有关 MySQL 建立索引的重要性，可以参见我的这篇文章底部的说明：http://www.xiaohui.com/weekly/20070307.htm
${PageNumber}    我首先检查了 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=?' 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=?' 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

${PageNumber} 继续检查：

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语句的原型如下：

$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>='")." 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 的这条语句，也导致负荷加重：

$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=' 
WHERE t.fid='$fid' AND t.displayorder>=' 
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

后面的工作，都是依此类推，经过检查，共查出有八处地方，需要增加索引，如果你也碰到了 discuz 论坛导致 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` );

    这些结构的优化之后，整个系统的 CPU 负荷在 10%~20%左右震荡，问题解决。

后记：

　　我很奇怪，设计数据库结构，是一个数据库开发人员的基本功，discuz 论坛好歹也是一个发展了有六七年的论坛了，为何数据库结构设计得如此糟糕？我想也许有如下三个原因：

    1)数据库开发人员设计时本身的疏忽
    2)意留下的缺陷，当普通论坛没有上数量级的记录时，不会感觉到这个问题，当数据量增大（例如千万级），此问题突现，以便针对用户提供个性服务收取服务费．呵呵，估且以最大的恶意来猜测此事，玩笑而已，不必当真。:)　
    3)另一个可能就是用户的论坛是从低版本升级而来，程序升了级，但数据结构也许没有做相应的更新

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

    今天查看网站日志的 reffer, 发现在 discuz 的官方论坛上，有人就此文引起了一些争论: http://www.discuz.net/thread-673887-1-1.html。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 自己的程序中的。

    本文已经由原作者xiaohui.com授权IT168服务器频道刊登，感谢作者对IT168的鼎力支持！
    原文：http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm

关注我们