节选：第2章 性能优化利器——PAT方法 小节2.5.4-2.5.7

2.5.4 优化“账户资费”问题（Perf_ACC_App）

根据系统分析的结果，性能问题是由于磁盘瓶颈导致的，如图2-7所示，查找PAT树，按照下面的步骤优化：

 问题监控。

1）找到热表

查询syscat.tables和sysibmadm.snaptab，查看当前表空间活跃程度比其他要高很多的表，从而确定热表。在本例中，我们确定了欠费账户信息表DWD_ACC_OWE_ITEM具有最高的活跃度。

CREATE TABLE "BI"."DWD_ACC_OWE_ITEM" (

                 "BILLING_CYCLE_ID" VARCHAR(6) ,

                 "BILL_DTL_ID" DECIMAL(15,0) ,

                 "DEFAULT_ACCT_ID" DECIMAL(9,0) ,

                 "ACCT_ID" DECIMAL(9,0) ,

                 "USER_ID" DECIMAL(9,0) ,

                 "SVC_NUM" VARCHAR(64) ,

                 "AREA_ID" VARCHAR(3) ,

                ...

                 "ETL_TIME" TIMESTAMP ,

                 "ETL_DATA_CYCLE" DATE )  

                COMPRESS YES 

                DISTRIBUTE BY HASH("USER_ID")  

                  INDEX IN "TBS_INDEX"

                ORGANIZE BY (

                 ( "BILLING_CYCLE_ID" ) )

图2-7“账户资费”PAT分析树

2）热表中数据在各个分区分布不均匀

根据图2-8所示的PAT树搜索路径，确认了具有10亿条记录以上的欠费账户信息表DWD_ACC_OWE_ITEM存在数据分布不均匀的问题。下面是查找该表在32个节点上数据分布所使用的语句：

select DBPARTITIONNUM,SUM(DATA_OBJECT_L_SIZE) lszkb,                      SUM(DATA_OBJECT_P_SIZE),pszkb, SUM(INDEX_OBJECT_P_SIZE) iszkb from TABLE

(SYSPROC.ADMIN_GET_TAB_INFO_V95('BI', 'DWD_ACC_OWE_ITEM')) AS T

GROUP BY DBPARTITIONNUM

ORDER BY DBPARTITIONNUM              

 配置检查：正常。

 设计检查：经过分析该表发现，开始设计账户欠费信息表（DWD_ACC_ OWE_ITEM）的时候认为，每个账户都会有一个关联的用户。但是后来由于历史数据被导入，导致很多账户没有对应的用户。反映在表设计上就是，DWD_ACC_OWE_ITEM是按照user_id字段做DPF的hash key，但是历史数据导入后发现有相当一部分记录的user_id为0。关于DPF相关内容，请参考本书3.5节。

 性能优化。

在2.3.3节我们分析过这个问题，现在发现其实是当初业务的问题，所以采用重新设计hash key。最后，改用了acct_id作为hash key解决了这个问题。

图2-8 确认数据分布不均匀问题

2.5.5 优化“数据质量管理”问题（Perf_Data_App）

在2.3.3节对该问题判断为内存瓶颈引起，下面我们根据图2-9所示的PAT树搜索路径来定位并解决问题。

图2-9“数据质量管理”PAT分析树

 问题监控。

使用vmstat和ps工具监控“数据质量管理”应用的资源利用率情况，发现数据库内存的使用要远超越以前的基准。

 配置检查。

获取DB的配置参数，发现SELF_TUNNING_MEM的设定是OFF。

 设计检查：正常。

 性能优化。

综合起来看，响应时间变慢是由于大量排序导致的，而这种大量排序必然会对内存有大量的需求。另外，由于该经营分析系统是从DB2 8升级而来，数据库管理员没有激活STMM，导致有大量排序发生时，数据库系统无法使用STMM的自调整功能；但为什么以前的基准测试数据的性能表现不错呢？后来对该问题涉及的8张表进行了历史调查，发现这些表原先建有索引，排序的时候可以使用索引扫描（Index Scan），后来由于应用升级，这些索引忘记重建。

最后，我们通过下面的方法成功地解决了该性能问题。

（1）重建索引。

（2）激活STMM。

2.5.6 优化“系统逐渐变慢”问题（Perf_SlowDown_Sys）

该问题比较棘手，需要用系统化的方法解决，即对图2-10所示的PAT树进行一次自上而下、自左而右的搜索即可。

图2-10“懒惰系统”PAT分析树

 问题监控。

1）系统内部存在明显的锁等待问题。

在系统正常负载下，首先通过下面语句从snapdb中获得锁的使用情况，发现目前系统中有51次锁等待发生。

SELECT LOCKS_HELD, LOCK_WAITS, LOCK_WAIT_TIME, DEADLOCKS,          LOCK_ESCALS, LOCKS_WAITING,LOCK_TIMEOUTS, INT_DEADLOCK_ROLLBACKS       FROM   SYSIBMADM.SNAPDB

输出结果如下：

SNAPDBLOCKS_HELD LOCK_WAITS LOCK_WAIT_TIME DEADLOCKS LOCK_ESCALS      LOCKS_WAITING LOCK_TIMEOUTS NT_DEADLOCK_ROLLBACKS

-------------------------------------------------------------

11 506 817243 0 0 51 8 3

接下来再从SYSIBMADM.LOCKWAITS中获得锁等待的详细信息

DWD_CUSTOMER_BUSINESS_DATA

SELECT SUBSTR(TABSCHEMA,1,8) AS TABSCHEMA,

SUBSTR(TABNAME,1,15) AS TABNAME,

LOCK_OBJECT_TYPE,

LOCK_MODE,

LOCK_MODE_REQUESTED,

AGENT_ID_HOLDING_LK

FROM SYSIBMADM.LOCKWAITS;

 

TABSCHEMA TABNAME LOCK_OBJECT_TYPE LOCK_MODE LOCK_MODE_REQUESTED  AGENT_ID_HOLDING_LK

--------------------------------------------------------------

EDW DWD_CUSTOMER_BUSINESS_DATA TABLE_LOCK X S 40

2）定位引起锁等待的SQL语句。

可以通过db2pd工具和db2 get snapshot结合使用来获得引起锁等待的SQL语句，在本书第9章有相关的详细介绍，所以在此不再详述。

 配置检查。

检查下面的参数配置，作为进行下一步的参考。

—   预取器、清除器有关的DB参数：

NUM_IOCLEANERS=AUTOMATIC

NUM_IOSERVERS=AUTOMATIC

—   锁有关的DB参数：

MAXLOCKS=AUTOMATIC

LOCKLIST=AUTOMATIC

—   DB2注册表变量：

DB2_EVALUNCOMMITTED=ON

DB2_SKIPINSERTED=ON

DB2_SKIPDELETED=OFF

—   日志配置。

其他参数配置正常，但是发现了诊断日志和事务日志写入路径的问题。

1）DB2诊断日志db2diag.log存放路径问题。由于经营分析系统采用了多分区环境（DPF），所以32个分区通过网络共享NFS文件系统来向同一路径写入诊断日志。

2）事务日志和数据表空间共享磁盘。

获得事务日志存放路径：

db2 get db config for edwdata | grep -i 'path to log files'

Path to log files = /db2fs/EDW/NODE0000/SQL00006/SQLOGDIR/            

获得数据表空间存放路径：

SELECT SUBSTR(TBSP_NAME,1,20) AS TBSP_NAME, INT(TBSP_ID) AS              TBSP_ID,SUBSTR(CONTAINER_NAME,1,45) AS CONTAINER_NAME FROM            

   SYSIBMADM.CONTAINER_UTILIZATION

TBSP_NAME TBSP_ID CONTAINER_NAME

--------------------------------------------------------------

SYSCATSPACE 0 /db2fs/EDW/NODE0000/ EDWDATA/T0000000/C000

TEMPSPACE1 1 /db2fs/EDW/NODE0000/ EDWDATA/T0000001/C000

db2 get db config for edwdata | grep -i 'path to log files'

 设计检查。

通过代码检查，本例中一些报表和分析类应用的业务逻辑涉及的SQL语句非常复杂，而且都在客户端实现，这导致随着数据量的增加，数据库服务器对客户端应用的响应速度在变慢。

 性能优化。

找到了导致系统变慢的原因后，最后我们组合使用下面的方法成功解决了这个棘手的性能问题。

1）DB2诊断日志db2diag.log存放路径问题。解决这个问题最简单的办法就是为每个分区指定一个单独的诊断日志路径，并指定一个专门的诊断日志文件。

2）事务日志和数据分开存放，不共享磁盘。

3）从应用角度，优化引起锁等待的SQL语句。例如，对查询语句的WHERE子句进行严格的谓词过虑，而不是依赖应用来对返回的大量记录进行处理。例如使用“OPTIMIZE FOR n ROWS”，“FETCH FIRST n ROWS ONLY”，“FOR READ ONLY”，“FOR FETCH ONLY”以及“FOR UPDATE”等语句来限定返回记录数。

4）从技术角度，优化引起锁等待的SQL语句。首先使用visual Explain或者db2exfmt来分析这些SQL语句；随后使用Design Advisor工具创建合适的索引。

5）除了优化外，本问题还从设计角度进行了必要的处理。由于报表和分析应用的SQL语句非常复杂，所以将引起锁等待的这些SQL语句通过存储过程实现。这样客户端只需要调用存储过程而无须在应用中硬编码业务逻辑，从而实现了减少响应时间的目标。

2.5.7 优化总结

现在我们已经使用PAT方法学完成了对经营分析系统6个性能问题的全部解决。其中所采取的步骤完全一致，只是结果不一样，对应的优化方法不一样。表2-6列出了解决上述6个问题的结果和优化方法。

表2-6 性能优化表

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25714482/viewspace-694371/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25714482/viewspace-694371/