Analyze the performance of DB2 transactions by transaction type (1)

Overview:TransactionsIs one of the core concepts in the database system. As the logical Unit of Work of the database system, transactions must have four attributes: atomicity, consistency, isolation, and durability ). The database system often uses the lock mechanism to ensure transaction isolation and the log mechanism to ensure transaction persistence. Applications can control the execution and stop of a transaction by starting, committing, and rolling back. From an application perspective, a transaction usually corresponds to a series of closely related user operations, such as deposits and transfers in the banking system. For a user, committing a transaction is equivalent to completing a transaction. Therefore, the time that spans before and after a transaction is one of the factors that affect the user experience.

The performance of the database system is one of the important factors in judging the database system,DB2As a successful database product, it provides many performance tuning features and functions. On the one hand, DB2 provides a large number of configurable parameters at the database manager layer and database layer. You can view and modify these parameters through db2 get/update dbm cfg and db2 get/update db cfg, the Configuration Advisor in the Control Center (db2cc) can be used to obtain the optimized Configuration parameter values. On the other hand, DB2 provides optimization functions for queries. For example, SQL Explain Facility can be used to analyze an Access Plan and Command Editor after an SQL statement is optimized) A graphical view of the access plan is also provided. However, if you want to monitor and analyze the performance of a transaction, such as the transaction execution time, the execution time of each SQL statement in the transaction, and the idle time in the transaction, it cannot be implemented simply by using existing tools. This article introduces a method to analyze the transaction performance of DB2 to help database designers and administrators optimize database performance.

Logical composition of transactions

A transaction can logically consist of a set of SQL statements and a commit/rollback operation. In DB2, transactions are implicitly started by the first SQL statement sent to the database, without the need to issue a command to start the transaction. All subsequent database read/write operations from the same application are classified into one transaction until the application issues a COMMIT or ROLLBACK statement. The ROLLBACK statement will cancel all modifications to the database caused by this transaction. If the application does not issue a COMMIT or ROLLBACK, the transaction will be automatically committed. If the application does not exit normally while the transaction is being executed, it will be rolled back automatically. Once the COMMIT/ROLLBACK command is issued, the command cannot be stopped. Because the transaction is composed of a string of SQL statements, there is no physical representation of the transaction.

During the execution of a transaction, the database and application may be in different States. For example, in the transaction shown in figure 1, the application executes three SQL statements sequentially and the COMMIT statement. The application is in the UOW Executing State or Lock wait within the time from t0 to t1. The UOW Executing State indicates that the application is performing database operations, the Lock wait Status refers to the Lock that the application is Waiting for on the database object. The Lock is in UOW Waiting between t1 and t2, And the UOW Waiting indicates that the application has not performed database operations currently. The time consumed by the execution of a transaction may be used to execute SQL statements, execute application code, or wait for the lock. If the performance of a transaction is poor, you need to identify the time consumed in which the changes are made.

Figure 1. Logical composition of transactions

Analyze transaction Performance

Because transactions do not have a physical representation in the database, they cannot directly obtain the monitoring information of a transaction. This article introduces a method to comprehensively analyze the transaction performance through the event captured by the DB2 event monitor and the information obtained from the snapshot. Figure 2 shows the process of this method.

Figure 2. flowchart of analyzing transaction Performance

The following describes how to analyze things in detail through an experiment following the steps in the flowchart. The experimental environment is DB2 V9.1 and the operating system is Windows XP. In this experiment, we use a stress testing tool to access a J2EE Application Trade6 [4] deployed on WebSphere Application Server to perform a series of database operations and capture database performance data, then the transaction performance of the database system is analyzed.

Figure 3. experiment environment