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

felixtong

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 自己的程序中的。
附2: 相关文章：
解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记

相关评论
我要评论
|
评论中心
|
给我写信
|
访问论坛

第1楼  hcy1122 写于 2007-07-01 12:24支持你，小辉！
我们要继续努力
你现在在哪呢？好久没有你写关于自己的随笔了
XiaoHui 回复于 2007-07-01 13:06 :
在长沙．：）
第2楼  游客 写于 2007-07-02 09:02索引不是越多越好，建立了索引之后，插入更新速度就是非常慢，只是select能比较快，这两者之间要有一个均衡点
第3楼  pzhai 写于 2007-07-02 09:33估计discuz!越来越看重中小论坛的市场了吧 ，再说本来mysql对海量数据就不太友好，发展瓶颈吧
第4楼  vagabond 写于 2007-07-09 11:07写不得不错啊，大哥现在从事什么工作
程序员出来都了什么工作
第5楼  继续革命 写于 2007-07-10 09:05$query = $db->query("SELECT uid, groupid, username, invisible,
lastactivity, action FROM {$tablepre}sessions
WHERE $guestwhere fid='$fid' AND invisible=0");
session 表为内存表，且定长，且频繁写入，且行数较少，在不建立的索引的情况下速度亦十分之快，建立过多的索引，反而会降低写入和更新的效率。楼主的网站如果在线人数达到1w，你再看是什么效果？
XiaoHui 回复于 2007-07-10 10:06 :
多谢指正. 抱歉, 我没有注意到 session 表是内存表. 我在 show processlist 观察到这条语句占用 cpu 比较频繁, 加上 index 之后, 效果有改善. 当然, 有更多的例子, 例如 cdb_threads 的 closed 字段, dataline 字段等.
一个事实是: 在添加了这些 INDEX 之后, 目标主机的 CPU 由100% 降到了 10~20%. 另外声明一下, 文中所说的主机不是我的, 我只是帮朋友处理一下.:)
第6楼  mad_frog 写于 2007-07-12 11:46discuz的这些所谓的优化，有些是不错的比如dateline的问题，但是你也应该知道如此（上千万级）数量的数据，做不做这样的索引优化，是有实际条件的，数据更新的频繁度导致了他们不敢这么做索引，这是论坛呀，不是文章管理系统。更新的占多数。
第7楼  Rocky_Li 写于 2007-08-14 17:35小辉加油！我看好你！
第8楼  rq 写于 2007-09-06 16:37小辉，我认为discuz没有这么多所谓的索引问题
优化千万级数据量的数据库最好是在硬件上下功夫，例如加大内存，使用SCSI磁盘阵列，或者直接使用多服务器负载均衡。
另外说说索引：
大多文中提到没有建立的索引其实都在复合索引中，是和查询语句相匹配的
例如：cdb_pms表中有msgfromid索引（msgfromid+folder+dateline），就对应WHERE msgfromid=11212 AND folder='outbox' ORDER BY dateline DESC这个查询，在多条件的查询中，复合索引命中率会比较高，效率要比单独建立msgfromid,folder,dateline三个索引效果好，并且节省资源。
同样cdb_thread的displayorder,closed也不必单独再建立索引，因为在文中提到的查询条件中tid已经是聚集+唯一索引，数据库查询优化器不会因为后面的条件受影响的：）
discuz在索引的设置上我认为是合理的。
索引加速了读取速度，但是也降低了写入和更新的速度，我认为索引要用到频繁读取的地方，例如帖子列表/帖子内容这样的地方。在小辉提到的2kw级别的bbs，在线人数/发帖回帖量都是一个比较大的规模，所以使用索引的平衡点更加重要。
100%资源占用造成的问题我想是因为mod使用不当，例如今日发帖排行、今日新帖、今日热帖一类的功能，这些查询才是罪魁祸首，小辉可以试试去掉所有插件后CPU还是不是这么高。
XiaoHui 回复于 2007-09-06 22:34 :
多谢批评指正. 复合索引这块我了解.
我后来查看了一下, 造成这个现象, 有两个原因:
1. 用户升级论坛程序之后, 没有升级数据表的结构. 新近的程序, 采用的数据表结构也不一样, 索引也不一样. 用户没有升级这块, 所以造成查询耗时. 这个原因, 是由于我在查看用户实际的数据表结构与 discuz 最新安装程序的 install SQL 时发现的.
2. discuz 本身有部分没有考虑到索引的问题. 这个我在查看最新的 discuz 论坛源码以及 install SQL 源码时, 得到的验证.
但总的来说, 以第一条原因居多. 所以我在文中对 discuz 的指责, 确实有些过于偏激. 在此愿意表示道歉.
索引的滥加确实会造成写入和更新的速度. 但我觉得这篇文章里提到的更改方式, 应该是正确的. 毕竟有一个看得见的 2kw 记录实际解决效果在这里.


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/24375/showart_380335.html