It was found that the host ran several Discuz forum programs, and several tables of the Discuz forum also had this problem. So smoothly together to solve, CPU occupancy down again.
A few days ago, a friend through this article found me, said he is running the latest version of the Discuz, MySQL occupies 100% of the CPU, leading to the system of suspended animation, every day to restart several times, spent one months of time has not been resolved, I hope I can help. After examining his most important table in this forum, the current Cdb_members table, with records 62,000; cdb_threads tables, records 110,000; cdb_posts tables, records 17.4 million, records of all data tables added up to over 20 million ; The size of the database exceeds 1GB. After half a day of debugging, finally completed the Discuz forum optimization, so it was resolved through the record in this article.
March 2007 I found that the database structure design of the Discuz forum has some negligence, there are many query clauses of the condition comparison, have not established index index. At that time I checked the data table, records only thousands of, so the CPU load is not large. Now this database table, tens of millions of records retrieval, you can imagine, if the data table structure design is not standardized, did not provide an index, the time spent is a scary figure. For information on the importance of MySQL indexing, see the instructions at the bottom of my article
For debugging convenience, I downloaded its latest dizcus! from Dizcus's website. 5.5.0 Forum Program.
I first checked the My.ini parameter configuration, everything is OK. Enter the MySQL command line, call show processlist statements, look for the heaviest load of SQL statements, combined with the Discuz Forum source code, found that the following statements caused the CPU to rise:
Copy Code code 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:
Copy Code code as follows:
Mysql> show columns from Cdb_pms;
+-----------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------------+------+-----+---------+----------------+
| PMID | int (a) 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 (a) unsigned | NO | | 0 | |
| message | Text | NO | | | |
| Delstatus | tinyint (1) unsigned | NO | | 0 | |
+-----------+------------------------+------+-----+---------+----------------+
Rows in Set (0.00 sec)
This statement: WHERE msgfromid=11212 and folder= ' Outbox ', we see that the Msgfromid field has been indexed in the Cdb_pms table, but the folder field is not. At present, the table has 7,823 records. Obviously, this can have a certain impact on the query. The index is then indexed:
Copy Code code 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:
Copy Code code 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 for the most important data table cdb_threads, because show processlist did not fully display this SQL statement, by contrast Discuz Forum source code, this SQL statement prototype is located in common.inc.php Line 283, which reads as follows:
Copy Code code as follows:
$query = $db->query ("Select T.tid, T.closed,".) Defined (' Sql_add_thread ')?
Sql_add_thread: "f.*, ff.* $accessadd 1 $modadd 1, 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 $accessadd 2 $modadd 2
WHERE t.tid= ' $tid '. ($auditstatuson? ":" and T.displayorder>=0 ")." LIMIT 1 ");
After checking, the datasheet is cdb_threads and no index is established for the Displayorder field. In the Discuz forum, the Displayorder field is more than once involved in a Where clause comparison. The index is then indexed:
Copy Code code 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 point the CPU has dropped a slight part.
Continue checking to see that the following Discuz SQL statement also causes the load to increase, which is in line 142th of the rss.php program.
Copy Code code 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 unixtime timestamp, in the entire forum program, most of the time the data is sorted based on this field, unexpectedly did not establish an index. And then add:
Copy Code code 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 CPU High Negative tea SQL statement, is a troublesome and boring thing, need a piece of exclusion, analysis. Behind the work, and so on, after inspection, a total of eight places to find, need to add index, if you also encounter the Discuz 5.5.0 Forum led to CPU consumption of 100%, you can directly copy the following statements in the past, in the MySQL command line execution can be:
Copy Code code 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 for the Discuz forum. If your table name prefix is not "cdb_", it should be changed to your corresponding table name. For example: My_threads, My_pms and so on.
After completing the optimization of these structures, the CPU load of the whole system is 10%~20% around and the problem is solved.
I am very strange, design database structure, is a database developer basic skills, Discuz forum is also a development has six or seven years of forum, why the database structure design so bad? I think there are three reasons for this:
- The negligence of the database developer when designing itself
- Intentionally left the flaw, when the general forum does not have the record of the order of magnitude, do not feel this problem, when the amount of data (such as TENS), this problem emergent, in order to provide personalized services for users to charge a service charge. Oh, estimate and with the greatest malice to guess this thing, joke just, don't take it seriously. :)
- Another possibility is that the user's forum is upgraded from a lower version, the program is up, but the data structure may not be updated accordingly
Appendix 1: Supplementary Notes 2007-07-09
Looking at the Reffer of the website log today, it was found that in Discuz's official forum, some controversy was raised about this article: http://www.discuz.net/thread-673887-1-1.html. DISCUZ Administrators and administrators have the following comments:
reference from Cnteacher:
On the contrary, the Discuz optimization measures and the index of the database are designed according to a large scale forum.
To the first floor: The database structure is designed according to the procedure, using any non-discuz! Code and programs other than standard versions, or changing standard data structures, may encounter unpredictable problems.
reference from Dong :
You can look at xxxxx, XXXX, such as the relatively large web site, this site use DZ Forum no problem, that DZ standard procedure is no problem, the landlord said the situation, mostly belong to the server or install some plug-ins caused by
Obviously the reason for pushing the problem to the plugin is incorrect. For a simple example: In the latest discuz 5.5.0 forumdisplay.php line 183th, there are the following statements:
$query = $db->query ("Select UID, GroupID, username, invisible,
lastactivity, action from {$tablepre}sessions
Invisible= 0 ");
The invisible here is not indexed. There are comments in this article that the session table is a memory table that is fast. Theory is so. But in show processlist, I observed that the above statement took up a lot of CPU, so I added the index to it. Fields such as closed in Cdb_threads are also involved in the where operation and are not indexed. The statements of these operations are discuz in their own programs.
Appendix 2: Supplementary Notes 2007-11-11
Since the publication of this note, in my comments on this article, as well as my contact messages, I often receive many of the following two types of comments and emails: first, many technical staff criticized me nonsense, Dizcus forum does not need to do optimization or not to build index; second, many use Dizcus webmaster find Me " Snow and ice naked kneeling begging "to solve their CPU occupancy 100% problem."
First, on the MySQL database optimization technology debate, my point of view is again stated as follows:
- The technical argument is open to discussion. And my level is really only half a bottle of water, the theoretical knowledge of the database only understand this point, cattle and cattle criticism, I humbly connect heart, thank you very much. However, criticism in the comments do not rise to personal attacks, otherwise, my site I am the main, directly deleted.
- Database optimization, there are many aspects to be involved. Intervention theory is no use, it depends on the fact to speak. An example of a TENS database optimization can not explain the problem, two of Tens database optimization may not explain the problem, but I believe that three, four, five can always explain the problem,-as of 2007.11.09, I have helped friends to optimize the number of five records more than 10 million of the Discuz Forum. I think the facts speak louder than words: Before optimization, the CPU is 100%; After optimization, the CPU drops to 30%~40%. Yes, doing add INDEX will increase the cost of database insert/update, but don't forget that the main action of the forum is SELECT query.
Second, for me to help solve the database optimization comments and emails, replies are as follows:
- Database optimization, different versions of the actual situation, the optimization of a database, short 32 hours, half a day slow. Please understand the pressure of the elderly man to support the family, my energy is limited, it is impossible to a bunch of.
- for a personal website without income, I can help in the spare time of Saturday Sunday. Please contact me beforehand.
- for the income of the website, um, consciously point, please bring the price to contact me, or directly arrange the beauty to invite me to eat, otherwise avoid talking. :) Please don't write to ask " how much do you charge to optimize our forum?" "This is not nutrition, but directly said" Help us optimize XXXX forum, XXXX RMB can not? "I feel fit to do it." Everyone is very busy, my time is very valuable, you want me to quote, I am afraid to scare you.
- please contact me through the 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: The DZ Forum on the home four grid plugin led to MySQL taking up a lot of CPU analysis
Today, Mobile bus webmaster (http://bbs.sj84.com) To find me, his Discuz forum, there are also CPU occupied 100% of the problem, the server from Win 2003 to CentOS, memory 2G, CPU 1.86G, data: cdb_t Hreads 40,000, cdb_posts 960,000, Cdb_members 350,000, has been optimized by the index described in my previous article. Ordinarily this configuration is sufficient to run the forum, but the problem has not been solved.
After debugging, the results of the slow query dump to/usr/local/mysql/var/localhost-slow.log, run/usr/local/mysql/bin/mysqldumpslow/usr/local/mysql/ Var/localhost-slow.log view, combining the show processlist command, finds that the slow query is set in the following statement:
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
However, search Dizcus Forum source code, and did not find this line. Suspicion is the reason for the plugin. After investigation, the forum installed the first four grid of Plug-ins, this line of statements in include/toplist.php: Careful examination of this line of code, found that there are many problems with performance or grammatical specifications:
- and t.closed not like ': t.closed is a numeric field and should not be used as a comparison in the form of ' s '.
- ORDER by T.views: t.views is not indexed in the Dizcus raw data table.
- SELECT t.*: This notation is not recommended. If you want to select all the fields within a table, it is best to write them all, 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 a character comparison. This has little impact on performance and is a matter of programming specifications.
- ....
These problems exist in the other three SQL statements that are toplist.php. If you want to target his SQL statements to optimize the MySQL structure, will bring undesirable consequences, if the direct change to his toplist.php program, if the webmaster later upgrade toplist.php and fear of bringing incompatible problems. So I suggested that he simply close the first four grid plug-ins.
Close the first four grid plug-in, the CPU dropped to 18% or so concussion, performance is very good.
If I write the first four of the program, I will not use this scheme, I will use a timed 15 minutes or 30 minutes to query the database, the results are written to TXT file or temporary table, and then the program read from, the efficiency will be much higher.
Conclusion:
- If the plugin installed forum encountered CPU high load, it is recommended to turn off the plug-in to evaluate performance.
- Carefully install the third party plug-ins. It's okay, don't stick it in. :)
Appendix 4: Supplementary Notes 2008-06-10: In this article, it is important to analyze the process, not the code for the correction.
Several recent comments and emails to me say that the 8-line ALTER TABLE code I gave in the text is not working after his 100% DZ forum. "
My explanation is as follows: This piece of code is not guaranteed to be generic in all versions of DZ. Specific issues, to be specific analysis. This code, it's me in dizcus!. 5.5.0 version of the basic analysis of the results obtained by the correction. The other version, not sure.
The main point of this article is not as a result of this code, but how to get the results of the analysis process. Knowing the principle, you can analyze it yourself.
