http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1209tunedb2/index.html

I/O 瓶颈 — 详细研究

如果 perfmon 显示有一个或多个磁盘的磁盘时间在 80% 以上，或资源监视器显示有一个或多个磁盘上的活动时间在 80% 以上，那么这通常意味着系统中存在一个 I/O 瓶颈。可以从 perfmon 或资源监视器确定具有很高利用率的一个或多个磁盘。一旦确定了大量使用的磁盘，就可以找出放置在磁盘上的内容。

是否有任何 DB2 表空间容器放置在磁盘上？

db2 list tablespace containers for <tbsid>

对数据库中的所有表空间重复此命令。

或者，DB2 日志文件是否被放置在大量使用的磁盘上？

db2 get db cfg for <dbname>

搜索 newlogpath 数据库配置参数。

或者，这些磁盘是否包含实用程序文件，比如备份目标或加载文件？查看已执行的备份/负载命令。根据大量使用的磁盘上的内容，解决方案也会有所不同。

表空间容器上的磁盘瓶颈

如果将大量使用的磁盘分配到表空间容器，那么请找出表空间中的对象。如果表空间对应于某个数据表空间，那么请找出在表空间中创建的表。

db2 select tabname from syscat.tables where tbspaceid = <hot tablespace id>

 

从 MON_GET_TABLE 表函数中找出最活跃的表。下面的查询将列出具有最多行读取的表。


清单 1. 表空间中最活跃的表

				
 
db2 "select varchar(tabschema,20) as tabschema, varchar(tabname,20) as tabname,
     table_scans, sum(rows_read) as total_rows_read, 
     sum(rows_inserted) as total_rows_inserted, 
     sum(rows_updated) as total_rows_updated, 
     sum(rows_deleted) as total_rows_deleted 
FROM TABLE(MON_GET_TABLE('','',-2)) AS t 
WHERE TBSP_ID = hot tablespace id
GROUP BY tabschema, tabname 
ORDER BY total_rows_read DESC"

 

注意：MON_GET_TABLE 函数提供了许多有用的信息。它在表上跟踪表扫描的数量。如果表扫描较多，那么可能意味着该表没有适当的索引，或者查询没有使用表上现有的索引。

一旦确定了活跃的表，就可以使用 MON_GET_PKG_CACHE_STMT 表函数轻松找出表上执行的 SQL 语句。


清单 2. 找出给定表上的查询

				
 
db2 "select section_type, executable_id, package_name,num_executions,
char(stmt_text, 100) 
from table (MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T
where stmt_text like '%hot table name%' "

 

通过这种方式，很容易找出活跃的表上导致大量读取/写入操作的 SQL 语句。使用 Design Advisor 确定 SQL 语句是否使用了表上正确的索引。如果表中有适当的指标，但没有用到这些指标，那么请检查表上的统计信息是否是最新信息。不正确或过时的统计信息可能会导致优化器选择一个次优的访问计划。

临时表空间上的磁盘瓶颈

如果将大量使用的磁盘分配到临时表空间，这表示数据库上有大量排序活动。高水平的临时表空间 I/O 活动往往与结果集较大或有大量排序的大查询一起出现。在这种情况下，需要确定是否有大量排序溢出到磁盘。 MON_GET_PKG_CACHE_STMT 可以提供造成排序溢出的 SQL 语句。


清单 3. 有大量排序活动的查询

				
 
db2 "select section_type, package_name,num_executions, total_sorts, sort_overflows,
char(stmt_text, 100) 
from table (MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T
where sort_overflows > 1 
order by sort_overflows desc"

 

如果有太多排序溢出，那么请检查 SORTHEAP 或 SHEAPTHRES 配置参数是否被设置为较低的值。如果排序的相关参数得到正确设置，那么请确定是否有可能通过创建索引来避免出现大量排序。对排序量较大的查询使用 Design Advisor，查看是否能够通过创建额外的索引来避免或减少排序。

事务日志上的磁盘瓶颈

在 OLTP 环境中，事务日志的性能是非常敏感的。MON_GET_TRANSACTION_LOG 表函数提供了有关事务日志的详细活动。


清单 4. 事务日志活动

				
 
db2 "select log_reads, log_read_time, log_write, log_write_time, num_log_buffer_full,
num_log_data_found_in_buffer 
from table(MON_GET_TRANSACTION_LOG(-1)) as T "

 

num_log_buffer_full 显示了在将新的日志写入缓冲区之前，日志缓冲区被充满并且必须刷新到磁盘的次数。如果这个数字在一段时间内一直在增加，则意味着日志缓冲区对于数据库上的工作负载而言过小。增大 LOGBUFSZ 数据库参数值有助于提高日志性能，并减少事务日志磁盘上的 I/O。

num_log_data_found_in_buffer 显示了代理从缓冲区中读取日志数据的次数。从缓冲区中读取日志数据优于从磁盘读取日志数据，因为后者较慢。可以结合使用此元素和 num_log_read_io element，以确定是否需要进一步增大 LOGBUFSZ 数据库配置参数。

回页首

步骤 3：CPU 瓶颈 — 详细研究

如果 perfmon 或资源监视器显示有一个或多个 CPU 的使用率超过 90%，那么这通常意味着系统存在 CPU 瓶颈。与 I/O 瓶颈一样，第一个步骤是识别消耗大量 CPU 的数据库操作。通常情况下，已知道有一些数据库操作会消耗大量的 CPU：

  • 语句编译
  • LOAD、BACKUP、runstats 等实用工具
  • 大量排序活动

要确定在查询编译中是否花费了大量 CPU，请查询 MON_GET_WORKLOAD 表函数。


清单 5. 在不同活动中花费的 CPU 时间

				
 
db2 "select varchar(workload_name,30) as workload_name,
sum(total_cpu_time),sum(total_compile_proc_time),
sum(act_rqsts_total), sum(total_compilations),
sum(total_act_time), sum(pkg_cache_inserts), 
sum(pkg_cache_lookups) 
from TABLE(MON_GET_WORKLOAD('',-2)) as T
group by workload_name"

 

如果 compile_proc_time 高于 5-10% 的 total_cpu_time，并且 pkg_cache_inserts/pkg_cache_lookups 高于 4-5%，则数据库在语句编译上花费了太多的时间。这可能是因为查询被重复编译，或者是因为包缓存过小，查询必须迁离到其他地方，以便为新的查询腾出空间。如果应用程序使用字符串文本而不是参数标记符，则会重复编译 SQL 语句。在 DB2 9.7 和 10.1 中，DB2 提供了一个数据库配置参数 STMT_CONC（语句集中器）。当启用该参数时，语句集中器会修改动态语句，以便允许共享更多的包缓存条目，并减少语句编译。

要找出造成高 CPU 利用率的所有实用工具，请查询 MON_GET_WORKLOAD 表函数。返回的指标展示了用于已提交的请求的所有指标的汇总，这些请求是由映射到已识别工作负载对象的连接提交的。在请求的执行过程中，指标在工作单元边界上或定期被汇总到一个工作负载。由该表函数报告的值，反映系统在最近一次汇总时的当前状态。指标是严格递增的值。为了确定在某时间间隔花费在 LOAD 和 reorg 等实用工具中的时间，可以使用 MON_GET_WORKLOAD 查询在时间间隔的开始和结束时使用的指标，并计算其差异。


清单 6. 在 LOAD 和 runstat 实用工具中花费的 CPU 时间

				
 
db2 "select varchar(workload_name,30) as workload_name,
sum(total_loads), sum(total_load_proc_time),
sum(total_runstats), sum(total_runstats_proc_time)
from TABLE(MON_GET_WORKLOAD('',-2)) as T 
group by workload_name"

 

LOAD 等实用工具旨在充分利用可用资源，最大限度地提高性能。如果这些实用工具占用了比预期多的 CPU，那么可以通过设置 util_impact_limit 数据库配置参数对实用程序进行节流。另一个占用大量 CPU 的数据库操作是 sort。正如在 I/O 瓶颈部分所介绍的，找出执行最多排序活动的查询，并使用 Design Advisor 确定是否可通过创建额外的索引来避免排序。

回页首

步骤 4：内存瓶颈

内存瓶颈并不是很常见，主要是因为数据库的堆和参数可以根据可用内存进行设置。大多数 DB2​​ 堆是自动的，并基于可用内存提供分配值。STMM 在利用可用内存和将内存分配给最需要内存的堆这两方面做得很好。但是，在不使用 STMM 的情况下，有可能存在内存使用不当的情况，如果内存分配得过多（也就是说，分配值高于可用内存），则有可能导致大量分页活动。如果 Perfmon 或资源监视器显示了许多分页活动，这通常是因为分配给不同堆的内存已超过实际内存。在这种情况下，最好是打开 STMM，让 DB2 调优缓冲池、排序堆和其他堆的内存。

回页首

步骤 5：网络瓶颈

出现网络瓶颈的原因通常是存在大量四处移动的数据（比如非常大的结果集和客户端负载等），或者操纵 LOB 的应用程序位于客户端-服务器架构中。MON_DB_SUMMARY 管理视图很好地说明了等待不同的资源所花费的时间。NETWORK_WAIT_TIME_PERCENT 字段提供了等待网络响应的时间百分比。通常情况下，等待网络所花费的时间应该小于 1%。如果该值高出几个百分点，并且 perfmon 和资源监视器显示网络带宽在大量被占用，那么系统可能遇到了网络瓶颈。在这种情况下，应用程序可以将一些应用程序逻辑以存储过程或者 UDF 的形式移动到服务器。在客户端负载的情况下，可以将负载拆分为更小的部分，在不同的时间执行它们，而不是一次全部加载它们，这样做可以减少网络流量。

回页首

步骤 6：锁定问题

如果系统没有任何资源瓶颈，但性能仍然较差，这可能是因为锁定问题。MON_DB_SUMMARY 管理视图中的 LOCK_WAIT_TIME_ PERCENT 字段提供一个高层次的视图，说明了在数据库级别的锁等待上花费的时间。为了获得在锁等待中花费了时间的工作负载的详细视图，请查询 MON_GET_WORKLOAD 监视器表函数。


清单 7. 在锁等待中花费的时间

				
 
db2 "SELECT varchar(workload_name,30) as workload_name,
sum(lock_wait_time) as total_lock_wait_time,
sum(lock_waits) as total_lock_waits,
sum(lock_timeouts) as total_lock_timeouts, 
sum(lock_escals) as total_lock_escals 
FROM TABLE(MON_GET_WORKLOAD('',-2)) AS t
GROUP BY workload_name 
ORDER BY total_lock_wait_time DESC"

 

回页首

步骤 7：调优页面清理活动

除了检查系统资源瓶颈和锁定问题，在所有数据库环境中还有另一些重要的事项需要注意。页面清理和预取是两项重要活动，需要对它们进行适当调优来获得最佳性能。在某些情况下，如果页面清理没有得到正确的调优，则有可能出现 I/O 瓶颈。监视表 MON_GET_BUFFERPOOL 提供了一些找出页面清理和预取活动的指标。


清单 8. 页面清理活动

				
 
db2 "WITH BPMETRICS AS (
SELECT bp_name,
pool_data_writes, pool_async_data_writes, 
pool_index_writes, pool_async_index_writes,
pool_no_victim_buffer, pool_lsn_gap_clns,
pool_drty_pg_steal_clns, pool_drty_pg_thrsh_clns
FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
SELECT
   VARCHAR(bp_name,20) AS bp_name,
   pool_data_writes, pool_async_data_writes,
  CASE WHEN pool_data_writes > 0
  THEN DEC((FLOAT(pool_async_data_writes) / FLOAT(pool_data_writes)) * 100,5,2)
    ELSE NULL
    END AS PAGE_CLN_RATIO,
     pool_index_writes, pool_async_index_writes,
  CASE WHEN pool_index_writes > 0
  THEN DEC((FLOAT(pool_async_index_writes) / FLOAT(pool_index_writes)) * 100,5,2)
    ELSE NULL
    END AS IND_CLN_RATIO,
      pool_no_victim_buffer, pool_lsn_gap_clns,
      pool_drty_pg_steal_clns, pool_drty_pg_thrsh_clns
    FROM BPMETRICS"

 

以上查询中的 Data 和 Index 页面清理率应该接近 100%。如果该值小于 90%，那么页面清洗的速度将无法跟上系统中脏页的增长速度。

dirty_page_steal_clns 显示了脏页面窃取情况。理想情况下，该值必须为零。如果它超过逻辑读取数量的一小部分，则需要清洁更多的页面。请降低 CHG_PGS_THRESHOLD，并确保有足够的 I/O 清洁器。

pool_no_victim_buffer 显示了代理在可用页面列表中无法找到可用页面的次数。如果这个数字超过逻辑读取几个百分点，那么它就是有害的。请降低 CHG_PGS_THRESHOLD，并确保有足够的 I/O 清洁器。

回页首

步骤 8：调优预取活动

类似于页面清理，我们还需要调优预取活动。在一个真正的 OLTP 环境中，预取可能没有用。但在 DSS 类的工作负载中，预取发挥着重要的作用。在理想的情况下，我们希望 IO_SERVERS IO_SERVERS（预取器）负责所有读取，该操作实质上是异步进行的。下面的查询显示了由 IO_SERVERS 完成的 I/O 读取百分比。


清单 9. 预取活动

				
 
db2 "WITH BPMETRICS AS (
    SELECT bp_name,
      pool_data_p_reads, pool_async_data_reads,
      pool_temp_data_p_reads,
      pool_index_p_reads, pool_async_index_reads
    FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
    SELECT
      VARCHAR(bp_name,20) AS bp_name,
      pool_data_p_reads, pool_async_data_reads,
    CASE WHEN pool_data_p_reads > 0
    THEN DEC((FLOAT(pool_async_data_reads) / FLOAT(pool_data_p_reads + 
                POOL_TEMP_DATA_P_READS)) * 100,5,2)
    ELSE NULL
    END AS PREFETCH_RATIO,
      pool_index_p_reads, pool_async_index_reads,
    CASE WHEN pool_index_p_reads > 0
    THEN DEC((FLOAT(pool_async_index_reads) / FLOAT(pool_index_p_reads)) * 100,5,2)
    ELSE NULL
    END AS PREFETCH_IDX_RATIO
FROM BPMETRICS"

 

大于 90% 的值对于 PREFETCH_RATIO 而言是适合的值。