调优DB2的最佳实践.docx
- 文档编号:2816444
- 上传时间:2023-05-04
- 格式:DOCX
- 页数:80
- 大小:133.09KB
调优DB2的最佳实践.docx
《调优DB2的最佳实践.docx》由会员分享,可在线阅读,更多相关《调优DB2的最佳实践.docx(80页珍藏版)》请在冰点文库上搜索。
调优DB2的最佳实践
简介
性能是关系到随需应变型应用程序成功与否的关键。
当那些应用程序使用IBM®DB2UniversalDatabase™作为数据存储时,至关重要的是,从一开始就应该知道有关如何在DB2UDB上取得尽可能好的性能的基础知识。
在本文中,我将给出关于调优DB2UDBV8系统的一些比较深入的建议。
我们将谈论这一过程中自始至终存在的性能问题。
您可以遵循从创建一个新数据库到运行应用程序这之间的流程。
通过本文可以看到如何使用DB2自动配置实用程序来初始配置数据库管理器和数据库环境。
接着,我将讨论创建缓冲池、表空间、表和索引的最佳实践。
另外,您可能还想改变一些重要配置参数的初始值,以便更好地支持应用程序,因此我们还将简介这些配置参数。
我们将论述基于监视器(monitor)细节输出的调优,从而展示如何使用快照监视(snapshotmonitoring)帮助调优SQL、缓冲池和各种不同的数据库管理器以及数据库配置参数。
接着,我们将进一步研究应用程序发送给DB2的SQL。
通过使用Explain,可以生成SQL采用的访问计划(accessplan),并寻找可以进一步优化的机会。
我们将考察DesignAdvisor这样一个工具,它可以根据所提供的SQL负载推荐出新的索引,或者评估现有的索引。
最后,我们还将讨论一些DB2SQL选项。
此外,持续(on-going)维护对于维持最佳性能非常重要。
所以我们将讨论一些可以帮助我们进行持续维护的实用程序。
对于那些正使用DB2ESEDatabasePartitioningFeature(DPF)的读者,我会用一节的篇幅谈论为使数据库高效运行而应该考虑的一些问题。
有时候可能会存在某种外在的瓶颈(来自DB2)而使您无法达到性能目标,本文列出了一些常见的瓶颈,以及用于监视这些瓶颈的实用程序。
最后,本文列出了一些有价值的IBM资源,以帮助您发现有价值的DB2信息。
本文是为那些在DB2数据库管理方面有中级技能的人而写的。
读前须知
在开始性能调优过程之前,应确保您已经应用了最新的DB2修订包(fixpack)。
修订包常常会带来性能的提高。
我们要使用DB2FixPak4作为本文的基础。
如果您使用的是FP4之前的版本,那么这种环境可能不能提供这里讨论的所有选项。
在进行调优时,最好是有一个关于数据库使用(即应用程序运行在DB2上的工作负载)的可再现场景,这样就可以利用这种可再现场景来量身定制调优效果。
例如,如果工作负载在不同的运行期间所经历的时间上有10%的变化量,那么就很难知道调优的真正效果如何。
此外,如果在两次运行中各自的工作负载不一样,也就难于衡量数据库管理器和数据库配置参数所发生的变化。
坚持跟踪所有的变化。
这样有助于开发调优脚本或者建议,以作为供其他DBA参考的历史,同时也有助于防止遵循不良的变化。
在大多数小节的最后,都有一些指向DB2v8HTMLDocumentation中相关小节的链接。
在线文档可以在
“十大”性能增强推动器
做了下面十件事情,您就几乎可以使数据库获得最佳性能。
通常您会发现,通过大约10%的配置变化,就可以达到最佳性能的90%。
我将在下面适当的小节(在圆括号中标出)中详细讨论其中的每一条:
确保有足够的磁盘(每个CPU有6-10个磁盘才是一个好的开端)。
每个表空间的容器应该跨越所有可用的磁盘。
有些表空间,例如SYSCATSPACE以及那些表数量不多的表空间,不需要展开到所有磁盘上,而那些具有大型用户或临时表的表空间则应该跨越所有磁盘。
(表空间)。
缓冲池应该占用可用内存的大约75%(OLTP)或50%(OLAP)(缓冲池)。
应该对所有表执行runstats,包括系统编目表(Runstats)。
使用DesignAdvisor为SQL工作负载推荐索引和检查索引(DesignAdvisor)。
使用ConfigurationAdvisor为应用程序环境配置数据库管理器和数据库(ConfigurationAdvisor)。
日志记录应该在一个独立的高速驱动器上进行,该驱动器由NEWLOGPATH数据库配置参数指定(Experimenting)。
通过频繁的提交可以增加并发性(SQL语句调优)。
应该增加SORTHEAP,以避免排序溢出(DBM和DB配置)。
对于系统编目表空间和临时表空间,表空间类型应该为SMS,而对于其他表空间,表空间类型应为DMS(rawdevice或者是文件)。
运行db2empfa,以便支持用于SMS表空间的多页(multi-page)文件的空间分配。
这将允许SMS表空间一次增长一个区段(Extend),而不是一页,从而可以加快那些大型的插入操作和溢出磁盘的排序操作(表空间)。
对于重复的语句,使用参数标记(SQL语句调优)。
创建数据库
创建一个数据库时,系统会缺省地创建3个系统管理存储(SystemManagedStorage,SMS)表空间(SYSCATSPACE、TEMPSPACE1和USERSPACE),以及一个4MB的缓冲池(IBMDEFAULTBP),这些表空间和缓冲池的页面大小都是4KB。
根据下面的建议,先删除TEMPSPACE1和USERSPACE然后再重新创建它们,通常这是一种可取的做法。
几乎在所有情况下,SYSCATSPACE都不需要再作进一步的优化,但是如果将其容器展开到几个磁盘上,性能上可能会有少量提升。
(稍后讨论)。
在创建数据库时,您可以利用自动配置选项来根据环境对数据库进行最初的配置。
当应用程序以编程方式创建DB2数据库时,这样做很方便,因为可以将这些选项从应用程序提供给DB2。
在自动配置数据库时不得不用到的另一个选项是更强大的ConfigurationAdvisorGUI,它不但可以配置数据库,而且还可以配置实例。
不过,要使用ConfigurationAdvisor,数据库必须首先存在。
我们将在随后的小节中讨论ConfigurationAdvisor。
在清单1中,我们使用CREATEDATABASE命令的自动配置选项在Windows中创建了一个数据库,该数据库有一个跨越两个可用磁盘的SYSCATSPACE。
清单1.使用自动配置选项创建数据库
createdatabaseprod1catalogtablespace
managedbysystemusing('c:
\\proddb\\cattbs\\01','d:
\\proddb\\cattbs\\02')
extentsize16prefetchsize32
autocon图usingmem_percent50workload_typesimplenum_stmts10
tpm20admin_priorityperformancenum_local_apps2num_remote_apps
200isolationCSbp_resizeableyesapplydbanddbm
表1显示了有效的自动配置输入关键字以及值:
表1.自动配置选项
关键字
有效值
缺省值
描述
mem_percent
1-100
25
分配给数据库的物理存储空间的百分比。
如果本服务器(不包括操作系统)上运行有其他应用程序,那么将其设为小于100的某个值
workload_type
simple,mixed,complex
mixed
simple型工作负载倾向于I/O密集型,并且大多数是事务处理(OLTP),而complex型工作负载则倾向于CPU密集型,并且大多数是查询(OLAP/DSS)
num_stmts
1-1000000
25
每个工作单元包含的语句条数
tpm
1-200000
60
每分钟的事务数。
admin_priority
performance,recovery,both
both
优化以获得更好性能(每分钟更多的事务数)或更好的回复时间
num_local_apps
0-5000
0
连接的本地应用程序的数目
num_remote_apps
0-5000
100
连接的远程应用程序的数目
isolation
RR,RS,CS,UR
RR
连接到该数据库的应用程序的隔离级别(RepeatableRead、ReadStability、CursorStability和UncommittedRead)。
bp_resizeable
yes,no
yes
是否可以在线更改缓冲池大小
ConfigurationAdvisor
如果您在创建数据库的时候已经使用了自动配置,那么这一步就不是很重要了。
ConfigurationAdvisor是一个GUI工具,它允许根据您针对一系列问题给出的回答自动配置数据库和实例。
通过使用这种工具,常常可以取得相当可观的性能提升。
这个工具可以从ControlCenter中通过右键单击数据库并选择"ConfigurationAdvisor"来打开。
当您回答完所有问题后,就可以生成结果,还可以选择应用结果。
图1展示了结果页面的屏幕快照:
图1.ConfigurationAdvisorResults屏幕
创建缓冲池
恰当地定义缓冲池是拥有一个运行良好的系统的关键之一。
对于32位操作系统,知道共享存储器的界限十分重要,因为这种界限将限制数据库的缓冲池(即数据库的全局存储器),使其不能超出以下界限(64位系统没有这样的界限):
∙AIX-1.75GB
∙Linux-1.75GB
∙Sun-3.35GB
∙HP-UX-approximately800MB
∙Windows-2-3GB(在NT/2000上的boot.ini中使用的是'3GB'switch)
用下面的公式计算近似的数据库全局存储器的使用:
清单2.计算全局存储器的使用(共享存储器)
bufferpools+dbheap+util_heap_sz+pkgcachesz+aslheapsz+locklist+approx10%overhead
如果启用了INTRA_PARALLEL,那么将sheapthres_shr的值加到总和中。
确定有多少缓冲池
对于数据库中一个表空间所使用的每一种页面大小,都需要至少一个缓冲池。
通常,缺省的IBMDEFAULTBP缓冲池是留给系统编目的。
为处理表空间的不同页面大小和行为,须创建新的缓冲池。
对于初学者,一开始为每种页面大小使用一个缓冲池,对于OLAP/DSS类型的工作负载更是如此。
DB2在其缓冲池的自我调优方面十分擅长,并且会将经常被访问的行放入内存,因此一个缓冲池就足够了。
(这一选择也避免了管理多个缓冲池的复杂性。
)
如果时间允许,并且需要进行改进,那么您可能希望使用多个缓冲池。
其思想是将访问最频繁的行放入一个缓冲池中。
在那些随机访问或者很少访问的表之间共享一个缓冲池可能会给缓冲池带来“污染”,因为有时候要为一个本来可能不会再去访问的行消耗空间,甚至可能将经常访问的行挤出到磁盘上。
如果将索引保留在它们自己的缓冲池中,那么在索引使用频繁的时候(例如,索引扫描)还可以显著地提高性能。
这与我们对表空间的讨论是紧密联系的,因为要根据表空间中表的行为来分配缓冲池。
如果采用多缓冲池的方法,对于初学者来说使用4个缓冲池比较合适:
∙一个中等大小的缓冲池,用于临时表空间。
∙一个大型的缓冲池,用于索引表空间。
∙一个大型的缓冲池,用于那些包含经常要访问的表的表空间。
∙一个小型的缓冲池,用于那些包含访问不多的表、随机访问的表或顺序访问的表的表空间。
对于DMS只包含LOB数据的表空间,可以为其分配任何缓冲池,因为LOB不占用缓冲池空间。
确定为缓冲池分配的内存
千万不要为缓冲池分配多于所能提供的内存,否则就会招致代价不菲的OS内存分页(memorypaging)。
通常来讲,如果没有进行监控,要想知道一开始为每个缓冲池分配多少内存是十分困难的。
对于OLTP类型的工作负载,一开始将75%的可用内存分配给缓冲池比较合适。
对于OLAP/DSS,经验法则告诉我们,应该将50%的可用内存分配给一个缓冲池(假设只有一种页面大小),而将剩下的50%分配给SORTHEAP。
使用基于块(block-based)的缓冲池
倚重于预取技术的OLAP查询可以得益于基于块的缓冲池。
缺省情况下,所有缓冲池都是基于页的,这意味着预取操作将把磁盘上相邻的页放入到不相邻的内存中。
而如果采用基于块的缓冲池,则DB2将使用块I/O一次将多个页读入缓冲池中,这样可以显著提高顺序预取的性能。
一个基于块的缓冲池由标准页区和一个块区同时组成。
CREATE和altERBUFFERPOOLSQL语句的NUMBLOCKPAGES参数用于定义块内存的大小,而BLOCKSIZE参数则指定每个块的大小,即在一次块I/O中从一个磁盘读取的页的数量。
共享相同区段大小的表空间应该成为一个特定的基于块的缓冲池的专门用户。
将BLOCKSIZE设置为等于正在使用该缓冲池的表空间的EXTENTSIZE。
确定分配多少内存给缓冲区内的块区要更为复杂一些。
如果要碰到大量的顺序预取操作,那么您很可能会想要更多基于块的缓冲池。
NUMBLOCKPAGES应该是BLOCKSIZE的倍数,并且不能大于缓冲池页面数量的98%。
先将它设小一点(不大于缓冲池总共大小的15%或刚好15%)。
在后面还可以根据快照监视(snapshotmonitoring)对其进行调整。
DB2v8Documentation:
∙Concepts==>Administration==>Databaseobjects==>BufferPoolManagement
∙Reference==>SQL==>SQLStatements==>CREATEBUFFERPOOL
∙Reference==>SQL==>SQLStatements==>altERBUFFERPOOL
∙Concepts==>Administration==>Performancetuning==>Operationalperformance==>Memory-useorganization
创建表空间
既然要为表空间分配缓冲池,关于缓冲池的上一节就跟涉及表空间的性能问题十分相关。
使用DB2ControlCenter是创建和配置表空间的最容易的方法,也是我们推荐的方法(右键单击数据库的TableSpaces文件夹并选择Create...)。
确定要创建的表空间的类型(DMS或SMS)
对于系统临时表空间和系统编目表空间,应该使用SystemManagedStorage(SMS),因为它允许表空间动态地增长和收缩。
如果有大量临时表要刷新到磁盘上(或者是没有足够的排序空间,或者是显式地创建临时表),则DMS会更有效一些。
当使用SMS时,应该运行实用程序'db2empfa',这个实用程序将支持多页文件分配,从而一次一个区段地增长表空间,而不是一次一页地增长表空间。
对于所有其他的表空间,应该使用DatabaseManagedStorage(DMS)。
DMS允许一个表跨越多个表空间(索引、用户数据和LOB),这样就减少了在预取和更新操作时索引、用户和LOB数据之间的争用,从而缩短了数据访问的时间。
通过使用DMSraw甚至还可以挤出额外的5-10%的性能提升。
确定页面大小
为了创建一个表,必须有一个表空间,其页面大小应足以容纳一行。
您可以选择使用4、8、16或32KB这几种页面大小。
有时候必须使用较大的页面大小,以回避某些数据库管理器的限制。
例如,表空间的最大尺寸与表空间的页面大小成比例。
如果使用4K的页面大小,那么表空间的大小(每个分区)最大是64GB,如果使用32K的页面大小,那么最大是512GB。
对于执行随机更新操作的OLTP应用程序,采用较小的页面大小更为可取,因为这样消耗的缓冲池中的空间更少。
对于要一次访问大量连续行的OLAP应用程序,通常使用较大页面大小效果会更好些,因为这样可以减少在读取特定数量的行时发出的I/O请求的数量。
较大的页面大小还允许您减少索引中的层数,因为在一页中可以保留更多的行指针。
然而,也有例外情况。
如果行长度小于页面大小的255分之1,则每一页中都将存在浪费的空间,因为每页最多只能有255行(对于索引数据页不适用)。
在这种情况下,采用较小的页面大小或许更合适一些。
例如,如果要使用32K的页面大小来存储平均大小为100字节的行,则一个32KB的页只能存储100*255=25500byte(24.9KB)。
这意味着每32KB中就有大约7KB要浪费掉。
确定表空间的数量
与缓冲池一样,一开始应该为每种页面大小使用一个缓冲池。
对于所使用的每种页面大小,必须存在一个具有匹配页面大小的系统临时表空间(以支持排序和重组)。
然后将所有享用匹配页面大小的表空间指派给具有相同页面大小的缓冲池。
如果您还关心性能问题,并且有时间投入,那么可以使用DMS表空间,并且根据使用情况来组织表。
另外,还要遵循前面给出的关于使用多个缓冲池的建议。
对于每种页面大小,创建一个:
∙系统临时表空间。
∙用于索引的常规表空间。
∙用于频繁访问的表的常规表空间。
∙用于访问不多的表、随机访问的表以及顺序访问的表的常规表空间。
∙用于LOB数据的大型表空间。
容器布局
一开始最好是对于每个CPU分配6-10个磁盘给表空间。
每个表空间应该跨越多个磁盘,也就是说,在每个可用磁盘上有一个(且不多于一个)容器。
有多少个表空间,就应该在每个磁盘上创建相同数量的逻辑卷(UNIX)。
这样一来,每个表空间在每个磁盘上都有自己的逻辑卷(logicalvolume),用以放置容器。
如果不是使用rawdevice,那么就需要在每个逻辑卷内创建一个文件系统。
磁盘阵列和存储子系统
对于大型磁盘系统,应该使用单个容器。
此外,还需要为表空间设置DB2ProfileRegistry变量DB2_PARALLEL_IO。
这一点放在概要注册表一节中讨论。
区段大小
ExtentSize指定在跳到下一个容器之前,可以写入到一个容器中的PAGESIZE页面的数量,这个参数是在创建表空间时定义的(之后不能轻易修改)。
处理较小的表时,使用较小的区段效率会更高一些。
下面的经验法则是建立在表空间中每个表的平均大小的基础上的:
∙如果小于25MB,ExtentSize为8
∙如果介于25到250MB之间,则ExtentSize为16
∙如果介于250MB到2GB之间,则ExtentSize为32
∙如果大于2GB,则ExtentSize为64
对于OLAP数据库和大部分都要扫描(仅限于查询)的表,或者增长速度很快的表,应使用较大的值。
如果表空间驻留在一个磁盘阵列上,则应将区段大小设置成条纹大小(也就是说,写入到阵列中一个磁盘上的数据)。
预取大小
通过使用altERTABLESPACE可以轻易地修改预取大小。
最优设置差不多是这样的:
PrefetchSize=(#Containersofthetablespaceondifferentphysicaldisks)*ExtentSize
如果表空间驻留在一个磁盘阵列上,则设置如下:
PREFETCHSIZE=EXTENTSIZE*(#ofnon-paritydisksinarray)。
DB2v8Documentation:
∙Concepts==>Administration==>Databasedesign==>Physical==>TableSpaceDesign
∙Reference==>SQL==>SQLStatements==>CREATETABLESPACE
∙Reference==>SQL==>SQLStatements==>altERTABLESPACE
创建表
多维群集(Multidimensionalclustering,MDC)
MDC提供了数据在多个维上的灵活的、连续的和自动的多维群集。
它提升了查询的性能,并且减少了在插入、更新和删除期间对REORG和索引维护的需要。
多维群集从物理上把表数据同时沿着多个维群集起来,这与使用表上的多个独立的群集的索引类似。
MDC通常用于帮助提高对大型表的复杂查询的性能。
这里不需要使用REORG来重新群集索引,因为MDC会自动地、动态地维护每个维上的群集。
对于一个MDC,最合适的是那些具有范围、相等和连接谓词的访问多行的查询。
千万不要使用具有惟一性的列作为一个维,因为这样会导致一个表不必要地变大。
如果具有每种维值组合(即单元)的行不是很多,应避免使用太多的维。
为获得最佳的性能,那么至少需要有足够的行来填满每个单元的块,也就是该表所在表空间的区段大小。
DB2v8Documentation:
∙Concepts==>Administration==>Databasedesign==>Logical==>Multidimensionalclustering(MDC)
∙Concepts==>Administration==>Databaseobjects==>Tables==>Multidimensionalclustering(MDC)tables
物化查询表(MQT)
MQT可用于提升使用GROUPBY、GROUPING、RANK或ROLLUPOLAP函数的查询的性能。
MQT的使用对用户来说是透明的,DB2选择何时使用MQT来达到优化的目的。
DB2使用MQT在内部维护被查询的分组的总结结果,这样用户就可以直接访问DB2维护的分组,而不必去读动辄数GB的数据来寻找答案。
这些MQT还可以在分区间复制,以避免这种信息在分区间的散播,从而帮助提升合并连接(collocatedjion)的性能。
CREATETABLE选项
对于30字节或更少字节的列,应避免使用VARCHAR数据类型,因为这种情况下,VARCHAR类型通常会浪费空间,所以建议使用CH
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 调优 DB2 最佳 实践