Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about DB2 database performance optimization skills, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Turn on the DB2 database monitoring switch to get the simplest and most effective performance information you need-Bufferpool. A buffer pool is a storage area in memory that temporarily reads and changes database pages (including table rows or index entries). The following editor to explain the DB2 database performance optimization skills?

What are the performance optimization skills of DB2 database?

The purpose of buffer pools is to improve the performance of database systems. Accessing data from memory is much faster than accessing data from disk. Therefore, the fewer times the database manager needs to read or write to disk, the better the performance. Configuring one or more buffer pools is the most important aspect of tuning because most of the data (excluding large objects and long fields data) of applications connected to the database is performed in the buffer pool.

By default, the application uses the buffer pool IBMDEFAULTBP, which is created when the database is created. When the NPAGES value of the buffer pool in the SYSCAT.BUFFERPOOLS catalog table is-1, the DB2 database configuration parameter BUFFPAGE controls the size of the buffer pool. Otherwise, the BUFFPAGE parameter is ignored and the buffer pool is created with the number of pages specified by the NPAGES parameter.

It is recommended that for applications that use only one buffer pool, change NPAGES to-1 so that BUFFPAGE can control the size of the buffer pool. This makes it easier to update and report buffer pool sizes and other DB2 database configuration parameters.

After ensuring that the buffer pool size can be controlled using the BUFFPAGE parameter in the database configuration, set the parameter to the appropriate value. It is safe to set this parameter to a reasonably large value based on the size of the database and the nature of the application. In general, the default value of this parameter is very small and may not meet the requirements.

Db2 "get snapshot for all bufferpools"

In the snapshot output of the database snapshot or buffer pool snapshot, look for the following "logical reads" and "physical reads" so that the buffer pool hit ratio can be calculated, which can help tune the buffer pool:

The buffer pool hit ratio indicates the percentage of time that the database manager can process page requests without loading the page from disk (that is, the page is already in the buffer pool). The higher the hit ratio of the buffer pool, the lower the frequency of using disk Ipicuro. The buffer pool hit ratio is calculated as follows:

What are the performance optimization skills of DB2 database?

(1-(buffer pool data physical reads + buffer pool index physical reads) /

(buffer pool data logical reads + pool index logical reads))

) * 100%

This calculation takes into account all pages (indexes and data) cached by the buffer pool. Ideally, the ratio should exceed 95% and be as close to 100% as possible. To improve the buffer pool hit ratio, try the following methods:

1. Increase the buffer pool size.

2. Consider allocating multiple buffer pools, and if possible, one buffer pool for the table space to which each frequently accessed large table belongs, and one buffer pool for a group of small tables. then try using buffer pools of different sizes to see which combination provides the best performance.

3. If the allocated memory does not help improve performance, avoid allocating too much memory to the buffer pool. The size of the buffer pool should be determined based on the snapshot information taken from the test environment.

4. A buffer pool that is too small will produce too much and unnecessary physical Ido. Too large buffer pools put the system at risk of operating system page scheduling and consume unnecessary CPU cycles to manage overallocated memory. The right buffer pool size is at some point of balance between "too small" and "too big". The appropriate size exists at the point where returns will begin to decrease.

Get the best performance-- SQL

A bad SQL statement can completely ruin everything. A relatively simple SQL statement can also mess up a well-tuned database and machine. For many of these statements, there is no DB2 UDB configuration parameter in the world (or in the file) to correct the high cost caused by incorrect SQL statements.

To make matters worse, DBA is often subject to restrictions: the SQL cannot be changed (probably because it is provided by the application vendor). This leaves DBA with only three paths to go:

1. Change or add an index

two。 Change Cluster

3. Change catalog statistics

A robust application consists of thousands of different SQL statements. The frequency of execution of these statements varies depending on the functionality of the application and the day-to-day business needs. The actual cost of a SQL statement is the cost of one execution multiplied by the number of times it executes.

The major task for each DBA is to identify the challenges of statements with the highest "real cost" and to reduce the cost of those statements.

The cost of resources used to execute a SQL statement can be calculated from native DB2 Explain utilities, tools provided by some third-party vendors, or DB2 UDB SQL Event Monitor data. But the frequency of statement execution can only be known through careful and time-consuming analysis of DB2 UDB SQL Event Monitor data.

Optimal performance requires not only excluding high-cost SQL statements, but also ensuring that the corresponding physical infrastructure is appropriate. The best performance is achieved when all the tuning knobs are set properly, memory is effectively allocated to the pool and heap, and Icano is evenly distributed to each disk.

Can not be omitted-- Lock

These lock-related controls are database configuration parameters:

LOCKLIST indicates the storage capacity allocated to the lock list. Each database has a lock list that contains locks held by all applications that connect concurrently to the database. Locking is a mechanism used by the database manager to control multiple applications to access data in the database concurrently. Both rows and tables can be locked. Depending on whether the object holds other locks, each lock requires a lock list of 32 or 64 bytes:

1. 64 bytes are required to hold a lock on an object on which no other locks are held.

2. It takes 32 bytes to record the lock on an object on which a lock is already held.

MAXLOCKS defines the percentage of lock lists held by the application, which must be populated before the database manager can perform a lock upgrade. When the percentage of lock lists used by an application reaches MAXLOCKS, the database manager upgrades these locks, which means replacing row locks with table locks, thereby reducing the number of locks in the list. When the number of locks held by any application reaches this percentage of the size of the entire lock list, the locks held by the application are upgraded. If the lock list runs out of space, lock escalation will also occur. The database manager determines which locks to upgrade by looking at the application's lock list and looking for the table with the most row locks. If you replace these row locks with a table lock, the MAXLOCKS value will no longer be exceeded, and lock escalation will stop. Otherwise, lock escalation will continue until the percentage of lock lists held is lower than MAXLOCKS. The MAXLOCKS parameter multiplied by the MAXAPPLS parameter cannot be less than 100.

After reading the above, do you have any further understanding of DB2 database performance optimization techniques? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.