Let the database fly up ten DB2 optimization techniques

DB2 is a series of relational database management systems exported by IBM, which serve on different operating system platforms. We will explain the DB2 ten optimization techniques.

To help DB2 DBAs avoid performance disasters and get high performance, I've summarized a troubleshooting process for our customers, users, and DB2 experts. The following is a detailed description of the 10 most important performance improvement techniques for e-commerce OLTP applications using DB2 UDB in Unix, Windows, and OS/2 environments, and hopefully the content covered below will be helpful.

First, the monitoring switch

Make sure the monitor switch is turned on. If they are not open, you will not be able to get the performance information you need. To open the monitor switch, issue the following command:

1 DB2 "Update monitor switches using

2 lock on Sort on bufferpool on UOW on

3 table ON statement "

Second, agent procedures

Ensure that there are enough DB2 agents to handle the workload. To find out the agent information, issue a command:

DB2 "Get snapshot for Database Manager"

And look for the following lines:

1 high water mark for agents registered = 7

2 high water mark to agents waiting for a token = 0

3 Agents registered= 7

4 Agents waiting for a token= 0

5 Idle agents= 5

6 Agents assigned from pool= 158

7 Agents created from empty Pool = 7

8 Agents stolen from another application= 0

9 High water mark for coordinating agents= 7

Ten Max agents overflow= 0

If you find that agents waiting for a token or agents stolen from another application is not 0, increase the number of agents available to the Database Manager (MAXAGENTS and/or Max_coordag Ents, whichever applies).

The maximum number of open files

DB2 as a "good citizen" under the constraints of operating system resources. One of its "good citizens" is to set an upper limit on the maximum number of open files at any time. Database configuration Parameters maxfilop constraints DB2 The maximum number of files that can be opened at the same time. When the number of open files reaches this number, DB2 will start to shut down and open its tablespace file (including bare devices) continuously. Constantly opening and closing files slows down SQL response time and consumes CPU cycles. To find out if DB2 is shutting down the file, issue the following command:

DB2 "Get snapshot for database on DBNAME"

And look for the following lines:

Database Files closed = 0

If the value of the above parameter is not 0, then increase the value of maxfilop until the state of the file is continuously opened and closed.

DB2 "Update db CFG for DBNAME using Maxfilop N"

Four, lock

The default value for Locktimeout is-1, which means there will be no lock timeouts (this can be disastrous for OLTP applications). Nonetheless, I often find many DB2 users with locktimeout=-1. Set the locktimeout to a very short time value, such as 10 or 15 seconds. The lock waits too long to create an avalanche effect on the lock.

First, use the following command to check the value of the Locktimeout:

DB2 "Get DB CFG for DBNAME"

and find the line that contains the following text:

Lock Timeout (sec) (LOCKTIMEOUT) = 1

If the value is-1, consider using the following command to change it to 15 seconds (be sure to first ask the application developer or your vendor to ensure that the application can handle the lock timeout):

DB2 "Update db CFG for DBNAME using LOCKTIMEOUT 15"

You should also monitor the number of lock waits, the lock wait time, and the amount of lock list memory that is being used. Please issue the following command:

DB2 "Get snapshot for database on DBNAME"

Look for the following line:

1 Locks held currently= 0

2 Lock waits= 0

3 time database waited on locks (ms) = 0

4 Lock list Memory in use (Bytes) = 576

5 Deadlocks detected= 0

6 Lock escalations= 0

7 Exclusive Lock escalations= 0

8 Agents currently waiting on Locks= 0

9 Lock timeouts= 0

If the lock list memory in use (Bytes) exceeds 50% of the defined locklist size, increase the number of 4k pages in the Locklist database configuration.

V. Temporary table space

To improve DB2 performing parallel I/O and improving the performance of sorting, hashing joins (hash joins) and other database operations using Tempspace, the temporal tablespace should have at least three containers on three different disk drives.

To know how many containers your staging tablespace has, issue the following command:

DB2 "List tablespaces Show Detail"

Look for a tempspace table space definition similar to the following example:

1 tablespace id= 1

2 Name= TEMPSPACE1

3 type= System Managed Space

4 contents= Temporary data

5 state= 0x0000

6 Detailed Explanation:normal

7 Total pages= 1

8 useable pages= 1

9 used pages= 1

Pages= Not applicable

Water mark (pages) = Not applicable

Page size (bytes) = 4096

Extent size (pages) = 32

Prefetch size (pages) = 96

Number of containers= 3

Note the value of number of containers is 3, and prefetch size is three times times the size of extent. For optimal parallel I/O performance, it is important that prefetch size be a multiple of extent size. This multiplier should be equal to the number of containers.

To find the definition of a container, issue the following command:

DB2 "List tablespace containers for 1 show Detail"

Refers to the tablespace ID #1, which is the TEMPSPACE1 in the example just given.

Six, memory sequencing

OLTP applications should not perform large sorting. They are extremely expensive in terms of CPU, I/O, and time spent, and will slow down any OLTP application. Therefore, the default sortheap size (1MB) of 256 4K pages (1MB) should be sufficient. You should also know the number of sort overflows and the number of sorts for each transaction.

Please issue the following command:

Db2 "Get snapshot for database on DBNAME"

And look for the following lines:

1 Total sort heap allocated= 0

2 Total Sorts = 1

3 Total sort time (ms) = 8

4 Sort overflows = 0

5 Active sorts = 0

6 Commit Statements attempted = 3

7 Rollback Statements attempted = 0

8 Let transactions = Commit statements attempted + Rollback

9 statements attempted

Ten let sortspertx= total sorts/transactions

One-let percentsortoverflows = Sort overflows * 100/total sorts

If percentsortoverflows (sort overflows *)/total sorts is greater than 3%, there will be a serious or unexpected sort problem in application SQL. Because it is the presence of overflow that indicates that a large sort has occurred, the ideal scenario is to find no sort overflow or at least its percentage is less than 1%.

If there are too many sort overflows, then the "contingency" solution is to increase the size of the sortheap. However, doing so only masks real performance issues. Instead, you should identify the SQL that caused the sort and change the SQL, index, or cluster to avoid or reduce the sorting overhead.

If SORTSPERTX is greater than 5 (as a matter of thumb), then the number of sorts per transaction can be large. Although some application transactions perform many small combinations of sorts (they do not overflow and execute for a short time), it consumes too much CPU. When the Sortspertx is large, in my experience, these machines are usually subject to CPU limitations. Determining which SQL is causing the ordering and improving the access scheme (through indexing, clustering, or changing SQL) is critical to increasing the throughput rate of the transaction.

Vii. Table Access

For each table, determine the number of rows read by DB2 for each transaction. You must issue two commands:

1 DB2 "Get snapshot for database on DBNAME"

2 DB2 "Get snapshot for tables on DBNAME"

After the first command is issued, determine how many transactions have occurred (by taking the sum of the commit statements attempted and rollback statements attempted-see Tip 3).

After the second command is issued, the number of rows read is divided by the number of transactions (ROWSPERTX). In each transaction, the OLTP application should normally read 1 to 20 rows from each table. If you find that hundreds or thousands of rows are being read for each transaction, then a scan operation may be required to create an index. (sometimes running runstats with a distributed and detailed index can also provide a workaround.) )

The sample output of "Get snapshot for tables on DBNAME" is as follows:

1 Snapshot timestamp = 09-25-2000

2 4:47:09.970811

3 Database name= Dgidb

4 Database path=/fs/inst1/inst1/node0000/sql00001/

5 Input Database alias= DGIDB

6 Number of accessed tables= 8

7 Table List

8 Table schema= INST1

9 Table name= Dgi_

Ten Sales_ LOGS_TB

Table type= User

0 Rows written=

Rows read= 98857

0 overflows=

Page reorgs= 0

A large number of overflows may mean that you need to reorganize the table. Overflow occurs when the width of the row is changed so that DB2 must locate a row on a page that is not ideal.

Viii. Table Space Analysis

Table space snapshots are extremely valuable in understanding what data is accessed and how it is accessed. To get a table space snapshot, issue the following command:

DB2 "Get snapshot for tablespaces on DBNAME"

For each tablespace, answer the following questions:

What is the average read time (MS)?

What is the average write time (ms)?

What percentage of physical I/O is accounted for by asynchronous (prefetch) relative to synchronization (random)?

What is the buffer pool hit rate per table space?

How many physical pages are read per minute?

How many physical and logical pages should be read for each transaction?

For all tablespaces, answer the following questions:

Which tablespace reads and writes the slowest time? Is it because the container is on a slow disk? is the container size equal? Compared to asynchronous access and synchronous access, is the access attribute consistent with expectations? The randomly read table should have a randomly read tablespace, in order to get a high percentage of synchronous reads, Typically higher buffer pool hit ratios and lower physical I/O rates.

For each tablespace, make sure that the prefetch size is equal to the chunk size multiplied by the number of containers. Please issue the following command:

DB2 "List tablespaces Show Detail"

If desired, you can change the prefetch size for a given table space. You can use the following command to examine the container definition:

DB2 "list tablespace containers for N Show Detail"

Here, N is the tablespace identification number.

Nine, Buffer pool optimization

I often find some DB2 UDB sites, although the machine has 2, 4 or 8GB of memory, but the DB2 database has only one buffer pool (IBMDEFAULTBP), its size only 16mb!

If this is the case at your site, create a buffer pool for the Syscatspace directory tablespace, create a buffer pool for the tempspace tablespace, and create at least two additional buffer pools: Bp_rand and Bp_seq. The randomly accessed tablespace should be assigned to a buffer pool (bp_rand) for random access. Table spaces for sequential access (using asynchronous prefetch I/O) should be assigned to the buffer pool (BP_SEQ) for sequential access. Depending on the performance objectives of some transactions, you can create additional buffer pools, for example, you can make a buffer pool large enough to store the entire "hot" (or very frequently accessed) table. When large tables are involved, some DB2 users put an index of important tables into an index (BP_IX) buffer pool for great success.

Too small a buffer pool can produce excessive, unnecessary physical I/O. Too large a buffer pool puts the system at risk of operating system page scheduling and consumes unnecessary CPU cycles to manage over-allocated memory. Just the right buffer pool size is at some point between "too small" and "too big". The appropriate size exists at the point where the return is about to begin to decrease. If you do not use tools to automate payback reduction analysis, you should scientifically test the buffer pool performance (hit ratio, I/O time, and physical I/O read rate) on an increasing buffer pool size until the best buffer pool size is reached. Because the business is constantly changing and growing, the "best size" decision should be reassessed periodically.

X. SQL Cost Analysis

A bad SQL statement will completely ruin your entire day. More than once, I saw a relatively simple SQL statement that messed up a well-tuned database and machine. For many of these statements, there is no DB2 UDB configuration parameter under the heavens (or in the file) to correct the high cost caused by the wrong SQL statement.

Worse, DBAs are often constrained by the inability to change SQL (perhaps because it is provided by the application vendor, such as SAP, PeopleSoft, or Siebel). This leaves the DBA with only three paths to go:

1. Change or add an index

2. Change the cluster

3. Change Catalog Statistics

In addition, today's robust applications consist of thousands of different SQL statements. The frequency of execution of these statements varies depending on the functionality of the application and the daily business needs. The actual cost of an SQL statement is the cost that it executes once 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 these statements.

Using the native DB2 Explain utility, tools provided by some third-party vendors, or DB2 UDB SQL Event Monitor data, you can calculate the resource cost for executing the SQL statement once. However, the frequency of statement execution can only be understood by carefully and time-consuming analysis of the data in the DB2 UDB SQL Event Monitor.

When researching SQL statement issues, the standard process that DBAs use is:

1. Create a SQL Event Monitor to write to the file:

$> DB2 "Create event monitor Sqlcost for statements write to ..."

2. Activate the event monitor (make sure you have sufficient free disk space):

$> DB2 "Set Event monitor Sqlcost state = 1"

3. Let the application run.

4. Deactivate the Event Monitor:

$> DB2 "Set Event monitor Sqlcost state = 0"

5. Use the Db2evmon tool provided by DB2 to format SQL Event Monitor raw data (which may require hundreds of megabytes of free disk space depending on the SQL throughput rate):

$> db2evmon-db DBNAME-EVM Sqlcost

> Sqltrace.txt

6. Browse through the entire formatted file, looking for a significantly larger cost (a time-consuming process):

$> more Sqltrace.txt

7. Perform a more complete analysis of the formatted file, which attempts to identify a unique statement (independent of literal values), the frequency of each unique statement (the number of occurrences it occurs), and its total CPU, sorting, and other resource costs. Such a thorough analysis may take a week or more in the 30-minute application SQL activity sample.

To reduce the time it takes to determine high-cost SQL statements, you can consider a number of available sources of information:

From Tip 4, be sure to calculate the number of rows read from each table in each transaction. If the resulting number looks large, the DBA can search the SQL Event Monitor formatted output for the table name (which will narrow the search and save some time), perhaps to find the problematic statement. From Tip 3, be sure to calculate the asynchronous read percentage and physical I/O read rate for each tablespace. If a table space has a high percentage of asynchronous reads and far exceeds the average physical I/O read rate, one or more tables in this tablespace are being scanned. Query the catalog and find out which tables are assigned to the suspect tablespace (each table space is assigned a table to provide the best performance detection), and then search for those tables in the SQL Event Monitor formatted output. These may also help narrow the search scope for high-cost SQL statements. Try to observe the DB2 Explain information for each SQL statement that the application executes. However, I found that high-frequency, low-cost statements often contend with machine capacity and capabilities to provide the desired performance. If the analysis time is short and the maximum performance is critical, consider using vendor-provided tools that quickly automate the process of identifying resource-intensive SQL statements. Database-guys Inc. 's Sql-guy tool provides cost-level analysis of accurate, real-time, and balanced SQL statements.

Continue to adjust

Optimal performance requires not only the elimination of high-cost SQL statements, but also the need to ensure that the appropriate physical infrastructure is appropriate. Optimal performance is achieved when all the adjustment knobs are properly set and the memory is allocated efficiently to the pool and heap and I/O is evenly distributed across the disks. While metrics and adjustments take time, DBAs who implement these 10 recommendations will be very successful in meeting internal and external DB2 customers. Because of the changes and growth of e-commerce, even the best-managed databases require regular fine-tuning. The DBA's work will never be finished!

Quick review of the best 10 tips

* Use sufficient agents for the workload.

* Do not allow DB2 to close and open files unnecessarily.

* Long-term lock waits are not allowed.

* Ensure the parallel I/O capability of the Tempspace table space of the database.

* Conservative management of DB2 sort memory does not have to cover sorting problems with large SORTHEAP.

* Analyze the table's access activity and determine the table that has a particularly high number of read rows or overflows per transaction.

* Analyze the performance characteristics of each tablespace and seek to improve the performance of the table space with the slowest read time, the longest wait time, the highest physical I/O read rate, the worst hit ratio, and the inconsistent access attributes as expected.

* Create multiple buffer pools with destinations to allocate tablespaces to the buffer pool for easy sharing of access properties.

* Check the DB2 UDB SQL Event Monitor information to find out which SQL statement consumes the most compute resources and take the correct action.

Once you have eliminated high-cost SQL, reevaluate your configuration and physical design settings immediately. About DB2 optimization Skills Nine for everyone to introduce here, I hope that we can have something to gain from it.

Let the database fly up ten DB2 optimization techniques