Solve the problem
For list query, the purpose of arc.typeid2='1' is to find the second category to which an article belongs. In fact, this function is rarely used in most cases, because we can use tags to complete the ownership of multiple different categories of an article. Therefore, we modify the file inc_arclist_view.php, directly delete the conditional judgment of typeid2 in the query statement, and improve the query execution function in pub_db_mysql.php (mainly used to improve the efficiency of SQL statement execution, and has little impact on the final result). The final test results obtained are as follows. The average generation time of each list page is less than 1 second, and 30,000 data is only less than 10 minutes to generate 600 list pages, and the optimization goal is achieved.
Optimized list page generation speed
3. Improve document management efficiency
Question raised
With the data volume of 900,000, dedecms opens the column article list, especially when opening all file lists to attempt to manage the article, the speed is simply unbearable. We clicked on all document lists and waited patiently for nearly 2 minutes before the article list page arrived late. What exactly restricts the speed of article listing?
Use all file list data to load completion time before optimization
Analyze the problem
The programs that control the display of document lists are dede/content_list.php and include/pub_datalist_dm.php, where content_list.php is the main control program, which completes the parameter passing of the list and the template display, while pub_datalist_dm.php is used to complete data query and paging operations. Through program debugging, we found that the problem is that the pub_datalist_dm.php program takes a lot of time to count all data, and querying list data using limit is not a performance bottleneck. The key codes that affect efficiency are:
$this->dsql->Query();
$this->totalResult = $this->dsql->GetTotalRow();
The method of counting the total number of data used here is to pass a complex query statement through content_list.php, then execute the query and save the results to the data set, and finally count the number of rows in the result set through the mysql_num_rows function. I never thought that dedecms actually took the farthest road to complete the simplest operation. No wonder the efficiency is terrible. Therefore, as long as we use the most common count method to count data, the execution efficiency can be greatly improved.
Solve the problem
First, we modified the program content_list.php and constructed the SQL statement used to save statistics. The code is as follows:
……
$conutquery=”
select count(*) as totalResult
from #@__archives
left join #@__arctype on #@__arctype.ID=#@__archives.typeid
left join #@__channeltype on #@__channeltype.ID=#@__archives.channel
left join #@__admin on #@__admin.ID=#@__archives.adminID
$whereSql
order by #@__archives.{$orderby} desc”;
……
Then pass this parameter and query $query parameter to pub_datalist_dm.php for processing.
$dlist->SetSource($query,$conutquery);
Then replace the code in pub_datalist_dm.php that gets the total number of data statistics with the $conutquery parameter:
……
//Statistical Optimization
$row = $this->dsql->GetOneSimple($this->countSql);
$this->totalResult = $row['totalResult'];
……
Among them, GetOneSimple is a newly constructed function used to execute SQL statements. I put it in the pub_db_mysql.php class. Its main purpose is to return the SQL statement execution results. $this->countSql is the SQL code used for statistics passed by the previous program, and $this->totalResult is used to record data statistics results. After the program is modified, satisfactory results are achieved.
After optimization, use all file list data to load completion time
Summarize
Through the above steps of optimization, our program background can now easily deal with the management and maintenance of 900,000 data. It turns out that the bottleneck of the load performance of dedecms is not mysql, server or operating system platform, etc. If unoptimized programs are placed on the Oracle database, use a higher-level server, and use a freebsd operating system, the performance will be unsatisfactory. Details determine success or failure. It seems that dedecms must work hard on program tuning and performance optimization. For more detailed solutions, please join us.
Below are the additions from other netizens
The previous article introduces the method of achieving perfect paging of DedeCMS column list pages, which avoids the impact of most duplicate column titles on search engines and is more beneficial to SEO. Today, let’s share a method for optimizing the performance of DedeCMS data load.
I have been in contact with Dream Weaver for more than three years, and I love and hate it. Its template is simple and easy to use, tag calls are more flexible, and secondary development is also very convenient. However, when the site data becomes huge (more than 300,000 pieces), the background will become extremely slow, and it is also difficult to generate HTML. It is no exaggeration to say that the hair is white. This makes me doubt the performance of DedeCMS data load?
After reviewing the relevant information and combining the actual situation of our own site, we have summarized a good set of DedeCMS data load performance optimization solutions. Without talking nonsense, just get to the point.
1) Data subtable storage to reduce the pressure of data single table
Since the DreamWork V5 version, DedeCMS has started to store tables to improve system load performance, which has indeed alleviated data pressure to a certain extent. Now the latest DedeCMS V5.7 version has been released. According to the official introduction, V5.7 has adjusted the cache processing and can handle data within 500,000. As for the authenticity, there is no problem. If the official statement is true, it is indeed a good thing for small and medium-sized webmasters, and there is no need to worry too much about normal data within a million level.
How to operate subtable storage?
If you are just a small site such as an individual or enterprise, and the amount of data is only tens of thousands, you don’t have to consider subtable storage at all. DedeCMS is completely competent. The operation of sub-table is very simple. You just need to go directly to the background, create a new model, and then set a column to correspond to a model. I personally recommend that a large channel column and sub-column correspond to a model. This requires planning based on the data that may be stored in your column and considering a more practical sub-table plan.
2) Modify system parameters arclist tag alternative optimization
In the DedeCMS V5 version, the official has actually made great efforts to optimize and introduced a caching mechanism. In fact, the culprit that affects the speed of HTML generation is the arclist tag in the template. Many webmasters like to use arclist tags to call the latest, popular, recommended, headlines and other article lists. However, arclist tags always carry a lot of conditions to query the main table, and may also be associated with additional tables. For a large number of articles at one time, it is just a repetition of arclist tags to query the database repeatedly, which will naturally take a lot of time. Now in the new version of DedeCMS, when generating HTML, the arclist tag will directly call the cached data, saving the arclist tags the time to repeatedly query the database, which makes the above work easier, and it is inevitable that the generation speed will be improved. You only use it in the system parameters -> performance options to find the arclist tag call cache (cfg_index_cache) (0 is not enabled, how many seconds is the value greater than 0), and adjust the cache call time according to your actual needs.
In fact, there is another solution, which is a bit troublesome, but it is very significant to improve performance. Although arclist tag call cache has increased the HTML generation speed to a certain extent, it still needs to judge the arclist cache. If this part of the time can be saved, will it be faster? The answer is affirmative and double negative. We can use the freelist function to create the latest, popular, recommended, headlines and other article list pages, and then use the include tag to directly introduce it into the template. The tag format is: {dede:include file='Article list page file name' ismake='no'/}. If your webmaster data is huge and the server hardware configuration is average, why not try it?
In addition, the default keyword replacement function (cfg_keyword_replace) in the system parameters-core settings is enabled. If the article is collected or closed, many keywords are meaningless, and there may even be garbled code to cause generation errors. Turning off this function is helpful to improve system performance.
3) Database table index optimization, performance improvement
Why optimize the DedeCMS database table index? It's very simple. In Mysql, indexing is undoubtedly the most effective tool to speed up queries. A reasonable index combination will greatly improve your query efficiency and system performance. Back to the point, you can manage your database through phpmyadmin or a software called Navicat for MySQL (recommended).
By analyzing the DEDECMS data table information, it is not difficult to find that all article data are stored in dede_archives and dede_arctiny, as well as the corresponding dede_addonarticle additional tables. When generating HTML, SQL queries mainly revolve around these three tables. Personally, I believe that all fields to be sorted and fields and document IDs and query conditions must be indexed. If one is not established, it will seriously affect the query efficiency of MySQL and eventually lead to slowing generation speed. The DEDECMS data table index creation method is as follows:
a) dede_archives is the main table of the article, storing information such as the article title, keywords, description, publication time, etc. The table size of 100,000 data may be around 30MB, which is also the focus of our optimization. The index fields you need to create are id, channel, pubdate, sortrank, ismake, typeid, mainindex, and lastpost; among them, the two combination indexes such as the system's default mainindex and lastpost, I personally think that the existence of them is not very meaningful, so you can delete them and consider them yourself. It should be noted that the click field is the number of clicks of the document. The frequency of update of this field will bring some pressure to system maintenance after indexing is established. In addition, some people say that frequent updates of indexing will easily lead to database corruption and cannot be verified. I personally recommend that the click field be retained and not indexed.
b) dede_arctiny, this table is relatively small, the size of the 100,000 data is less than 5MB. It is recommended not to create an index. You can delete the included ones, or only the sortrank index is retained.
c) dede_addonarticle is an article additional table, mainly used to store article content and is not considered for indexing.
After the above index is successfully established, test your HTML generation speed. Will it make you feel energetic?
4) Build a high concurrent web server that is ten times better than Apache. Nginx + PHP (FastCGI)
The importance of a web server does not need to be said and has a direct impact on improving website performance. In PHP development, the most commonly used environment is LAMP:Linux+apache+mysql+php. There is WAMP:Windows+apache/iis+mysql+php under Windows. My WEB site is also developed in this environment. Nginx + PHP (FastCGI) is undoubtedly your best choice. It can be installed on both Windows and Linux, but Nginx performance under Windows is far inferior to Linux.
The operation of the DedeCMS system depends on the PHP+MYSQL environment, so how important is a web server that runs fast and consumes little resources to improve system performance. If conditions permit, it is recommended to use Nginx + PHP (FastCGI) WEB server environments.
The above is the optimization solution for DedeCMS data load performance. It is aimed at webmasters with independent WEB servers or control permissions. As for virtual hosts, it is still very difficult to achieve this speed, but it can also be used as a reference for DedeCMS performance optimization, and you have thought about it yourself. Of course, if there is a better way to improve the performance of DedeCMS data load, I hope to share it. In fact, under normal circumstances (excluding collection), the data volume of the site is limited, and 200,000 is a great thing, right? I think the above DedeCMS optimization solution is enough to solve it. When it really reaches the millions or tens of millions of data, it is not something that ordinary webmasters need to consider.