DB2数据库性能优化技巧详解

DB2数据库性能优化技巧详解最简单而最见成效的Bufferpool缓冲池是内存中的一块存储区域，用于临时读入和更改数据库页(包含表行或索引项)。缓冲池的用途是为了提高数据库系统的性能。从内存访问数据要比从磁盘访问数据快得多。因此，数据库管理器需要从磁盘读取或写入磁盘的次数越少，性能就越好。对一个或多个缓冲池进行配置之所以是调优的最重要方面，是因为连接至数据库的应用程序的大多数数据(不包括大对象和长字段数据)操作都在缓冲池中进行。缺省情况下，应用程序使用缓冲池 IBMDEFAULTBP，它是在创建数据库时创建的。当 SYSCAT.BUFFERPOOLS 目录表中该缓冲池的 NPAGES 值为 -1 时，DB2 数据库配置参数 BUFFPAGE 控制着缓冲池的大小。否则会忽略 BUFFPAGE 参数，并且用 NPAGES 参数所指定的页数创建缓冲池。建议对于仅使用一个缓冲池的应用程序，将 NPAGES 更改成 -1，这样 BUFFPAGE 就可以控制该缓冲池的大小。这使得更新和报告缓冲池大小以及其它 DB2 数据库配置参数变得更加方便。确保可以使用数据库配置中的 BUFFPAGE 参数来控制缓冲池大小之后，将该参数设置成合适的值。根据数据库的大小和应用程序的性质将该参数设置成一个合理的大值，这种做法很安全。通常，该参数的缺省值非常小，可能满足不了要求。db2 get snapshot for all bufferpools在数据库快照或缓冲池快照的快照输出中，查找下列logical reads和physical reads，这样就可以计算出缓冲池命中率，它可以帮助调优缓冲池:缓冲池命中率表明数据库管理器不需要从磁盘装入页(即该页已经在缓冲池中)就能处理页请求的时间百分比。缓冲池的命中率越高，使用磁盘 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%。要提高缓冲池命中率，请尝试下面这些方法:1. 增加缓冲池大小。2. 考虑分配多个缓冲池，如果可能的话，为每个经常被访问的大表所属的表空间分配一个缓冲池，为一组小表分配一个缓冲池，然后尝试一下使用不同大小的缓冲池以查看哪种组合会提供最佳性能。3. 如果已分配的内存不能帮助提高性能，那么请避免给缓冲池分配过多的内存。应当根据取自测试环境的快照信息来决定缓冲池的大小。4. 太小的缓冲池会产生过多的、不必要的物理 I/O。太大的缓冲池使系统处在操作系统页面调度的风险中并消耗不必要的 CPU 周期来管理过度分配的内存。正好合适的缓冲池大小就在太小和太大之间的某个平衡点上。适当的大小存在于回报将要开始减少的点上。获得最佳性能的SQL一条糟糕的 SQL 语句会彻底破坏一切。一个相对简单的 SQL 语句也能够搞糟一个调整得很好的数据库和机器。对于很多这些语句，天底下(或在文件中)没有 DB2 UDB 配置参数能够纠正因错误的 SQL 语句导致的高成本的情况。更糟糕的是，DBA 常常受到种种束缚:不能更改 SQL(可能是因为它是应用程序供应商提供的)。这给 DBA 只留下三条路可走：1. 更改或添加索引2. 更改群集3. 更改目录统计信息健壮的应用程序由成千上万条不同的 SQL 语句组成。这些语句执行的频率随应用程序的功能和日常的业务需要的不同而不同。SQL 语句的实际成本是它执行一次的成本乘以它执行的次数。每个DBA 所面临的重大的任务是，识别具有最高实际成本的语句的挑战，并且减少这些语句的成本。通过本机 DB2 Explain 实用程序、一些第三方供应商提供的工具或 DB2 UDB SQL Event Monitor 数据，可以计算出执行一次 SQL 语句所用的资源成本。但是语句执行频率只能通过仔细和耗时地分析 DB2 UDB SQL Event Monitor 的数据来了解。最佳性能不仅需要排除高成本 SQL 语句，而且需要确保相应的物理基础结构是适当的。当所有的调节旋钮都设置得恰到好处、内存被有效地分配到池和堆而且 I/O 均匀地分配到各个磁盘时，才可得到最佳性能。不可遗漏的Lock这些与锁相关的控制都是数据库配置参数:LOCKLIST 表明分配给锁列表的存储容量。每个数据库都有一个锁列表，锁列表包含了并发连接到该数据库的所有应用程序所持有的锁。锁定是数据库管理器用来控制多个应用程序并发访问数据库中数据的机制。行和表都可以被锁定。根据对象是否还持有其它锁，每把锁需要 32 个或 64 个字节的锁列表：1. 需要 64 个字节来持有某个对象上的锁，在这个对象上，没有持有其它锁。2. 需要 32 个字节来记录某个对象上的锁，在这个对象上，已经持有一个锁。MAXLOCKS 定义了应用程序持有的锁列表的百分比，在数据库管理器执行锁升级之前必须填充该锁列表。当一个应用程序所使用的锁列表百分比达到 MAXLOCKS 时，数据库管理器会升级这些锁，这意味着用表锁代替行锁，从而减少列表中锁的数量。当任何一个应用程序所持有的锁数量达到整个锁列表大小的这个百分比时，对该应用程序所持有的锁进行锁升级。如果锁列表用完了空间，那么也会发生锁升级。数据库管理器通过查看应用程序的锁列表并查找行锁最多的表，来决定对哪些锁进行升级。如果用一个表锁替换这些行锁，将不再会超出 MAXLOCKS 值，那么锁升级就会停止。否则，锁升级就会一直进行，直到所持有的锁列表百分比低于 MAXLOCKS。MAXLOCKS 参数乘以 MAXAPPLS 参数不能小于 100。虽然升级过程本身并不用花很多时间，但是锁定整个表(相对于锁定个别行)降低了并发性，而且数据库的整体性能可能会由于对受锁升级影响的表的后续访问而降低。LOCKTIMEOUT 的缺省值是 -1，这意味着将没有锁超时(对 OLTP 应用程序，这种情况可能会是灾难性的)。许多 DB2 用户用 LOCKTIMEOUT = -1。将 LOCKTIMEOUT 设置为很短的时间值，例如 10 或 15 秒。在锁上等待过长时间会在锁上产生雪崩效应。首先，用以下命令检查 LOCKTIMEOUT 的值：db2 get db cfg for DBNAME并查找包含以下文本的行：Lock timeout (sec) (LOCKTIMEOUT) = -1如果值是 -1，考虑使用以下命令将它更改为 15 秒(一定要首先询问应用程序开发者或供应商以确保应用程序能够处理锁超时)：db2 update db cfg for DBNAME using LOCKTIMEOUT 15同时应该监视锁等待的数量、锁等待时间和正在使用锁列表内存(lock list memory)的量。请发出以下命令：db2 get snapshot for database on DBNAME如果 Lock list memory in use (Bytes) 超过所定义 LOCKLIST 大小的 50%，那么在 LOCKLIST 数据库配置中增加 4k 页的数量。掩盖问题的SORTHEAPSORTHEAP 是一个数据库配置参数，它定义了私有排序所使用的私有内存页的最大数目，或共享排序所使用的共享内存页的最大数目。如果排序是私有排序，那么该参数影响代理程序私有内存。如果排序是共享排序，那么该参数影响数据库的共享内存。每个排序都有单独的由数据库管理器按需分配的排序堆。在排序堆中对数据进行排序。如果由优化器来指导排序堆大小的分配，那么用优化器提供的信息来分配的排序堆的大小要小于由该参数所指定的排序堆大小。SHEAPTHRES 是一个数据库管理器配置参数。私有和共享排序所使用内存的来源不一样。共享排序内存区的大小是在第一次连接到数据库时根据 SHEAPTHRES 值以静态方式预先确定的。私有排序内存区的大小是不受限制的。对于私有排序和共享排序，应用 SHEAPTHRES 参数的方式不同:对于私有排序，SHEAPTHRES 是对私有排序在任何给定的时间可以消耗的全部内存的实例级软限制。当实例的总私有排序内存消耗量达到这一限制时，为其它进入的私有排序请求而分配的内存会大大减少。对于共享排序，SHEAPTHRES 是对共享排序在任何给定的时间可以消耗的全部内存的数据库级硬限制。当达到这一限制时，不允许有其它共享排序内存请求，直到总的共享内存消耗量回落到 SHEAPTHRES 所指定的限制以下。使用排序堆的操作示例包括内存中表的散列连接和操作。阈值的显式定义防止数据库管理器将过多数量的内存用于大量排序。建议 使用数据库系统监视器来跟踪排序活动。 使用合适的索引使排序堆的使用降到最低。 当需要频繁进行大型排序时，增加 SORTHEAP 的值。 如果增加 SORTHEAP，请确定是否还需要调整数据库管理器配置文件中的 SHEAPTHRES 参数。 优化器用排序堆大小来确定存取路径。在更改该参数后请考虑重新绑定应用程序(使用 REBIND PACKAGE 命令)。 理想情况下，应当将排序堆阈值(SHEAPTHRES)参数合理地设置为在数据库管理器实例中设置的 SORTHEAP 参数最大值的倍数。该参数至少应当是实例中任何数据库所定义的最大 SORTHEAP 的两倍。如何更改这些参数要更改 SORTHEAP 和 SHEAPTHRES 的值，请运行以下命令:- SORTHEAP should be changed for individual database -db2 update db cfg for DB_NAME using SORTHEAP a_value- SHEAPTHRES is a database manager parameter -db2 update dbm cfg using SHEAPTHRES b_value研究步骤OLTP 应用程序不应该执行大型排序。大型排序在 CPU 和 I/O 资源方面的成本太高了。通常，SORTHEAP 大小的缺省值(256 个 4KB 页)就足够了。事实上，对于高并发性 OLTP，可能希望降低这个缺省值。当需要进一步研究时，可以发出下面这条命令:db2 update monitor switches using sort on然后，让应用程序运行一会，然后输入:db2 get snapshot for database on DBNAME根据该输出，可以计算每个事务的排序数目，并可以计算溢出了可用于排序的内存的那部分排序的百分比。SortsPerTransaction= (Total Sorts) / (Commit statements attempted + Rollback statements atte