调优 DB2 实现高性能：案例研究

狐狸情

　　简介
　　设计不良的系统可能会在上线之后很快出现性能问题。即便经过良好调优的系统在长时间的操作或重大功能变更之后也会遇到性能问题。调优系统是系 统管理员不可逃避的任务。作为大多数应用系统的一个重要部分，数据库性能调优是此任务中的一个重要方面。统计数据显示，数据库调优可使从未调优过的系统实 现 20% 的性能提升但是，如果未能合理地执行调优，则会给生产系统带来风险。这篇文章将展示 IBM DB2 for Linux, UNIX, and Windows 环境中的数据库性能调优的实际案例研究。
　　本案例研究中调优的系统是一个基于 JIRA Enterprise 包的工作流应用程序，它使用 DB2 作为后端数据库。应用程序使用两种操作模式：夜间批处理模式和日间 OLTP 模式。在夜间批处理时间中，将执行一系列的 shell 脚本，以便将外部数据（采用纯文本文件的形式）传输给数据库。在日间 OLTP 时间中，操作人员按照 JIRA 中定义的工作流处理这种业务数据。
　　在应用程序运行大约一年之后，客户并未看到问题事故率的显著下降。调查表明，其中某些事故是由性能问题引起的，例如数据库死锁和 JIRA 文件锁定超时。根据合同，每年约有 5% 的工作负载增加。如果系统性能未得到改进，则未来会出现更多与性能相关的事故。性能调优势在必行。
　　发现问题
　　系统性能调优的前提任务就是找到性能问题源自何处。Nigel 的 Linux 性能监视器（NMON）就是一种收集关键性能数据的出色工具，例如 CPU 利用率、内存利用率、磁盘忙率、顶级进程等。NMON 用于为系统内的各服务器收集性能信息。
　　查看了所收集的 NMON 数据之后，确定了两个性能问题：

• 在夜间批处理时间中，数据库服务器的 CPU 利用率在至少 1 个小时的时间内保持 80%。
  
• 数据库服务器的某些磁盘会在一天内定期转为 100% 忙。
  

　　常规数据库性能调优包含以下阶段：

• 收集数据库服务器信息
  
• 收集数据库使用信息
  
• 分析数据库信息
  
• 设计调优活动
  
• 实现和评估调优结果
  

　　以下几节详细讨论了各个阶段。
回页首
　　收集数据库服务器信息
　　在此阶段中，您要收集数据库服务器的硬件和软件信息以及数据库的配置。下面给出了您需要收集的一些信息：

• 数据库服务器的类型
  
• CPU 的数量和类型
  
• 内存数量
  
• 磁盘驱动器的数量和制造商
  
• 存储子系统的类型和制造商
  
• 存储子系统的配置
  
• 操作系统和数据库信息
  
• db2look 工具对于服务器中运行的各个实例的输出（db2look –d dbname –e –o outputfile ）
  
• 各表空间及其容器的描述（db2 list tablespaces 和 db2 list tablespace containers for tablespacename show details ）
  

　　请记住，信息越是丰富，能提供的帮助就越多。您在这里收集的任何信息都将在稍后的分析中提供很大的帮助。例如，在本案例研究中，db2look 和表空间信息解释了磁盘忙为何会成为问题：所有用户数据表都是在相同的表空间上创建的，位于相同的磁盘中。
回页首
　　收集数据库使用信息
　　收集数据库使用信息的方法有两种：获取快照和监视事件。两种方法都会收集实时数据库使用信息，例如缓冲池活动、锁定状态、SQL 语句信息等。然而，它们都有着不同的监视机制，也就是说可以在不同的场景中使用。
　　正如名称所表示的那样，快照捕捉数据库在特定时间点的即时信息。按照指定间隔获取的快照可用于观察趋势或者预测潜在的问题。它们对于排查已知时间段内发生的问题或者即席 数据库健康检查极有帮助。利用快照时比使用事件监视器时消耗的资源更少。
　　另一方面，事件监视器是事件驱动的。在预先定义的时间段内，事件监视器可在发生指定时间时创建记录。与快照相比，事件监视器可以提供更多基于 数据库对象的统计信息（例如，数据库、表、表空间等对象的统计信息）。监视是连续的，因此它会收集所监视时间段内的整体数据库使用情况。由于连续性，在目 标系统极为繁忙时，所消耗的资源数量可能非常惊人。您应尽力避免在调查生产系统时因监视而导致系统受损。
　　在探索如何降低监视的性能影响之前，应观察设置事件监视器时的选项：表事件监视器、文件事件监视器和管道事件监视器。正如其名称所表示的那 样，各种事件监视器之间的差别在于在何处创建事件记录：SQL 表、文件或通过指定管道创建。由于管道事件监视器在实践中并不常用（需要使用一个程序从指定管道中读取数据），因此本文仅关注表和文件事件监视器。
　　
表 1. 降低事件监视器的性能影响的提示


类别
选项
使用方法


表和文件提示
Eventtype
CREATE EVENT MONITOR emon1 FOR STATEMENTS


STATEMENTS 监视器是最大的性能威胁。如果关注性能，那么就应该将 STATEMENTS 监视器与其他监视器分离开来，使其自成一体。


Buffersize
CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE BUFFERSIZE 8


为 了减少表插入或者文件写入操作的开销，事件记录首先将写入一个缓冲区空间。在缓冲区空间充满时，事件记录随之移入事件表或文件。出于性能方面的原因，高度 活跃的事件监视器应比相对来说活跃度较低的监视器具有更大的缓冲区。BUFFERSIZE 表示缓冲区空间的容量（以 4K 页面为单位）。由于空间是从数据库监视器堆中分配的，因此所有事件监视器的合并容量不应超过最大大小（使用 db2 get dbm cfg | grep MON_HEAP_SZ 来确定这个值）。


Blocked/Nonblocked
CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE BLOCKED/NONBLOCKED


如 果设置了 BLOCKED，则在事件记录从缓冲区空间移动到表/文件时（即缓冲区空间已满时），生成事件的每个代理都会等待移动完成。通过这样的方式即可保证不丢失 任何事件数据。然而，这种方式也会降低数据库性能。因此，在关注性能时，事件监视器应设置为 NONBLOCKED。此时将会存在数据丢失的现象，但对数据库性能的影响可降低到最低限度。


特定于表的提示
逻辑数据组监视器元素
CREATE EVENT MONITOR emon1 FOR DEADLOCKS WITH DETAILS WRITE TO TABLE DLCONN (EXCLUDES(agent_id, lock_wait_start_time)), DLLOCK (INCLUDES(lock_mode, table_name))


每个事件监视器都使用多个数据库表来存储所收集到的数据。举例来 说，STATEMENTS 事件监视器收集语句数据并将其存储在表中：CONNHEADER、STMT、SUBSECTION 和 CONTROL。通过避免收集不必要的事件表和字段，对性能的影响即可降低到最低限度。


表空间
CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE CONN (TABLE conns, IN mytablespace)


在磁盘忙成为性能瓶颈问题时，应将事件表放置在独立的表空间和独立的磁盘中，以便使磁盘写入操作更加平均地分布。


PCTDEACTIVATE
CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE CONN PCTDEACTIVATE 90


PCTDEACTIVATE 选项用于控制事件监视器的存储占用。它定义为一个百分比数字。举例来说，如果 PCTDEACTIVATE 设置为 90，在事件表所在的表空间的占用容量达到 90% 时，事件监视器将自动禁用。这个选项仅可用于数据库托管表空间（DMS）。


特定于文件的提示
Maxfiles/Maxfilesize
EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO FILE myfile MAXFILES 10 MAXFILESIZE 32


与 PCTDEACTIVATE 选项相似，MAXFILES 和 MAXFILESIZE 可共同用于控制事件监视器有权使用多少存储空间。MAXFILESZIE 定义单独一个事件监视器文件可以包含的 4K 页面的数量。在达到最大数量时，即创建一个新文件来存储传入的事件数据。这种处理方式将一直继续到文件数量达到预先定义的 MAXFILES 值，此时事件监视器将被自动禁用。
　　通过应用以下两种方法，即可进一步降低影响生产性能的风险：

• 在生产环境中执行事件监视之前，应在测试环境中执行测试，或者在生产环境中执行短期的试运行，以便评估实际性能影响。
  
• 为各性能指标设定一个阈值（例如，CPU 利用率：90%），并在监视期间密切监视这些指标。在超出阈值时立即停止监视。
  

回页首
　　分析数据库信息
　　收集了所有信息之后，即可执行本节中介绍的各种分析。
　　SQL 语句分析
　　SQL 语句分析的主要信息资源是语句事件监视器。如果事件是使用事件文件监视的，即可使用 db2evmon 来格式化输出，如 清单 1 所示。
　　
清单 1. db2evmon 命令

db2evmon –path event_files_directory > output_filename

　　结果项如 清单 2 所示。
　　
清单 2. 语句事件项示例

1) Statement Event ... Appl Handle: 53793 Appl Id: *LOCAL.db2inst1.101126060601 Appl Seq number: 00003 Record is the result of a flush: FALSE ------------------------------------------- Type     : Dynamic Operation: Describe Section  : 201 Creator  : NULLID Package  : SQLC2G15 Consistency Token  : AAAAALIY Package Version ID  : Cursor   : SQLCUR201 Cursor was blocking: TRUE Text     : select * from schema.table ------------------------------------------- Start Time: 11/26/2010 15:06:35.641755 Stop Time:  11/26/2010 15:06:35.665380 Elapsed Execution Time:  0.023625 seconds Number of Agents created: 1 User CPU: 0.003768 seconds System CPU: 0.000000 seconds Statistic fabrication time (milliseconds): 0 Synchronous runstats time  (milliseconds): 0 Fetch Count: 62 Sorts: 0 Total sort time: 0 Sort overflows: 0 Rows read: 62 Rows written: 0 Internal rows deleted: 0 Internal rows updated: 0 Internal rows inserted: 0 Bufferpool data logical reads: 1 Bufferpool data physical reads: 0 Bufferpool temporary data logical reads: 0 Bufferpool temporary data physical reads: 0 Bufferpool index logical reads: 0 Bufferpool index physical reads: 0 Bufferpool temporary index logical reads: 0 Bufferpool temporary index physical reads: 0 Bufferpool xda logical page reads: 0 Bufferpool xda physical page reads: 0 Bufferpool temporary xda logical page reads: 0 Bufferpool temporary xda physical page reads: 0 SQLCA: sqlcode: 0 sqlstate: 00000

　　Text 行显示了所执行的 SQL 语句。Elapsed Execution Time 表明执行此 SQL 语句所耗费的时间。可以通过加总相同语句的所有执行耗时来计算各个 SQL 语句的总执行时间。随后，总执行时间最长的语句将成为 SQL 语句分析的候选项。
　　IBM 提供了一系列工具来分析 SQL 语句。Visual Explain、db2exfmt 和 db2expln 对于检查各语句的访问计划是非常有用的。db2advis 工具提供了是否需要新索引来优化执行性能的建议。
　　死锁分析
　　死锁事件监视器提供了有关死锁发生原因和所发生的各死锁的历史记录的详细信息。清单 3 展示了一个示例死锁事件项。
　　
清单 3. 死锁事件项示例

3382) Deadlocked Connection ... Deadlock ID:   1 Participant no.: 2 Participant no. holding the lock: 1 Appl Id: 10.207.4.51.40897.100826202041 Appl Seq number: 03988 Tpmon Client Workstation: server01 Appl Id of connection holding the lock: 10.207.4.51.39361.100826202035 Seq. no. of connection holding the lock: 00001 Lock wait start time: 08/27/2010 10:38:13.168058 Lock Name       : 0x020012032900E9161100000052 Lock Attributes : 0x00000000 Release Flags   : 0x20000000 Lock Count      : 1 Hold Count      : 0 Current Mode    : none Deadlock detection time: 08/27/2010 10:38:22.765817 Table of lock waited on      : table Schema of lock waited on     : schema Data partition id for table  : 0 Tablespace of lock waited on : USERSPACE1 Type of lock: Row Mode of lock: X   - Exclusive Mode application requested on lock: U   - Update Node lock occured on: 0 Lock object name: 73398812713 Application Handle: 957 Deadlocked Statement: Type     : Dynamic Operation: Fetch Section  : 1 Creator  : NULLID Package  : SYSSH200 Cursor   : SQL_CURSH200C1 Cursor was blocking: FALSE Text     : SELECT value1, value2 FROM schema.table WHERE value1 = ? for update with rs List of Locks: …… Lock Name                   : 0x020012032900EC161100000052 Lock Attributes             : 0x00000000 Release Flags               : 0x00000080 Lock Count                  : 1 Hold Count                  : 0 Lock Object Name            : 73399009321 Object Type                 : Row Tablespace Name             : table Table Schema                : schema      Table Name                  : EXCLUSION Data partition id           : 0 Mode                        : U   - Update …… 13384) Deadlocked Connection ... Deadlock ID:   1 Participant no.: 1 Participant no. holding the lock: 2 Appl Id: 10.207.4.51.39361.100826202035 Appl Seq number: 09195 Tpmon Client Workstation: server01 Appl Id of connection holding the lock: 10.207.4.51.40897.100826202041 Seq. no. of connection holding the lock: 00001 Lock wait start time: 08/27/2010 10:38:13.166513 Lock Name       : 0x020012032900EC161100000052 Lock Attributes : 0x00000000 Release Flags   : 0x40000000 Lock Count      : 1 Hold Count      : 0 Current Mode    : none Deadlock detection time: 08/27/2010 10:38:22.787777 Table of lock waited on      : table Schema of lock waited on     : schema Data partition id for table  : 0 Tablespace of lock waited on : USERSPACE1 Type of lock: Row Mode of lock: U   - Update Mode application requested on lock: U   - Update Node lock occured on: 0 Lock object name: 73399009321 Application Handle: 951 Deadlocked Statement: Type     : Dynamic Operation: Execute Section  : 1 Creator  : NULLID Package  : SYSSH200 Cursor   : SQL_CURSH200C1 Cursor was blocking: FALSE Text     : UPDATE schema.table SET value2 = ?, value3 = ? WHERE value1 IN (?,?) List of Locks: Lock Name                   : 0x020012032900E9161100000052 Lock Attributes             : 0x00000000 Release Flags               : 0x40000000 Lock Count                  : 1 Hold Count                  : 0 Lock Object Name            : 73398812713 Object Type                 : Row Tablespace Name             : USERSPACE1 Table Schema                : schema      Table Name                  : table ……

　　清单 3 展示了死锁中涉及到了哪两个锁定、各锁定的类型以及对应的 SQL 语句。通过修改相关语句，即可减少死锁的出现。
　　缓冲池分析
　　您可以利用缓冲池事件监视器提供的信息来执行缓冲池分析，如 清单 4 所示。
　　
清单 4. 缓冲池事件项示例

3) Bufferpool Event ... Bufferpool Name: IBMDEFAULTBP Database Name: database    Database Path: /shared/dbg/db2inst3/db2inst3/NODE0000/SQL00001/ Buffer Pool Statistics: Buffer pool data logical page reads: 14871152 Buffer pool data physical page reads: 1699818 Buffer pool data page writes: 53823 Buffer pool index logical page reads: 8606405 Buffer pool index physical page reads: 290822 Buffer pool index page writes: 272282 Buffer pool xda logical page reads: 0 Buffer pool xda physical page reads: 0 Buffer pool xda page writes: 0 Buffer pool read time (milliseconds): 1536574 Buffer pool write time (milliseconds): 353641 Files closed: 0 Buffer pool asynch data page reads: 1694131 Buffer pool asynch data page read reqs: 59110 Buffer pool asynch data page writes: 53371 Buffer pool asynch index page reads: 227455 Buffer pool asynch index page read reqs: 8527 Buffer pool asynch index page writes: 270292 Buffer pool asynch xda page reads: 0 Buffer pool asynch xda page read reqs: 0 Buffer pool asynch xda writes: 0 Buffer pool asynch read time: 1327887 Buffer pool asynch write time: 347809 No victim buffers available: 1509238 Unread prefetch pages: 2995 Direct I/O Statistics: Sectors read directly: 13610 Sectors written directly: 1695616 Direct read requests: 1382 Direct write requests: 3763 Direct read time: 3758 Direct write time: 22236 Vectored IOs: 67407 Pages from vectored IOs: 1921234 Block IOs: 0 Pages from block IOs: 0

　　可以利用 清单 5 中的公式大致地计算缓冲池的效率。
　　
清单 5. 计算缓冲池效率的公式

1 – (Bufferpool data logical page reads + Bufferpool index logical page reads) divided by (Bufferpool data physical page reads + Bufferpool index physical page reads)

　　如果计算得出的数量低于 90%，则增加缓冲池的大小将是一种合理的调优选项。
　　内存分析
　　数据库事件监视器提供的信息可用于进行内存分析，如 清单 6 所示。
　　
清单 6. 内存事件项示例

3) Database Event Record is the result of a flush: FALSE Lock Statistics: Lock Waits: 0 Total time waited on locks (milliseconds): 0 Deadlocks: 0 Lock escalations:  0 X lock escalations:  0 Lock timeouts:  0 Sort Statistics: Sorts: 844 Total sort time (milliseconds): 160043 Sort overflows: 80 Sort share heap high water mark: 9851 Post Shared Memory Threshold Sorts: 20 Hash Statistics: Hash Joins: 25 Hash Loops: 0 Hash Join Small Overflows: 0 Hash Join Overflows: 0 Post Shared Memory Threshold Hash Joins: 0 …… Node Number: 0 Memory Pool Type:  Backup/Restore/Util Heap Current size (bytes): 65536 High water mark (bytes): 196608 Configured size (bytes): 319815680

　　如果输出中包含过多锁升级或者 X 锁升级，则可能表明某个 LOCKLIST 内存分配不足。较高的排序溢出率（排序溢出除以排序数量）或者较高的散列连接溢出率（（散列连接小溢出 + 散列连接溢出）/ 散列连接数量）表示未为 SORTHEAP 分配足够的内存。如果内存高位接近所配置的大小，则表示所分配的内存大小过小。
　　表空间与表分析
　　表空间和表事件监视器信息可用于确定哪个表空间或者表最常被访问，如 清单 7 所示。
　　
清单 7. 表空间/表事件项示例

5) Tablespace Event ... Tablespace Name: USERSPACE1 Record is the result of a flush: FALSE File System Caching: Yes Buffer Pool Statistics: Buffer pool data logical page reads: 14846454 Buffer pool data physical page reads: 1699227 Buffer pool data page writes: 31111 Buffer pool index logical page reads: 8593610 Buffer pool index physical page reads: 290381 Buffer pool index page writes: 272125 Buffer pool xda logical page reads: 0 Buffer pool xda physical page reads: 0 Buffer pool xda page writes: 0 Buffer pool read time (milliseconds): 1529939 Buffer pool write time (milliseconds): 350770 Files closed: 0 Buffer pool asynch data page reads: 1693042 Buffer pool asynch data page read reqs: 58409 Buffer pool asynch data page writes: 30761 Buffer pool asynch index page reads: 227412 Buffer pool asynch index page read reqs: 8489 Buffer pool asynch index page writes: 270137 Buffer pool asynch xda page reads: 0 Buffer pool asynch xda page read reqs: 0 Buffer pool asynch xda writes: 0 Buffer pool asynch read time: 1325077 Buffer pool asynch write time: 345169 No victim buffers available: 1435565 Unread prefetch pages: 2982 Direct I/O Statistics: Sectors read directly: 3488 Sectors written directly: 1695176 Direct read requests: 436 Direct write requests: 3752 Direct read time: 476 Direct write time: 22217 4) Table Event ... Table schema: SCHEMA Table name: TEMP (00001,00002) Data partition id: 0 Record is the result of a flush: FALSE Table type: Temporary Data object pages: 1 Index object pages: 0 Lob object pages: 0 Long object pages: 0 Rows read: 3 Rows written: 1 Overflow Accesses: 0 Page reorgs: 0 Tablespace id: 1

　　读取/写入数量表示表空间或者表的繁忙程度。如果最常被访问的表与其他表位于相同的磁盘中，则最好将其重新放置在不同的磁盘中，以便更加平均地分散磁盘读取和写入操作。另外一种解决方案是在多个物理磁盘上分散表中的数据。
回页首
　　设计调优活动
　　您可以根据所收集到的全部信息，设计实际调优活动。然而，每项调优活动都有着一定的风险和成本。在决定实现解决方案之前，必须进行谨慎的风险 和 ROI 分析。分析结果可能将调优活动分为以下类别：立即实现、有条件地实现或不实现。对于本案例研究，我们制作了 表 2 来帮助制定调优决策。
　　
表 2. 调优决策表


调优活动
性能改进
风险
ROI
决策
条件


添加新索引
低
低
低
立即
无


升级 CPU
中
低
中
有条件地
峰值 CPU 利用率达到 90%


重新分配数据库表
高
高
中
有条件地
观察到较高的 CPU 等待 I/O

回页首
　　实现和评估调优结果
　　测试了调优活动之后，即可将其部署到生产环境之中。为了评估调优结果，您可以再次使用 NMON 来评估调优实现了怎样的性能改进。
　　在这个案例研究中，在向利益相关者展示了调优结果之后，利益相关者仅选择了添加新索引 选项。另外一个选项未为利益相关者提供合理的 ROI。他们还认为其他选项要么成本过高、要么风险过高。利益相关者希望仅在绝对必要的情况下实施这些举措。
　　我们的案例研究关注仅调优对于改进有价值的 SQL 语句，而非调优全部 SQL 语句。调优的目标设定为总执行时间超过 60 秒的 SQL 语句。对于这些目标 SQL 语句运行 db2advis 得到了如 清单 8 所示的结果。
　　
清单 8. 示例 db2advis 输出

Your SQL Statement execution started at timestamp 2011-04-06-11.02.28.049293 Recommending indexes... total disk space needed for initial set [   0.134] MB total disk space constrained to         [  67.464] MB Trying variations of the solution set. Optimization finished. 3  indexes in current solution [ 16.9089] timerons  (without recommendations) [  7.5935] timerons  (with current solution) [55.09%] improvement -- -- LIST OF RECOMMENDED INDEXES -- =========================== -- index[1],    0.134MB CREATE INDEX "SCHEMA "."IDX107060204130000" ON "SCHEMA"."TABLE1" ("FIELD1" ASC, "FIELD2" ASC, "FIELD3" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS; COMMIT WORK ; -- -- RECOMMENDED EXISTING INDEXES -- ============================ -- RUNSTATS ON TABLE "SCHEMA"."TABLE1" FOR SAMPLED DETAILED INDEX INDEX1 ; -- COMMIT WORK ; -- -- UNUSED EXISTING INDEXES -- ============================ -- DROP INDEX INDEX2; -- =========================== 13 solutions were evaluated by the advisor DB2 Workload Performance Advisor tool is finished.

　　表 4 对比了初始的 db2advis 结果与调优后的结果。
　　
表 3. db2advis 结果


数据库
SQL 语句
执行时间（秒）
改进（百分比）
节约的时间（秒）


DB1
SQL1
188
0.00%
0


SQL2
60
0.00%
0


DB2
SQL1
421
0.00%
0


SQL2
236
55.09%
130


SQL3
153
3.45%
5


SQL4
63
49.94%
31


SQL5
62
0.00%
0


DB3
SQL1
1222
13.45%
164


SQL2
365
0.00%
0


SQL3
355
1.42%
5


SQL4
354
1.42%
5


SQL5
94
49.96%
47


SQL6
92
19.95%
18


SQL7
83
0.00%
0


SQL8
67
0.00%
0
　　根据 db2advis 工具提供的结果，实现了能够节约最多时间的四项 SQL 调优活动。随后执行了第二次 NMON 分析，评估调优结果。与预期效果相同，峰值 CPU 利用率未显著降低。然而，峰值时间从大约 55 分钟缩短到 50 分钟以内。利益相关者对于这样的结果非常满意。
　　当然，谨慎的做法是连续监视 CPU 利用率和 CPU 等待 I/O 数据。在这些数字达到预先定义的阈值之后，案例研究中将采取进一步的举措。
回页首
　　结束语
　　这篇文章描述了一种研究 DB2 for Linux, UNIX, and Windows 数据库中的性能问题以及在保证生产系统最低风险的前提下实现可能带来最优成果的改进的方法。