DB2 performance problem classification and analysis ideas

BKJIA Summary: in the previous article, we have read several major factors in DB2 performance optimization. This time, let's take a look at DB2 performance issues and see how these problems are classified, what is an analysis idea.

DML performance problems

DML (Data Manipulation Language) includes query, add, delete, and update records.

First, let's take a look.Query performance problemsSometimes the response time is long when you query a joint query of one or more tables, which makes it unbearable. To solve this problem, you can use the following methods:

Whether the related fields in the query join or condition clause are indexed. (For more information about SQL optimization, see SQL optimization ).

Check the size of the buffer pool. If the buffer pool is too small, many data cannot be read from the buffer pool directly from the hard disk, resulting in a large bottleneck. On the other hand, the buffer pool prefetch setting usually sets the PREFETCHSIZE to the product of the segment size and the number of containers, so that the concurrency of prefetch can be maximized.

When the order by clause is involved in a query, if the sorting field is not indexed, the sorting will use the sortheap in memory ). If the sorting heap is too small, the sorting will overflow to the hard disk (Overflowed), resulting in a performance decline.

We also need to consider the RUNSTATS/REORG factors. The RUNSTATS command updates the statistics in the table. When the data in the table is frequently added, deleted, and modified, the corresponding statistical information will change, and the optimizer selects the execution plan based on this statistical information, therefore, running RUNSTATS is particularly important. REORG can organize the physical structure of data storage, reduce data scanning time, and improve query performance.

In terms of storage, it should be noted that it is better to select the DMS of the bare device than the SMS, because it lacks a buffer of the file system and directly accesses the buffer pool.

You can use the optimize for n rows clause to increase the display speed of the previous n records. This allows you to quickly view the n records and then view other records. This reduces the user's waiting time.

Materialized query table (MQT) is also a means to improve query performance. It can store frequently used query result sets in an intermediate table, reducing the time required for data retrieval during queries.

Using MPP or SMP in architecture is also a means to improve query or write operation performance.

For complex queries, you can set the value of the Database Configuration Parameter DFT_QUERYOPT (default query optimization class) to a higher value (7 or 9 ), for a simple query, you can set it to a lower value (3 or 5), because the higher the setting, the deeper the analysis the optimizer performs, and the more time it takes to generate the plan.

For a query of the C/S structure, you can write the query statement on the server to generate a stored procedure to reduce the network transmission of data and the pressure on the client. The compiled stored procedure is executed more efficiently.

Taking into account the isolation level and lock factors, the higher the isolation level, the more data integrity can be guaranteed, but at the same time it will weaken concurrency. This requires consideration.

Network factors cannot be ignored. Setting the RQRIOBLK parameter of the database server to 65534 can increase the network throughput accordingly. (Default value: 32767)

The last thing to consider is the database structure. In some cases, adding redundant fields in some tables sacrifices some space and maintenance costs, but it can reduce many connection operations during query, this greatly improves the query performance. It is to exchange space for time.

Next, let's take a look.Performance Optimization Methods for addition, deletion, and modification:

The first is the index factor. When adding, deleting, and modifying indexes, the database will modify the indexes in the table. This consumes a certain amount of resources. Therefore, you can delete the index before ensuring data integrity. After adding, deleting, and modifying the index, You can recreate the index. This also saves some time. Adding indexes and data to Different Hard Disks also increases the concurrency of write operations.

Second, we should consider the log factor. While writing data, the database system is also maintaining transaction logs, so we should minimize the cost of log maintenance. Setting auto commit to false reduces the number of submissions (and also reduces the number of log writes ). Increase LOGBUFSZ to reduce the number of log refreshes and the number of Log File switches. You can also change the table attribute to "activate not logged initially", which can block table log operations to improve the write performance. However, it is difficult to repair the data of tables that lose transaction logs, this must be balanced.

Adding logs and data to different hard disks can also increase the concurrency of write operations.

Using append mode when inserting records can eliminate the time required for DB2 to find the free space in the table and insert it directly to the end of the table, thus improving the insert performance.

The MPP mode can be used for Parallel Processing to increase the performance of write operations. The performance of write operations can also be increased when containers are dispersed on different hard disks.

We also need to consider the impact of constraints and triggers. We should try to avoid constraints and triggers in the table during write operations. When data integrity is ensured, constraints or triggers can be removed before frequent mass write operations.

Like a query, write operations also take into account the isolation level and lock factors (see the query optimization section ).

Multiple insert statements can reduce the number of client-server communications and improve the insert performance. For example, insert into table1 values (1, 'A'), (2, 'B'), (3, 'C ').

Another factor to be considered is the adoption of the thread model of DB2 V95 on UNIX, which reduces the overhead in the operating system, making the write operation performance better than the previous DB2 version.