提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  工具

当前栏目

千万级记录的Discuz论坛导致MySQLCPU100%的优化笔记

笔记 优化 记录 导致 论坛 discuz 千万级
2023-06-13 09:14:26 时间
发现此主机运行了几个Discuz的论坛程序，Discuz论坛的好几个表也存在着这个问题。于是顺手一并解决，cpu占用再次降下来了。
　　前几天，一位朋友通过这篇文章找到了我，说他就是运行最新的discuz版本，MySQL占用CPU100%，导致系统假死，每天都要重启好几次，花了一个多月的时间一直没有解决，希望我帮忙一下。经过检查，他的这个论坛最重要的几个表中，目前cdb_members表，有记录6.2万；cdb_threads表，有记录11万；cdb_posts表，有记录1740万；所有数据表的记录加起来，超过2000万；数据库的大小超过1GB。经过半天的调试，总算完成了discuz论坛优化，于是将其解决经过记录在这篇文章中。

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

　　为了调试方便，我从dizcus的官网下载了其最新的Dizcus!5.5.0论坛程序．

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

复制代码代码如下:

mysql>showprocesslist;
+-----+------+----------------+---------+---------+------+------------+---------
-----------------------------------------------------------------+
|Id|User|Host|db|Command|Time|State|Info

+-----+------+----------------+---------+---------+------+------------+---------
-----------------------------------------------------------------+
|363|root|localhost:1393|history|Query|0|statistics|SELECTC
OUNT(*)FROMcdb_pmsWHEREmsgfromid=11212ANDfolder="outbox"|
+-----+------+----------------+---------+---------+------+------------+---------

检查cdb_pms表的结构：
复制代码代码如下:

mysql>showcolumnsfromcdb_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||
+-----------+------------------------+------+-----+---------+----------------+
10rowsinset(0.00sec)

这条语句:WHEREmsgfromid=11212ANDfolder="outbox"，我们看到，在cdb_pms表中，msgfromid字段已经建立了索引，但是，folder字段并没有。目前这个表已经有记录7823条。显然，这会对查询造成一定影响。于是为其建立索引：
复制代码代码如下:
mysql>ALTERTABLE`cdb_pms`ADDINDEX(`folder`);
QueryOK,7823rowsaffected(1.05sec)
Records:7823Duplicates:0Warnings:0

继续检查：
复制代码代码如下:
mysql>showprocesslist;
+------+------+----------------+---------+---------+------+------------+--------
--------------------------------------------------------------------------------
--------------+
|Id|User|Host|db|Command|Time|State|Info

|
+------+------+----------------+---------+---------+------+------------+--------
--------------------------------------------------------------------------------
--------------+

|1583|root|localhost:2616|history|Query|0|statistics|SELECT
t.tid,t.closed,f.*,ff.*,f.fidASfid
FROMcdb_threadst
INNERJOINcdb_forumsf|
+------+------+----------------+---------+---------+------+------------+--------
--------------------------------------------------------------------------------
--------------+
1rowsinset(0.00sec)

这条SQL语句是针对最重要的数据表cdb_threads进行操作的，由于showprocesslist没有将这条SQL语句全部显示完全，经对比Discuz论坛的源码，此SQL语句的原型位于common.inc.php的Line283，内容如下：
复制代码代码如下:
$query=$db->query("SELECTt.tid,t.closed,".(defined("SQL_ADD_THREAD")?
SQL_ADD_THREAD:"")."f.*,ff.*$accessadd1$modadd1,f.fidASfid
FROM{$tablepre}threadst
INNERJOIN{$tablepre}forumsfONf.fid=t.fid
LEFTJOIN{$tablepre}forumfieldsffONff.fid=f.fid$accessadd2$modadd2
WHEREt.tid="$tid"".($auditstatuson?"":"ANDt.displayorder>=0")."LIMIT1");

经检查，数据表cdb_threads,并没有针对displayorder字段建立索引。在discuz论坛中，displayorder字段多次参与了Where子句的比较。于是为其建立索引：
复制代码代码如下:
mysql>ALTERTABLE`cdb_threads`ADDINDEX(`displayorder`);
QueryOK,110330rowsaffected(2.36sec)
Records:110330Duplicates:0Warnings:0

此时cpu已经轻微下降了一部分。

继续检查，发现下面这条discuz的SQL语句，也导致负荷增加，这条语句位于rss.php程序中的第142行。
复制代码代码如下:
$query=$db->query("SELECTt.tid,t.readperm,t.price,t.author,t.dateline,t.subject,p.message
FROM{$tablepre}threadst
LEFTJOIN{$tablepre}postspONp.tid=t.tidANDp.first=1
WHEREt.fid="$fid"ANDt.displayorder>=0
ORDERBYt.datelineDESCLIMIT$num");

在这个Orderby子句中，用到了cdb_threads表中的dataline字段。这个字段是用来存储unixtime的时间戳，在整个论坛程序中，大部分时候数据的排序也是基于这个字段，竟然没有建立索引。于是加上：
复制代码代码如下:
mysql>ALTERTABLE`cdb_threads`ADDINDEX(`dateline`);
QueryOK,110330rowsaffected(12.27sec)
Records:110330Duplicates:0Warnings:0

查找占用CPU高负茶的SQL语句，是一件麻烦而又枯燥的事，需要一条一条排除、分析。后面的工作，都是依此类推，经过检查，共查出有八处地方，需要增加索引，如果你也碰到了discuz5.5.0论坛导致cpu占用100%的情况，可以直接将下列语句复制过去，在mysql的命令行下执行即可：
复制代码代码如下:
ALTERTABLE`cdb_pms`ADDINDEX(`folder`);
ALTERTABLE`cdb_threads`ADDINDEX(`displayorder`);
ALTERTABLE`cdb_threads`ADDINDEX(`dateline`);
ALTERTABLE`cdb_threads`ADDINDEX(`closed`);
ALTERTABLE`cdb_threadsmod`ADDINDEX(`dateline`);
ALTERTABLE`cdb_sessions`ADDINDEX(`invisible`);
ALTERTABLE`cdb_forums`ADDINDEX(`type`);
ALTERTABLE`cdb_forums`ADDINDEX(`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标准程序是没有问题，出现楼主说的情况，多半属于服务器或者安装一些插件造成的

　　显然将问题推给插件的原因是不正确的．举个简单的例子：在最新的discuz5.5.0forumdisplay.php第183行，有如下语句：

$query=$db->query("SELECTuid,groupid,username,invisible,
lastactivity,actionFROM{$tablepre}sessions
WHERE$guestwherefid="$fid"ANDinvisible=0");
　　这里的invisible并没有建立索引。本文中有评论认为session表是内存表,速度会很快。理论是如此。不过我在showprocesslist中，观察到上面这条语句占用了大量CPU，所以也将其一并加上了index。cdb_threads中的closed等字段,也多次参与where运算,也没有建立索引。这些运算的语句,是discuz自己的程序中的。

附2:补充笔记2007-11-11

　　自从这篇笔记发表以来，在我的这篇文章的评论、以及我的联系消息中，就经常收到许多下面两种类型的评论和邮件：一、许多技术人员批评我胡说八道、Dizcus论坛不需要做优化或者不能乱建索引的；二、许多使用Dizcus的站长找我“冰天雪地裸体跪求”解决他们的CPU占用100%的问题。

　　一、关于MySQL数据库优化技术上的争论，我的观点再次声明如下：

  1. 技术上的争论是可以放开了讨论的。而我的水平也确实只是半瓶水，对数据库的理论知识也只懂这么点，牛牛们的批评，我虚心接心，非常感谢。但是，评论里的批评不要上升到人身攻击，否则，我的地盘我作主，直接删除。

  2. 数据库的优化，要涉及到的方方面面很多。关说理论是没有用的，得靠事实说话。一个千万级数据库的实例优化说明不了问题，两个千万级的数据库优化也许还说明不了问题，但我相信，三个、四个、五个总是可以说明问题的，－－截止到2007.11.09，我已经帮助朋友优化过五个记录数超过1000万的discuz论坛了。我想事实胜于雄辩：优化之前，cpu都是100%；优化之后，cpu降到30%~40%左右。没错，做ADDINDEX　会增加数据库INSERT/UPDATE时的开销，但别忘了论坛最主要的操作，是SELECT查询。

　　二、关于找我帮忙解决数据库优化的评论和邮件，答复如下:

  1. 数据库的优化，不同的版本有不同的实际情况，优化一个database，短则三两小时，慢则半天一天。请大家理解这个中年老男人养家的压力，我的精力有限，不可能一一帮到。
  2. 对于没有收入的个人网站，我可以在周六周日的空余时间内帮忙。请事先与我联系好。
  3. 对于有收入的网站，嗯嗯，自觉点，请带价格与我联系，或者直接安排美女请我吃饭，否则免谈。:)请不要来信问“优化我们这个论坛你要多少费用？”这样没营养的话，而是直接说“帮我们优化XXXX论坛，XXXXRMB可以不？”，我觉得合适就做。大家都很忙，我的时间很值钱，你要我自己报价，我怕吓着你。
  4. 请通过http://www.xiaohui.com/support/与我联系。不要在评论里留个QQ号然后要我加你，我不会时时盯着评论看。

附3:补充笔记2007-11-17:关于装有首页四格插件的dz论坛导致MySQL占用大量CPU的分析

　　今天手机巴士的站长(http://bbs.sj84.com)找到我，他的基于Discuz的论坛，也存在CPU占用100%的问题，服务器从Win2003换到　CentOS，内存2G,CPU1.86G,　数据：cdb_threads4万，cdb_posts96万，cdb_members35万，已经按我上面文章所说的优化过索引。按说这个配置足够运行论坛了，但问题一直得不到解决。

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

SELECTt.*,f.nameFROMcdb_threadst,cdb_forumsfWHERE
t.fid<>"S"
ANDf.fid=t.fid
ANDf.fidNOTIN(N,N,N,N)
ANDt.closedNOTLIKE"S"
ANDt.replies!=N
ANDt.displayorder>=N
ORDERBYt.viewsDESCLIMITN,N
　　然而搜索Dizcus论坛的源码，并没有找到这行代码。怀疑是插件的原因。经查，论坛装了首页四格的插件，这行语句位于include/toplist.php中:仔细检查这行代码，发现存在许多性能或语法规范上的问题：

  1. ANDt.closedNOTLIKE"S":t.closed是数值字段，不应该用LIKE"S"的形式参与比较。　
  2. ORDERBYt.views:t.views在dizcus的原始数据表中，是没有做索引的。
  3. SELECTt.*:这种写法，是不被推荐的。如果要选择某个表内的所有字段，最好是按实全部写出来，例如：selectt.aa,t.bb,t.cc,t.dd,...
  4. WHERE　t.fid<>"S":t.fid是数值型字段，不应该写成字符比较的形式。这个对性能影响不大，是个编程规范的问题。
  5. ....

　　toplist.php的其他三条sql语句，都存在这些问题。如果要针对他的sql语句去优化MySQL结构，会带来不良的后果；如果直接改他的toplist.php程序，如果站长以后升级　toplist.php又怕带来不兼容问题。于是我建议他干脆关闭首页四格插件。

　　关闭首页四格插件之后，CPU降到18%左右震荡，表现非常良好。

　　如果是我来写首页四格的程序，我不会采用这种方案，我会用定时15分钟或30分钟查询一次数据库，将结果写入TXT文件或临时表，然后程序再从中读取，效率会高许多。

　　结论：

  1. 如果装了插件的论坛碰到　CPU高负荷时，建议关掉插件再评估性能。
  2. 慎装第三方插件。没事不要乱插。:)

附4：补充笔记2008-06-10：这篇文章，重要的是分析过程，而不是进行修正的那段代码

　　最近有几位在评论中留言，以及给我EMAIL，说到将我在文中给出的那8行ALTERTABLE代码，在他的出现CPU100%的dz论坛上，用了之后没有效果。

　　我的解释如下：这段代码，不能保证在dz的所有版本下通用。具体问题，要具体分析。这段代码，是我在Dizcus!5.5.0的版本的基本下进行分析得出的校正结果。其他的版本，不敢保证。

　　这篇文章的重点，并不是作为结果的这段代码，而是如何得出这个结果的分析过程。知道了原理，你自己一样可以分析。

相关文章