老鬼! 不知道你看过没有!

小李 · Aug 15, 2007

千万级记录的Discuz论坛导致MySQL CPU 100%的优化笔记

--------------------------------------------------------------------------------
http://www.XiaoHui.com 2007-07-01 [原创]

关键字: mysql, cpu, 优化, optimize, cpu 100%, mysql cpu 100%, discuz 优化, mysql 优化, cpu 负荷

摘要　: 朋友网站使用 Discuz 论坛，目前 cdb_posts 表有记录1740万, cdb_threads 表有记录 11万，cdb_members 表有记录6.2 万，MySQL 占用 CPU 100%，导致系统假死，每天都要重启好几次。我针对这个问题, 对Discuz论坛作了一次优化
　　2007年3月，我写过一篇文章《解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记》( http://www.xiaohui.com/weekly/20070307.htm )，谈到自己在解决一个拥有 60 万条记录的 MySQL 数据库访问时，导致 MySQL CPU 占用 100% 的经过。在解决问题完成优化(optimize)之后，我发现 Discuz 论坛也存在这个问题，当时稍微提了一下：
发现此主机运行了几个 Discuz 的论坛程序， Discuz论坛的好几个表也存在着这个问题。于是顺手一并解决，cpu占用再次降下来了。
　　前几天，一位朋友通过这篇文章找到了我，说他就是运行最新的 discuz 版本，MySQL 占用 CPU 100%，导致系统假死，每天都要重启好几次，花了一个多月的时间一直没有解决，希望我帮忙一下。经过检查，他的这个论坛最重要的几个表中，目前 cdb_members 表，有记录 6.2 万；cdb_threads 表，有记录 11万；cdb_posts表，有记录 1740 万；所有数据表的记录加起来，超过 2000 万；数据库的大小超过 1GB。经过半天的调试，总算完成了 discuz 论坛优化，于是将其解决经过记录在这篇文章 http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm 中。

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

　　为了调试方便，我从 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 论坛导致 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 的官方论坛上，有人就此文引起了一些争论: 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 自己的程序中的。
Click to expand...

原文:http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm

平行线 · Aug 15, 2007

都是说DZ?

laogui · Aug 15, 2007

vbb索引最多，数据库奇大

apple · Aug 15, 2007

如果打算删除一个算是比较大的数据库咋整？
偶不会，一点一点删，弄的心情很糟糕！

Forums

老鬼! 不知道你看过没有!

小李 New Member

平行线 New Member

laogui Administrator
Staff Member

apple New Member

Articles

Media

Resources

Members

Useful Searches

老鬼! 不知道你看过没有!

小李 New Member

平行线 New Member

laogui Administrator Staff Member

apple New Member

Articles

Media

Resources

Members

Useful Searches

laogui Administrator
Staff Member