Sqlserver high concurrency and big data storage solution, SQL Server Data Storage

Sqlserver high concurrency and big data storage solution, SQL Server Data Storage

With the increasing number of users, daily activity and peak value, database processing performance is facing a huge challenge. Next we will share with you the database optimization solution for the platform with over 0.1 million actual peaks. Discuss with everyone and learn from each other!

Case: game platform.

1. High concurrency

When the number of client connections reaches the peak value, the server will not discuss the connection maintenance and handling. When multiple write requests are sent to the database, you need to insert multiple tables. In particular, some tables are stored for more than 10 million RMB per day, the traditional synchronous data writing method is obviously not desirable. After experiment, the asynchronous insertion method has improved a lot, but at the same time, the real-time data reading needs to be sacrificed.

There are many asynchronous methods. The current method is to convert the data in the temporary table to the real table at intervals (5 min, 10 min .. depending on the requirement setting) of the job.

1. The original table A is actually used for reading.

2. Create B and C in the same structure as the original table A for data transfer. The synchronization process is C-> B->.

3. create A job Job1 for data synchronization and A table that records the running status of Job1. During data synchronization, it is important to check the current status of Job1. If data of B is being synchronized to, store the data from the server to C, then import the data to B, and then transfer the data to A when the next Job is executed. 1:

Figure 1

At the same time, in order to ensure security and facilitate troubleshooting, you should use a stored procedure to record the entire database instance and check the job execution results in a short period of time. If an exception fails, relevant personnel should be notified in other ways in a timely manner. Such as writing to the email and text message table, so that a Tcp notification program can regularly read and send messages.

Note: If the data size in a day reaches dozens of GB, and the query requirement for this table is required (the partition will be mentioned below), the following is one of the following strategies:

B can be synchronized to multiple servers at the same time to share the query pressure and reduce resource competition. Because the resources of the entire database are limited, such as the insert operation, a shared lock is obtained first, and then a row of data is located through the clustered index, and then upgraded to the intention lock, SQL Server requires different memory sizes for Lock maintenance, which leads to resource competition. Therefore, we should try our best to separate reading and writing. You can divide the data based on the business model and according to the set rules. In a platform project, we should prioritize data insertion.

It is inevitable that querying big data will consume a large amount of resources. In case of batch deletion, you can switch to the cyclic batch (for example, 2000 entries at a time) method, in this way, the entire database will not be suspended due to this process, and some unexpected bugs will be generated. After practice, it is effective and feasible, but sacrifices the storage space. You can also split the fields with a large amount of data in the table to the new table based on the query requirements. Of course, these fields must be set based on the needs of each business scenario, and you can design a suitable solution that does not need to be gorgeous.

2. Solve storage problems

If the data in a single table reaches dozens of GB every day, you can't wait to improve the storage solution. Now I am sharing my own solutions. I am still sticking to the front-line despite the surging data! For examples, refer:

Existing data table A adds 30 GB of data to A single table every day. Data is synchronized asynchronously during storage. Some tables cannot be cleared and can be divided into file groups after partitioning, allocate file groups to different disks to reduce the competition for IO resources and ensure the normal operation of existing resources. Currently, historical data is retained for 5 days based on requirements:

1. In this case, the job needs to generate a partition scheme based on the partition function, such as Partitioning Based on the userid or time field;

2. After partitioning a table, you can quickly locate a partition using the corresponding index;

3. Use the job to merge partitions to transfer unwanted partition data to a table with the same structure and index, and then clear the data in the table.

2:

Figure 2

You can use the SQL query trace to capture the time-consuming queries, and use the Stored Procedure sp_lock, dm_tran_locks, and dblockinfo to view the types and granularity of the locks of the current instance.

Find the specific query statement or stored procedure, and then take the right action! Medicine is eliminated!

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!