Dedecms server load performance optimization instance. Three moves make your dedecms faster than 10 times. Page 1/2

Document directory

• Problem proposal
• Problem Analysis
• Solve the problem
• Problem proposal
• Problem Analysis

Or is the performance seriously degraded due to the big data of a table? Do we have to store multiple tables to solve the problem? In the following example, we will analyze and optimize the Data Management Performance of dedecms. Do not make mysql a scapegoat.
The test data is accidentally obtained from the enterprise yellow pages. The data volume is nearly 0.9 million and is completely real data. The test program is dedecms4.0. Why not use dedecms5.1? This is because we have made a lot of changes to dedecms for optimization. If dedecms5.1 is used, we are afraid to receive a court summons ......, In addition, the following optimization methods can be used in dedecms5.1. Please complete the optimization based on understanding its principles.

Before optimization, we test that there are three major regular operations that affect the management performance when the dede data volume is large. They are document generation, list page generation, and column listing, we will optimize these three aspects.
The basic information of the test data is as follows:

The number of documents is close to 0.9 million

Each topic contains nearly 30 thousand pieces of data

1. Improvement of document generation speed

Similar to our previous evaluation results, dedecms's document generation speed is terrible. You can use the crop template (article_article.htm) to generate 20 pages () in an average of nearly 30 seconds. Based on this speed, 0.9 million of the data is generated until the hair is white. So where is the problem?

Optimized the generation speed of a single topic document

Problem Analysis

Remove the table index first, because the dede database has created indexes for the main fields in the Data master table (dede_archives. Then, the extraction efficiency of the main content is eliminated. Because the document data on the page is read during the page generation process, you need to obtain the data content with unique id values from the select statement in the master table and the appendix each time, the efficiency of this SQL statement is determined by running the SQL statement test in mysql. The execution time is very short, so this is not the biggest bottleneck.

Finally, during the page generation process, we found that the Program executed several master table (dede_archives) queries and retrieved operations that meet a set of complex query conditions. The query efficiency was very low, it affects efficiency! Through debugging tracking, we have located the key to the problem. The culprit is the arclist tag in the template. The Arclist label is a favorite label for many people. Because it is flexible, it can retrieve popular, up-to-date, and related articles from data, however, the arclist tag queries the primary table with a large search condition each time. In fact, if the same template is used to generate a large number of articles at a time, arclist queries the database simply and mechanically. Therefore, it is definitely not worthwhile to spend a lot of time. Next, we will provide suggestions for solving the problem.

Solve the problem

Solution 1: Remove the arclist tag from the final page template or use it as little as possible. Although this method can greatly improve the efficiency, it is tantamount to sprinkling water to let the children go. It is not recommended for websites that attempt to increase pv access.

Solution 2: Create an arclist cache to store the data generated by arclist in a temporary directory or cache. During the document generation process, check whether the cache is updated. If no update is available, use the cached data directly. This method does not need to change the template, but it also has some effect on improving the generation efficiency. However, due to the large changes to the program, we should consider using this method as appropriate.

Solution 3: It is also the solution recommended by the group, that is, to fully explore the existing dedecms functions and greatly improve the efficiency without changing the program as much as possible. The specific method is to use the freelist (Free List generation) function to generate a list page of popular articles, latest articles, related articles, and other content in advance, and then use the include tag provided by dedecms to directly introduce the document page. Tag format: {dede: include file = 'list page file name' ismake = 'no '/}. This solution only adds some operation steps and does not change any program. The performance improvement is also very obvious. That is, we used this method to optimize the generation speed. It took only 50 seconds to generate more than 1500 pages of articles to achieve the goal. This solution is used with caution due to the addition of operation steps.

The document generation speed of a single topic after optimization

2. Raise the issue of improving the generation speed of the List page

Next, we continued to test the generation of the notebook page. This time, we learned how to delete the arclist tag in the template (list_article.htm) Before testing, but the generation effect is still very unsatisfactory. For example, the generation time of each list page is close to 20 seconds (we modified the PAGE result output prompt to make it easier for you to see the generation time of each list page), calculated based on 50 data entries per page, it takes more than three hours to generate 30 thousand data for a single topic theoretically ...... Language-free ing. The list content uses the list tag, which is a bit similar to that of arclist. Therefore, we cannot solve the problem by following the above steps.

Optimized the generation speed of the previous list page

Problem Analysis

Because the target is locked in the list tag, the test process is simple. We directly used the list query statement in dedecms for optimization analysis, and soon found the problem. We tested the SQL query statement in list. The following code is the SQL statement used by list to query the corresponding conditions in the database. The execution time is about 15 seconds, which is inefficient.

Select arc. ID, arc. title, arc. iscommend, arc. color, arc. typeid, arc. ismake, arc. money, arc. description, arc. title, arc. memberid, arc. writer, arc. postnum, arc. lastpost, arc. pubdate, arc. senddate, arc. arcrank, arc. click, arc. litpic, tp. typedir, tp. typename, tp. isdefault, tp. defaultname, tp. namerule, tp. namerule2, tp. ispart, tp. moresite, tp. siteurl from dede_archives arc left join dede_arctype tp on arc. typeid = tp. ID left join qiye_addonarticle on arc. ID = qiye_addonarticle.aid where arc. arcrank>-1 And (arc. typeid = '1') or arc. typeid2 = '1') order by arc. sortrank desc limit 0, 50

We have noticed that the where clause in this SQL statement uses and or to determine multiple conditions. experience tells us that if the in or statement is used in the query clause, it will cause a full table scan, in this way, the index efficiency cannot be reflected. We simplified the judgment conditions of the where clause for testing. The results showed that the query efficiency was significantly improved after the or clause was deleted. The query results were obtained in less than one second. This is the key to the problem.