Breakthrough Bottleneck Database performance optimization "Roadmap"

The increase of data interaction complexity and frequency leads to the performance problem of database in operation dimension, migration and scale expansion process. As a key technology to ensure the healthy operation of the enterprise IT basic components, the implementation path of database performance optimization and IT system management architecture are becoming more and more inseparable.

The performance problem of the billing system of a provincial-level telecom operator in the south soon after the running of the online operation. The main problem is that the end user's interactive response is less than expected, especially when the business peak is not able to respond in a timely manner. From the point of view of the host system, the problem mainly concentrates on the system's I/O (input/output) waiting. The accounting system consists of business applications, Oracle database, IBM AIX host, IBM Enterprise Storage Server and other components, performance bottleneck positioning and optimization process are more complex.

Database experts through the application of performance monitoring tools to analyze the operation of the system, in the host and storage system tuning, and to ensure that it can meet the current production system processing needs, the Oracle database optimization proposed a specific proposal. The suggestion includes that in order to increase the processing pressure after the system expansion, the performance parameters of Oracle database are modified; by monitoring software to troubleshoot the logical design of the SQL statements with the largest resource consumption, the table structure and index of these SQL statements are stored separately, and the appropriate partitioning index is established. , through monitoring software to the database and application of data analysis, accurate positioning application system operation bottleneck, and make corresponding inspection and adjustment.

This typical case concentrates on several key steps in the process of optimizing database performance and its role positioning in the overall IT management process. In the age of mature application of database, the performance optimization of database has evolved into a fairly rigorous system engineering. As one of the core parts of enterprise IT infrastructure, database is not an isolated system, it is closely connected with the network, operating system, storage and other hardware systems, which determines that the database performance optimization is a comprehensive technology with the multiple connection characteristics of other IT parts.

In the application of clustered and highly distributed environment, it is not easy to sum up a set of "Sihai" Database performance optimization methodology. However, the process of optimizing the performance of the enterprise with its own characteristics can be traced. In the process of establishing process, the first thing that the enterprise needs to be clear is that no matter from what angle, we should ensure that the optimization behavior can be coordinated with the overall management framework of IT system, because the performance problem of the database is not limited to the internal database. In most cases, its optimization behavior revolves around the idea of overall it performance tuning.

Path Global Planning

The old saying goes: "Do not seek the whole person, insufficient seek a domain." "The point is that if you don't consider the problem from a global perspective, you can't get local wisdom." Specific to database performance optimization, typically includes optimized operations for applications, parameters, storage, hardware, networks, and operating systems. Statistics show that the network, hardware, operating system, database parameters to optimize the performance gains, only accounting for database system performance improvement of about 40%, the remaining 60% of the system can be improved from the optimization of the application. As a senior database consultant in the financial and telecommunications industry, Dr. New, a database expert at Zhungliang Technology (Beijing) Co., said that database performance optimization needs to solve problems outside the database in many cases, requiring practitioners to have a complete knowledge system, which is a very challenging task.

"System resource stress is the root cause of database performance problems." "said Dr. New. For common system resource losses from CPU, memory, disk I/O, and network, he sums up a set of general problem processes. Specifically include positioning performance bottlenecks, to determine the most consumed resources of the application or SQL, the logical resources of the Division, and analysis of performance problems are limited to the database, tracing the cause of the problem and other steps.

When expounding the integrity of database performance optimization, customs database expert Xiang Wang emphasizes that single point tuning has little significance for improving database performance. He points out that, in addition to the optimization of the common distributed database engine, the primary tasks of the DBA (database administrator) include tuning of the network, architecture, storage, and optimization of business applications, along with the optimization of the user experience that is often overlooked by the IT department. "There are a lot of programmatic aspects to database performance optimization, and each DBA has its own way of solving problems, and the common denominator of these routines is to implement the actions from a global perspective." "Xiang Wang said.

At this stage, user feedback and operational monitoring are the main sources of DBA Discovery database performance problems. When a failure is discovered, the DBA needs to use manual instructions or professional monitoring and analysis software to troubleshoot the system's fault points and determine reasonable performance optimization steps. Its basic guiding principle is to obtain the required data with as few disk accesses as possible (the general tuning strategy is shown in Figure 1). Application optimization outside the database system usually involves the adjustment of source code and SQL statement. In comparison, the source code modification has a higher time cost, while the gain of performance gains is limited; Because the application's operations on the database typically ultimately represent the operation of the SQL statement on the database, the optimization of the SQL statement can improve the performance of the database in a way that improves the efficiency of SQL execution.

Figure 1: Database Performance Tuning general policies