诊断DB2数据库故障与性能瓶颈

1、在db2数据库主机遇到重大故障时我们可以通过db2support收集数据库诊断日志数据

#在可以连接的时候

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

#不能连接的时候

#db2support . -c -g -s

2、bufferpool设置的太大连接数据库时导致系统宕机的解决方法：

操作步骤：

#db2set DB2_OVERRIDE_BPF=10000

#db2 terminate

#db2stop

#db2start

#db2 connect to db

#连上db

#db2 alter bufferpool buffer001 numblockpages

#原来的块SIZE太大，我们这里禁用它

#db2 force applications all

#db2 connect to db

#db2 alter bufferpool buffername immediate size 50000

#将SIZE改小

#db2set DB2_OVERRIDE_BPF= #设置为空，还原回去

#db2 terminate

#db2 force applications all

#db2stop

#db2start

3、如何快速定位问题

1)如果系统的CPU利用很高，IO很少，那么数据库的排序较多

2)如果系统的IO繁忙，CPU很多是wait，那么说明数据库有过多的IO

3)如果系统CPU，IO都很空闲，那么说明可以是有锁的问题

4)如果系统IO，CPU都非常忙，说明有执行代价非常高的sql在执行

4、快速找到执行成本较高的sql

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

#db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on

#在系统最繁忙的时候，运行

#db2 get snapshot for all applications > app.out

#然后在该文件中查找处于Executing状态的应用，找到执行的对应的sql语句。

#如果用这种方法找不到，再收集sql的快照

#db2 get snapshot for dynamic sql on > sql.out

5、如何优化sql语句

DB2提供了很好的工具来做sql语句优化。首先要对找到的sql语句进行分析，看是否是该语句引起了性能问题。我们可以使用db2expln来查看sql语句的访问计划和执行成本。

#首先将找到的sql语句写到一个文本文件中以;结尾，然后运行

#db2expln –d dbname -f sqlfile -g -o sql.explain查看 sql.explain可以看到这个sql语句的执行成本。

#如果确认该语句有问题，可以使用db2advis来通过建索引的方法来优化该语句.db2advis -d -i sqlfile如果通过创建索引无法优化该语句，一般只能从业务角度优化。

6、如果发生锁的问题如何处理

发生锁的问题，一般有两种情况，一是锁等待，二是死锁。

#首先检查数据库配置参数locktimeout，该参数一定不能设为-1，因为会引起某些应用无限期的等待。

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

#db2 get snapshot for db on

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

#Deadlocks detected = 0

#Lock Timeouts = 0

#如果发生了死锁，可以通过创建死锁监视器来分析产生死锁的原因，命令如下：

#mkdir /tmp/dlmon

#db2 connect to dbname

#db2 create event monitor dlmon for deadlocks with detail write to file ‘/tmp/dlmon’ replace

#db2 set event monitor dlmon state 1等有死锁发生后

#db2 set event monitor dlmon state 0

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

#分析/tmp/dlmon.out文件就可以找到造成死锁的信息，结合应用就可以找到造成死锁的原因了。

二、关于bufferpool

1、大缓冲池的优点

它们使频繁请求的数据页能够保存在缓冲池中，这样可更快地访问。更少的 I/O 操作可以减少 I/O 争用，从而可提供更短的响应时间并减少 I/O 操作所需的处理器资源。

它们提供在相同的响应时间内到达更高的事务处理速率的机会。

它们防止频繁使用的磁盘存储设备(如目录表)和频繁引用的用户表和索引的 I/O 争用。由于包含临时表空间的磁盘存储设备上的 I/O 争用减少，查询所需的排序也从中受益。

许多缓冲池的优点

2、基于下列原因可考虑使用多个缓冲池：

可以把临时表空间分配给单个缓冲池中，以便为需要临时存储器的查询尤其是执行大量排序的查询提供更佳性能。

如果数据必须由很多小的更新事务应用程序反复地快速访问，考虑将包含该数据的表空间指定给单独的缓冲池。如果此缓冲池的大小定得合适，将有更多的机会找到它的页，以利于缩短响应时间和降低事务成本。

您可以将数据隔离到不同的缓冲池中，以利于特定的应用程序、数据和索引。例如，您可能要将频繁更新的表和索引置于一个单独的缓冲池中，与那些虽频繁查询但不频繁更新的表和索引分开。此更改将减少对第一组表的频繁更新对第二组表的频繁查询具有的影响。

对于很少使用的应用程序所访问的数据，可使用较小的缓冲池，尤其对于需要对一个很大的表非常随机地进行访问的应用程序。在 这种情况下，不需要将数据保存在缓冲池中的时间超过单个查询的保存时间。最好为此数据保留一个小缓冲池，而释放多余的内存用于其他用途，如用于其他缓冲 池。

当将不同的活动和数据隔离到不同的缓冲池之后，可从统计信息和记帐跟踪获得一个良好的、相对成本较低的性能诊断数据。