内容


IBM InfoSphere Federation Server 性能问题的一般解决方法

Comments
免费下载:IBM® DB2® Express-C 9.7.2 免费版 或者 DB2® 9.7 for Linux®, UNIX®, and Windows® 试用版
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

IFS 性能问题的特点

IBM InfoSphere Federation Server (IFS) 是一款功能强大的联邦数据库产品。在企业里面,它一般被用于对大量生产数据的集成。由于要集成大批量的数据,又和关键的生产系统相互影响,它的性能问题显得至关重要。这些性能问题和一般的数据库性能问题有相通之处,同时又有其作为联邦数据库的特点:

  1. IFS 执行查询过程中,通过向远处服务器发出 SQL 语句,来访问远程数据,并将结果取到本地,来完成查询。所以,远程服务器执行这些 SQL 的性能以及将结果取到本地的时间直接影响整个查询的性能。
  2. 对于从远程取到本地的数据以及查询中的本地表,IFS 有可能在本地进行扫描,连接,排序,分组等操作。所以本地数据库的各种参数的设置和瓶颈分析也是解决性能问题的重要方面。
  3. IFS 优化器需要根据远程表(即 Nickname)的信息,查询的中间结果大小等情况,在保证语义正确的情况下,决定将查询中的哪部分放到远程执行,哪部分放在本地,并在查询计划中体现出来。这决定了与远程服务器交互的数据量和次数,在查询性能中至关重要。

基于上述特点和实践,可以形成如图 1 所示的解决问题的思路:

图 1. 解决 IFS 性能问题的思路
解决 IFS 性能问题的思路
解决 IFS 性能问题的思路

为了使读者掌握利用这个思路进行性能问题分析的方法,下面的章节介绍了工具使用,解释了分析步骤和解决办法,并详解了几个有代表性的例子。文中提到的一些 IFS 特有的概念,像 Nickname,Wrapper,Function Mapping,Server Option,可以在 参考资料找到详细的定义,这里不作赘述。

性能问题分析工具

工欲善其工必先利其器。分析 IFS 性能问题主要用到两种工具:性能监视工具和计划查看工具。前者用于分析 SQL 语句运行的基本数据,以及系统资源的使用状况。后者用于查看 SQL 的执行环境,统计信息和执行方式等。

监视工具

使用 DB2 监视器,主要是 Snapshot,可以看到 SQL 的执行时间和次数,读取的记录数等信息。使用下面的命令可以开启和取出 Snapshot:

清单 1. 使用快照的命令
    db2 update monitor switches using statement on 
    db2 get snapshot for dynamic sql on test

这不仅可以得到用户直接输入的 SQL 的数据,还可以看到 IFS 发到远程执行服务器的 SQL 的完成时间和取回来的结果大小。值得关注的几个数据有“Total execution time”,“Number of executions”,“Rows selected”等

除此以外,操作系统的监视工具有时候也非常有用。比如 vmstat,netstat,top 等。在 AIX 上可以使用 topas 看到每个 CPU 的负载情况,网络流量情况等。

计划查看工具

db2exfmt 是我们分析查询计划的最重要的工具。使用它可以得到查询计划 (Query Plan) 的详细信息,包括 SQL 的运行环境(包括 SORT HEAP SIZE,BUFFER POOL SIZE,CPU SPEED),数据库的并行设置,用户输入的原始 SQL 和查询优化器器对 SQL 进行逻辑优化后的结果 (Optimized Statement)。你能看到整个计划树,包括中间结果的大小,IO 数,总代价。还有每个计划节点的详细信息,以及发到远程执行的语句 (Remote Statement,出现在 SHIP 和 Return 节点 )。最后是关于 Nickname 和本地表以及索引,字段的信息。所有这些信息对于我们分析判断性能问题的根源是必不可少的。可以用下面的命令方便的得到一个 SQL 的查询计划

清单 2. 获取查询计划
    explain plan for sql_statement 
    db2exfmt -d db_name -1 -o out_file

使用这些简单易用的工具,就可以我们就可以开始解决问题了。

性能问题的分析与解决

本地表和 Nickname 的查询性能

本地表查询慢

当系统出现性能问题时,一般会表现为吞吐量下降,某个或某些 SQL 执行慢。针对某个具体的 SQL,我们可以按照图 1 的思路,从上向下一步步分析。另一方面,也可以从可能性比较大的方面入手,比如当某个 SQL 很慢而其他的都正常时,查询计划出问题的可能性就比较大,可以直接从查看查询计划入手。

Nickname 查询慢

这里我们要确定性能问题是不是由于远程服务器执行远程语句过慢造成的(从 db2exfmt 工具输出的计划中的 Remote statement 可以直接得到 IFS 发到远程执行的查询)。这个远程语句的执行和数据返回需要经历从远程服务器到 IFS 的下面几个层次:远程数据源服务器 -> 网络 -> 数据源在本地的客户端 ->IFS-> 应用程序。这里面每个层次都可能成为瓶颈。我们通过各个层次逐次排查:

  1. 在远程服务器上直接执行远程语句(即 Remote Statement)并记录时间。如果时间过长,则需要对此语句在远程服务器上进行调优,或者通过后面“查询计划”一节提到的方法,改变下推到此服务器的语句。
  2. 利用远程服务器在本地的客户端执行远程语句。必要的时候可以编写简单的应用程序连接客户端进行测试。如果这样就能重现问题,说明此性能问题跟 IFS 没有直接关系,可能是网络状况,所使用的客户端 API(比如 ODBC 驱动器),客户端的配置或者远程服务器本身的问题引起的。这时应该联系远程服务器产品的提供商解决问题。
  3. 用 IFS 在 PASSTHRU 模式下面执行。这样我们避免了 IFS 里面对查询语句的优化过程,跟直接在远程服务器的客户端运行非常相似。如果存在性能问题,可能是 Wrapper 的配置,网络状况,客户端 API 等引起的。如果已经尝试上述第 1,2 步,排除了远程服务器本身以及网络的问题,则应该联系 IBM 的客户支持人员来解决。
  4. 在 IFS 上直接运行原来的查询语句。如果原来的查询含有参数(即 Host Variable 或者 Parameter Marker),则应该尝试把它拿出来,为所有的参数赋上常数值,直接在 IFS 上执行。如果能重现问题,则可能问题是由于系统瓶颈,或者查询计划引起的。可以运用下面的方法继续向下分析。
  5. 在应用程序中运行查询。如果只在应用程序中才能重现性能问题,则要检查应用程序本身的连接 IFS 的方式,使用的隔离级别等。对于含有参数的查询,如果在第 4 步无法重现问题,而在应用程序层次能重现,则可能 IFS 在查询含有参数(而不是常数)时生成了特殊的低效查询计划,因此需要运用后面的方法检查含有参数时生成的计划。

通过上面的排查,排除了远程服务器和应用程序方面的问题后,我们需要将目光放在系统瓶颈和查询计划上来。

系统瓶颈

CPU 瓶颈

如果使用 vmstat 或者 topas 发现 CPU 使用率很高(90% 以上),说明 CPU 可能已经成为瓶颈。可以试试能否使用下面的方法改进:

  1. 增加并行性:如果是单个 CPU 的使用率很高,而其他 CPU 使用率不高,可以通过增加并行性,使各个 CPU 的负载均衡。具体可以用多个用户或者连接并行查询。因为每个用户连接对应一个远程连接,多个连接意味着多个与远程服务器的连接,增加了效率。
  2. 避免 Codepage 转换。当远程数据库的数据和本地数据库不一致时,会发生 Codepage 转换,并耗费 CPU。所以应该尽量避免这种情况,比如将本地数据库创建为和远程数据源一样的 Codepage。
  3. 避免从远程获取过多的记录。当从远处获取过多的记录,CPU 和网络都会忙于处理这些记录,造成瓶颈。我们应当在发到远程的语句中尽量多的增加谓词,减少记录数。请参看“查询计划”一节进行处理。
  4. 使用 Fetch First N Rows。如果并不是所有的结果都是我们实际需要的,则应该使用 Fetch First N Rows 子句对结果集进行限制。这样减少了 CPU 需要处理的记录数和从远处获取的记录数。

Memory 瓶颈

IFS 环境下,如果查询不涉及本地表,Buffer Pool 基本上不会对性能提高起太大作用。而 Sort Heap 在对远程来的数据进行排序,散列连接和合并连接时会被使用。所以如果应用程序需要大量访问远程表,且没有使用本地表,应该尽可能调大 Sort Heap 而减少 Buffer Pool 的大小。

网络瓶颈

可以通过 topas 或者 netstat 查看当前的网络输入输出情况。利用简单的语句,比如”select * from nickname” 可以看出 IFS 从远程取数据的速度。这方面应该注意下面的因素:

  1. 可以通过 topas 或者 netstat 查看当前的网络输入输出情况。利用简单的语句,比如”select * from nickname” 可以看出 IFS 从远程取数据的速度。这方面应该注意下面的因素:
  2. 与远程交互的通信缓存区大小:参数 RQRIOBLK 决定了 IFS 和远程 DRDA 服务器(包括 DB2 LUW,DB2/z,AS400 等)交互时使用的缓冲区大小。如果这个参数设的太小,会造成从远程取批量数据时产生过多的中断,这对于低带宽的环境(比如 WLAN)下的性能有比较大的影响。这时应该使用下面的命令增大这个参数:
    清单 3. 修改网络通信缓冲区大小
        db2 update dbm cfg using RQRIOBLK 65535
  3. 网络负载过大也可能是因为查询计划不好,不必要的从远程数据库取过多数据造成,这就需要用“查询计划”一节的方法调优查询计划了。
  4. 调优客户端。远程服务器的客户端(比如 ODBC 的驱动器)可能有些参数与性能相关。通过改变这些参数,可以改善 IFS 通过客户端与远程服务器交互的性能。请参见各个服务器客户端提供商的手册。

大部分情况下,调优系统方面并不能完全解决问题,问题的根源很可能在查询计划方面。

查询计划

查询计划是 IFS 执行查询的方式。由于每个查询都有很多种可能的执行方式,IFS 的优化器要根据远程服务器的能力,Nickname 的统计信息(记录数,每个字段的值的情况)和代价模型得出最优的计划等信息,得出最优的查询计划。查询计划的问题主要来自于:

  1. 查询没有被下推。把查询中的一部分(包括谓词,连接,排序,分组等)或者全部发送到远程执行,称为“下推”(Pushdown)。在很多情况下,如果查询被下推,性能就会比较好;而如果某一部分没有下推,则可能造成从远程取过多的记录,形成性能瓶颈。所以查询是否下推成为 IFS 性能的一个中心问题。可以通过下面方法确定是否有查询的某些部分没有下推。
    • 查看计划树。当查询涉及 Nickname,计划树中会出现名为 SHIP 的节点(对于非关系型数据源会对应 RPD 节点)。每个 SHIP 节点代表着 IFS 要发送给远程服务器一个远程查询语句 ( 即 Remote Statement),然后把结果取回来。SHIP 之上的操作都是在本地执行的。如果 SHIP 上面直接跟着 FILTER,SORT,或 GROUP,意味着有些谓词,排序,分组等操作放在了本地而没有发到远程执行。如果一个连接操作的两个输入表(OUTER 表和 INNER 表)都来自于 SHIP,说明这个连接是先将数据拿到本地,然后做连接,意味着连接没有下推。
    • 查看查询计划中的 Remote Statement,并与 Optimized Statement 对比。从查询计划中的 Optimized Statement 可以看到经过逻辑优化后每个 Nickname 上有哪些谓词。从 Remote Statement 语句可以看出哪些谓词实际被下推到了远程执行。两相对比,就能看出哪些谓词没有被下推。

    为了保证下推,应该做到:

    • IFS 准确知道远程数据服务器的能力,即支持的语法,函数等等。IFS 做决定时首先考虑正确性,保证放到远程执行的操作都是远程支持的,不支持的操作不会发到远程。必要时,我们需要定义 Function Mapping 和设定 Server Options,告诉 IFS 远程服务器的对各种语法和函数的支持能力。
    • 在 IFS 能准确知道远程服务器的能力后,为了能做出正确的决策,Nickname 上的统计信息应该准确和完整。
    • 在某些情况下,在完善了 Nickname 的统计信息后,IFS 仍然没有给出我们想要的计划(比如一个将查询的所有部分都放到远程执行的计划),这时候需要使用 db2_maximal_pushdown 进行特殊处理。
  2. 不同服务器的 Nickname 之间,或者 Nickname 与本地表之间的连接方法问题。IFS 运用统计信息进行代价计算而选出连接方法。但是有可能由于 Nickname 的统计信息无法做到像本地表那样详尽,或者在某些情况下 IFS 优化器的代价模型不适应特殊的应用场景,IFS 采用了不合适的连接方法。这时如果想得到我们想要的连接方法,则可能需要一些特殊的方法,比如 Plan Hint。
  3. 统计信息的准确性和完整性。IFS 根据统计信息和代价模型来选取最优的计划,包括确定连接的顺序和方法,下推的语句等。因此统计信息的准确和完整性非常重要。我们所说的统计信息,包括 Nickname 的记录数,字段的值的情况,还包括索引信息,唯一性约束和各 Nickname 之间的键值约束。上述的第 1,2 点实际上跟统计信息都有关系。
  4. 还有一些其他方面,可能阻止了最优计划的生成,比如不应该在 MPP 模式下创建 UNFENCED 的 Wrapper。

下面我们从这几个方面依次对查询计划进行分析。

下推问题

要实现下推,首先要通过定义 Function Mapping 和设置 Server Option 让 IFS 准确知道远处服务器是否支持查询中出现的函数和语法。对于这些函数和语法,IFS 优化器会根据代价计算的结果最终决定是否下推。如果 IFS 对可以下推的语法并没有最终下推,则可以设定 db2_maximal_pushdown 这个参数强制下推。对于远程服务器不支持的语法,IFS 是无法下推的,所以应该尽量避免在查询中使用远程服务器不支持的语法。我们可以从下面几个方面促进下推。

1. Function Mapping

当查询中出现的函数没有 Function Mapping 时,则 IFS 不会将其下推,也会连带含有这个函数的谓词或子查询无法下推。例如,对于下面的选择谓词,

清单 4. 选择谓词示例
    N1.c1 = ADD_MONTHS(TO_DATE( '20100819' , 'YYYYMMDD' ), -1)

尽管远处服务器支持 ADD_MONTHS 函数,但由于没有 Function Mapping,IFS 并不知道远处是否支持以及它在远程的语法形式,所以整个谓词都没有被下推。这就使得 IFS 必须从远程取出所有 N1.C1 的值,再在本地应用选择谓词,从而产生了不必要的开销。其他的例子还有,CASE,IN,IS NULL 等表达式。为了下推,可以创建类似下面的 Function Mapping 下推此函数:

清单 5. ADD_MONTHS 的 Function Mapping
 CREATE FUNCTION MAPPING my_mapping  FOR 
 SYSIBM.ADD_MONTHS (SYSIBM.TIMESTAMP,SYSIBM.INTEGER)  
 SERVER MY_SERVER  OPTIONS (REMOTE_NAME ' SYSIBM.ADD_MONTHS(:1P,:2P)')

下面是一个连接谓词例子:

清单 6. 连接谓词示例
    N1.C1 = SUBSTR(N2.C2, 1, 13)

如果没有对 SUBSTR 函数的 FUNCTION MAPPING,这个连接谓词就不会被下推。结果可能造成整个连接无法放到远程执行,IFS 将被迫把两个 Nickname 的记录取到本地,再做连接。如果远程支持此函数,可以用下面的方法创建 Function Mapping:

清单 7. SUBSTR 的 Function Mapping
 CREATE FUNCTION MAPPING my_mapping1  FOR 
 SYSIBM.SUBSTR(SYSIBM.VARCHAR(10),SYSIBM.INTEGER,SYSIBM.INTEGER)  
 SERVER MY_SERVER  OPTIONS (REMOTE_NAME 'SUBSTR(:1P,:2P,:3P)')

IFS 其实自动定义了一些缺省的 Function Mapping。这些缺省的 Function Mapping 也会随着版本升级不断补充。但你仍然有可能用到没有缺省 Function Mapping 的函数。另一方面,像 ROWNUM 这样的系统函数,内部被转换成特殊的形式,所以无法为其创建 Function Mapping,应尽量避免使用。

注意,创建 Funciton Mapping 使用的函数签名应该根据查询计划中的 Optimized Statement 里面的形式确定,因为 Optimized Statement 里面得函数签名,是 IFS 经过内部处理形成的,也是它寻找 Function Mapping 匹配时实际用到的函数签名。

2 . Server Option 的设置:

与下推相关的 Server Option 分为两种,标识远处服务器能力(是否支持某种语法)的 Server Option 和标识远处服务器的状态(例如 Codepage 是否与本地相同)的 Server Option。如下的各种查询没有下推的情况,是有各自不同的 Server Option 引起的:

1) . GROUP BY,SORT,MAX,MIN 没有被下推。这些操作不被下推一般是由于 Server Option collating_sequence 为‘ N ’造成的。所以应该尽量保证远程数据库的 collating sequence 与本地的一致,然后设置 collating_sequence 为‘ Y ’,保证这些操作的顺利下推。

2) . Nickname 和本地表的连接谓词没有下推。Nickname 作为内表(Inner Table)使用嵌套连接(Nested Loop)和本地表连接时,一般连接谓词是可以下推的,其形式一般为:

清单 8. 嵌套连接的连接谓词
    N1.C1 = :H0

它意味着,IFS 每次从作为外表的本地表中取出一个值,作为 Parameter Marker :H0 的值发送到远程。但如果连接谓词的两个域长度不一致,比如一个是 Varchar(10),另一个 Varchar(20),并且 Server Option db2_same_codeset 为‘ N ’,那么为了保证连接结果的正确性,这个连接谓词就不会下推。后面一章会看到实际的例子。所以正确的做法是,保持连接的两个字段长度相同,或者设置 db2_same_codeset(此 Option 标识远处服务器的 Codepage 是否与本地相同)为‘ Y ’。

3) .进行字符串比较的谓词没有下推,可能是没有设置 Server Option varchar_no_trailing_blanks 引起的。这个 Option 标识远程的 Nickname 字段是否有 Trailing Blank。如果没有,应该将其设为‘ Y ’。否则 IFS 会为了保证正确性而不下推谓词。

4) .子查询没有下推:子查询出现在 Select 子句,Having 子句等位置,有可能由于远程不支持而不能下推,应该尽量将子查询转换为一般的连接,这样下推的可能性最大。与之相关的 Server Option 有 db2_nested_tab_expr, db2_SQ_w_corr, db2_nested_tab_expr_w_corr, db2_select_scalar_SQ,可以试着将他们设为‘ Y ’,来下推子查询。

5) .外连接没有下推:很多数据源对外连接 (Outer Join) 不支持,所以应该尽量避免对它们使用这种连接。与外连接下推有关的 Option 有 db2_nested_tab_expr , db2_nested_tab_expr_w_oj , db2_outer_joins,可以试着将它们设为‘ Y ’,但要保证不会产生错误。

6) . Insert-select 查询有可能因为 IFS 是 Oracle compatibility 模式而不能下推。如果设了这个模式,会导致 IFS 为了保证符合本地数据库的语义,而不下推很多涉及字符串,Timestamp 的函数,甚至是简单的 Insert-select 语句。如果远处的数据源也开启了这个模式,可以将 varchar2_compat, date_compat, number_compat 设为‘ Y ’,以有助于下推。

3. 使用 db2_maximal_pushdown

有时候,由于 Nickname 的统计信息不完整,或者 IFS 优化器的代价模型不适合某个具体的场景等原因,无法得到一个完全下推的计划。而设置 db2_maximal_pushdown 则可能得到一个下推的计划。 这个 Server Option 实际上强制优化器直接选择 SHIP 数目(即一个计划树中所有的 SHIP 的节点数之和)最少的计划,以此来得到一个完全下推的计划(当然,它对本来就只可能有一个 SHIP 的查询影响不大)。

由于使优化器不再以代价作为选择计划的基本依据,就可能导致不好的计划被使用。而且它会影响所有和这个远程服务器相关的查询,在为一个查询带来好处的同时,可能会使其他查询的计划变坏。比如设置 db2_maximal_pushdown 之前,我们得到一个这样的计划:

清单 9. 没有下推的连接
                  1000                                
                 >NLJOIN                                     
          /--------+---------\                                     
        100                    100                                      
       SHIP                    SHIP                                       
         |                       | 
        N1                       N2

使用这个计划,只需要从远程取 200 个记录,然后在本地做连接,连接结果是 1000 个记录。但是设置了 db2_maximal_pushdown 后,我们只会看到这样的计划:

清单 10. 下推后的连接
                     |                                                        
                   1000                                                         
                   SHIP                                                             
                     |                  
                     1000        
                 >NLJOIN                                     
          /--------+---------\                                     
       100                   100 
        N1                    N2

执行这个计划,IFS 要从远程取 1000 个记录,通信代价大大提高,使性能下降。

为了避免设置 db2_maximal_pushdown 影响所有的查询,我们可以使用下面的方法对单独一个 SQL 设置它:

清单 11. 将 db2_maximal_pushdown 设为 Y
    set server option db2_maximal_pushdown to 'Y' for server oraserv

用这种方法设置,只会对本次连接后续的语句起作用。然后可以执行 SQL,执行完后再将它设置回来:

清单 12. 将 db2_maximal_pushdown 设为 N
    set server option db2_maximal_pushdown to 'N' for server oraserv

4 .改变优化器相关的 Server Option

优化器使用下面三个 Server Option 标识远程服务器的处理能力和网络速度。当远程服务器的处理能力与 IFS 相差很大时(比如远程服务器使用了 MPP,而本地没有),可以调节它们,使优化器的代价估算更合理。

cpu_ratio 这个值标识 IFS 与远程服务器的 CPU 速度的比例,比如如果远程服务器 CPU 很强,可以使用下面的语句更改这个值:

清单 13. 更改 cpu_ratio
    alter server server_name options (add cpu_ratio '0.1')

io_ratio 这个值标识 IFS 与远程服务器 IO 处理能力的比值。可以用下面的语句更改:

清单 14. 更改 io_ratio
    alter server server_name options (add io_ratio '0.5')

comm_rate 这个值标识 IFS 与远程服务器通信的带宽。这个值越小,说明带宽越小(缺省为 2)。可以用下面的语句更改:

清单 15. 更改 comm_rate
    alter server server_name options (add comm_rate '1')

统计信息

如果没有由于 Server Option 或操作不被远程支持而造成的不下推情况,生成的计划仍然性能不高,则可能是统计信息没有更新或不完整的原因。请从以下方面查看统计信息:

1 .一般的统计信息。检查统计信息最简单的方法,是查看计划树中的 Nickname 节点。如果上面显示的记录数为 1000(缺省值),则意味着统计信息可能没有搜集。而 db2exfmt 输出的最后也会显示 Nickname 及其索引的基本的统计信息。通过下面的语句可以更准确的检查 Nickname 的统计信息:

清单 16. 查询 Nickname 统计信息的语句
 select tabname, card, fpages, npages, overflow 
 from sysstat.tables 
 where tabname  = 'MY_NICKNAME' and tabschema = 'MY_SCHEMA'

 select colname, colcard, high2key, low2key, avgcollen 
 from sysstat.columns 
 where tabname   = ' MY_NICKNAME' and tabschema = 'MY_SCHEMA'

 select indname, nleaf,nlevels,clusterratio,firstkeycard,fullkeycard 
 from sysstat.indexes 
 where tabname = 'MY_NICKNAME' and tabschema = 'MY_SCHEMA' and indname = 'MY_INDEX'

将上面的语句的结果,与远程服务器端的实际情况相对照,就能看出本地系统表中 Nickname 的统计信息是否准确和完整了。注意 IFS 对不同数据源从远程获取和使用的统计信息有所不同,参见 参考资料。那些没有被使用的统计信息会被置成缺省状态(比如 -1,NULL 等)。要验证统计信息是否准确有效,还要看看计划树中间结果大小的估计与实际的中间结果的之间的差别。为了保证代价估计的准确,这个差别一般不要太大。需要注意的是,如果查询中出现本地的表,也要确保本地表的统计信息完整准确,这对生成大的最优计划也是至关重要的。

实际上,每当远程表出现大的修改,就应及时更新 Nickname 的统计信息。Nickname 的统计信息的更新,包括两个步骤:在远程服务器上运行搜集统计信息的命令,对 Nickname 对应的那些远程表计算统计信息,然后通过重新创建 Nickname 或者运行 NNSTAT 命令更新本地系统表中的统计信息。运行 NNSTAT 一般推荐使用 Method 0 (见 参考资料),这样搜集的信息会最全。

当怀疑统计信息不准确时,可以用下面的方式手动改变统计信息,看看能不能生成一个好的计划,以此来快速判断统计信息是否造成性能问题的根源:

清单 17. 手动更改统计信息
 select count(*)as CD from N1 

 select max(c1) as H2K, min(c1) as L2K, count(distinct c1) as CC from N1 

 update sysstat.tables 
 set card = CD 
 where tabname  = 'MY_NICKNAME' and tabschema = 'MY_SCHEMA'

 update sysstat.columns 
 set colcard = CC, high2key = H2K, low2key = L2K 
 where tabname = 'MY_NICKNAME' and tabschema = 'MY_SCHEMA' and colname = 'C1'

2 .索引问题。 一般情况下,Nickname 被创建时,它的远程表的索引信息也会被取到本地的系统表中。但是如果索引是 Nickname 创建后在远程另外创建的,则只运行 NNSTAT 无法使其信息到达本地系统表。这时候需要重建 Nickname 或者使用下面的命令在本地系统表存入这个索引信息:

清单 18. 创建 Index Specification
 create index Ni on N1(C1) specification only

3 .检查是否有为远程视图创建的 Nickname。. 为远程视图创建的 Nickname 很难有完整的统计信息。这些 Nickname 创建时几乎不会有任何统计信息被设置。运行 NNSTAT 也无法取得像 NPAGS 这样的关于表的空间大小的统计信息。也没有关于索引的信息。这会造成优化器无法生成最优的执行计划。

连接方法

当本地表和 Nickname 连接或者两个不同服务器的 Nickname 连接时,连接需要在 IFS 端进行。这时如果连接方法不合理,会造成严重的性能问题。例如下面的连接方法采用了嵌套连接(Nested Loop Join),这意味着,对于外表(Outer Table)的每个记录(共 500000 条),都要做一次执行 SHIP 的操作。而每次执行 SHIP 都会与远程服务器交互,从而有不小的开销。这个计划的性能有可能将非常低下。

清单 19. 嵌套连接
                                 >NLJOIN                                      
                     /--------------+---------------\                          
               500000                               10                    
               TBSCAN                               SHIP

如果这样的计划被证实有性能问题,可以试着将这个连接改为合并连接(Merge Join)或者散列(Hash Join)。比如,改为合并连接,

清单 20. 合并连接
                                 >MSJOIN                                      
                     /--------------+---------------\                          
               500000                            600000                    
               TBSCAN                               SHIP

因为我们只用执行一次 SHIP,总和算来,可能总的耗费比嵌套连接要好。为了得到这样的连接方法,在更新统计信息不奏效的情况下,可以考虑采用 Plan Hint 强制改变连接方法。具体方法请参见 参考资料

Wrapper 的 Fenced 模式

在 MPP 模式下,创建 Wrapper 时应尽量使用 Fenced 模式。这是因为,只有 Fenced 模式的 Wrapper,其 Nickname 的数据到本地处理时才可能使用 TQ 发送到各个 MPP 的 partition 并行处理。对于 Unfenced 模式 (DB2_FENCED 这个选项为‘ N ’ ) 的 nickname 无法产生这种并行的计划。

其他优化方法

如果按照上面的方法仍然不能获得满意的性能,可以考虑使用 MQT 和 ATQ。

通过在 Nickname 上建立 MQT 可以把远程的数据缓存到本地,使查询速度大幅提高。具体的使用方法可以参见 参考资料。ATQ 的使用要求 IFS 处于 MPP 模式,并且 Wrapper 为 Fenced 模式。ATQ 可以使对远程服务器的访问与本地的操作并行执行,一定程度上可以提高效率。具体方法参见 参考资料

性能问题实例

这里举几个典型的 IFS 性能问题的例子。

Nickname 统计信息不全

本地是 AIX 上的 IFS 系统,远程是 AS400 数据库,所用的查询涉及 Nickname 和本地表的连接。有个查询在系统升级后变慢,原来 10 秒内能完成,现在需要 3 分钟左右。NNSTAT 已经运行过。用 db2exfmt 查看它现在的计划中的 Nickname 部分如下:

清单 21. 性能问题 -1
                                      /-----------------+----                  
                                5.73982e+08                                    
                                  >NLJOIN                                      
                                  (   4)                                       
                                3.95942e+08                                    
                                   6289                                        
                     /--------------+---------------\                          
               4.5666e+06                           125.691                    
                 >NLJOIN                            TBSCAN                     
                 (   5)                             (  10)                     
                 317762                             2136.46                    
                   177                                500                      
          /--------+---------\                        |                        
      305.776                14934.5                 78557                     
      TBSCAN                 SHIP                   TEMP                       
      (   6)                 (   9)                 (  11)                     
      417.147                1563.87                2049.82                    
        84                   21.344                   500                      
        |               /------+------\               |                        
      305.776     9.33404e+06       9.33404e+06      78557                     
      SORT     NCKIDX: N2i            NICKNM: N2       SHIP                       
      (   7)                                         (  12)                     
      417.119                                          2016.79                    
        84                                            500                      
        |                                             |                        
      305.776                                        78557                     
      SHIP                                     NICKNM: N3                       
      (   8)                                                                    
      416.724                                                                  
        84                                                                     
        |                                                                      
       76444                                                                   
       NICKNM: N1

这个计划的主要代价应该是花费在第二个 Nested Loop 连接上。其不合理之处是对 N3 没有使用索引,第一个连接的结果的估计(4.5666e+06)也偏大。检查下推情况,把 db2_maxmal_pushdown 设为‘ Y ’,可以得到完全下推的计划。说明没有阻碍下推的因素,而是优化器根据正常的代价计算得出的这个计划。所以怀疑统计信息的问题导致对中间结果估计有偏差。用上一章所述的检查统计信息的方法查看,发现 sysstat.columns 中的对应 Nickname 的 column card,high2key, low2key 都是空值。试着手动修改 sysstat.columns 中的统计信息,发现中间结果的估计变成了 766,索引也被用上了。最后检查原因是先前运行 NNSTAT 时错误的指定了第 3 个参数的值为空串(应该为 NULL 或者一个域的列表),造成没有对域搜集统计信息。重新对每个域运行 NNSTAT,得到完整的统计信息,问题解决。

连接谓词没有被下推

本地是 AIX 上的 IFS 系统,远程是 DB2,数据量达到 TB 级。系统升级后,发现吞吐量下降。从 Snapshot 中看到,一个远程的查询语句的 Rows selected 为上亿,数量很大。查看这个查询的计划:

清单 22. 性能问题 -2
                                                      Rows 
                                                      RETURN 
                                                      (   1) 
                                                       Cost 
                                                       I/O 
                                                        | 
                                                   1.11053e-05 
                                                     NLJOIN 
                                                     (   2) 
                                                      153854 
                                                      18867.4 
                                          /--------------+-------------\ 
                                  5.2079e-07                          21.3239 
                                       BTQ                               FILTER 
                                      (   3)                             (  23) 
                                      6490.66                           147364 
                                      13059.4                            5808 
                                        |                                    | 
                                    4.82213e-09                         648842 
                                      NLJOIN                              SHIP 
                                      (   4)                              (  24) 
                                      6490.35                            147219 
                                      13059.4                             5808 
                      /----------------+----------------\                    | 
                   4.82213e-09                           1             1.46054e+06 
                    DTQ                                FETCH           NCKIDX: N1 
                   (   5)                              (  21)

发现 SHIP 上面有个 FILTER 节点,说明有些谓词没有被下推,有可能是选择谓词,也可能是连接谓词。Row Selected 数量很大就是因为没有把谓词推下去,SHIP 从远程拿的数据过多。查看 FILTER 的情况,发现它有一个连接谓词 N1.C = T.C,而且等式两端的域的长度不一致。可见是由于域长度不一致再加上 db2_same_codeset 为 N 造成此连接谓词没有下推。在远程数据库与本地 Code Page 相同的情况下,我们把 db2_same_codeset 设为 Y,下推了连接谓词,性能问题解决。

远程服务器问题

远程服务器为 AS400。有一个本地表与 Nickname 的连接查询过慢(3 分钟左右)。本地建立一个与 Nickname 相同的本地表,代替 Nickname 做连接,只需不到一秒。查看查询的计划:

清单 23. 性能问题 -3
             0.653697 
                  NLJOIN 
                  (   4) 
                  36.8303 
                  3.26706 
          /----------+----------\ 
        1                      0.653697 
     IXSCAN                     SHIP  
     (   5)                     (   6) 
     20.2689                    16.5615 
     2.61336                   0.653697 
       |                    /------+------\ 
     190460             1000               1000 
 INDEX: T1            NICKNM:N1             NCKIDX: N1i

发现 Nickname 的记录数的统计信息是缺省的(1000),怀疑没有统计信息。于是在远程搜集统计信息,本地用 Method 2 运行 NNSTAT。统计信息虽然改善,但计划还是没有变化。仔细分析,所有的谓词都已下推,其实这个计划已经是最好的了,于是分析哪部分实际耗费了最多的时间。查看 Snapshot,发现远程语句花去了大部分时间。这个发送到远程的语句,有形如 N1.C1=:H0 and N1.C2 IN ( … .) 的选择谓词,而且 IN 谓词中有很多值。直接把 :H0 换成常数,在远程执行速度很快。于是怀疑是不是当有 :H0 和长的 IN 谓词同时出现,远程服务器执行会特别慢。用自己写的 CLI 程序直接用这样的谓词查询远程表验证,果然如此。于是,性能问题的根本原因便是,远程服务器在执行这种 SQL(有 :H0 和长的 IN 谓词)时特别慢导致的。解决方法是重写 SQL 避免很长的 IN 谓词(把 IN 里面的值放入 values(..) 表达式,然后放入 From 子句,把谓词转换为一个连接)。

总结

从上面的章节可以看出,IFS 的性能问题集中在网络,统计信息,查询计划等方面,并有其规律。熟练运用本文提出的方法,处理 IFS 的性能问题将会得心应手。


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=697244
ArticleTitle=IBM InfoSphere Federation Server 性能问题的一般解决方法
publish-date=07052011