《MYSQL教程千萬級記錄的Discuz論壇導致MySQL CPU 100%的優化筆記》要點：
本文介紹了MYSQL教程千萬級記錄的Discuz論壇導致MySQL CPU 100%的優化筆記，希望對您有用。如果有疑問，可以聯系我們。
發現此主機運行了幾個 Discuz 的論壇程序, Discuz論壇的好幾個表也存在著這個問題.于是順手一并辦理,cpu占用再次降下來了.
前幾天,一位朋友通過這篇文章找到了我,說他就是運行最新的 discuz 版本,MySQL 占用 CPU 100%,導致系統假死,每天都要重啟好幾次,花了一個多月的時間一直沒有辦理,希望我幫忙一下.經過檢查,他的這個論壇最重要的幾個表中,目前 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 的命令行,調用 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 5.5.0 論壇導致 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 等等.
MYSQL應用 完成這些結構的優化之后,整個系統的 CPU 負荷在 10%~20%左右震蕩,問題辦理.
MYSQL應用 我很奇怪,設計數據庫結構,是一個數據庫開發人員的基本功,discuz 論壇好歹也是一個發展了有六七年的論壇了,為何數據庫結構設計得如此糟糕?我想也許有如下三個原因：
- 數據庫開發人員設計時本身的疏忽
- 故意留下的缺陷,當普通論壇沒有上數量級的記錄時,不會感覺到這個問題,當數據量增大(例如千萬級),此問題突現,以便針對用戶提供個性服務收取服務費．呵呵,估且以最大的惡意來猜測此事,玩笑罷了,不必當真.:)
- 另一個可能就是用戶的論壇是從低版本升級而來,程序升了級,但數據結構也許沒有做相應的更新
附1: 補充筆記 2007-07-09
MYSQL應用 今天查看網站日志的 reffer, 發現在 discuz 的官方論壇上,有人就此文引起了一些爭論: http://www.discuz.net/thread-673887-1-1.html.discuz 的管理員和管理員有如下言論：
MYSQL應用引用自 cnteacher:
MYSQL應用恰恰相反,discuz 的優化措施和數據庫的索引是依照大規模論壇設計的.
MYSQL應用TO 一樓：數據庫結構的設計都是依照程序應用來進行的,使用任何非Discuz! 標準版本以外的代碼和程序,或者變更標準數據結構,均可能遇到不可預知的各種問題.
MYSQL應用引用自 童虎:
MYSQL應用你們可以看看xxxxx, xxxx之類的比較大型的網站,這種網站使用dz論壇都沒有問題,說明dz標準程序是沒有問題,出現樓主說的情況,多半屬于服務器或者安裝一些插件造成的
MYSQL應用 顯然將問題推給插件的原因是不正確的．舉個簡單的例子：在最新的 discuz 5.5.0 forumdisplay.php 第183 行,有如下語句：
MYSQL應用$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: 補充筆記 2007-11-11
自從這篇筆記頒發以來,在我的這篇文章的評論、以及我的聯系消息中,就經常收到許多下面兩種類型的評論和郵件：一、許多技術人員批評我胡說八道、Dizcus 論壇不需要做優化或者不能亂建索引的；二、許多使用Dizcus 的站長找我“冰天雪地裸體跪求”解決他們的 CPU 占用 100% 的問題.
MYSQL應用 一、關于 MySQL 數據庫優化技術上的爭論,我的觀點再次聲明如下：
- 技術上的爭論是可以放開了討論的.而我的水平也確實只是半瓶水,對數據庫的理論知識也只懂這么點,牛牛們的批評,我虛心接心,非常感謝.但是,評論里的批評不要上升到人身攻擊,否則,我的地盤我作主,直接刪除.
- 數據庫的優化,要涉及到的方方面面很多.關說理論是沒有用的,得靠事實說話.一個千萬級數據庫的實例優化說明不了問題,兩個千萬級的數據庫優化也許還說明不了問題,但我相信,三個、四個、五個總是可以說明問題的,－－截止到 2007.11.09,我已經幫助朋友優化過五個記錄數超過 1000 萬的 discuz 論壇了.我想事實勝于雄辯：優化之前,cpu 都是 100%；優化之后,cpu 降到 30%~40% 左右.沒錯,做 ADD INDEX 會增加數據庫 INSERT/UPDATE 時的開銷,但別忘了論壇最主要的操作,是 SELECT 查詢.
MYSQL應用 二、關于找我幫忙辦理數據庫優化的評論和郵件,答復如下:
- 數據庫的優化,不同的版本有不同的實際情況,優化一個 database,短則三兩小時,慢則半天一天.請大家理解這個中年老男人養家的壓力,我的精力有限,不可能一一幫到.
-
對于沒有收入的個人網站,我可以在周六周日的空余時間內幫忙.請事先與我聯系好.
-
對于有收入的網站,嗯嗯,自覺點,請帶價格與我聯系,或者直接支配美女請我吃飯,否則免談.:) 請不要來信問“優化我們這個論壇你要多少費用?”這樣沒營養的話,而是直接說“幫我們優化 XXXX 論壇, XXXX RMB 可以不?”,我覺得合適就做.大家都很忙,我的時間很值錢,你要我自己報價,我怕嚇著你.
-
請通過 http://www.xiaohui.com/support/ 與我聯系.不要在評論里留個 QQ 號然后要我加你,我不會時時盯著評論看.
附3: 補充筆記 2007-11-17: 關于裝有首頁四格插件的 dz 論壇導致 MySQL 占用 大量CPU 的分析
今天手機巴士的站長( http://bbs.sj84.com )找到我,他的基于 Discuz 的論壇,也存在 CPU 占用 100% 的問題,服務器從 Win 2003 換到 CentOS,內存 2G, CPU 1.86G, 數據：cdb_threads 4 萬,cdb_posts 96 萬,cdb_members 35 萬,已經按我上面文章所說的優化過索引.按說這個配置足夠運行論壇了,但問題一直得不到解決.
MYSQL應用 經過調試,將慢查詢的結果 dump 到 /usr/local/mysql/var/localhost-slow.log,運行 /usr/local/mysql/bin/mysqldumpslow /usr/local/mysql/var/localhost-slow.log 查看,結合 show processlist 命令,發現慢查詢集中在下列語句:
MYSQL應用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
然而搜索 Dizcus 論壇的源碼,并沒有找到這行代碼.懷疑是插件的原因.經查,論壇裝了首頁四格的插件,這行語句位于 include/toplist.php 中: 仔細檢查這行代碼,發現存在許多性能或語法規范上的問題：
-
AND t.closed NOT LIKE 'S':t.closed 是數值字段,不應該用 LIKE 'S' 的形式參與比較.
-
ORDER BY t.views: t.views 在 dizcus 的原始數據表中,是沒有做索引的.
-
SELECT t.*: 這種寫法,是不被保舉的.如果要選擇某個表內的所有字段,最好是按實全部寫出來,例如：select t.aa, t.bb, t.cc, t.dd, ...
-
WHERE t.fid <> 'S': t.fid 是數值型字段,不應該寫成 字符比較的形式.這個對性能影響不大,是個編程規范的問題.
- ....
MYSQL應用 toplist.php 的其他三條 sql 語句,都存在這些問題.如果要針對他的 sql 語句去優化 MySQL 結構,會帶來不良的后果；如果直接改他的 toplist.php 程序,如果站長以后升級 toplist.php 又怕帶來不兼容問題.于是我建議他干脆關閉首頁四格插件.
MYSQL應用 關閉首頁四格插件之后,CPU 降到 18% 左右震蕩,表現非常良好.
MYSQL應用 如果是我來寫首頁四格的程序,我不會采用這種方案,我會用定時15分鐘或30分鐘查詢一次數據庫,將結果寫入 TXT 文件或臨時表,然后程序再從中讀取,效率會高許多.
MYSQL應用 結論：
- 如果裝了插件的論壇碰到 CPU 高負荷時,建議關掉插件再評估性能.
- 慎裝第三方插件.沒事不要亂插.:)
附4：補充筆記 2008-06-10：這篇文章,重要的是分析過程,而不是進行修正的那段代碼
MYSQL應用 最近有幾位在評論中留言,以及給我 EMAIL,說到將我在文中給出的 那8行 ALTER TABLE 代碼,在他的出現 CPU 100% 的 dz 論壇上,用了之后沒有效果.
MYSQL應用 我的解釋如下：這段代碼,不能保證在 dz 的所有版本下通用.具體問題,要具體分析.這段代碼,是我在 Dizcus! 5.5.0 的版本的基本下進行分析得出的校正結果.其他的版本,不敢保證.
MYSQL應用 這篇文章的重點,并不是作為結果的這段代碼,而是如何得出這個結果的分析過程.知道了原理,你本身一樣可以分析.
《MYSQL教程千萬級記錄的Discuz論壇導致MySQL CPU 100%的優化筆記》是否對您有啟發，歡迎查看更多與《MYSQL教程千萬級記錄的Discuz論壇導致MySQL CPU 100%的優化筆記》相關教程，學精學透。維易PHP學院為您提供精彩教程。
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/11964.html
