Tens recorded Discuz forum results in optimized notes for MySQL CPU 100%

Tens record of Discuz forum led to MySQL CPU 100% optimization Note In March 2007, I wrote an article on solving a MySQL server process CPU usage 100% Technical Note (http://www.xiaohui.com/weekly/ 20070307.htm), when it comes to resolving a MySQL database access that has 600,000 records, causes the MySQL CPU to occupy 100% . After solving the problem completion optimization (optimize), I found that the Discuz forum also had this problem, when I mentioned it a little bit:

It was

found that this host ran several Discuz forum programs, and there were several tables in the Discuz forum. As a result, the CPU consumption is lowered again.

A few days ago, a friend through this article found me, said he is running the latest version of Discuz, MySQL occupied CPU 100%, causing the system to suspend death, every day to restart several times, took one months of time has not been resolved, I hope I help. After examining the most important tables of his this forum, the present Cdb_members table, with records 62,000; Cdb_threads table, with records 110,000 ; cdb_posts table, with records 17.4 million ; Records of all data tables add up to more than 20 million , and the database size exceeds 1GB. After a half-day debugging, finally completed the discuz Forum optimization , so that its resolution is recorded in this article http://www.xiaohui.com/dev/server/20070701- The discuz-mysql-cpu-100-optimize.htm.

March 2007 I found that the database structure design of DISCUZ forum has some negligence, there are many query clauses in the condition comparison, have not established index index . The data sheet I checked at that time was only thousands of records, so the CPU load was small. Now this database table, tens of millions of records retrieved, it can be imagined that if the data table structure design is not standardized, do not provide an index, the time spent is a scary number. For the importance of MySQL indexing, see the note at the bottom of my article: http://www.xiaohui.com/weekly/20070307.htm

For debugging convenience, I downloaded the latest dizcus! from Dizcus's official website. 5.5.0 Forum Program.

I first checked the My.ini parameter configuration, everything is OK. Go to MySQL command line, call the show Processlist statement, find the heaviest load SQL statement , combined with the Discuz Forum source code, found that the following statements caused the CPU to rise:

Mysql> Show processlist;+-----+------+----------------+---------+---------+------+------------+-------------- ------------------------------------------------------------+| Id  | User | Host           | db      | Command | Time | State      | Info                                                                 |+-----+------+----------------+---------+---------+------+------------+------------------------------ --------------------------------------------+| 363 | Root | localhost:1393 | History | Query   |    0 | Statistics | SELECT COUNT (*) 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 (Ten) 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 (Ten) unsigned |     NO | |                0 | || message | Text |     NO |         |                | || Delstatus | tinyint (1) unsigned |     NO | |                0 | |+-----------+------------------------+------+-----+---------+----------------+10 rows in Set (0.00 sec)

This statement: where msgfromid=11212 and folder= ' Outbox ', we see that in the Cdb_pms table, the Msgfromid field has been indexed, but the folder field does not. There are currently 7,823 records in this table. Obviously, this can have a certain effect on the query. The index is then built:

mysql> ALTER TABLE ' Cdb_pms ' ADD INDEX (' folder '); Query OK, 7823 rows affected (1.05 sec) records:7823  duplicates:0  warnings:0

Continue check:

  mysql> Show processlist;+------+------+----------------+---------+---------+------+------------+---- --------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+------+------+----------------+---------+---------+------+------------+------------------------------- -----------------------------------------------------------------------+              || 1583 | Root | localhost:2616 | History |    Query | 0 | Statistics | Selectt.tid, t.closed, f.*, ff.*, F.fid as fid from Cdb_threads T INNER JO In Cdb_forums F |+------+------+----------------+---------+---------+------+------------+------------------------ ------------------------------------------------------------------------------+1 rows in Set (0.00 sec)  

This SQL statement is for the most important data table cdb_threads operation, because show processlist does not have this SQL statement full display, by contrast Discuz Forum source code, this SQL statement prototype is located in the common.inc.php Line 283, which reads 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 {$TABLEPR E}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 data table cdb_threads, and there is no index for the Displayorder field. In the Discuz forum, the Displayorder field participates more than once in the Where clause comparison. The index is then built:

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 been slightly reduced by a fraction.

Continuing the check and discovering the following Discuz SQL statement also leads to an increase in load, which is located in line 142th of the rss.php program.

    $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.displayorde R>=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 the unixtime, and most of the time the data is sorted based on this field, and it is not indexed at all. Then add:

mysql> ALTER TABLE ' cdb_threads ' ADD INDEX (' Dateline '); Query OK, 110330 rows Affected (12.27 sec) records:110330  duplicates:0  warnings:0

Finding the SQL statement that consumes high CPU and negative tea is a troublesome and boring thing, which requires a piece of elimination and analysis. Behind the work, are and so on, after inspection, a total of eight places, need to increase the index, if you also met the Discuz 5.5.0 forum led to CPU consumption of 100%, you can directly copy the following statements in the past, the command line in MySQL to execute:

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 table name. For example: My_threads, My_pms and so on.

After the optimization of these structures, the CPU load of the whole system is 10%~20%, and the problem is solved.

I am surprised that the design of database structure, is a database developer's basic skills, Discuz forum is also a development of six or seven years of the Forum, why the database structure is so badly designed? I think maybe there are three reasons:

• The negligence of the design of the database developers
• Intentionally left defects, when the general forum does not have an order of magnitude records, will not feel this problem, when the amount of data increased (such as tens), this problem emergent, in order to provide personalized service for users to charge service charges. Oh, estimate and the greatest malice to guess this matter, joke just, do not take 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

Attached 1: Supplemental notes 2007-07-09

Today to check the site log reffer, found in Discuz's official forum, someone in this article caused some controversy: http://www.discuz.net/thread-673887-1-1.html. DISCUZ Administrators and administrators have the following comments:

reference from Cnteacher:

On the contrary, Discuz's optimization measures and database indexing are designed in large-scale forums.

To the first floor: The database structure is designed to be used in accordance with the program, using any non-discuz! Code and programs other than the standard version, or changes to standard data structures, may encounter unpredictable problems.

reference from Dong :

You can see XXXXX, XXXX and other large-scale website, this website use DZ Forum have no problem, show DZ standard procedure is no problem, appear landlord said situation, mostly belong to the server or install some plug-in caused by

Obviously the reason to push the problem to the plugin is incorrect. As a simple example: In the latest discuz 5.5.0 forumdisplay.php 183th Line, there is the following statement:

$query = $db->query ("Select UID, GroupID, username, invisible,  lastactivity, action from {$tablepre}sessions   Invisible= 0 ");

The invisible here are not indexed. The comments in this article suggest that the session table is a memory table and will be fast. Theory is so. But in show processlist, I observed that the above statement took up a lot of CPU, so I added index as well. Fields such as closed in Cdb_threads are also involved in the where operation several times, and no index is established. The statements of these operations are discuz in their own programs.

Attached 2: Supplemental Notes 2007-11-11

Since this note was published, in my comments on this article, as well as my contact information, I have often received 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 can not be built index; second, many use Dizcus webmaster find Me " The ice and snow and the naked kneel beg "to solve their CPU consumption 100% problem.

First, on the MySQL database optimization technology controversy, my view is again stated as follows:

1. The technical argument is open to discussion. And my level is really just half a bottle of water, the theoretical knowledge of the database only know so little, cattle and cattle criticism, I humbly answer, thank you very much. However, the criticism in the comments do not rise to personal attacks, otherwise, my site I am the master, directly deleted.

2. Database optimization, to be involved in many aspects. Intervention theory is useless and depends on the facts. An instance of a Tens database optimization does not explain the problem, two 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 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% or so. Yes, do add INDEX will increase the cost of database insert/update, but do not forget the main operation of the forum, is a SELECT query.

Second, about me help to solve the database optimization of comments and Mail, reply as follows:

1. Database optimization, different versions have different realities, optimize a database, short 32 hours, slow two or three days. My energy is limited, it is impossible to get a bunch.
2. I can help in my spare time at the end of the month for personal websites that have no income. Please contact me in advance.
3. for the income of the website, please bring the price to contact me, or directly arrange for the beauty to invite me to dinner, otherwise avoid talking. :) Please don't write a letter asking " How much do you charge for optimizing our forum?" "If this is not nutritious, please say directly" help us to optimize the XXXX website or forum, XXXX RMB can not? "I think it would be appropriate to reply to you." Everyone is very busy.
4. please contact me via http://www.xiaohui.com/support/mail . Don't leave a QQ number in the comments and ask me to add you, I will not always read the comments.

Attachment 3: Additional Notes 2007-11-17: about the DZ Forum with home Four plugins causes MySQL to consume a lot of CPU analysis

Today the Mobile bus webmaster (http://bbs.sj84.com) found me, his Discuz-based forum, there is also a CPU consumption of 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 for indexing as I said in the previous article. This configuration is supposed to be sufficient to run the forum, but the problem has not been resolved.

After debugging, the results of 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, combined with the show Processlist command, discovers 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, the Search Dizcus Forum source code, and did not find this line. Suspicion is the cause of the plugin. After the review, the forum installed the first page four of the plug-in, this line of statements in include/toplist.php: Carefully examine this line of code, found that there are many performance or syntax problems:

1. and t.closed not like ' s ': t.closed is a numeric field and should not be used in the form of like ' s '.
2. ORDER by T.views: T.views is not indexed in the original data table of Dizcus.
3. 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 out, for example: Select T.aa, T.bb, t.cc, T.dd, ...
4. 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 programming norm.
5. ....

These issues exist for the other three SQL statements in toplist.php. If you want to optimize the MySQL structure for his SQL statement, it will bring bad consequences, if directly change his toplist.php program, if the webmaster later upgrade toplist.php and afraid to bring incompatibility problems. So I suggested that he simply close the home four grid plugin.

After closing the first four grid plug-in, the CPU has dropped to about 18% shocks and performed very well.

If I am to write the four-grid program, I will not use this scheme, I will use the time 15 minutes or 30 minutes to query the database, the results are written to the TXT file or temporary table, and then the program read from it, the efficiency is much higher.

Conclusion:

1. If a plugin-loaded forum encounters a high CPU load, it is recommended to turn off the plug-in to evaluate performance.
2. Install third-party plugins with caution. No, don't mess around. :)

Attachment 4: Supplementary Note 2008-06-10: This article is about the analysis process, not the code that made the correction

Recently, a few comments in the comment, as well as send me an e-mail, said that I gave in the text of the 8 ALTER TABLE code, in his appearance CPU 100% dz Forum, used after no effect.

My explanation is as follows: This code is not guaranteed to be common in all versions of DZ. Specific problems, to be specific analysis. This code, I was in dizcus! 5.5.0 version of the basic under the analysis of the results of the correction. Other versions, not guaranteed.

The focus of this article is not as a result of this code, but how to derive the analytical process of the result. Knowing the principle, you can analyze it as well as yourself.

Tens recorded Discuz forum results in optimized notes for MySQL CPU 100%