DB2数据仓库规划实践:之二_st


Business Intelligence Center of Competency Team (China) © Copyright IBM Corporation 2007 数据仓库系统规划、实现及优化 ——基于IBM DB2技术的数据仓库系统 (之二) 区波 IBM 软件部 BICOC 010-63612733 oubo@cn.ibm.com IBM Business Intelligence © Copyright IBM Corporation 2007 议程 . DB2数据仓库设计回顾 . BCU深度探索 – 中端配置 – 高端配置 . InfoSphere Warehouse (DWE)在BCU上的部署 . Q & A . 附一:关于表压缩 . 附二:常用参数建议 IBM Business Intelligence © Copyright IBM Corporation 2007 查询性能得到有效地提高 数据库架构 服务器 存储 内存 CPUs 群集/ SMP 架构 部署 LUNs 磁盘 容量 RAID 100% 100% IBM 方法 系统 软件 Share Nothing 架构 LUN部署 每个Array 中 LUN的数量 磁盘容量 RAID100% 存储 实际吞吐量 50% 传统的方法 . 30%+ 消耗在 I/O . 较高的 I/O 等待 . 处理器使用较低 . BI 性能问题 60%以上和I/O相关 + + = TotalStorage® 高效的吞吐 系统规划的考虑 IBM Business Intelligence © Copyright IBM Corporation 2007 数据库的逻辑结构 Non- DB2 ETL Administration BCU Catalog BPU Coordinator BPU 1 Coordinator BPU for Non-DB2 ETL Single-partition Data BPU 1 Preferred BCU Solution for a Data Warehouse using DB2 with DPF Partitions Multi-partition Data BPU 1 DB2 Data BCU Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 8 Multi-partition Data BPU 1 DB2 Data BCU Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 8 Multi-partition Data BPU 1 DB2 Data BCU Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 8 Multi-partition Data BPU 1 DB2 Data BCU Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 8 Non- DB2 ETL 管理分区 1 管理分区 . Catalog . Coordinator . Single-Partition Data Coordinator 分区 Non-DB2 ETL 推荐的数据仓库应用中 DB2 DPF 部署 Multi-partition Data BPU 1 DB2 数据分区 Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1多分区数据 8个分区 Multi-partition Data BPU 1 DB2 数据分区 Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1 Multi-partition Data BPU 1 DB2 数据分区 Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1 Multi-partition Data BPU 1 DB2 数据分区 Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1Multi-partition Data BPU 1 多分区数据 8个分区 多分区数据 8个分区 多分区数据 8个分区 IBM Business Intelligence © Copyright IBM Corporation 2007 数据库分区--资源的平衡分配 . 服务器资源(LPAR或单台服务器) – 8 Power5 CPUs – 32GB RAM – FC卡: •4GB:4块数据/2块备份 – 以太网:4×1000Mb – 独占IO的存储 . 支持多个数据分区,每个数据分区 – 1 CPU – 4 GB RAM 8 CPUs I/O Channels Power5 BCU 32GB of memory Communication 8 BPUs AIX OS image Storage Capacity 12 3 4 56 7 8 主机:4×4GB FC 1400MBs SAN:4×4GB 口 1400MBs 存储:2×DS4800 1400MBs BCU v2.1 IBM Business Intelligence © Copyright IBM Corporation 2007 服务器群集 . 每台服务器上多个分区 . 多台服务器共同构成的MPP群集 数据节点 。。。 HA节点 汇集/ETL节点 管理节点 IBM Business Intelligence © Copyright IBM Corporation 2007 IBM 平衡数据仓库™ ——构建途径 Foundation Module Data Module User Module Application Module Data Module Data Module Data Module Failover Module If additional support is required from the application end, an optional Application Module can be added to support more demanding programs IBM Business Intelligence © Copyright IBM Corporation 2007 议程 . DB2数据仓库设计回顾 . BCU深度探索 – 中端配置 – 高端配置 . InfoSphere Warehouse (DWE)在BCU上的部署 . Q & A . 附一:关于表压缩 . 附二:常用参数建议 IBM Business Intelligence © Copyright IBM Corporation 2007 BCU 数据仓库环境深度探索 IBM Business Intelligence © Copyright IBM Corporation 2007 基础模块 . p5 550 server – 2 dual-core 2.1GHz processors – 32 GB memory . DS4700 storage – 16 internal 146 GB 15k disks – Optional EXP810 with 16 146 GB 15k disks IBM Business Intelligence © Copyright IBM Corporation 2007 DS4700 DS4700 磁盘排布 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 o DD o D o D o P oo D o D o D oo DP o D o DD o D o P o S o • 基础模块运行在一套操作系统映象内,支持3类功能 : •catalog •coordinator •non-partitioned data 基础模块的存储设计 IBM Business Intelligence © Copyright IBM Corporation 2007 基础模块 IBM Business Intelligence © Copyright IBM Corporation 2007 数据模块 . p570 服务器 – 2 * dual-core 4.7GHz CPU – 32 GB 内存 . DS4800 存储 – 9 * EXP810 扩展柜 – 共133 块 146 GB 15k rpm磁盘 IBM Business Intelligence © Copyright IBM Corporation 2007 数据模块的构成 I/O 通道 1BCU = 8 * BPU 1 CPU 4GB 内存 1FC卡 Zone LUN LUN LUN LUN 1BPU 注: 1. 图中CPU按p5考虑,如为p6则为每BPC 1/2 Core 2. FC卡按2G考虑,如为4G卡则为每BPC 1/2FC卡(200MB/s) I/O 通道 1 CPU 4GB 内存 1FC卡 Zone LUN LUN LUN LUN 1BPU 1 CPU 4GB 内存 1FC卡 Zone LUN LUN LUN LUN 1BPU 1 CPU 4GB 内存 1FC卡 Zone LUN LUN LUN LUN 1BPU 1 CPU 4GB 内存 1FC卡 Zone LUN LUN LUN LUN 1BPU 1 CPU 4GB 内存 1FC卡 Zone LUN LUN LUN LUN 1BPU 1 CPU 4GB 内存 1FC卡 Zone LUN LUN LUN LUN 1BPU 1 CPU 4GB 内存 1FC卡 Zone LUN LUN LUN LUN 1BPU p5-570 DS4x00 200MB/s 200MB/s IBM Business Intelligence © Copyright IBM Corporation 2007 数据模块的构成 I/O 通道 1BCU = 8 * BPU 4GB 内存 Zone LUN LUN LUN LUN 1BPU 注: 1. 采用p6则为每BPU 1/2 Core 2. 采用4G FC卡则为每BPU 1/2FC卡 I/O 通道 4GB 内存 LUN LUN LUN LUN 1BPU 4GB 内存 Zone LUN LUN LUN LUN 1BPU 4GB 内存 LUN LUN LUN LUN 1BPU 4GB 内存 Zone LUN LUN LUN LUN 1BPU 4GB 内存 LUN LUN LUN LUN 1BPU 4GB 内存 Zone LUN LUN LUN LUN 1BPU 4GB 内存 LUN LUN LUN LUN 1BPU DS4800 400MB/s 1 CPU 1FC卡 1 CPU 1FC卡 1 CPU 1FC卡 1 CPU 1FC卡 p6-570 400MB/s IBM Business Intelligence © Copyright IBM Corporation 2007 SAN的配置 p570 A FC4 FC5FC0 FC1 A BA B Storage Partition 0 Storage Partition 1 注: FC2, FC3 为备份带库保留 IBM Business Intelligence © Copyright IBM Corporation 2007 San Switch San Switch -- AA San Switch - B p570 A FC4 FC5FC0 FC1 A B Storage Partition 1 A B Storage Partition 0 A B Storage Partition 1 A B Storage Partition 0 p570 B FC4 FC5FC0 FC1 Zone 1 Zone 2Zone 3 Zone 4 SAN的配置——考虑HA 注: FC2, FC3 为备份带库保留 IBM Business Intelligence © Copyright IBM Corporation 2007 DS4800 存储 . 8 个 4 Gb/s 口连成环路,最多支持 224块磁盘 – 每个口的都能达同等的吞吐量: 400 MB/s Port Port 44 33 22 11 Port Port 11 22 33 44 Channel Channel 2211 Channel Channel 4433 IBM Business Intelligence © Copyright IBM Corporation 2007 Expansion ports A AB B EXP810扩展柜 IBM Business Intelligence © Copyright IBM Corporation 2007 Controller A Controller B EXP810 - 11 EXP810 - 12 EXP810 - 25 EXP810 - 26 EXP810 - 31 EXP810 - 32 EXP810 - 45 EXP810 - 46 Loop pair 1 Loop pair 2 Loop pair 3 Loop pair 4 4 3 2 1 1 2 3 4 EXP810 - 13 DS4800 与扩展柜联接 IBM Business Intelligence © Copyright IBM Corporation 2007 Note: Number inside the matrix cell indicates the array # [1..32] or H for global hot spare 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 11 12 25 26 31 32 45 46 13 31 3 27 1 2 32 30 28 1 31 29 5 3 2 32 30 6 191 31 7 5 3 1 9 7 5 3 11 9 7 15 13 11 9 17 15 13 11 19 17 15 13 21 19 17 15 23 17 21 25 23 21 19 27 25 23 21 29 27 25 23 31 2 4 2 32 8 4 6 412 10 8 6 14 12 10 8 16 14 12 10 18 16 14 12 20 18 16 14 22 20 18 16 24 22 20 18 26 24 22 20 28 26 22 30 28 26 24 24 Enclosure ID Loop Pair 1 1 2 2 3 3 4 4 1 Logical Drawer 11 9 7 5 13 25 29 27 10 426 6 8 32 30 28 HHHHH 29 EXP810 Slot RAID考虑: 4 * (3+P) arrays/partition * 8 partitions IBM Business Intelligence © Copyright IBM Corporation 2007 数据模块 IBM Business Intelligence © Copyright IBM Corporation 2007 硬件设计的考虑 Performance variance minimal. 4+p vs 9+p arrays align with AIX recommendations on lru_file_repage, minperm, maxperm, maxclient AIX vmo parameters 4K -> 16KDS4800 cache block size Decided to combine temp and data/index filesystems together. Allows more flexibility for use of existing space and can take advantage of auto-resize capabilities Combined file systems Testing done with 4 Gb4 Gb FC adapters 1400 MB/sScan rate Design resultsDesign Component IBM Business Intelligence © Copyright IBM Corporation 2007 数据库设计的考虑 Behaviour changes in DB2 9. Use new settings and AUTOMATICFCM parameters Use large RID for all table spaces. Old document uses “regular” keyword in create table space. This must be removed. Large RID SMS -> DMS FILE Can take advantage of large RID Performance slightly better to comparable Temporary tablespace type Recommend row compression for fact tables Testing showed improvements on multi-user streams as well as majority of Atomic queries Row compression 8K -> 16K Design and performance testing shows improvement with 16K page. 8K page still acceptable for those with higher OLTP-like requirements. DB2 pagesize Design resultsDesign Component IBM Business Intelligence © Copyright IBM Corporation 2007 数据库设计的考虑 new automatic parameters: fcm_num_buffers, num_ioservers, num_iocleaners Automatic configuration parameters Disabled by default for DPF environments. Could be used, but needs careful testing. Our recommended settings for memory usage have already proved out. Care would need to be taken to ensure it is active (explicit activate db) as well as to limit database memory so that system memory does not get overallocated. STMM Useful for smaller database systems. BCU prescribes the storage containers. Disabled at database level. Automatic storage Auto runstats requires good knowledge of customer’s environment to set up correctly. Beyond the scope of the initial BCU setup Disabled Auto_runstats Design resultsDesign Component IBM Business Intelligence © Copyright IBM Corporation 2007 采用DS8000系列存储的BCU IBM Business Intelligence © Copyright IBM Corporation 2007 SAN的联接——带库 IBM Business Intelligence © Copyright IBM Corporation 2007 SAN的联接——盘阵 IBM Business Intelligence © Copyright IBM Corporation 2007 采用DS8100 BCU 硬件设计的考虑 One container per table space per partitionTable space containers Disabled at database levelAutomatic storage Disabled; design document discussionAuto_runstats Disabled by default in DPFSTMM Automatic parameters: fcm_num_buffers, num_iocleaners, prefetchsize Automatic configuration parameters Recommend considering row compression for fact tablesRow compression DMS fileTemporary table space type Align with AIX recommendations on lru_file_repage, minperm, maxperm, maxclient AIX vmo parameters File system striped across two arraysOne file system for data & temps 32GBDS8100 cache size 16KDB2 page size recommendation Separate p5 575, two database partitions, and three LUNs on the first DS8100. Administration node Decided to use proportional striping across the two LUNs in the volume group for container LVs, to deal with the different sizes of the two LUNs. File system striping 4 Gb p575 host ports and 4GB DS8100 I/O ports4 Gb FC adapters 1400 MB/sScan rate Results / choiceDesign component IBM Business Intelligence © Copyright IBM Corporation 2007 采用DS8100 和 DS4800不同的参数 15 Registry DB2_PARALLEL_IO 0.08 4.8 Table space TRANSFERRATE OVERHEAD SettingsDesign component IBM Business Intelligence © Copyright IBM Corporation 2007 Required . AIX 5.3 operating system . MPIO and the SDDPCM plugin (如果采用DS8000系列) . InfoSphere Warehouse (DWE) >=V9.1.1 ( Enterprise Edition or Base Edition) – DB2 9 with Database Partitioning Feature Recommended . Consider Row compression feature . TSM for LAN-free backup . CSM for cluster management . Query Patroller . DB2 Performance Expert Optional and validated . TSA for automated failover (High availability) 软件环境的考虑 IBM Business Intelligence © Copyright IBM Corporation 2007 议程 . DB2数据仓库设计回顾 . BCU深度探索 – 中端配置 – 高端配置 . InfoSphere Warehouse (DWE)在BCU上的部署 . Q & A . 附一:关于表压缩 . 附二:常用参数建议 IBM Business Intelligence © Copyright IBM Corporation 2007 InfoSphere(DWE)在BCU 上的部署 Non BCU External Server Non BCU Windows Client BCU Cluster -Admin BCU - Data BCU IBM Business Intelligence © Copyright IBM Corporation 2007 BCU Instance BCU DB BCU Data Server BCU Data Server BCU Data Server BCU Data Server BCU Admin Server DWE App Server DWE Client DWE Client DWE Client LEGEND DWE Server DB2 Adm Client DWE Client DB2 Adm Client BCU Node DB2 ESE W/ DPF WAS Browser Connection DB2 Adm Client Connection DB2 DPF Cluster Connection … DB2 ESE Instance DB2 DB HTTP DB2 访问DB2 访问 InfoSphere(DWE)在BCU 上的部署 IBM Business Intelligence © Copyright IBM Corporation 2007 安装的顺序 . Preparing To Install . Install DWE on the Admin BCU (Partition 0) . Install DWE on Data BCUs and/or Additional Admin BCUs (the order is not important and can be done simultaneously.) . Install DWE on the Application Server (Do not run the DWE configuration tool yet.) . Update WAS on the Application Server. . Setup and Configure the db2 instance and start it. . Create the BCU database according to the BCU specifications. . Create the DWE Specific Tablespaces as documented in the BCU Documentation. . Grant permission to the DWEADMIN user on the DWE Tablespaces. . Run the Configuration tool on the DWE Application Server. . Enable the database for Cube Views and Mining. . Enable the database for Query Patroller. . Install A DWE Client IBM Business Intelligence © Copyright IBM Corporation 2007 Before Installing DWE . Ensure all users and groups are created according to the BCU Documentation and are consistent across all nodes in the cluster. The following users must exist prior to DWE installation: – bcuaix -- instance owner – bcufenc1 -- fenced user – bcuadm1 -- DAS User – dweadmin -- Initial DWE Administrative User – wasadmin -- WAS Administrative User (Only required on Application Server) . Ensure the BCU Database Instance owner bcuaix has a home directory that exists on an NFS exported drive and is available to all BCU Nodes. . Copy All DWE Installation Media to an NFS Shared Drive. These should be accessible to all BCU Nodes and the Application Server. Please refer to the DWE Installation Guide Chapter 4 for the list of Installation Media. IBM Business Intelligence © Copyright IBM Corporation 2007 Before Installing DWE (Additional Notes) . For AIX, you’ll need to download a fix for WAS 6.0.2 to update the JDK to 1.4.2 SR3 Cumulative fix. This will be applied only to the Application Server after installation. – http://www-1.ibm.com/support/docview.wss?rs=180&uid=swg24011105 . Ensure that an X-Windows Server is available to display the DWE Installer and the DWE Configuration Tool screens. DWE does not support silent installations in 9.1.1. Install works with VNC as well as CYGWIN/X on Windows. . DWE installs in /usr/opt. Verify that there is enough space in /usr/opt on all BCU Nodes and the Application Server. There should be approximately 3 GB of free space. . Umask. Verify that the umask for root is 022 or ensure that root has a umask of 022 prior to running the installation programs. Stricter umasks like 077 have shown to cause permission problems when installing DWE. . Double Check: Once DWE 9.1.1 GAs the install guide will be available from the External Website. The current DWE 9.1 install guide is available from the DWE support page. http://www- 306.ibm.com/software/data/db2/dwe/support.html IBM Business Intelligence © Copyright IBM Corporation 2007 Before Installing DWE (additional notes) . The DWE 9.1.1 Integrated Installer now supports installing DB2 DPF on a clustered environment. If you choose to install DB2 ESE using the DWE Installer: – You must install DWE on the Admin BCU (Partition 0) before installing on other nodes. – You must choose to install DWE on the Admin BCU (Partition 0) as the “Partition Owning Instance”. – The installer will create the DB2 Instance on the Admin BCU. – The installer will create the DAS Instance on the Admin BCU – The installer will enable the instance for Cube Views, Intelligent Miner and Query Patroller if those components are selected. – The installer will create a DB2 Response file in the /dwe directory which will need to be available to the other nodes prior to their installation. – You must perform post-installation steps in order to ensure that the created DB2 Instance will work. This is documented in the BCU documentation. • Modifying the /etc/services file on all BCU Nodes • Modifying the ~bcuaix/sqllib/db2nodes.cfg file. • Ensuring the bcuaix user can rsh or ssh freely, without passwords, from BCU Node to BCU Node. IBM Business Intelligence © Copyright IBM Corporation 2007 Before Installing DWE (additional notes) . Installing DWE on Data BCUs or Additional Admin BCUs. – For the purposes of DWE installation, these two types of Nodes are the treated the same. – Both types of nodes are simply “Additional Partitions” and both will only be installed with DB2 ESE and DWE Intelligent Miner. – Simply verify that the DB2 Response file is available to each Node prior to install. – After installing on the Admin BCU (Partition 0) the rest of the BCU nodes can be installed in any order, and can be installed simultaneously. Remember to copy the DB2 response file from the /dwe directory on the Admin BCU to a location accessible to these nodes. IBM Business Intelligence © Copyright IBM Corporation 2007 Before Installing DWE (additional notes) . Installing DWE on the Application Server – Installation can take place on the Application Server at any time. Configuration must take place however after the BCU database has been configured and the DWE Tablespaces have been created. – DWE will require access to a DB2 instance which will serve two purposes. – 1. Provides binary files used for connecting to the database. – 2. Provides the db2 catalog necessary for JDBC Type 2 Connections. – DWE will install the DB2 Admin Client and will create the client instance if necessary. Ensure that the dweadmin user exists and that its home directory is write accessible via the root user. IBM Business Intelligence © Copyright IBM Corporation 2007 Post Installation Tasks . Admin BCU – Update /etc/services file – Update db2nodes.cfg – Apply the BCU Database Instance Parameters . Data BCU – Update /etc/services file – Start the Database Instance once all Admin and Data nodes are modified. . Application Server – Ensure DB2 Client Instance is available. – Catalog Admin BCU Node – Catalog BCU Database on Admin BCU Node – Prepare the root environment (or your user with root privileges that will be used to start and stop WAS) references the ~clientinstanceowner/sqllib/db2profile. – Update the JDK for WAS as per DWE Post Installation Instructions. (AIX Only). – Run the Configuration Tool IBM Business Intelligence © Copyright IBM Corporation 2007 Post Configuration Tasks . Admin BCU – Enable Cube Views for the Database. – Enable Intelligent Miner for the Database. – Enable Query Patroller for the Database. . Data BCU – Verify Intelligent Miner is installed and linked files in the sqllib/function directory resolve correctly to IM files. . Application Server – Verify WAS is started. – Login to the WAS Admin Console. – Login to the DWE Admin Console. – Create Initial Database Profile For BCU Database . DWE Client – Install and Configure a DWE Client IBM Business Intelligence © Copyright IBM Corporation 2007 系统拓扑示意图 IBM Business Intelligence © Copyright IBM Corporation 2007 数据库分区的调整 数据分区9-16DB-1a 数据分区17-24DB-2 数据分区25-32DB-2a ETL/LOAD连接分区33ETL 数据分区1-8DB-1 系统分区、查询和报表连接分区、单分区小表0Adm 说明分区号主机 8个分区 1~8 P570(DB-1) 8个分区 9~16 P570(DB-1a) 8个分区 17~24 P570(DB-2) 8个分区 25~32 P570(DB-2a) 1个分区 0 P550(Adm) 1个分区 33 P550(ETL) IBM Business Intelligence © Copyright IBM Corporation 2007 数据库分区 DB分区: 小表 系统表 临时表空间 用户表空间 大表临时表空间 索引表空间 0 报表 查询连接 主机: Load ETL 1-8 9-16 17-24 25-32 33 小表 小表 小表 小表 P570(DB-1) P570(DB-1a) P570(DB-2) P570(DB-2a)P550(Adm) P550(ETL) IBM Business Intelligence © Copyright IBM Corporation 2007 IBM 商业智能解决方案 —— IBM DB2 Data Warehouse Edition (DWE) 集成化的数据仓库设计平台BI Design Studio 数据仓库内SQL ETLDB2 SQL Warehouse Tool 数据仓库物理建模Rational Data Architect 提供开发分析应用的组件,构建分析应用DB2 Alphablox 将规律(模型)图示化DB2 IM Visualization 在数据仓库中直接实现(实时)评分DB2 IM Scoring 从数据仓库中发现规律(模型)DB2 IM Modeling 提高数据仓库吞吐量DB2 Query Patroller OLAP模型元数据交换、加快汇总报表查询速度DB2 Cube Views 高度并行、可扩展性DB2 UDB DPF 数据仓库引擎DB2 UDB ESE ValueDWE Feature IBM Business Intelligence © Copyright IBM Corporation 2007 议程 . DB2数据仓库设计回顾 . BCU深度探索 – 中端配置 – 高端配置 . InfoSphere Warehouse (DWE)在BCU上的部署 . Q & A . 附一:关于表压缩 . 附二:常用参数建议 IBM Business Intelligence © Copyright IBM Corporation 2007 附一:关于表压缩的使用 CPU Total throughput 7/25/2006 0 10 20 30 40 50 60 70 80 90 100 17:50 17:55 18:00 18:05 18:10 18:15 18:20 18:25 18:30 18:35 18:40 18:45 18:50 18:55 19:00 19:05 19:10 19:15 19:20 19:25 19:30 19:35 19:40 19:45 19:50 19:55 20:00 20:05 20:10 20:15 20:20 20:25 20:30 20:35 User% Sys% Wait% 5-stream compressed CPU Total throughput 7/28/2006 0 10 20 30 40 50 60 70 80 90 100 6:23 6:28 6:33 6:38 6:43 6:48 6:53 6:58 7:03 7:08 7:13 7:18 7:23 7:28 7:33 7:38 7:43 7:48 7:53 7:58 8:03 8:08 8:13 8:18 8:23 8:29 8:34 8:39 8:44 8:49 8:54 8:59 9:04 User% Sys% Wait% 5-stream uncompressed Resource usage characteristics change, but resulted in 24% improvement in elapsed times IBM Business Intelligence © Copyright IBM Corporation 2007 评估行压缩对现有系统性能的影响: . CPU Usage vs. IO Wait less than 60% CPU usage is good for compression, more than 80% is bad. (assuming CPU is being used for db2agents) . Logical Design analysis Big Fact tables are good candidates for row compression Small Dimension tables are not . Random read vs. sequential read CPU Usage / IO Load ratio of random table access is better for compression than sequential read access 行压缩测试结果 IBM Business Intelligence © Copyright IBM Corporation 2007 附二:常用参数建议——DB2注册表参数 IBM Business Intelligence © Copyright IBM Corporation 2007 附二:常用参数建议—— DB2注册表参数(续) IBM Business Intelligence © Copyright IBM Corporation 2007 附二:常用参数建议—— DBM参数 IBM Business Intelligence © Copyright IBM Corporation 2007 附二:常用参数建议—— DB参数 IBM Business Intelligence © Copyright IBM Corporation 2007 附二:常用参数建议—— DB参数
还剩53页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

lc_lichm

贡献于2014-04-09

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf