前几天,一位朋友通过这篇文章找到了我,说他就是运行最新的 discuz 6.0 版本,MySQL 占用 CPU 100%,导致系统假死,每天都要重启好几次,花了一个多月的时间一直没有解决,希望我帮忙一下。经过检查,他的这个论坛最重要的几个表中,目前 cdb_members 表,有记录 6.2 万;cdb_threads 表,有记录 11万;cdb_posts表,有记录 1740 万;所有数据表的记录加起来,超过 2000 万;数据库的大小超过 1GB。经过半天的调试,总算完成了 discuz 论坛优化,于是将其解决经过记录在本篇文章中,供各位参考。
2007年3月我发现 discuz 论坛的数据库结构设计有一些疏忽,有许多查询子句的条件比较,都没有建立 Index 索引。当时我所检查的那个数据表,记录只有几千条,因此对 CPU 负荷不大。现在这个数据库表,上千万的记录检索,可以想象,如果数据表结构设计不规范,没有提供索引,所耗费的时间是一个恐怖的数字。有关 MySQL 建立索引的重要性,
我首先检查了 my.ini 的参数配置,一切正常。进入 MySQL 的命令行,调用 show processlist 语句,查找负荷最重的 SQL 语句,结合 Discuz 论坛的源码,发现有以下语句导致 CPU 上升:
检查 cdb_pms 表的结构:
这条语句: WHERE msgfromid=?' 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
继续检查:
SQL 语句是针对最重要的数据表 cdb_threads 进行操作的,由于 show processlist 没有将这条 SQL 语句全部显示完全,经对比 Discuz 论坛的源码,此SQL语句的原型如下:
经检查,数据表 cdb_threads, 并没有针对 displayorder 字段建立索引。在 discuz 论坛中,displayorder字段多次参与了 Where 子句的比较。于是为其建立索引:
此时 cpu 已经轻微下降了一部分。
继续检查,发现 discuz 的这条语句,也导致负荷加重:
在这个 Order by 子句中,用到了 cdb_threads 表中的 dataline 字段。这个字段是用来存储 unixtime 的时间戳,在整个论坛程序中,整个数据的排序也是基于这个字段,竟然没有建立索引,已经无语了。于是加上:
后面的工作,都是依此类推,经过检查,共查出有八处地方,需要增加索引,如果你也碰到了 discuz 论坛导致 cpu 占用 100% 的情况,可以直接将下列语句复制过去,在 mysql 的命令行下执行即可:
这些结构的优化之后,整个系统的 CPU 负荷在 10%~20%左右震荡,问题解决。
2007年3月我发现 discuz 论坛的数据库结构设计有一些疏忽,有许多查询子句的条件比较,都没有建立 Index 索引。当时我所检查的那个数据表,记录只有几千条,因此对 CPU 负荷不大。现在这个数据库表,上千万的记录检索,可以想象,如果数据表结构设计不规范,没有提供索引,所耗费的时间是一个恐怖的数字。有关 MySQL 建立索引的重要性,
我首先检查了 my.ini 的参数配置,一切正常。进入 MySQL 的命令行,调用 show processlist 语句,查找负荷最重的 SQL 语句,结合 Discuz 论坛的源码,发现有以下语句导致 CPU 上升:
CODE:
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=?' AND folder='outbox' |
+-----+------+----------------+---------+---------+------+------------+---------
+-----+------+----------------+---------+---------+------+------------+---------
-----------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+-----+------+----------------+---------+---------+------+------------+---------
-----------------------------------------------------------------+
| 363 | root | localhost:1393 | history | Query | 0 | statistics | SELECT C
OUNT(*) FROM cdb_pms WHERE msgfromid=?' AND folder='outbox' |
+-----+------+----------------+---------+---------+------+------------+---------
检查 cdb_pms 表的结构:
CODE:
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)
+-----------+------------------------+------+-----+---------+----------------+
| 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=?' 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
继续检查:
CODE:
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)
+------+------+----------------+---------+---------+------+------------+--------
--------------------------------------------------------------------------------
--------------+
| 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语句的原型如下:
CODE:
$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>='")." LIMIT 1");
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>='")." LIMIT 1");
经检查,数据表 cdb_threads, 并没有针对 displayorder 字段建立索引。在 discuz 论坛中,displayorder字段多次参与了 Where 子句的比较。于是为其建立索引:
CODE:
mysql> ALTER TABLE `cdb_threads` ADD INDEX ( `displayorder` );
Query OK, 110330 rows affected (2.36 sec)
Records: 110330 Duplicates: 0 Warnings: 0
Query OK, 110330 rows affected (2.36 sec)
Records: 110330 Duplicates: 0 Warnings: 0
此时 cpu 已经轻微下降了一部分。
继续检查,发现 discuz 的这条语句,也导致负荷加重:
CODE:
$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='
WHERE t.fid='$fid' AND t.displayorder>='
ORDER BY t.dateline DESC LIMIT $num");
FROM ...{$tablepre}threads t
LEFT JOIN ...{$tablepre}posts p ON p.tid=t.tid AND p.first='
WHERE t.fid='$fid' AND t.displayorder>='
ORDER BY t.dateline DESC LIMIT $num");
在这个 Order by 子句中,用到了 cdb_threads 表中的 dataline 字段。这个字段是用来存储 unixtime 的时间戳,在整个论坛程序中,整个数据的排序也是基于这个字段,竟然没有建立索引,已经无语了。于是加上:
CODE:
mysql> ALTER TABLE `cdb_threads` ADD INDEX ( `dateline` );
Query OK, 110330 rows affected (12.27 sec)
Records: 110330 Duplicates: 0 Warnings: 0
Query OK, 110330 rows affected (12.27 sec)
Records: 110330 Duplicates: 0 Warnings: 0
后面的工作,都是依此类推,经过检查,共查出有八处地方,需要增加索引,如果你也碰到了 discuz 论坛导致 cpu 占用 100% 的情况,可以直接将下列语句复制过去,在 mysql 的命令行下执行即可:
CODE:
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` );
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%左右震荡,问题解决。