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

这篇文章主要为大家详细介绍了千万级记录的Discuz论坛导致MySQL CPU 100%的优化笔记，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

 
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' | 
+-----+------+----------------+---------+---------+------+------------+--------- 

代码如下:

 
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) 

代码如下:

 
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) 

代码如下:

 
$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"); 

代码如下:

 
mysql> ALTER TABLE `cdb_threads` ADD INDEX ( `displayorder` ); 
Query OK, 110330 rows affected (2.36 sec) 
Records: 110330 Duplicates: 0 Warnings: 0 

代码如下:

 
$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"); 

代码如下:

 
mysql> ALTER TABLE `cdb_threads` ADD INDEX ( `dateline` ); 
Query OK, 110330 rows affected (12.27 sec) 
Records: 110330 Duplicates: 0 Warnings: 0 

代码如下:

 
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: 补充笔记 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");

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

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

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

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

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

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

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

　　经过调试，将慢查询的结果 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

1. AND t.closed NOT LIKE 'S':t.closed 是数值字段，不应该用 LIKE 'S' 的形式参与比较。　
2. ORDER BY t.views: t.views 在 dizcus 的原始数据表中，是没有做索引的。
3. SELECT t.*: 这种写法，是不被推荐的。如果要选择某个表内的所有字段，最好是按实全部写出来，例如：select t.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行 ALTER TABLE 代码，在他的出现 CPU 100% 的 dz 论坛上，用了之后没有效果。

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

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

注：关于千万级记录的Discuz论坛导致MySQL CPU 100%的优化笔记的内容就先介绍到这里，更多相关文章的可以留意