Evolution of websites and databases (III ))

Source: Internet
Author: User

Followed by the previous article 《Evolution of websites and databases (2). I have discussed the advantages and disadvantages of several static solutions above. Some friends may want to explain it in detail. Haha, this is not part of the scope of this article. Some friends also said that some websites are not suitable for static operations. This is the case. However, during this period, the website is still in the initial stage of development. In the initial stage, the number of website users is usually small, and most of them focus on providing consultation. A typical web1.0 system has a static solution closely related to this background. What kind of problems will the website encounter with its gradual development? This depends on the actual situation of website development. There are two types in general: 1. It refers to information. Users generally come from search engines and there are not many interactive tasks; 2. Use SNS or highly interactive products such as forums (this example is not used for download or text reading from forums ).

1. Content-based systems

there are two scenarios for the first website that provides content. One is that the data capacity is too large and the database access speed is slow due to early design mistakes. The other is that there are too many visitors, resulting in insufficient IIS response, if the access speed is slow or service unavailable error. Or both cases occur.

the database needs to be optimized when data access is slow. Including optimizing query statements, optimizing database structures, and optimizing indexes. The optimization of tens of millions of data entries in a single table requires table sharding. Not available in versions earlier than sql2005. You do not need to use the built-in table partition function. Generally, data is stored in different tables by time. Then, use the view function to aggregate table queries. This method is much different from the table partition in sql2005, and the efficiency is far lower than that in sql2005. Why? For example, SQL2000 creates two tables with the same structure and stores data. Table 1 and Table 2 both have 5 million data records. During the query, It is not slow to filter data from table 1, filter data from table 2, merge data, and sort data by conditions or by single thread? Sql2005 allows you to place indexes in different partitions and operate on multiple threads. Because data is filtered and sorted in the process, the speed is still very fast. Of course, the premise is that the server has many cores. (Sql2005 table partitions can only be used in the server version .)

slow IIS response or service unavailable may be because the bandwidth is too small or the number of connections is too large. I remember someone tested that the maximum number of TCP connections in IIS is about 8000, and Apache in Unix (or httpd forgot .) The maximum number of connections is over 10 thousand. It seems that it is the limitation of the TCP/IP stack in the operating system. I don't know much about this. If the Web Service is unstable due to exceeding this volume or other similar causes, you should add the server.

2. Highly Interactive System

highly interactive systems are prone to high database concurrency. Many database operations are locked. The locks are stored in the system table. If the throughput of the system cannot meet the requirements, the locks will become faulty. You can think that a database can have up to 100 connections at a time (tested on the sql2005 server version ). If the number exceeds the limit, 101st will time out. If a statement takes a long time and is frequently operated, the database timeout error may easily occur.

If the database itself cannot meet this requirement, we can use an interceptor to solve this problem. You also need to consider how to design an interceptor. Suppose there are 100 database operation commands per second, and these 100 commands are different, and the database can process these 100 commands in just one second. Now there are 101 commands per second, and the commands are still different. The commands generated per second are also different, so it is useless to implement the interceptor. There can be only one relief effect at most. Because an unhandled command is added every second.

figure 2.1

Fortunately, many statements are repeated. For example, the interceptor 2.1 currently works the same way. In one second, it intercepts 101 commands and merges 20 statements into the same query content (generally a list page ), finally, 40 commands need to be operated, and then the command is executed. After obtaining the database, the 101 requests are distributed. That is to say, 101 jobs are compressed into 40 jobs.

You can also cache infrequently changed data. For example, the classification of Article and the user name (this depends on the Growth of registered users ). Change the model in figure 2.1 to figure 2.2.


figure 2.2

of course, the cache block can also be added to the web application. It is mainly used to store data that is not updated for a period of time. Of course, this cache has an expiration policy.

cache can also help optimize SQL queries. For example, a joint query queries an article classification table and an article table. Only the article table can be queried, but the article table only has a category ID. What should I do when displaying the table? In the memory, a category dictionary is cached. The key is the category ID and the value is the category name. When displayed, you can use the category ID in the document to find it in the dictionary. This improves the efficiency of SQL statements.

for big tables, refer to the first part of this article.

now, there is still a problem that cannot be solved in this article, which is left behind by the first part. How can I deploy a new server?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.