Talent DISCUZ Forum leads to mysqlcpu100% optimization notes
It is found that this host is running several Discuz forum programs, and several tables of Discuz forum also have this problem.So it was solved together, and the cpu occupancy dropped again.
A few days ago, a friend found me through this article, and said that he is running the latest discuz version, MySQL occupies 100% of the CPU, causing the system to hang up, restarting several times a day, it took more than a month Time has not been resolved, I hope I can help.After checking, among the most important tables in his forum, the cdb_members table currently has 62,000 records; the cdb_threads table has 110,000 records; the cdb_posts table has 17.4 million records; the total number of records in all data tables exceeds 2000.Ten thousand; the size of the database exceeds 1GB.After half a day of debugging, I finally completed the optimization of the discuz forum, so I recorded the solution in this article.
In March 2007, I found that the database structure design of the discuz forum has some negligence.There are many query clause comparisons, and no Index is established.The data table I checked at that time had only a few thousand records, so it didn't load the CPU much.Now this database table has tens of millions of records retrieved.It is conceivable that if the data table structure is not standardized and no index is provided, the time spent is a terrible number.For the importance of MySQL indexing, please refer to the description at the bottom of my article.
For debugging convenience, I downloaded the latest Dizcus! 5.5.0 forum program from the official website of dizcus.
I first checked the parameter configuration of my.ini, everything is normal.Enter the MySQL command line, call the show processlist statement, find the most heavily loaded SQL statement, combined with the source code of the Discuz forum, find that the following statement causes the CPU to rise:
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' |
+-----+------+----------------+---------+---------+------+------------+---------
Check the structure of the cdb_pms table:
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)
This sentence: WHERE msgfromid=11212 AND folder='outbox', we see that in the cdb_pms table, ms The gfromid field has been indexed, but the folder field is not.At present, there are 7823 records in this table.Obviously, this will have a certain impact on the query.So create an index for it:
mysql> ALTER TABLE `cdb_pms` ADD INDEX (`folder`);
Query OK, 7823 rows affected (1.05 sec)
Records: 7823 Duplicates: 0 Warnings: 0
Continue to check:
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)
This SQL statement is operated on the most important data table cdb_threads.Since show processlist does not display all of this SQL statement, after comparing the source code of Discuz forum, the prototype of this SQL statement is located in common.inc.php The content of Line 283 is as follows:
$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");
Checked , The data table cdb_threads, does not build an index for the displayorder field.In the discuz forum, the displayorder field has participated in the comparison of Where clauses many times.So create an index for it:
mysql> ALTER TABLE `cdb_threads` ADD INDEX (`displayorder`);
Query OK, 110330 rows affected (2.36 sec)
Records: 110330 Duplicates: 0 Warnings: 0
At this time, the cpu has dropped slightly.
Continue to check and find that the following SQL statement of discuz also causes the load to increase.This statement is located in the rss.php programLine 142 of.
$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");
In this Order by clause, the dataline field in the cdb_threads table is used.This field is used to store the timestamp of unixtime.In the entire forum program, most of the time the data is sorted based on this field, and there is no index.So add:
mysql> ALTER TABLE `cdb_threads` ADD INDEX (`dateline `);
Query OK, 110330 rows affected (12.27 sec)
Records: 110330 Duplicates: 0 Warnings: 0
Find SQL statements that take up high CPU load.This is one Troublesome and boring things need to be eliminated and analyzed one by one.The rest of the work is done in the same way.After inspection, there are eight places that need to be added to the index.If you also encounter the situation that the discuz 5.5.0 forum causes the cpu to occupy 100%, you can directly copy the following statement., You can execute it under the mysql command line:
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`);
Note: "cdb_" is the default data table prefix of discuz forum.If your table name prefix is not "cdb_", you should change it to your corresponding table name.For example: my_threads, my_pms, etc.
A few days ago, a friend found me through this article, and said that he is running the latest discuz version, MySQL occupies 100% of the CPU, causing the system to hang up, restarting several times a day, it took more than a month Time has not been resolved, I hope I can help.After checking, among the most important tables in his forum, the cdb_members table currently has 62,000 records; the cdb_threads table has 110,000 records; the cdb_posts table has 17.4 million records; the total number of records in all data tables exceeds 2000.Ten thousand; the size of the database exceeds 1GB.After half a day of debugging, I finally completed the optimization of the discuz forum, so I recorded the solution in this article.
In March 2007, I found that the database structure design of the discuz forum has some negligence.There are many query clause comparisons, and no Index is established.The data table I checked at that time had only a few thousand records, so it didn't load the CPU much.Now this database table has tens of millions of records retrieved.It is conceivable that if the data table structure is not standardized and no index is provided, the time spent is a terrible number.For the importance of MySQL indexing, please refer to the description at the bottom of my article.
For debugging convenience, I downloaded the latest Dizcus! 5.5.0 forum program from the official website of dizcus.
I first checked the parameter configuration of my.ini, everything is normal.Enter the MySQL command line, call the show processlist statement, find the most heavily loaded SQL statement, combined with the source code of the Discuz forum, find that the following statement causes the CPU to rise:
The code is as follows:
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' |
+-----+------+----------------+---------+---------+------+------------+---------
Check the structure of the cdb_pms table:
The code is as follows:
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)
This sentence: WHERE msgfromid=11212 AND folder='outbox', we see that in the cdb_pms table, ms The gfromid field has been indexed, but the folder field is not.At present, there are 7823 records in this table.Obviously, this will have a certain impact on the query.So create an index for it:
The code is as follows:
mysql> ALTER TABLE `cdb_pms` ADD INDEX (`folder`);
Query OK, 7823 rows affected (1.05 sec)
Records: 7823 Duplicates: 0 Warnings: 0
Continue to check:
The code is as follows:
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)
This SQL statement is operated on the most important data table cdb_threads.Since show processlist does not display all of this SQL statement, after comparing the source code of Discuz forum, the prototype of this SQL statement is located in common.inc.php The content of Line 283 is as follows:
The code is as follows:
$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");
Checked , The data table cdb_threads, does not build an index for the displayorder field.In the discuz forum, the displayorder field has participated in the comparison of Where clauses many times.So create an index for it:
The code is as follows:
mysql> ALTER TABLE `cdb_threads` ADD INDEX (`displayorder`);
Query OK, 110330 rows affected (2.36 sec)
Records: 110330 Duplicates: 0 Warnings: 0
At this time, the cpu has dropped slightly.
Continue to check and find that the following SQL statement of discuz also causes the load to increase.This statement is located in the rss.php programLine 142 of.
The code is as follows:
$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");
In this Order by clause, the dataline field in the cdb_threads table is used.This field is used to store the timestamp of unixtime.In the entire forum program, most of the time the data is sorted based on this field, and there is no index.So add:
The code is as follows:
mysql> ALTER TABLE `cdb_threads` ADD INDEX (`dateline `);
Query OK, 110330 rows affected (12.27 sec)
Records: 110330 Duplicates: 0 Warnings: 0
Find SQL statements that take up high CPU load.This is one Troublesome and boring things need to be eliminated and analyzed one by one.The rest of the work is done in the same way.After inspection, there are eight places that need to be added to the index.If you also encounter the situation that the discuz 5.5.0 forum causes the cpu to occupy 100%, you can directly copy the following statement., You can execute it under the mysql command line:
The code is as follows:
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`);
Note: "cdb_" is the default data table prefix of discuz forum.If your table name prefix is not "cdb_", you should change it to your corresponding table name.For example: my_threads, my_pms, etc.
After completing the optimization of these structures, the CPU load of the entire system fluctuates around 10%~20%, and the problem is solved.
I’m very surprised.Designing a database structure is a basic skill for a database developer.The discuz forum is also a forum that has been developed for six or seven years.Why is the database structure designed so badly? I think there may be three reasons as follows:
- The negligence of database developers in design
- Defects left deliberately.When ordinary forums have no records of orders of magnitude, this problem will not be felt.When the amount of data increases (such as tens of millions), this problem emerges so as to provide users with Personalized services charge service fees.Haha, guess and guess the matter with the greatest malice.It's just a joke, don't take it seriously.:)
- Another possibility is that the user’s forum is upgraded from a lower version, the program is upgraded, but the data structure may not be updated accordingly
Attachment 1: Supplementary notes 2007-07-09
Checking the reffer of the website log today, I found that on the official forum of discuz, someone caused some controversy about this article: http://www.discuz.net/thread-673887-1-1.html.The administrators and administrators of discuz have the following remarks:
Quoted from cnteacher:
On the contrary, discuz's optimization measures and database indexes are designed according to large-scale forums.
TO first floor: The design of the database structure is carried out in accordance with the application of the program.Using any code and program other than the Discuz! standard version, or changing the standard data structure, may encounter various unpredictable problems.
Quoted from Tong Hu:
You can look at relatively large websites such as xxxxx and xxxx.There is no problem with this kind of website using the dz forum, indicating that there is no problem with the dz standard program.If the host said the situation, it is mostly caused by the server or installing some plug-ins.Of
Obviously the reason for pushing the problem to the plug-in is incorrect.A simple example: in the latest discuz 5.5.0 forumdisplay.php line 183, there is the following statement:
$query=$db->query("SELECT uid, groupid, username, invisible,
lastactivity, action FROM {$tablepre}sessions
WHERE $guestwhere fid='$fid' AND invisible=0");
The invisible here is not indexed.There are comments in this article that the session table is a memory table, and the speed will be very fast.The theory is so.However, in the show processlist, I observed that the above statement occupies a lot of CPU, so I also added index.The closed fields in cdb_threads have also participated in the where operation many times, and no index has been established.The statements for these operations are in discuz's own program.
Attachment 2: Supplementary Notes 2007-11-11
Since the publication of this note, in my comments on this article and in my contact messages, I have often received many of the following Two types of comments and emails: 1.Many technicians criticized me for nonsense, Dizcus forums do not need to be optimized or cannot be indexed randomly; 2.Many webmasters who use Dizcus asked me to "pretend to be naked in the snow" to solve their problems.The problem of CPU occupancy 100%.1.Regarding the debate on MySQL database optimization technology, my opinion is once again stated as follows:
- Technical disputes can be discussed.And my level is really only half a bottle of water, and I only know so much about the theoretical knowledge of the database.I am open-minded for the criticism of the cattle, and I am very grateful.However, the criticisms in the comments should not rise to personal attacks, otherwise, I will be in charge of my territory and will be deleted directly.
- Database optimization involves many aspects.It's useless to talk about theories, you have to rely on facts to speak.The optimization of an instance of a tens of millions of databases cannot explain the problem, and the optimization of two tens of millions of databases may not explain the problem, but I believe that three, four, and five can always explain the problem, as of 2007.11.09, I have helped my friends optimize five discuz forums with more than 10 million records.I think Facts speak louder than words: Before optimization, cpu is 100%; after optimization, cpu drops to about 30%~40%.Yes, doing ADD INDEX will increase the overhead of database INSERT/UPDATE, but don’t forget that the main operation of the forum is SELECT query.
2.Regarding the comments and emails about asking me to help solve database optimization, the reply is as follows:
- Database optimization.Different versions have different actual conditions.Optimizing a database can be as short as three or two hours, and as slow as half a day a day.Please understand the pressure of this middle-aged man to support his family.My energy is limited and it is impossible to help them one by one.
- For personal websites with no income, I can help in my spare time on Saturday and Sunday.Please contact me in advance.
- For websites with income, um, please be self-conscious, please contact me with the price, or directly arrange for a beautiful woman to invite me to dinner, otherwise it will not be discussed.:) Please don’t write a letter asking "How much do you cost to optimize our forum?" If this is not nutritious, just say "Help us Optimize the XXXX forum, is XXXX RMB OK?", I will do it if it feels appropriate.Everyone is very busy.My time is very valuable.If you want me to quote, I am afraid to scare you.
- Please contact me at http://www.xiaohui.com/support/.Don't leave a QQ number in the comments and ask me to add you, I don't always stare at the comments.
Appendix 3: Supplementary notes 2007-11-17: Analysis on the dz forum with the four grid plug-in on the homepage causing MySQL to take up a lot of CPU
Today’s mobile bus stationmaster (http://bbs.sj84.com) found me, his Discuz-based forum also has the problem of 100% CPU usage.The server was changed from Win 2003 to CentOS, memory 2G, CPU 1.86G, data: cdb_threads 40,000, cdb_posts 960,000, cdb_members 350,000, the index has been optimized according to my article above.It is said that this configuration is enough to run the forum, but the problem has not been solved.After debugging, dump the result of slow query to/usr/local/mysql/var/localhost-slow.log, run/usr/local/mysql/bin/mysqldumpslow/usr/local/mysql/var/localhost-Viewing slow.log, combined with the show processlist command, found that slow queries are concentrated in the following statements:
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, NHowever, searching the source code of the Dizcus forum, this line of code was not found.Suspected is the reason for the plug-in.After investigation, the forum has installed a four-frame plug-in on the homepage.This line of statement is located in include/toplist.php: Check this line of code carefully and find that there are manyMultiple performance or grammatical specifications:
- AND t.closed NOT LIKE'S': t.closed is a numeric field and should not be used for comparison in the form of LIKE'S'.
- ORDER BY t.views: t.views is not indexed in the original data table of dizcus.
- SELECT t.*: This way of writing is not recommended.If you want to select all the fields in a table, it is best to write them all according to the facts, for example: select t.aa, t.bb, t.cc, t.dd,...
- WHERE t.fid <>'S': t.fid is a numeric field and should not be written in the form of character comparison.This has little effect on performance and is a programming specification issue.
- ....
The other three sql statements in toplist.php all have these problems.If you want to optimize the MySQL structure for his sql statement, it will bring bad consequences; if you directly change his toplist.php program, if the webmaster upgrades toplist.php in the future, he is afraid of incompatibility problems.So I suggested that he simply turn off the four grid plug-in on the homepage.
After closing the four-frame plug-in on the homepage, the CPU dropped to about 18%, and the performance was very good.
If I were to write a four-frame program on the front page, I would not use this scheme.I would query the database every 15 minutes or 30 minutes, write the results into a TXT file or a temporary table, and then the program would read from it.The efficiency will be much higher.
Conclusion:
- If the forums with plug-ins are experiencing high CPU load, it is recommended to turn off the plug-ins and evaluate the performance.
- Install third-party plug-ins carefully.Don't intervene if it's okay.:)
Attachment 4: Supplementary notes 2008-06-10: In this article, the important thing is the analysis process, not the code for correction
Recently, several people have left messages in the comments and emailed me.When it comes to the 8 lines of ALTER TABLE code I gave in the article, on his dz forum where CPU 100% appears, it has no effect after using it..
My explanation is as follows: This code is not guaranteed to be universal under all versions of dz.Specific issues must be analyzed in detail.This code is the result of my analysis based on Dizcus! 5.5.0 version.Other versions are not guaranteed.
The focus of this article is not the code as the result, but the analysis process of how to get the result.Knowing the principle, you can analyze it yourself.
Tags
Technical otaku
Sought technology together
7 Comments