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

This article is about how to use DB2 UDB's e-commerce OLTP application for performance optimization. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. Monitoring switch

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

Db2 "update monitor switches using

Lock ON sort ON bufferpool ON uow ON

Table ON statement ON "

2. Agent program

Make sure there are enough DB2 agents to handle the workload. To find out about the agent, issue the command:

Db2 "get snapshot for database manager"

And look for the following lines:

High water mark for agents registered = 7

High water mark for agents waiting for a token = 0

Agents registered= 7

Agents waiting for a token= 0

Idle agents= 5

Agents assigned from pool= 158

Agents created from empty Pool = 7

Agents stolen from another application= 0

High water mark for coordinating agents= 7

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_COORDAGENTS as applicable).

III. Maximum number of files opened

DB2 tries to be a "good citizen" under the constraints of operating system resources. One of its "good citizens" actions is to set a limit on the maximum number of files that can be opened at any one time. The database configuration parameter MAXFILOP constrains the maximum number of files that DB2 can open simultaneously. When the number of files opened reaches this number, DB2 starts constantly closing and opening its tablespace files (including bare devices). Constantly opening and closing files slows SQL response time and consumes CPU cycles. To find out if DB2 is closing 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, increase the value of MAXFILOP until the state of opening and closing the file stops.

Db2 "update db cfg for DBNAME using MAXFILOP N"

IV. Lock

The default value for LOCKTIMEOUT is-1, which means that there will be no lock timeout (which can be catastrophic for OLTP applications). Still, I often find that many DB2 users use LOCKTIMEOUT=-1. Set LOCKTIMEOUT to a very short time value, such as 10 or 15 seconds. Waiting on the lock for too long will have 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 lines that contain the following text:

Lock timeout (sec) (LOCKTIMEOUT) =-1

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

Db2 "update db cfg for DBNAME using LOCKTIMEOUT 15"

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

Db2 "get snapshot for database on DBNAME"

Look for the following lines:

Locks held currently= 0

Lock waits= 0

Time database waited on locks (ms) = 0

Lock list memory in use (Bytes) = 576

Deadlocks detected= 0

Lock escalations= 0

Exclusive lock escalations= 0

Agents currently waiting on locks= 0

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.

How to optimize the performance of DB2 UDB's e-commerce OLTP application

Temporary tablespaces

In order to improve the performance of DB2 in performing parallel IDB2 O and to improve the performance of sorting, hash join (hash join), and other database operations using TEMPSPACE, temporary tablespaces should have at least three containers on three different disk drives.

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

Db2 "list tablespaces show detail"

Find an TEMPSPACE tablespace definition similar to the following example:

Tablespace ID= 1

Name= TEMPSPACE1

Type= System managed space

Contents= Temporary data

State= 0x0000

Detailed explanation: Normal

Total pages= 1

Useable pages= 1

Used pages= 1

Free pages= Not applicable

High water mark (pages) = Not applicable

Page size (bytes) = 4096

Extent size (pages) = 32

Prefetch size (pages) = 96

Number of containers= 3

Notice that the value of Number of containers is 3, and Prefetch size is three times that of Extent size. In order to get the best performance of parallel Imax O, it is important that Prefetch size is a multiple of Extent size. This multiple should be equal to the number of containers.

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

Db2 "list tablespace containers for 1 show detail"

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

VI. Memory sorting

OLTP applications should not perform large sorting. They are extremely expensive in terms of CPU, Iamp 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 per transaction.

Please issue the following command:

Db2 "get snapshot for database on DBNAME"

And look for the following lines:

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

10 Let SortsPerTX= Total sorts / transactions

11 Let PercentSortOverflows = Sort overflows * 100 / Total sorts

If the PercentSortOverflows ((Sort overflows * 100) / Total sorts) is greater than 3%, serious or unexpected sorting problems will occur in the application SQL. Because it is the existence of the overflow that indicates that a large sort has occurred, it is ideal to find that there is no sort overflow or at least its percentage is less than one percentage point.

If there are too many sort overflows, the "emergency" solution is to increase the size of the SORTHEAP. However, this only masks the real performance problem. Instead, you should determine the SQL that causes the sort and change the SQL, index, or cluster to avoid or reduce sorting overhead.

If SortsPerTX is greater than 5 (as a rule of thumb), the number of sorts per transaction may be large. Although some application transactions perform many small combinatorial sorting (they do not overflow and have a short execution time), it consumes too much CPU. When the SortsPerTX is very large, in my experience, these machines are usually limited by CPU. Determining the SQL that causes sorting and improving the access scheme (through indexing, clustering, or changing SQL) is extremely important to improve transaction throughput.

VII. Table access

For each table, determine the number of rows that DB2 reads 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 issuing the first command, determine how many transactions have occurred (by taking the sum of Commit statements attempted and Rollback statements attempted-see tip 3).

After issuing the second command, divide the number of rows read by the number of transactions (RowsPerTX). In each transaction, the OLTP application should typically read 1 to 20 rows from each table. If you find that hundreds of rows are being read for each transaction, a scan operation occurs and an index may need to be created. (sometimes running runstats with distributed and detailed indexes also provides a solution.)

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

Snapshot timestamp = 09-25-2000

4:47:09.970811

Database name= DGIDB

Database path= / fs/inst1/inst1/NODE0000/SQL00001/

Input database alias= DGIDB

Number of accessed tables= 8

Table List

Table Schema= INST1

Table Name= DGI_

SALES_ LOGS_TB

Table Type= User

Rows Written= 0

Rows Read= 98857

Overflows= 0

Page Reorgs= 0

The large number of Overflows may mean that you need to reorganize the table. An overflow occurs when DB2 has to locate a row on a less-than-ideal page because of a change in the width of the row.

Thank you for reading! On "how to use DB2 UDB e-commerce OLTP applications for performance optimization" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

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.