Summary of system performance optimization-database access Optimization

Next we will continue to introduce the next topic, database access optimization rules:

To optimize SQL correctly, we need to quickly locate the bottleneck of performance, that is, to quickly find out where the main overhead of SQL is? In most cases, the device with the slowest performance will be the bottleneck. For example, the network speed during download may be the bottleneck, and the hard disk may be the bottleneck during local file copy, why can we quickly identify the bottleneck of these general tasks, because we have some basic knowledge about the performance data of these slow devices, for example, the network bandwidth is 2 Mbps, the hard disk is 5400 \ 7200 RPM, and so on. Therefore, in order to quickly find the performance bottleneck of SQL, we need to understand the basic hardware performance indicators of the computer system and display the current mainstream computer performance indicators.

The figure shows that each device has two metrics:

Latency (Response Time): indicates the hardware burst processing capability;

Bandwidth (throughput): represents the hardware processing capability.

It can be seen that the hardware performance of the computer system is from high to generation:

CPU -- cache (L1-L2-L3) -- memory -- SSD hard disk -- Network -- Hard Disk

Based on the database knowledge, we can list the main work of each hardware type:

CPUAnd memory: Cache data access, comparison, sorting, transaction detection, SQL parsing, functions or logical operations;

Network: Result data transmission, SQL requests, and remote database access (dblink );

Hard Disk: Data access, data writing, logging, sorting of large data volumes, etc.

Based on the basic performance indicators of the current computer hardware and its main operations in the database, you can sort out the basic performance optimization rules as shown in:

This optimization rule is summarized into five layers:

1. Reduce Data Access (reduce disk access) such as: Create and correctly use indexes, and check whether the indexes are used in the SQL Execution Plan.

2. Return less data (reducing network transmission or disk access) such as data paging and returning only required fields (emphasis)

3. Reduce interactions (reduce network transmission) such as batch DML (batch submission)

4. Reduce server CPU overhead (reduce CPU and memory overhead)
For example, bind a variable (in Java, preparestatement is the object provided for processing the bound variable) reduce comparative operations and a large number of complex operations in client processing (high concurrency does not affect the normal operation of the database)

5. Use more resources (add resources). For example, the client uses multi-process parallel access.

Summary:

Because the optimization rules of each layer solve the performance problems of the corresponding hardware, the performance improvement ratio is also different. Traditional database systems are designed to provide optimization methods for low-speed devices as much as possible. Therefore, there are more Optimization Methods for low-speed devices, and the optimization cost is also lower. The performance optimization of any of our SQL statements should follow the rules mentioned above to diagnose the problem and propose a solution, instead of increasing resources to solve the problem.