ITPub博客

首页 > Linux操作系统 > Linux操作系统 > OLTP 应用程序的 DB2 调优技巧

OLTP 应用程序的 DB2 调优技巧

原创 Linux操作系统 作者:myfriend2010 时间:2019-07-17 07:42:02 0 删除 编辑

简介

DB2 Universal Database®UDB)是第一个支持多媒体和 Web 的关系数据库管理系统,它的功能非常强大,足以满足大公司的需求,并且它非常灵活,足以满足中小企业的要求。DB2 产品系列软件和因特网技术的结合使我们可以方便地跨不同平台访问信息、使用信息并且保证信息安全。全世界有 30 多万家公司的 6000 多万个 DB2 用户依赖于 IBM 数据管理解决方案。

DB2 UDB 为大多数需要电子商务的应用程序(比如电子商务、企业资源计划、客户关系管理、供应链管理、Web 自助服务和商业智能)提供支持。这是一种可伸缩的、工业级数据库,非常适合用作电子商务发展过程中的数据管理基础。

联机事务处理(Online transaction processingOLTP)是一类能为面向事务应用程序提供便利的应用程序,并可用来管理面向事务的应用程序,它通常用于处理许多行业的数据输入和检索事务,这些行业包括银行、航空、邮购、超市和制造业。通常,OLTP 工作负载包括许多并发运行的短事务。如今的联机事务处理日益要求支持跨网络以及可能包括多家公司的事务。因此,新的 OLTP 软件使用了客户机/服务器处理和代理软件,这种软件允许事务在一个网络的不同计算机平台上运行。

在任何一种数据库系统中,性能是最重要的因素之一。本文根据由运行 OLTP 类型的性能基准测试程序(TPC-CTPC-WTrade2 等)所得到的经验,着重讨论了许多 DB2 性能调优技巧。虽然数据库应用程序的性能会受许多因素影响,但是我们着重讨论配置而不是诸如容量规划、数据库设计或应用程序设计之类的因素。

本文的组织结构如下:

有关性能的一些基本要素

更新目录统计信息,这部分强调收集和维护最新数据库统计信息的重要性,缺少这项工作常常是导致许多性能问题的源头所在。

监控和调优数据库配置参数,这部分按照重要性的顺序描述了一列数据库管理器参数和数据库参数。通常,没必要尝试列表中的所有参数以实现性能目标。可以只尝试其中位于列表顶部的那几个,以查看是否有性能方面的改进。

有了这些技巧,就可以启动自己的 OLTP 应用程序并使其拥有非常好的运行性能。

有关性能的一些基本要素

1. 有足够的内存。

o 对于 32 位系统,每个 CPU 至少使用 512 MB RAM,最高可达每台机器 4 GB,以支持大量并发用户所需的缓冲池、DB2 代理程序和其它共享内存对象。(请参阅 缓冲池大小(BUFFPAGE一节以获取有关缓冲池的更多信息。)可能需要更多的内存来支持在本地运行或作为存储过程运行的应用程序。在 AIX® JFS 文件高速缓存可以使用额外的内存来补充缓冲池。

o 对于 64 位系统,缓冲池实际上可以是任何大小。但是,对于使用大型数据库的大多数电子商务 OLTP 应用程序,缓冲池大小实际上不需要超过 8 GB。越大当然越好,但是在某一点,当缓冲池命中率达到 98+% 时,会随内存的增加命中率反而下降。并发用户的数目(它影响 DB2 代理程序的数量)决定需要多少内存。

o 每个用户连接至数据库(即 DB2 代理程序)所需的内存数量取决于应用程序所执行的 SQL 语句的性质 - 比如打开的并发游标数以及所需的排序和临时空间的数量。对于 OLTP 应用程序,所需的排序和临时空间会比较少,一次只打开少数并发游标。

o 经验:对于每个 DB2 代理程序,在 UNIX 中最少使用 1 MB 内存,在 Windows 中最少使用 500 KB 内存。如果使用了受防护的存储过程,那么除了运行存储过程应用程序所需的内存之外,每个用户连接还有两个 DB2 代理程序。

2. 有足够的 I/O 处理能力。

o 必须有足够的磁盘设备来确保充分的 I/O 并行性,以支持大容量的并发事务。对于中等工作负载而言,每个 CPU 至少应当有 5 10 个磁盘,对于高 I/O OLTP 工作负载而言,至少要有 20 个磁盘。操作系统(包括调页空间)、DB2 日志和 DB2 表空间应当拥有各自的专用磁盘。应当有多个磁盘用于 DB2 日志、表和索引。

o 估计良好性能所需的 I/O 处理能力的正确方式,实际上是制作事务原型并找出每个事务需要多少 I/O,以及每秒需要处理多少事务。然后找出磁盘控制器和磁盘子系统的 I/O 速率以帮助确定需要多少控制器和磁盘。

3. 有足够的网络带宽。

必须有足够大的网络带宽以支持工作负载。请确保网络或任何中间集线器都不会成为瓶颈。当支持远程访问时这一点尤为重要。例如,T1 线路支持 1.544 Mb/s,这仅为 0.193 MB/s,而通常的 10 Mb/s 以太局域网可以支持 1.25 MB/s,吞吐量为 T1 线路的 6 倍。在 UNIX 上使用诸如 netstat 这样的命令可以监控连接上的流量。

4. 使用 DB2 控制中心(DB2 Control Center)的 DB2 性能配置向导(DB2 Performance Configuration Wizard)来设置初始的 DB2 数据库管理器(Database Manager)和数据库配置(Database Configuration)参数。

这个工具会询问您一系列有关工作负载性质的问题,以便确定配置参数值的起始设置。您可以修改这些参数以满足生产工作负载的需要。

5. 适当地为表列建立索引。

o 确保查询中进行连接操作的列都有索引。

o 如果为 ORDER BY GROUP BY 所涉及的列建立了索引,那么可以提高性能。

o 也可以将经常被访问的数据作为 INCLUDE 子句中的列包含在索引中。

o 根据所使用的表和 SQL 语句,使用索引顾问程序(Index Advisor)(也称为索引向导 (Index Wizard),可以从 DB2 控制中心调用该程序)来帮助确定使用一组合适的索引。

6. 确保应用程序持有锁的时间尽可能短。

o 当用户操作涉及多个交互作用时,每个交互作用应当提交自己的事务并且应当在将活动返回给用户之前释放所有锁。通过尽可能晚地启动事务的第一个 SQL 语句(它启动一个事务)并使事务的更新(插入、更新和删除,这些操作要用到互斥锁)尽可能接近提交阶段,从而使事务的持续时间尽可能的短。

o 使用 DB2 注册表参数 DB2_RR_TO_RS,通过不锁定插入或更新行的下一个键,可以改进并发性。如果对同一组表进行操作的任何程序都没有使用隔离级别 RR(可重复读,Repeatable Read),那么就可以使用上述操作。使用 DB2 快照(DB2 Snapshot)监控死锁和锁等待的数目。

7. 使用存储过程或复合 SQL 使网络成本降到最低。

o 将用于 SQL 语句的网络往返通信次数降至最低,可以减少网络等待时间和上下文切换,这样可以使应用程序持锁的时间更短。通常,当 OLTP 事务有 4 个或 5 个以上语句时应当使用存储过程。

o 另一方面,如果应用程序逻辑中涉及了某个复杂的 CPU 密集型处理,那么将它放在运行于数据库服务器上的存储过程中会用光数据库服务器上的额外 CPU 周期,从而牺牲一些数据库操作。在这种情况下,要么不使用存储过程,要么在客户机端执行一部分逻辑,而在存储过程中执行其余的逻辑。

8. 有效地使用 SQL

o 通常,如果一条 SQL 语句能完成任务,那么就不使用多条 SQL 语句。当通过在查询中设置更多谓词来提供更详细的搜索条件时,优化器就有机会作出更好的选择。您还应该使查询具有可选择性,这样数据库就不会返回您不需要的行和列。例如,使用 SQL 来过滤您想要的行;不用返回所有行,然后要求应用程序执行过滤操作。

9. 分析存取方案。

o 使用可视化说明(Visual Explain)或 db2exfmt 来分析每一条 SQL 语句。请确保使用合适的索引,从而在选择和连接(join)表时,将必须在内部访存的行数减到最少。

更新目录统计信息

背景知识

RUNSTATS 实用程序用于更新系统目录表中的统计信息,以帮助查询优化过程。如果没有这些统计信息,数据库管理器可能会做出对 SQL 语句的性能产生不利影响的决定。RUNSTATS 实用程序允许您收集表和/或索引中所包含数据的统计信息。使用 RUNSTATS 实用程序收集基于表和索引数据的统计信息,以便为下列情形中的存取方案选择过程提供精确的信息:

· 当向表装入数据并创建了合适的索引时。

· 当用 REORG 实用程序重新组织表时。

· 当存在大量影响表及其索引的更新、删除和插入操作时。(此处的大量可能意味着 10% 20% 的表和索引数据都受到了影响。)

· 在绑定性能至关重要的应用程序之前。

· 当您希望将新的和以前的统计信息进行比较时。定期进行统计使您能够在早期阶段发现性能问题。

· 当预取数量发生变化时。

· 当您已经使用了 REDISTRIBUTE NODEGROUP 实用程序时。

当对 SQL 查询进行优化时,SQL 编译器所做出的决定会受到优化器的数据库内容模型的重大影响。优化器使用该数据模型来估计可以用于解决某个特定查询的其它存取路径的成本。数据模型中的关键元素是一组统计信息,该统计信息收集了有关数据库中所包含的数据和系统目录表中所存储的数据的信息。这包括表、别名(nickname)、索引、列和用户定义的函数(UDF)的统计信息。数据统计信息中的变化会引起对存取方案的选择发生变化,该存取方案作为访问所期望数据的最有效方法。

下面列举了一些统计信息,这些统计信息可以帮助给优化器定义数据模型:

· 表中的页数和非空的页数。

· 从原始页移到其它(溢出)页的程度。

· 表中的行数。

· 有关单个列的统计信息,比如一列中唯一值的数量。

· 一个索引的群集程度;即,表中行的物理顺序与索引的符合程度。

· 有关索引的统计信息,比如索引级别的数量和每个索引中叶子页的数量。

· 经常使用的列值的出现次数。

· 列值在列中所有值中的分布状况。

· 用户定义的函数(UDF)的成本估计。

RUNSTATS 可以帮助您确定对数据库的更改与性能之间的关系。统计信息显示出表中的数据分布状况。常规使用时,RUNSTATS 提供了在一段时期内有关表和索引的数据,从而随着时间的流逝,可以确定数据模型的性能趋势。在使用 RUNSTATS 之后需要重新绑定使用静态 SQL 的应用程序,这样查询优化器就可以选择新统计信息所给出的最佳存取方案。但是,对于使用动态 SQL 的应用程序(比如大多数供应商应用程序)而言,没必要进行重新绑定,因为语句的优化是根据统计信息在运行时进行的。当有关表的统计信息不准确时,可能会造成性能问题。最糟的情况是,某个特定的 SQL 语句可能会造成 DB2 使用表扫描而不是使用索引扫描。

如何更新统计信息

只有当进行显式的请求时,对象的统计信息才会在系统目录表中被更新。有几种方法可以更新部分或全部统计信息:

· 使用 RUNSTATS(运行统计信息,run statistics)实用程序。

· 使用带有指定的统计信息收集选项的 LOAD

· 对针对一组预先定义的目录视图进行操作的 SQL UPDATE 语句进行编码。

· 使用“reorgchk update statistics”命令。

当您不完全知道所有表名或表名实在太多时,进行 RUNSTATS 的最简单方法就是使用“db2 reorgchk update statistics”命令。正确的脚本如下:

db2 -v connect to DB_NAME

db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes"

db2 -v reorgchk update statistics on table all

db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes"

db2 -v terminate

我们上面所选的示例不需要表名。这一命令对所有表执行 RUNSTATS

记住在填充数据库之后再运行 RUNSTATS 实用程序。

如果您知道表名并且想避免对大量表运行 RUNSTATS 实用程序(因为这样做可能要花很长时间),那么一次对一张表进行 RUNSTATS 更为可取。命令如下:

db2 -v runstats on table

TAB_NAME and indexes all

这个命令将收集该表及其所有索引(基本级别)的统计信息。

查看是否运行了 RUNSTATS

要查看是否对数据库执行了 RUNSTATS,一种快捷方法便是查询一些系统目录表。例如,如上面的脚本所示,可以运行下面这条命令:

db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes"

如果还未运行 RUNSTATS,您会看到 nleaf nlevels 列为“-1” stats_time 列为“-”。如果已经运行了 RUNSTATS,则这些列包含实际的数字,并且如果运行过 RUNSTATS,则 stats_time 列将包含时间戳记。如果您认为 stats_time 中所示时间离现在已有很长一段时间,那就该再次运行 RUNSTATS

监控和调优数据库配置参数

下面这些有关数据库配置调优的技巧将使您在 OLTP 环境中取得非常好的性能,同时使您能够避免显而易见的陷阱。在配置参数中,数据库管理器配置参数需要重新启动数据库管理器,而为了使更改生效,大多数数据库配置参数都要求应用程序重新连接到数据库。

这里描述的配置参数包括:

· 缓冲池大小

· 日志缓冲区大小

· 应用程序堆大小

· 排序堆大小和排序堆阈值

· 代理程序的数目

·

· 活动应用程序的最大数目

· 异步页清除程序的数目

· I/O 服务器的数目

· 编入组中的提交数目

缓冲池大小

背景知识

缓冲池是内存中的一块存储区域,用于临时读入和更改数据库页(包含表行或索引项)。缓冲池的用途是为了提高数据库系统的性能。从内存访问数据要比从磁盘访问数据快得多。因此,数据库管理器需要从磁盘读取或写入磁盘的次数越少,性能就越好。对一个或多个缓冲池进行配置之所以是调优的最重要方面,是因为连接至数据库的应用程序的大多数数据(不包括大对象和长字段数据)操作都在缓冲池中进行。

缺省情况下,应用程序使用缓冲池 IBMDEFAULTBP,它是在创建数据库时创建的。当 SYSCAT.BUFFERPOOLS 目录表中该缓冲池的 NPAGES 值为 -1 时,DB2 数据库配置参数 BUFFPAGE 控制着缓冲池的大小。否则会忽略 BUFFPAGE 参数,并且用 NPAGES 参数所指定的页数创建缓冲池。

建议

对于仅使用一个缓冲池的应用程序,将 NPAGES 更改成 -1,这样 BUFFPAGE 就可以控制该缓冲池的大小。这使得更新和报告缓冲池大小以及其它 DB2 数据库配置参数变得更加方便。

确保可以使用数据库配置中的 BUFFPAGE 参数来控制缓冲池大小之后,将该参数设置成合适的值。根据数据库的大小和应用程序的性质将该参数设置成一个合理的大值,这种做法很安全。通常,该参数的缺省值非常小,可能满足不了要求。请考虑下列情况:

· 一开始,如果您的机器上有足够大的内存,请将 BUFFPAGE 设置成 40000 个页(160 MB),或者等于机器总内存的 10%

· 对于大型 OLTP 数据库,在保持系统稳定的同时为缓冲池留出尽可能多的内存。一开始,先尝试使用 1.6 GB 的内存,然后尝试用更多内存。

如何更改该参数

运行下面这个脚本,以便:

1. 验证目录值

2. 启用数据库配置参数 BUFFPAGE

3. 更新所有数据库的 BUFFPAGE 值。

4.

db2 -v connect to DB_NAME

db2 -v select * from syscat.bufferpools

db2 -v alter bufferpool IBMDEFAULTBP size -1

db2 -v connect reset

db2 -v update db cfg for dbname using BUFFPAGE bigger_value

db2 -v terminate

研究步骤

要确定数据库的缓冲池大小是否由 BUFFPAGE 参数所决定,请运行:

db2 -v connect to DB_NAME

db2 -v SELECT * from SYSCAT.BUFFERPOOLS

db2 -v connect reset

db2 -v terminate

检查结果。如果每个缓冲池都有一个为“-1” NPAGES 值,那么缓冲池大小是由数据库配置中的 BUFFPAGE 参数控制的。

要确定缓冲池大小是否足够大,请在运行应用程序时收集数据库和/或缓冲池的快照。类似于下面的脚本为您提供这些所需的信息:

db2 -v update monitor switches using bufferpool on

db2 -v get monitor switches

db2 -v reset monitor all


-- run your application --

db2 -v get snapshot for all databases > snap.out

db2 -v get snapshot for dbm >> snap.out

db2 -v get snapshot for all bufferpools >> snap.out

db2 -v reset monitor all

db2 -v terminate

请确保您在断开数据库连接之前发出“db2 -v get snapshot”。当最后一个应用程序与数据库断开连接时,该数据库停止运行,同时所有快照统计信息将会丢失。要确保一直存在使数据库处于正常运行状态的连接,请使用下列方法之一:

· 在收集快照的窗口中保持一个单独的连接。

· 使用 DB2 ACTIVATE DATABASE 命令。

在数据库快照或缓冲池快照的快照输出中,查找下列“logical reads”“physical reads”,这样就可以计算出缓冲池命中率,它可以帮助您调优缓冲池:

-- Related lines from a sample of bufferpool snapshots --

Buffer pool data logical reads = 702033

Buffer pool data physical reads = 0

Buffer pool data writes = 414

Buffer pool index logical reads = 168255

Buffer pool index physical reads = 0

缓冲池命中率表明数据库管理器不需要从磁盘装入页(即该页已经在缓冲池中)就能处理页请求的时间百分比。缓冲池的命中率越高,使用磁盘 I/O 的频率就越低。按如下计算缓冲池命中率:

(1 - ((buffer pool data physical reads + buffer pool index physical reads) /

(buffer pool data logical reads + pool index logical reads))

) * 100%

这个计算考虑了缓冲池高速缓存的所有页(索引和数据)。理想情况下,该比率应当超过 95%,并尽可能接近 100%。要提高缓冲池命中率,请尝试下面这些方法:

· 增加缓冲池大小。

· 考虑分配多个缓冲池,如果可能的话,为每个经常被访问的大表所属的表空间分配一个缓冲池,为一组小表分配一个缓冲池,然后尝试一下使用不同大小的缓冲池以查看哪种组合会提供最佳性能。

如果已分配的内存不能帮助提高性能,那么请避免给缓冲池分配过多的内存。应当根据取自测试环境的快照信息来决定缓冲池的大小。

日志缓冲区大小(LOGBUFSZ

背景知识

LOGBUFSZ 是一个数据库配置参数。它是用于日志缓冲区的参数。它允许您指定数据库共享内存的大小以用作在将日志记录写到磁盘之前这些记录的缓冲区。当下列事件之一发生时会将日志记录写到磁盘:

· 事务提交。

· 日志缓冲区已满。

· 其它某个内部数据库管理器事件发生时。

将日志记录存在缓冲区将产生更加有效的日志文件 I/O,这是因为这样一来可以降低将日志记录写到磁盘的频率,同时每次可写更多的日志记录。如果对专用的日志磁盘有相当多的读操作,或者希望有较高的磁盘利用率,那么可以增加这个缓冲区的大小。当增加这个参数的值时,也要考虑 DBHEAP 参数,因为日志缓冲区使用的空间由 DBHEAP 参数所控制。

如何更改该参数

我们发现该参数的缺省值为 84KB 页),这对于 OLTP 数据库而言通常不够大。LOGBUFSZ 的最佳值为 128 个或 256 4KB 页。例如,可以使用下面这个命令来更改该参数值:

db2 -v update database cfg for DB_NAME using LOGBUFSZ 256

db2 -v terminate

研究步骤

通过查看下面这个示例中所示各行,使用数据库快照来确定 LOGBUFSZ 参数的值是否为最佳值:

Log pages read = 0

Log pages written = 12644

一般而言,“log pages read”“log pages written”之比应当尽可能小。理想情况下,“log pages read”的值应为 0,而“log pages written”的值应很大。当 log pages read 太多时,意味着需要一个较大的 LOGBUFSZ

应用程序堆大小(APPHEAPSZ

背景知识

APPHEAPSZ 是一个数据库配置参数,它定义了代表某个特定代理程序或子代理程序的数据库管理器可以使用的私有内存页数。在为应用程序初始化代理程序或子代理程序时分配堆。分配的堆大小是处理给予代理程序或子代理程序的请求所需的最小值。当代理程序或子代理程序需要更多的堆空间以处理较大的 SQL 语句时,数据库管理器将按照需要分配内存,所分配的内存大小最大可达到该参数所指定的最大值。

如何更改该参数

下面这条命令可以将缺省值(DB2 EE 128 4KB 页,DB2 EEE 64 4KB 页)更改成最佳值:

db2 -v update db cfg for DB_NAME using applheapsz 256

db2 -v terminate

研究步骤

当应用程序接收到一个表明应用程序堆中存储空间不够的错误时,应该增加 APPHEAPSZ 的值。

排序堆大小(SORTHEAP)和排序堆阈值(SHEAPTHRES

背景知识

SORTHEAP 是一个数据库配置参数,它定义了私有排序所使用的私有内存页的最大数目,或共享排序所使用的共享内存页的最大数目。如果排序是私有排序,那么该参数影响代理程序私有内存。如果排序是共享排序,那么该参数影响数据库的共享内存。每个排序都有单独的由数据库管理器按需分配的排序堆。在排序堆中对数据进行排序。如果由优化器来指导排序堆大小的分配,那么用优化器提供的信息来分配的排序堆的大小要小于由该参数所指定的排序堆大小。

SHEAPTHRES 是一个数据库管理器配置参数。私有和共享排序所使用内存的来源不一样。共享排序内存区的大小是在第一次连接到数据库时根据 SHEAPTHRES 值以静态方式预先确定的。私有排序内存区的大小是不受限制的。对于私有排序和共享排序,应用 SHEAPTHRES 参数的方式不同:

· 对于私有排序,SHEAPTHRES 是对私有排序在任何给定的时间可以消耗的全部内存的实例级限制。当实例的总私有排序内存消耗量达到这一限制时,为其它进入的私有排序请求而分配的内存会大大减少。

· 对于共享排序,SHEAPTHRES 是对共享排序在任何给定的时间可以消耗的全部内存的数据库级限制。当达到这一限制时,不允许有其它共享排序内存请求,直到总的共享内存消耗量回落到 SHEAPTHRES 所指定的限制以下。

使用排序堆的操作示例包括内存中表的散列连接和操作。阈值的显式定义防止数据库管理器将过多数量的内存用于大量排序。

建议

· 使用数据库系统监视器来跟踪排序活动。

· 使用合适的索引使排序堆的使用降到最低。

· 当需要频繁进行大型排序时,增加 SORTHEAP 的值。

· 如果增加 SORTHEAP,请确定是否还需要调整数据库管理器配置文件中的 SHEAPTHRES 参数。

· 优化器用排序堆大小来确定存取路径。在更改该参数后请考虑重新绑定应用程序(使用 REBIND PACKAGE 命令)。

· 理想情况下,应当将排序堆阈值(SHEAPTHRES)参数合理地设置为在数据库管理器实例中设置的 SORTHEAP 参数最大值的倍数。该参数至少应当是实例中任何数据库所定义的最大 SORTHEAP 的两倍。

如何更改这些参数

要更改 SORTHEAP SHEAPTHRES 的值,请运行以下命令:

-- SORTHEAP should be changed for individual database --

db2 -v update db cfg for DB_NAME using SORTHEAP a_value

-- SHEAPTHRES is a database manager parameter --

db2 -v update dbm cfg using SHEAPTHRES b_value

db2 -v terminate

研究步骤

OLTP 应用程序不应该执行大型排序。大型排序在 CPU I/O 资源方面的成本太高了。通常,SORTHEAP 大小的缺省值(256 4KB 页)就足够了。事实上,对于高并发性 OLTP,您可能希望降低这个缺省值。当需要进一步研究时,可以发出下面这条命令:

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

下一篇: 八月十五的雨
请登录后发表评论 登录
全部评论

注册时间:2018-09-01

  • 博文量
    187
  • 访问量
    127085