DB2 optimization (simple version) preparation - Monitors on
DB2 "Update Monitor Switch Using Lock On Sort On Bufferpool on Uow on Table on Statement On"
Open the monitor switch to get the required performance information
The simplest and most effective-bufferpool
Buffer pools are a storage area in memory for temporarily reading and changing database pages (including table rows or indexes). The purpose of the buffer pool is to improve the performance of the database system. From memory access data is much faster than accessing data from disk. Therefore, the smaller number of times the database manager needs to read or write to the disk, the better performance. The most important aspect of configuring one or more buffer pools is because most data of applications connected to the database (excluding large objects and long field data) operations are in the buffer pool. By default, the application uses buffer pool IBMDEFAULTBP, which is created when creating a database. When the NPAGES value of the buffer pool is -1 in the syscat.bufferpools directory table, 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 to change NPages to -1 for applications that use only one buffer pool, so that BUFFPAGE can control the size of the buffer pool. This makes the update and reporting buffer pool size and other DB2 database configuration parameters more convenient. Be sure to use the buffpage parameter in the database configuration to control the buffer pool size, the parameter is set to a suitable value. The parameter is set to a reasonable value based on the size of the database and the nature of the application, which is safe. Typically, the default value of this parameter is very small and may not meet the requirements. DB2 "Get Snapshot for All BufferPools" Find the following "Logical Reads" and "Physical Reads" in the database snapshot or buffer pool snapshot, which can calculate the buffer pool hit rate, which can help adjust the buffer pool: Buffer pool hit rate indicates that the database manager does not need to put a page from disk (ie, the page is already in the buffer pool) to process the page request time percentage. The higher the hit rate of the buffer pool, the lower the frequency of using the disk I / O. Calculate the hit rate as follows: (1 - (Buffer Pool Data Physical Reads Buffer Pool Index Physical Reads) / (Buffer Pool Data Logical Reads Pool Index Logical Reads)) * 100% This calculation considers buffer pool high speed All pages (indexes and data) cached. Ideally, this ratio should exceed 95% and close to 100% as close as possible. To increase the hit rate of the buffer pool, try the following: Add the buffer pool size. Consider allocating a plurality of buffer pools, if possible, assign a buffer pool for each table space that is often accessed, assigns a buffer pool for a set of small tables, then try to use different sizes of buffer pools to view Which combination provides the best performance. If the assigned 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. Too small buffer pool will produce too much, unnecessary physics I / O. Too big buffer pool allows the system in the risk of operating system page scheduling and consumes unnecessary CPU cycles to manage excessive allocation. Just a suitable buffer pool size is on a balance between "too small" and "too big". The appropriate size is present on the rendering to begin to decrease.
Get the best performance-SQL
A bad SQL statement will completely destroy everything. A relatively simple SQL statement can also mean a very good database and machine. For many these statements, under the sky (or in the file) does not have DB2 UDB configuration parameters to correct high costs caused by the error SQL statement. Worse, DBA is often binding: you can't change SQL (may be because it is provided by the application vendor). This gives only three roads to DBA: 1. Change or add Index 2. Change the cluster 3. Change the directory statistics to the aggregate application consist of thousands of different SQL statements. The frequencies performed by these statements are different depending on the functionality of the application, and the daily business needs. The actual cost of the SQL statement is that it performs a cost multiplier that is executed by it. The major task of each DBA is to identify the challenge of the statement with the highest "actual cost" and reduce the cost of these statements. By this machine DB2 Explain utility, some third-party vendor supplied with the tool or DB2 UDB SQL Event Monitor data, the resource costs used to perform a SQL statement can be calculated. However, the statement execution frequency can only be understood by carefully analyzing DB2 UDB SQL Event Monitor data. Best performance not only requires exclusion of high cost SQL statements, but also requires that the corresponding physical infrastructure is appropriate. Best performance can be obtained when all of which are just right, and memory is effectively assigned to the pool and piles and I / O is allocated to each disk. Unaissible -lock
These and lock-related controls are database configuration parameters: LockList indicates that the storage capacity assigned to the lock list. Each database has a lock list, and the lock list contains a lock held by connecting to all applications connected to the database. Lock is the mechanism for the database manager to control multiple applications concurrently in the database. The rows and tables can be locked. Depending on whether the object also holds other locks, each lock requires 32 or 64 bytes of lock list: 64 bytes to hold a lock on an object, on this object, no other locks. 32 bytes are required to record locks on an object, on this object, already held a lock. MaxLocks defines the percentage of the lock list holder, and must populate the lock list before the Database Manager performs the lock. When a list of locks used by an application reaches MaxLocks, the database manager will upgrade these locks, which means that the latch is used to reduce the number of locks in the list. When the number of locks held by any application reaches this percentage of the entire lock list, the lock held by the application is upgraded. If the lock list is running out of space, then a lock upgrade will occur. Database Manager determines which locks will be upgraded by viewing the list of indiscriminate lockers. If you replace these row locks with a table block, it will no longer be exceeded, and the lock upgrade will stop. Otherwise, the lock upgrade will continue until the percentage of the holding list is lower than MaxLocks. The MaxLocks parameter is multiplied by a maxAppls parameter. Although the upgrade process itself doesn't have to spend a lot of time, it is locked the entire table (relative to locking individual lines) to reduce concurrency, and the overall performance of the database may decrease due to subsequent access to the table affected by the lock upgrade. The default value of LockTimeout is -1, which means that there will be no lock timeout (for the OLTP application, this situation may be catastrophic). Many DB2 users use LockTimeout = -1. Set the LockTimeout to a very short time value, such as 10 or 15 seconds. Waiting for a long time in the lock to produce an avalanche effect on the lock. First, check the value of LockTimeout with the following command: DB2 "Get DB CFG for DBNAME" and look for rows containing the following text: Lock Timeout (sec) = -1 If the value is -1, consider using the following command to change it For 15 seconds (must first ask the application developer or supplier to ensure that the application can handle the lock timeout): DB2 "Update DB CFG for DBNAME USING LOCKTIMEOUT 15" should also monitor the number of locks waiting time, lock waiting time and is using Lock list memory (LOCK LIST MEMORY). Please issue the following command: DB2 "Get Snapshot for Database On DBNAME" If the LOCK LIST MEMORY IN USE (Bytes) exceeds 50% of the defined LockList size, add 4K pages in the LockList database configuration. -Sortheap covering problems
SortHeap is a database configuration parameter that defines the maximum number of private memory pages used by private sorting, or the maximum number of shared memory hoses used by sharing sorting. If the sort is privately sorted, the parameter affects the agent private memory. If the sort is a shared order, then this parameter affects the shared memory of the database. Each sort has a separate sorting that is individually assigned by the Database Manager. Sort the data in the sorting heap. If the assignment of the sorting heap size is directed by an optimizer, the size of the sorting stack assigned by the information provided by the optimizer is smaller than the sorting heap size specified by this parameter. SheaPthres is a database manager configuration parameter. Private and share sorts of memory sources are not the same. The size of the shared sort memory is predetermined according to the SheaPthres value when the SHEAPTHRES value is connected to the database. The size of the private sort memory area is unrestricted. For private sorting and sharing, the way the SheaPThres parameter is applied differently: for private sorting, SheaPthres is the instance-level "soft" limit for all memory that can be consumed at any given time at any given time. When the total private sort memory consumption of the instance reaches this limit, the memory allocated for other entering private sorting requests will greatly decrease. For shared sorting, SheaPthres is a database-level "hard" restriction of all memory that can be consumed at any given time on any given time. When this limit is reached, other shared sort memory requests are not allowed until the total shared memory consumption falls below SHEAPTHRES, specified below. Examples of operating examples of sorting include a hash connection and operation of the memory table. The explicit definition of the threshold prevents the database manager from being used in large numbers of memory. It is recommended to use the database system monitor to track the sort activity. Use the appropriate index to minimize the use of sorting stacks. Increase the value of SortHeap when you need frequent sorting frequently. If you add sortheap, make sure you need to adjust the SheaPthres parameters in the Database Manager configuration file. The optimizer uses the sort heap size to determine the access path. After changing this parameter, consider the rebound application (using the rebind package command). Ideally, the sorting heap threshold should be reasonably set to the multiple of the SortHeap parameter maximum value set in the Database Manager instance. This parameter should be twice that of the largest SortHeAP defined in any database in the instance. How to change these parameters To change the value SORTHEAP and SHEAPTHRES, run the following command: - SORTHEAP should be changed for individual database - db2 "update db cfg for DB_NAME using SORTHEAP a_value" - SHEAPTHRES is a database manager parameter - db2 The "Update DBM CFG Using Sheapthres B_Value" Research Procedure OLTP application should not perform large sorting. Large sorting is too high in CPU and I / O resources. Typically, the default value of SortHeap (256 4KB pages) is sufficient. In fact, for high concurrency OLTP, it may be desirable to reduce this default. When you need further research, you can issue the following command: DB2 "Update Monitor Switches Using Sort On" then let the application run for a while, then enter: DB2 "get snapshot for database on dbname" According to the output, you can calculate each The number of sequencing of a transaction, and can calculate the percentage of the partial sorting that overflows that can be used for sorting. SortsPerTransaction = (Total Sorts) / (Commit statements attempted Rollback statements attempted) PercentSortOverflow = (Sort overflows * 100) / (Total sorts) Experience: If SortsPerTransaction greater than 5, it may indicate ordering too much of each transaction.
