大数据量数据整合与查询性能优化IBM AIX 53v10文档格式.docx
- 文档编号:16190341
- 上传时间:2022-11-21
- 格式:DOCX
- 页数:25
- 大小:348.68KB
大数据量数据整合与查询性能优化IBM AIX 53v10文档格式.docx
《大数据量数据整合与查询性能优化IBM AIX 53v10文档格式.docx》由会员分享,可在线阅读,更多相关《大数据量数据整合与查询性能优化IBM AIX 53v10文档格式.docx(25页珍藏版)》请在冰豆网上搜索。
1DB2数据库-4-
1.1数据库对象-4-
1.1.1实例-5-
1.1.2数据库-5-
1.1.3节点组-5-
1.1.4表-5-
1.1.5视图-5-
1.1.6索引-5-
1.1.7模式-7-
1.1.8系统目录表-7-
1.2存储器对象-7-
1.2.1表空间-7-
1.2.2容器-9-
1.2.3缓冲池-9-
2表空间存储-10-
2.1AIX系统存储管理简介-10-
2.2裸设备vs文件系统-11-
2.3系统管理vs数据库管理-12-
2.4如何合理创建表空间设备-13-
2.4.1原则-13-
2.4.2表空间容器的创建方法-13-
2.4.3整合数据库表空间设备情况-14-
2.5建库语句参考-14-
3缓冲池和表空间-16-
3.1缓冲池-16-
3.2缓冲池大小-16-
3.3表空间页大小及容器分布-16-
3.4系统临时表空间-17-
3.5区段大小和预取大小-17-
3.5.1区段大小-18-
3.5.2预取大小-18-
4提高SQL语句执行效率-19-
4.1建立索引-19-
4.2采用表分区-20-
4.3减少对数据库的更新和删除操作-20-
4.4如何使访问更高效-21-
5参数调优-22-
5.1AIX系统-22-
5.2DB2环境变量-23-
5.3DB2数据库实例参数-23-
5.4DB2数据库参数-24-
6开始行动-25-
7参考文献-26-
1DB2数据库
1.1数据库对象
关键的数据库对象包括:
实例、数据库、节点组、表、视图、索引、模式、系统目录表。
图1显示了一些数据库对象之间的关系。
它也显示了表、索引和长整型数据存储在表空间中的情况。
图1.一些数据库对象之间的关系
1.1.1实例
实例(有时称为数据库管理程序)是管理数据的DB2代码。
它控制可对数据执行的操作,并管理分配给它的系统资源。
每一个实例都是一个完整的环境。
一个实例有它自己的数据库(其他的实例不能存取它)。
它还有独立的安全性,不受同一机器(系统)上其他实例的影响。
1.1.2数据库
关系数据库将数据表示成表的集合。
表由数目已定的列和任意数目的行组成。
每个数据库都包括一组描述数据的逻辑和物理结构的系统目录表,一个包含为该数据库分配的参数值的配置文件以及一个带有正在进行的事务和可归档事务的恢复日志。
1.1.3节点组
节点组是一个或多个数据库分区组成的集合。
当您想为数据库创建表时,首先创建节点组以存储表空间,然后创建表空间以存储表。
1.1.4表
表由逻辑排列的行和列数据组成。
所有数据库的表数据都被存储在表空间中。
1.1.5视图
视图是高效率的数据呈现方法(无需维护数据)。
视图不是实际的表,不需要永久性存储器。
创建并使用一个“虚拟表”。
视图可以包括它所基于的表中的所有或某些列或行。
例如,可以在视图中连接一个部门表和一个员工表,以便可以列出特定部门中的所有雇员。
图2显示了表和视图的关系。
1.1.6索引
索引是一组关键字,其每一个都指向一些行。
例如,图3中,表A的一个索引基于表中的员工号码。
此关键字提供指向表行的指针:
员工号码19指向员工KMP。
通过使用指针创建指向数据的直接路径,索引是更有效的存取表行成为可能。
SQL优化器自动选择最有效率的存取表中数据的方法。
当确定最快速的数据存取路径时,优化器会将索引考虑在内。
可创建唯一索引以确保索引关键字的唯一性。
索引关键字是定义了索引的一个列或一些列的有序集合。
使用唯一索引将确保在编入的索引的列中,每个索引关键字的值都是唯一的。
图3显示了索引与表之间的关系。
图2.表和视图之间的关系
图3.索引与表之间的关系
1.1.7模式
模式是一个标识符,如用户ID,它帮助分组表和其他数据库对象。
模式可以归个人拥有,拥有者可以控制对数据以及其中的对象的存取。
模式也可以是数据库中的对象。
它可以在创建模式中的第一个对象时自动创建。
这样的对象可以是任何可以由模式名限定的对象,如表、索引、视图、程序包、单值类型、函数或触发器。
1.1.8系统目录表
每个数据库都包括一组描述数据的逻辑和物理结构的系统目录表。
DB2为每个数据库维护一大组系统目录表。
这些表包含有关数据库对象(例如,用户表、视图和索引)的定义信息,以及用户对这些对象所拥有的权限的安全性信息。
它们在数据库创建时被创建,并在常规操作期间得到更新。
不能显示的创建或卸载下它们,但是可以使用目录视图查询和查看它们的内容。
1.2存储器对象
下列数据库对象用来定义在系统上存储数据的方式以及改进(与存取数据相关的)性能的方法:
表空间、容器和缓冲池。
1.2.1表空间
数据库由称为表空间的部件组成。
表空间是用来存储表的位置。
当创建表时,您可以决定将特定对象(如索引和大对象)数据与其余表数据分开存放。
表空间也可以分布在一个或多个物理存储设备上。
图4显示了在表空间之间分布数据时具有的一些灵活性。
图4.表空间
将容器分配给表空间。
容器是分配的物理存储器(如文件和设备)。
表空间可以使系统管理空间(SMS)或数据库管理空间(DMS)。
对于SMS表空间,每个容器都是操作系统的文件空间中的一个目录,由操作系统的文件管理器控制存储空间。
对于DMS表空间,每个容器或者是固定大小的预分配文件,或者是物理设备如磁盘,由数据库管理程序控制存储空间。
表空间有三种类型:
规则、临时和长整数。
包含用户数据的表存放在规则表空间中。
缺省用户表空间名为USERSPACE1。
索引也存储在规则表空间中。
系统目录表存放在规则表空间中。
缺省系统目录表空间名为SYSCATSAPCE。
包含长整数字段数据或长整数对象数据(如多媒体对象)的表存放在长整数表空间中。
临时表空间分为系统临时表空间或用户临时表空间。
系统临时表空间用来存储SQL操作(如排序、重组表、创建索引和连接表)期间所需的内部临时数据。
虽然可以创建任意数目个系统临时表空间,但建议您只适用大多数表所使用的页大小创建一个。
缺省系统临时表空间名为TEMPSPACE1。
用户临时表空间用来存储已说明全局临时表(已说明全局临时表存储的是应用程序临时数据)。
用户临时表空间不是在数据库创建时缺省创建的。
1.2.2容器
容器是物理存储设备。
它可以由目录名、设备名或文件名标识。
将为表空间分配容器。
单个表空间可以横跨多个容器,但每个容器只能属于一个表空间。
图5举例说明了表与数据库中的表空间、相关联的容器和磁盘之间的关系。
图5.表空间、相关联的容器和磁盘之间的关系
EMPLOYEE、DEPARTMENT和PROJECT表在HUMANRES表空间中,该表空间横跨容器0、1、2、3和4。
此示例显示每个容器存在于不同的磁盘上。
任何表的数据都以循环方式存储在表空间中的所有容器中。
这能在属于给定表空间容器之间平衡数据。
数据库管理程序在使用另一个容器之前写入一个容器的页数称为数据块大小。
1.2.3缓冲池
缓冲池指的是从磁盘读去高速缓存表和索引数据页时或修改它们时分配给它们的主存储器。
缓冲池的目的是改进系统性能。
从内存存取数据要比从磁盘存取数据块的多;
因此,数据库管理程序需要读写(I/O)的次数越少,性能也越好。
(可以创建多个缓冲池,虽然在大多数情况下只需要一个。
)
因为可以缩短慢速I/O所造成的延迟,所以缓冲池的配置是最重要的调整项目。
2表空间存储
在存储管理上,AIX系统采用逻辑卷管理策略。
一个设备(逻辑卷)可以跨越多个磁盘,这就为并行读写创造了可能。
目录表空间和日志表空间均存储在文件系统中,而数据表空间的存储类型通常是裸设备。
一个表空间使用一个或多个设备(文件系统或裸设备)。
2.1AIX系统存储管理简介
在AIX系统中,文件系统是建立在逻辑卷上的,逻辑卷是属于一个卷组的,卷组是由实际的物理磁盘组成,逻辑卷在实际物理硬盘中可以不连续,它可以跨越多个物理硬盘而存在。
AIX系统中物理与逻辑区段的映射如图6所示
图6.物理到逻辑区段的映射
在逻辑上,逻辑卷存在于卷组中,而在物理上,它存在于物理卷上,物理卷是硬盘。
硬盘的界面分为5个同心区域:
外边缘(Outer-Edge)、外中间(Outer-Middle)、中央(Center)、内中间(Inner-Middle)和内边缘(Inner-Edge),如图7所示。
由于磁盘机械装置的物理移动,边缘(Edge)区域的搜索时间最长。
逻辑卷位于哪个区域上,对于逻辑卷的读写效率影响很大。
其中,中央(Center)区域是磁盘搜索时间最短和访问速度最快的。
通常,把经常要访问的逻辑卷放在磁盘的中央区域,把很少访问的逻辑卷放在磁盘的边缘(Edge)区域,其它逻辑卷放在磁盘的中间(Middle)区域。
图7.硬盘的分布
2.2裸设备vs文件系统
数据库的物理实现对数据库的运行时性能至关重要,物理实现更多的需要考虑硬件和系统平台。
整合库部署在一台1900MHz*4CPU,16GB内存的IBM小型机上,操作系统是IBMAIX5.3,数据库管理系统是DB2v9.1。
数据库服务器的性能通常是通过事务吞吐量和应用程序响应时间来测量的,该性能主要取决于I/O子系统的性能。
为了获得最大可能的I/O吞吐量,数据库管理员和系统管理员需要特别注意数据库表数据的布局。
在AIX系统中通常有两种数据存储形式:
裸设备、文件系统。
裸设备,也叫裸分区(原始分区),是一种没有经过格式化,不被Unix通过文件系统来读取的特殊字符设备。
它由应用程序负责对它进行读写操作。
不经过文件系统的缓冲。
因为使用裸设备避免了再经过AIX操作系统这一层,数据库管理系统直接读写硬盘,所以使用裸设备对于读写频繁的数据库应用来说,可以极大地提高数据库系统的性能。
当然,这是以磁盘的I/O非常大,磁盘I/O已经成为系统瓶颈的情况下才成立。
如果磁盘读写确实非常频繁,以至于磁盘读写成为系统瓶颈的情况成立,那么采用裸设备确实可以大大提高性能。
DB2数据库有三种表空间:
目录表空间、数据表空间和日志表空间。
目录表空间和日志表空间均存储在文件系统中,而数据表空间的存储类型是基于实际情况和以往的开发经验来决定。
在我们的系统中,在ETL的任何一个阶段都要求高速的磁盘I/O,尤其是数据抽取、匹配存储过程和数据导入整合区存储过程这三个过程,需要大批量的数据读取或写入,磁盘I/O是整合库系统性能的瓶颈。
根据经验,数据表空间将采用裸设备的存储方式。
于是,整合库的物理实现如下表所示。
通常的系统可以采用这种模式。
逻辑结构
DB2表空间
逻辑设备/文件系统
空间大小
备注
目录表空间
SYSCATSPACE
/db2_tag
3GB
文件系统
数据处理区
TBSPTEMP
/dev/rdb2tbsptemplv
/dev/rdb2tbsptemp2lv
10GB
裸设备
代码对应区
TBSPINTE
/dev/rdb2tbspintelv
20GB
整合区
日志表空间
-
/intedb_log
2.3系统管理vs数据库管理
图8.表空间类型比较:
系统管理和数据库管理
2.4如何合理创建表空间设备
2.4.1原则
1.一个表空间跨多个物理磁盘,以获得更好的I/O并行性
2.根据重要性不同,让表空间分布于磁盘的不同位置
▫索引、重要数据位于中心
▫将次重要数据、文件系统放在中间和边缘
2.4.2表空间容器的创建方法
按以下说明创建逻辑卷,并根据设备的重要性依次创建(首先创建最重要的),可以保证最重要的位于中央,按重要性依次往外排列,可以做到读写性能最优。
1.smitlv
2.选择多个物理盘(PHYSICALVOLUMEnames),界面如图9所示。
移动光标,用ESC+7选择多块物理磁盘PV。
图9.创建逻辑卷时选择多个物理盘
3.在LogicalvolumeTYPE中输入raw,即裸设备类型。
4.物理盘区域(POSITIONonphysicalvolume)选择center,将范围(RANGEofphysicalvolumes)选择最大(maximum)。
如图10所示。
图10.使逻辑卷位于中央
2.4.3整合数据库表空间设备情况
整合数据库在创建表空间设备时完全遵循了2.4.1节中的原则。
在创建逻辑卷时按照图9所示创建,保证了一个设备跨越多个物理磁盘(尽可能多),以获得更好的I/O并行性。
并且,严格按照重要性降低的次序作为创建逻辑卷的顺序。
图11显示的是整合数据库在磁盘hdiskpower8上的设备创建情况。
由图中看出,整合数据库严格遵循了设备按照重要性分布的原则:
图11.设备按照重要性分布
2.5建库语句参考
统计分析系统数据库部分建库语句:
---创建数据库在一个2G的文件系统/anly_tag
db2createdatabaseanlyon/anly_tagaliasanlyusingcodesetgbkterritorycncollateusingsystem
---创建bufferpool
db2connecttoanlyuserdb2admin
db2createbufferpoolTMPBF32Kimmediatesize32000pagesize32k
---创建数据表空间(8G*4)并指定相应的bufferpool
db2createtablespaceTBSPANLYpagesize32kmanagedbydatabaseusing(device'
/dev/ranlytbsplv'
262144)bufferpoolTMPBF32K
db2altertablespaceTBSPANLYadd(device'
/dev/ranlytbsplv2'
262144)
/dev/ranlytbsplv3'
/dev/ranlytbsplv4'
---创建系统临时表空间(2G的文件系统/anly_tmp/tmp)并指定相应的bufferpool
db2createsystemtemporarytablespaceTBSPTMP32Kpagesize32kmanagedbysystemusing('
/anly_tmp/tmp'
)extentsize8prefetchsize16bufferpoolTMPBF32K
----修改日志文件大小为25000*4K
db2updatedbcfgforanlyusingLOGFILSIZ25000
db2updatedbcfgforanlyusingLOGPRIMARY20
db2updatedbcfgforanlyusingLOGSECOND50
db2updatedbcfgforanlyusingNEWLOGPATH/anly_log
----修改表空间大小
db2altertablespacetbsptempdrop(device'
/dev/rdb2tbsptemp2lv'
db2altertablespacetbsptempresize(device'
/dev/rdb2tbsptemp3lv'
327680)
通常,为每一页面大小的系统临时表空间建立一个缓冲池就足够了。
因为在缓冲池管理方面,我们完全可以信任DB2。
如果想在这方面继续提高性能,则可以参考本节的文字。
DB2在其缓冲池的自我调优方面十分擅长,并且会将经常被访问的行放入内存,因此一个缓冲池就足够了。
(这一选择也避免了管理多个缓冲池的复杂性。
如果时间允许,并且需要进行改进,那么可能需要使用多个缓冲池。
其思想是将访问最频繁的行放入一个缓冲池中。
在那些随机访问或者很少访问的表之间共享一个缓冲池可能会给缓冲池带来“污染”,因为有时候要为一个本来可能不会再去访问的行消耗空间,甚至可能将经常访问的行挤出到磁盘上。
如果将索引保留在它们自己的缓冲池中,那么在索引使用频繁的时候(例如,索引扫描)还可以显著地提高性能。
这与我们对表空间的讨论是紧密联系的,因为要根据表空间中表的行为来分配缓冲池。
如果采用多缓冲池的方法,采用4个缓冲池比较合适:
1.一个中等大小的缓冲池,用于临时表空间。
2.一个大型的缓冲池,用于索引表空间。
3.一个大型的缓冲池,用于那些包含经常要访问的表的表空间。
4.一个小型的缓冲池,用于那些包含访问不多的表、随机访问的表或顺序访问的表的表空间。
缓冲池大小需要综合考虑系统内存和服务器工作负载情况而定。
通常,OLTP类型分配75%的可用内存;
OLAP类型分配50%的可用内存,将剩下的50%分配给SORTHEAP。
缓冲池对应于系统的内存空间,所以千万不要为缓冲池分配多于所能提供的内存,否则就会招致代价不菲的OS内存分页。
通常来讲,如果没有进行监控,要想知道一开始为每个缓冲池分配多少内存是十分困难的。
对于OLTP类型的工作负载,一开始将75%的可用内存分配给缓冲池比较合适。
对于OLAP/DSS,经验法则告诉我们,应该将50%的可用内存分配给一个缓冲池(假设只有一种页面大小),而将剩下的50%分配给SORTHEAP。
3.3表空间页大小及容器分布
在页大小足以容纳一条记录的前提下,随机更新操作,倾向于用较小的页大小;
而对于一次访问大量行的操作,较大的页大小可以提供更好的性能。
每个表空间应该跨越多个物理磁盘。
以获得更好的I/O并行性。
为了创建一个表,必须有一个表空间,其页面大小应足以容纳一行。
您可以选择使用4、8、16或32KB这几种页面大小。
有时候必须使用较大的页面大小,以回避某些数据库管理器的限制。
例如,表空间的最大尺寸与表空间的页面大小成比例。
如果使用4K的页面大小,那么表空间的大小(每个分区)最大是64GB,如果使用32K的页面大小,那么最大是512GB。
对于执行随机更新操作的OLTP应用程序,采用较小的页面大小更为可取,因为这样消耗的缓冲池中的空间更少。
对于要一次访问大量连续行的OLAP应用程序,通常使用较大页面大小效果会更好些,因为这样可以减少在读取特定数量的行时发出的I/O请求的数量。
较大的页面大小还允许您减少索引中的层数,因为在一页中可以保留更多的行指针。
然而,也有例外情况。
如果行长度小于页面大小的255分之1,则每一页中都将存在浪费的空间,因为每页最多只能有255行(对于索引数据页不适用)。
在这种情况下,采用较小的页面大小或许更合适一些。
为每种页面大小的表空间,建立一个系统临时表空间,通常使用文件系统,大小根据排序和重组事物的大小确定。
对于所使用的每种页面大小,必须存在一个具有匹配页面大小的系统临时表空间(以支持排序和重组)。
然后将所有享用匹配页面大小的表空间指派给具有相同页面大小的缓冲池。
如果对性能还有更苛刻的要求,并且有时间投入,那么可以使用DMS表空间,并且根据使用情况来组织表。
另外,还要遵循前面给出的关于使用多个缓冲池的建议。
对于每种页面大小,创建一个:
1.系统临时表空间。
2.用于索引的常规表空间。
3.用于频繁访问的表的常规表空间。
4.用于访问不多的表、随机访问的表以及顺序访问的表的常规表空间。
5.用于LOB数据的大型表空间。
区段大小(extentsize)和预取大小(prefetchsize)是表空间的参数,对于数据存取性能至关重要。
区段大小跟表空间中表的平均大小有一定的比例关系,通常表越大,区段大小也越大。
如果表空间驻留在磁盘阵列上,则区段大小应设置成条纹大小。
预取大小通过公式确定:
prefetchsize=(#containersofthetablespaceondifferentphysicaldisks)*extentsize。
磁盘阵列的情况:
prefetchsize=extentsize*(#ofnon-paritydisksinarray)。
3.5.1区段大小
extentsize指定在跳到下一个容器之前,可以写入到一个容器中的PAGESIZE页面的数量,这个参数是在创建表空间时定义的(之后不能轻易修改)。
处理较小的表时,使用较小的区段效率会更高一些。
下面的经验法则是建立在表空间中每个表的平均大小的基础上的:
1.如果小于25MB,extentsize为8
2.如果介于25到250MB之间,则extentsize为16
3.如果介于250MB到2GB之间,则extentsize为32
4.如果大于2GB,则extentsize为64
对于OLAP数据库和大部分都要扫描(仅限于查询)的表,或者增长速度很快的表,应使用较大的值。
如果表空间驻留在一个磁盘阵列上,则应将区段大小设置成条纹大小(也就是说,写入到阵列中一个磁盘上的数据)。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 大数据量数据整合与查询性能优化IBM AIX 53v10 数据量 数据 整合 查询 性能 优化 IBM 53 v10
![提示](https://static.bdocx.com/images/bang_tan.gif)