Asp. Net Website optimization series database optimization measures use master/Slave databases (full)

After the website scale reaches a certain level, the score is also divided, and the optimization is also optimized, but it still cannot meet the performance requirements of the business. At this time, we can consider using the Master/Slave database.

The master-slave database is two databases on two servers. The master database performs addition, deletion, modification, and query of the latest data at the fastest speed. The slave database is responsible for querying older data, do some analysis that requires less effectiveness and report generation. In this way, the database pressure is shared to the two servers to ensure the timeliness of the system response.

SQL Server provides a replication mechanism to help us implement the master-slave database mechanism. Let's take a look at how to practice SQL server 2005:

Before practice, you need to create a new Test database, which creates a Test table.

1. Open the SQL server Enterprise Manager, select copy à local release in the object Resource Manager, right-click to choose new release
 

2. Open the new release wizard, click Next, and select the database for data release.


3. Select the Test database, click Next, and select the release type.


Here we choose transactional publishing, which ensures that data is distributed to the subscription server as quickly as possible after updates. For Use Cases of several other release types, see msdn
4. Click Next and select the object to be released. Here we only release the table.
 
5. Click "Next" to filter data settings. Here we will copy all the data in the table, so we will not set it.
 
6. Click Next to specify when to run the snapshot. We will select the initial session data and the default running snapshot frequency.
 
7. continue to the next step. Set the account for running the snapshot agent. Select SQL server agent account.
 
8. Click "Next" and select "create release". Click "Next" to set the release name.
 
9. Click "finish" to complete the release settings and create a release. Now we have added a new release to the local release.
 

Now we have successfully created a release. We also need to create a subscription: Right-click the local subscription folder and choose create subscription. You can easily create a subscription through the wizard, when creating a subscription, you can choose to create it by means of publisher push or subscriber initiative. The procedure is as follows:
1. Right-click the menu to open the new subscription, click Next, and select the release we just created as the subscription source.

2. choose whether to synchronize data through push or proactive requests.
 
3. Set the account for executing the distribution agent
 
4. Set the synchronization frequency of proxy requests
 
5. Set whether to initialize data immediately.
 
6. Complete subscription Creation

After creation, We can insert n pieces of data into the master database table, and then verify whether the replication is successful by querying from the database.

Replication in SQL server2005 is easy to create. We need to set the replication type and synchronization Frequency Based on business needs. Next we will talk about how to effectively use the master-slave database in the program.

The master-slave database is a publish-subscribe relationship. The publisher and subscriber are not synchronized in real time. Generally, there is a delay of several minutes, and even several hours. Therefore, we need to avoid the problem of latency through reasonable use.

We hope that the primary database can participate in queries as little as possible to improve the timeliness of writing. At the same time, we also want the slave database to share the pressure of the primary database as much as possible without affecting the accuracy and timeliness of data reading.

The master and slave databases must configure two connection strings, CONN_Master and CONN_Slave, in the configuration file. We need to set some rules to determine whether the current query should be queried from the master database or from the slave database. This rule is not formulated and can only be determined based on business needs. Here are a few examples:

1. Taking the detailed page of The Douban book as the assumption scenario, you can click here to see the structure on the next page (I am not a Douban technology, here I just take this page as an example)
We will analyze the data required to present this page and the effectiveness requirements of the data.
1) Timeliness requirements for detailed information of books: Timely requirements
2) effectiveness of common tags of Douban members: No need to timely
3) books that people who like to read this book also like to read are data analysis and do not need to be timely
4) The latest book reviews must be timely
5) the number of users reading this book is not timely
6) The group that people like this book often go to does not need to be timely in analyzing data.
From the above analysis, we can see that only 1), 4) the two data items need to be read from the master database, while 2), 3), 5), 6) the non-timely data can be read from the slave database. Of course, we can cache these ineffective data.

2. taking the forum post list page as the assumption scenario, Forum players like to pin their posts to the first page for more people to pay attention, there are very few people who read the post after 50 pages. Based on this business logic feature, we can decide to read the Post list data from the master database when the user accesses the first 50 pages, when a user accesses data with more than 50 pages, the user queries the data from the slave database.

3. taking orders as an example, orders that have been placed for more than three months will not change any more. If we design the order number as the date format, when you query an order based on the order number, you can determine whether to access the master database or slave database based on the order number.

Let's take the third scenario as an example. Let's write a simple diagram of the Code.
Copy codeThe Code is as follows:
// The format of orderNo is 20100528120105000001, that is, yyyyMMddHHmmss + serial number.
Public OrderInfo GetOrder (string orderNo ){
String connString = ConnStringGetter. GetForOrder (orderNo );
Using (SqlConnection conn = new SqlConnection (connString ))
{
...
}
}

Public class ConnStringGetter
{
Public static string GetForOrder (string orderNo ){
Int year = int. Parse (orderNo. Substring (0, 4 ));
Int money = int. Parse (orderNo. Substring (4, 2 ));
Int date = int. Parse (orderNo. Substring (6, 2 ));
DateTime orderTime = new DateTime (year, money, date );

TimeSpan ts = DateTime. Now-orderTime;
// Use the master database or slave database based on the order time
If (ts. TotalDays> 30) return ConfigurationManager. ConnectionStrings ["CONN_Slave"]. ConnectionString;
Return ConfigurationManager. ConnectionStrings ["CONN_Master"]. ConnectionString;
}
}


Correct use of the Master/Slave database can greatly improve the system performance. The choice of using the master database or slave database depends on the business logic.