SQL Server database operation experience on hundreds of millions of tables _ MySQL

SQL Server database's experience in operating hundreds of millions of tables is summarized. sorting hundreds of millions of tables or joining hundreds of millions of tables may cause the system to lose response.

◆ 1. I did make a large query. the involved data table contains 0.2 billion records and a group by operation, resulting in high CPU, memory, and disk overhead. Then I tried again with Microsoft, and my query would actually slow down the system response. Later, we also tried to calculate the number of rows on the 0.2 billion table, that is, select count (*) from table1. it took about 1 minute, and the memory increased by 5 GB, the disk subsystem is heavily loaded and the CPU usage suddenly increases. This indicates that such operations on hundreds of millions of tables will seriously reduce the efficiency.

◆ 2. the disk allocation of the entire server is like this. the database library accessed by the website is located in the disk array, while the temporary database for our statistics is located in disk D. C and D seem to be a disk group, that is equivalent to the C drive. A large amount of disk I/O in our database will lead to slow system response. Therefore, when my query is very large, it will slow down the entire server system.

◆ 3. the self-growth mode of data files in the database is 1024 KB each time. the space of data files is close to the use of light, but it is expected to increase, and the demand for space for growth should be relatively large, therefore, applications will not stop increasing, resulting in high disk overhead.

◆ 4. A large number of logs are generated when the delete operation is involved. after resizing last week, we found that the log file is smaller than before. It is estimated that the log file is rebuilt, yesterday's operation will cause the log files to grow and the disk load will increase.

Solution:

For example:

To avoid large table operations, all operations can be separated by province or time. in this way, a large table can be split into more than 30 small tables in terms of time or region. Then merge the results to avoid the above problems.

For example:

There is no solution, but we recommend that you separate our database into a group of disks and do not compete with the system.

For example:

Delete useless temporary data in time to ensure database space and perform space monitoring. Once the data file space increases, send an alert email to The DBA, after receiving the email, we can immediately process it.

For example:

Currently, log files have increased significantly. we will perform the log truncation operation to keep the space usage of log files at a low level.