Diagnosing DB2 database failures and performance bottlenecks

1, in the DB2 database host encountered a major failure, we can collect database diagnostic log data through Db2support

#在可以连接的时候

#db2support. -D sample-c-g-s

#不能连接的时候

#db2support. -c-g-S

2, Bufferpool set too large to connect the database caused system downtime solution:

Operation Steps:

#db2set db2_override_bpf=10000

#db2 Terminate

#db2stop

#db2start

#db2 Connect to DB

#连上db

#db2 Alter Bufferpool buffer001 numblockpages

#原来的块SIZE太大, we disable it here.

#db2 Force Applications All

#db2 Connect to DB

#db2 alter Bufferpool buffername immediate size 50000

#将SIZE改小

#db2set db2_override_bpf= #设置为空, restore it back.

#db2 Terminate

#db2 Force Applications All

#db2stop

#db2start

3, how to quickly locate the problem

1 if the CPU utilization of the system is very high, Io is very few, then the database is sorted more

2 If the system is busy IO, the CPU is a lot of wait, then the database has too much IO

3 If the system Cpu,io are idle, then the problem can be a lock

4 If the system io,cpu are very busy, indicating that there is a very high execution cost of SQL in the execution

4, quickly find the execution of the high cost of SQL

#首先要打开监视器的开关

#db2 Update monitor switches using Bufferpool on "lock on" "Statement on" table on "UOW on"

#在系统最繁忙的时候, run

#db2 get snapshot to all applications > App.out

#然后在该文件中查找处于Executing状态的应用, locate the corresponding SQL statement that was executed.

#如果用这种方法找不到, and then collect a snapshot of SQL

#db2 get snapshot for dynamic SQL on > Sql.out

5. How to optimize SQL statements

DB2 provides a good tool for SQL statement optimization. The first step is to parse the SQL statement found to see if the statement is causing a performance problem. We can use DB2EXPLN to view the access plans and execution costs of SQL statements.

#首先将找到的sql语句写到一个文本文件中以; end, then run

#db2expln –d dbname-f sqlfile-g-o sql.explain view sql.explain You can see the execution cost of this SQL statement.

#如果确认该语句有问题, you can use Db2advis to optimize the statement by using an indexed method. db2advis-d-I sqlfile If the statement cannot be optimized by creating an index, it is generally optimized from a business perspective.

6, if the problem of how to deal with the lock

The problem of the lock, there are generally two situations, one is the lock waiting, the other is deadlock.

#首先检查数据库配置参数locktimeout, this parameter must not be set to-1, because some applications will wait indefinitely.

#其次可以通过快照来确定数据库发生的问题是哪一种.

#db2 get snapshot to db on

#查看输出中的下列内容:

#Deadlocks detected = 0

#Lock Timeouts = 0

#如果发生了死锁, you can analyze the cause of the deadlock by creating a deadlock monitor, which commands the following:

#mkdir/tmp/dlmon

#db2 Connect to DBName

#db2 Create Event Monitor Dlmon for deadlocks and detail write to file '/tmp/dlmon ' replace

#db2 Set Event Monitor Dlmon State 1 after a deadlock occurs

#db2 Set Event Monitor Dlmon State 0

#db2evmon –d/tmp/dlmon >/tmp/dlmon.out

#分析/tmp/dlmon.out files can be found to cause deadlock information, combined with the application can find the cause of the deadlock.

Ii. about Bufferpool

1, the advantages of large buffer pool

They enable frequently requested data pages to be saved in the buffer pool for faster access. Fewer I/O operations reduce I/O contention, providing shorter response times and reducing the processor resources required for I/O operations.

They provide the opportunity to reach a higher transaction rate within the same response time.

They prevent I/O contention for frequently used disk storage devices, such as catalog tables, and frequently referenced user tables and indexes. The sort required by the query also benefits from reduced I/O contention on the disk storage device that contains the temporary table space.

Advantages of many buffer pools

2. You can consider using multiple buffer pools for the following reasons:

Temporary table spaces can be assigned to a single buffer pool to provide better performance for queries that require temporary storage, especially for queries that perform a large number of sorts.

If the data must be accessed repeatedly and quickly by many small update transaction applications, consider assigning the tablespace containing the data to a separate buffer pool. If the buffer pool is sized appropriately, there will be more opportunities to find its pages to help shorten response times and reduce transaction costs.

You can isolate data into different buffer pools to benefit specific applications, data, and indexes. For example, you might want to place frequently updated tables and indexes in a separate buffer pool, separated from tables and indexes that are frequently queried but infrequently updated. This change will reduce the impact of frequent updates of the first set of tables on the second set of tables for frequent queries.

For data accessed by infrequently used applications, you can use a smaller buffer pool, especially for applications that require very random access to a large table. In this case, you do not need to keep the data in the buffer pool longer than the save time of the individual query. It is best to keep a small buffer pool for this data and release excess memory for other purposes, such as for other buffer pools.

When different activities and data are isolated to different buffer pools, a good, relatively inexpensive performance diagnostic data can be obtained from statistical information and accounting tracking.