第1周 db2基础


DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2设计与 性能优化 第1周 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 法律声明 【声明】本视频和幻灯片为炼数成金网络课程的教 学资料,所有资料只能在课程内使用,不得在课 程以外范围散播,违者将可能被追究法律和经济 责任。 课程详情访问炼数成金培训网站 http://edu.dataguru.cn DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 图数据库参考书 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 本周内容  DB2产品 – Editions & Features – Licensing  DB2基础 – Architecture – Users – Instances – DB2 Client – Storage • Table spaces • Buffer pools – Automatic Feature Break free with DB2 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 版本介绍 Everyplace Edition Personal Edition Express & Express-C Editions Workgroup Server Edition Enterprise Server Edition Database Enterprise Developer Edition Allows developers to develop and perform quality assurance. The edition is a product bundle that includes many DB2 features. DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 和Oracle版本对比  DB2 Express-C – no cost download and deploy - optional license available – Windows, Linux, Solaris (Beta: Mac OS X) – 2 GB memory, 2 CPU – No Database size limit  DB2 Express Edition – Windows, Linux, – 4 GB memory, 4 CPU – Includes HADR  DB2 Workgroup Server Edition – Windows, Linux, Solaris – 16 GB memory, 4 sockets  DB2 Enterprise Server Edition – Windows, Linux, Solaris, IBM AIX®, HP-UX, Linux on system z – No limit on memory and CPU ■ Oracle Express Edition – No cost and deploy – Windows, Linux, UNIX – 1GB memory, 1 CPU – Up to 4GB user data ■ Oracle Standard One Edition – Windows, Linux, UNIX – 2 sockets – Does not include RAC ■ Oracle Standard Edition – Windows, Linux, UNIX – 4 sockets ■ Oracle Enterprise Edition – Windows, Linux, UNIX – No limit on memory and CPU DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2处理机制  DB2 引擎 (db2sysc) – 包含主引擎处理器的线程模型 – 引擎分派单元(EDU)以线程的形式存在并执行各种任务 – 线程模型的优点: • 优化性能 • 减少内存使用量  DB2 代理 (db2agent) –EDU作为应用程序的代理,负责执行各种任务,包括处理数据库应用程序请求 – DB2 保持多个代理存在来保证当有服务请求时能够及时响应 – 两种主要的代理:协调代理,子代理 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2实例  独立的DB2环境  每个数据服务器可以有多 个实例  所有实例共享同一个可执 行的二进制文件  每个实例有自己的配置参 数文件  每个实例可以有不同的软 件级别 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 实例  一些有关实例的指令  以下多数指令可以在控制中心中进行 指令 指令描述 例子 db2start 启动默认实例 db2start db2stop 停止当前实例 db2stop -f db2icrt 创建实例 db2icrt –u db2fenc1 db2inst1 db2idrop 删除实例 db2idrop –f db2inst1 db2ilist 列出所有实例 db2ilist db2imigr 升级DB2后迁移一个实例 db2imigr –u db2fenc1 db2inst1 db2iupdt 安装补丁后升级实例 db2iupdt –u db2fenc1 db2inst1 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 实例:实例和数据库配置文件  查看和更改实例配置文件  查看和更改数据库配置文件: 描述 例子 查看数据库管理器配置 db2 get dbm cfg show detail 更改数据库管理器配置 db2 update dbm cfg using health_mon off 描述 例子 查看数据库设置 db2 get db cfg for testdb db2 connect to testdb db2 get db cfg show detail 更改一个数据库设置 db2 update db cfg using logprimary 10 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 日志  通过DIAGLEVEL参数来控制日志的数量  db2diag.log • 存放于$DB2INSTANCE_HOME/sqllib/db2dump/ – 该日志包含DB2所有错误和警告日志信息  stmm.log – 存放于 $DB2INSTANCE_HOME/sqllib/db2dump/stmm – 日志存放于多个文件中(最多5个) – 该日志包含性能统计数据和DB2的优化结果 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 手动添加一个远程目录  作为实例用户来向目录中添加远程节点  你也可以将DAS添加到远程节点  完成以上操作后,可以开始正常使用远程数据库: db2 catalog tcpip node db2node remote SERVER1 server 50001 alias hostname/IP address port # DAS在DB2 9.7中不可用 db2 catalog admin tcpip node db2das remote SERVER1 db2 catalog db SAMPLE at node db2node authentication SERVER db2 connect to SAMPLE user db2inst1 using password DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 显示本地目录信息 • 使用以下命令来显示本地目录中的结点,DAS和数据库信息: db2 list database directory db2 list admin node directory db2 list node directory DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2存储: 表空间  表空间由一个或多个容器组成的 逻辑存储集合  所有的表,索引和其它数据类型 都存在表空间中  给表空间添加磁盘物理地址(容器) 来控制表空间的数据存放在磁盘 的什么位置. DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2存储: 表空间  表空间可以由操作系统管理(SMS),或者由数据库管理(DMS), 或者由DB2的自动存储功能管理  系统管理空间 (SMS) – 数据存放在文件系统的文件中 – 使用操作系统的标准I/O函数来访问数据 – 除非需要,否则空间不会被额外分配 – 适用于小型,个人数据库以及存储空间变化频繁的数据库 – 对维护和监控要求较低 CREATE TABLESPACE tbsp1 MANAGED BY SYSTEM USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp') DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2存储: 表空间  数据库管理空间 (DMS) • 数据存储在文件或者Raw设备中 • 能够绕过操作系统的I/O函数从而增加性能 • 适用于对数据库性能要求比较高的应用程序 • 需要较频繁的维护和监控 • 当容器空间不足时,可以通过设置参数来自动增加容器空间 CREATE TABLESPACE tbsp1 PAGESIZE 8K MANAGED BY DATABASE USING (FILE 'd:\db2data\acc_tbsp' 5000, FILE 'e:\db2data\acc_tbsp' 5000) CREATE TABLESPACE tbsp2 PAGESIZE 8K MANAGED BY DATABASE USING (FILE ' /storage/dms1' 10 M) AUTORESIZE YES DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2存储: 表空间  自动存储表空间 • 数据库允许使用自动存储时 • 数据库管理器能够自动分配容器 (物理存储地址) • 自动调整表空间的大小 • 对普通/大型表空间来使用数据库管理 • 对用户或系统临时表空间使用系统管理 db2 create database MYDB on /ASPATH dbpath on /DBPATH CONNECT TO mydb CREATE TABLESPACE tbsp1 MANAGED BY AUTOMATIC STORAGE DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2存储: 缓冲池  用来放置表和索引的缓存数据的内 存区  能够有效的减少访问数据的时间  每个数据库至少需要一个缓冲池  通过内存自调优功能(STMM)来自 动调价缓冲池达到优化目的 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2存储: 缓冲池 • 每个表空间都链接有一个同样页面大小的缓冲池 • 根据表的用途设置多个缓冲池,能够通过缓冲不同数量的数据来达到增加数据库性能的目 的,比如说: • 大型的随机访问表空间需要一个较小的缓冲池 • OLTP应用程序设置的表空间需要一个较大的缓冲池 • 如果创建表空间时,没有特别指定缓冲池,那么数据库会使用默认的IBMDEFAULTBP缓冲 池 CREATE BUFFERPOOL bp4k PAGESIZE 4K CREATE TABLESPACE tbsp1 PAGESIZE 4K BUFFERPOOL bp4k DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 工具和产品对比 Oracle IBM Description Oracle® 11g Enterprise Edition DB2 9.7 Enterprise Edition RDBMS Oracle Enterprise Manager Enterprise Login Assist Database Configuration Assistant Administration Assistant IBM Data Studio IBM Optim Database Administrator DB2 Control Center DB2 Recovery Expert DB2 Performance Expert Database Management SQL Developer Command Editor IBM Data Studio SQL and Procedure developer Oracle SQL Plus CPLPlus Command Line Processor (CLP) Command line administration tool Oracle NET Configuration Assistant Oracle NET Manager DB2 Configuration Assistant Node cataloging / connecting, networking Oracle SQL Analyzer DB2 Visual Explain Db2xfmt db2expln Query performance analysis Query visual explanation Oracle Automatic Database Diagnostic Monitor Data Studio Administration Console, Health Monitor DB2 Event Monitor & Analyzer Database event information and statistic collection Oracle Forms Developer DB2 Table Editor Developer Suite Forms creation, development, usage Oracle Database Resource Manager DB2 Workload Manager DB2 Governor DB2 Query Patroller Workload management Oracle Data Guard High Availability and Disaster Recovery Feature High Availability and Disaster Recovery DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 Oracle服务器端架构  Oracle System ID  An Oracle SID can access only one database at a time  Common terms: –“Instance” Memory structure and the background processes used to access data from a database –“Database” physical files used to store information SGA (shared memory) Database buffer cache Data dictionary cache Library cache Control file Control File Data files Control File Redo Log files Oracle Database Oracle Instance SID Virtual Physical DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 和 Oracle 术语对比 Alert Log Autoextend Data dictionary Data dictionary cache Data cache Data file Large pool Pfile / spfile (init.ora) Transparent appl. failover Dynamic views (V_$ views) db2diag Autoresize System catalog Catalog cache Buffer pool Container Utility Heap DB / DBM configuration Automatic client reroute Administration views (SYSIBMADM) DB2 Oracle DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 安装与需求 Installation Windows UNIX db2setup Wizard db2_install command Response file Installation Methods Resource Oracle DB2 CPU 1Ghz processor 1Ghz processor RAM 1GB 512MB Temp space 200MB 60MB Minimum Requirements DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 Oracle目录结构  Windows ORACLE_BASE Oracle Product directory ORACLE_HOME Database directory listener.ora tnsnames.ora sqlnet.ora, etc. sqlplus.exe sqlldr.exe rman.exe, etc \oraclexe \db_1 \mydb1 \product \11.1.0 \admin \bdump \cdump \create \pfile \udump \network \admin \bin DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2目录结构  Linux / UNIX (Automatic Storage) Main DB2 software directories Linux/UNIX instance owner’s home directory DB2 Instance directory Stored Procedure Directory – External and Internal Automatic Storage directory (for SAMPLE database) Default DB2 install location Instance software directories linked to main DB2 software DB2 diagnostic logs and other logs Audit and Security information Initialization profile for Unix shell Instance configuration parameters binary file System Database directory – Catalogs are kept here Local Database directory Databases are created under this node Database ID (directory for SAMPLE database) T0000000, T0000001, T0000002 – System, Temporary, User table spaces / /home/db2inst1 /sqllib /bin /opt/ibm/db2/V9.7 /lib /java /bnd /conv /include /function /db2dump /security db2profile db2systm /sqldbdir /sqldbdir /SQL0001 /SAMPLE /NODE0000 /bin /lib /java /bnd /conv /include DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 参数配置比较 Oracle DB2 DB2 9.7 has nearly 210 parameters DB2 automatically generates all parameters during instance / database creation DB2 AUTOCONFIGURE feature suggests database parameter values at database creation No conversion is necessary. The DB and DBM have that functionality by default Oracle 11g contains nearly 300 parameters PFILEs are needed for database creation Oracle needs either PFILES or templates to create a database PFILE to SPFILE conversion is necessary otherwise the parameter changes won't persist DB2 Oracle DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 注册变量和环境变量比较  DB2 registry variables – Managed by using db2set or the Configuration Assistant  Oracle environment variables db2adminserver db2comm db2include db2instance db2instdef db2path Administration server instance Communications manager SQL searches path Current instance Default instance DB2 directory Variable Function ora_cwd ora_sid_pfile oracle_home oracle_sid oracle_base Current working directory Path to initialization parameter file (init.ora) Home directory in which Oracle is installed Oracle DB instance on the host computer Top-level Oracle directory Variable Function db2set -h db2set db2comm=tcpip db2set db2comm= db2set -all Help Assign a value to a variable Resets a variable to it's default Displays all variables db2set command [e] DB2PATH=C:\Program Files\IBM\SQLLIB [i] DB2ACCOUNTNAME=IBM-UPDT123H3BR\db2admin [i] DB2INSTOWNER=IBM-UPDT123H3BR [i] DB2PORTRANGE=60000:60003 [i] DB2INSTPROF=C:\PROGRA~1\IBM\SQLLIB [i] DB2COMM=TCPIP [g] DB2_USERSGROUP=DB2USERS [g] DB2_ADMINGROUP=DB2ADMNS [g] DB2_EXTSECURITY=YES [g] DB2SYSTEM=IBM-UPDT123H3BR [g] DB2PATH=C:\Program Files\IBM\SQLLIB [g] DB2INSTDEF=DB2 [g] DB2ADMINSERVER=DB2DAS00 [e] Environment Variables – OS controlled [g] Global Registry Variables – effects entire server [i] Instance Registry Variables – effects current instance DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 数据库创建  Oracle 1) Set environment variables • ORACLE_SID, ORACLE_BASE, ORACLE_HOME 2) Create password file 3) Create pfile 4) STARTUP NOMOUNT 5) CREATE DATABASE... 6) Create data dictionary views 7) Create PL/SQL packages 8) Add the SID to tsnames.ora ■ DB2 1) Set registry variables • DB2INST, DB2INSTPROF 2) DB2START 3) CREATE DATABASE... 4) CATALOG DATABASE (Optional) DB2 uses external authentication, no password file necessary DB2 uses “autoconfigure” feature to set the variables. No files (like Oracle pfile's) are needed During the database creation, when the compatibility vector is enabled, DB2 automatically builds the data dictionary views and PL/SQL packages DB2 automatically enable Self Tunning Manager enabled by default DB2 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 实例和数据库管理 STARTUP NOMOUNT DB2START Starting an instance STARTUP 1. DB2START 2. DB2 ACTIVATE DATABASE Starting an instance and database 1.ALTER DATABASE MOUNT 2.ALTER DATABASE OPEN DB2 ACTIVATE DATABASE Starting a database SHUTDOWN (NORMAL) DB2STOP Stopping an instance SHUTDOWN IMMEDIATE DB2STOP FORCE Stopping an instance (forced) ALTER SYSTEM KILL SESSION (SID) FORCE APPLICATION [ALL | ID] Killing a session DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 管理命令  Other commands Command Description Example db2start Start the default instance db2start db2stop Stop the current instance db2stop -f db2icrt Create an instance db2icrt –u db2fenc1 db2inst1 db2idrop Drop an instance db2idrop –f db2inst1 db2ilist List all instances db2ilist db2imigr Migrate an instance after upgrading DB2 db2imigr –u db2fenc1 db2inst1 db2iupdt Update an instance after installation of a fix pack db2iupdt –u db2fenc1 db2inst1 Description Example View Database Manager Settings db2 get dbm cfg show detail Change a Database Manager Setting db2 update dbm cfg using health_mon off Description Example View Database Settings db2 get db cfg for testdb db2 connect to testdb db2 get db cfg show detail Change a DB Setting db2 update db cfg using logprimary 10 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 Sample 数据库  To create the sample database populated with both relational data and XML data  Verify the database creation by simply connecting and querying the data  For remote databases: db2sampl –sql –xml db2 connect to sample db2 catalog database sample as sample at node mynode1 db2 connect to sample DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 诊断日志: db2diag.log  Trouble shooting and diagnostic purposes  Located in $DB2INSTANCE_HOME/sqllib/db2dump/ by default  General log which contains all DB2 errors and warnings  Use “db2diag” tool to filter and forma db2diag log files 2 forms: Configuration parameters:  Diagsize: size of the log files for rotating log files form; 0 for single log file form  Diagpath: Location of the log file(s)  Diaglevel: Types of errors to be written to log Single diagnostic log file (db2diag.log) single active log file that grows indefinitely. DEFAULT behavior Rotating diagnostic log files (db2diag.N.log) set of files that the active log file closes and opens db2diag.N+1.log when it reaches the limit size DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 Oracle 字典 vs DB2 系统编目 Oracle dictionary views will be created by setting the DB2_COMPATIBILITY_VECTOR registry variable Oracle Data Dictionary DB2 System Catalog SYSIBM - Base tables SYSIBMADM – Admin views SYSCAT – Catalog base tables SYSSTAT – Updateable views SELECT FROM syscat.tables WHERE OWNER=user AND TYPE=‘T’; SELECT FROM syscat.tables WHERE OWNER=user AND TYPE=‘V’; SYS.xx$ - Base tables SYS.GV_$ - Admin views SYS.V_$ - Admin views USER_$ - Users views ALL_$ - Expanded users views DBA_$ - Database Admin views SELECT FROM user_tables; SELECT FROM user_views; DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 存储模型比较 Oracle Database Table space Segment Extent Oracle Block O.S block Datafile Database Table space Object Extent DB2 Block O.S block Container DB2 Physical Logical Physical Logical Physical Logical Physical Logical Similar Storage Model DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 存储模型比较 – 表空间  Table Spaces – Default •SYSTEM •SYSAUX • UNDOTBSP1 •USER – Permanent Table spaces • Bigfile (1 large datafile) • Smallfile (several small datafiles) – Temporary Table spaces • Locally Managed (default) • Dictionary Managed – Undo Table spaces – Default •SYSCATSPACE • USERSPACE1 • TEMPSPACE1 – System Managed (SMS) • Regular • Temporary – Database Managed (DMS) • Regular • Temporary • Large Oracle DB2 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 表空间类型 Catalog table spaces SYSCATSPACE (1 required) ■ Catalog tables with metadata ■ Must exist User temporary table space USERTEMPSPACE (1 or more required) Store temp data from tables created with DECLARE GLOBAL TEMPORARY TABLE User Table Spaces USERSPACE1 (1 or more required)  Default user table space  Can be deleted  All user defined tables System temporary table space TEMPSPACE1 (1 required)  System temporary space  Work area for operations, for example: join, sorts DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 表空间管理  System Managed (SMS) – Oracle has NO equivalent! – Data stored in files representing data objects – Space is allocated on demand – Access to data controlled using standard I/O functions of the OS Ideal for small, personal databases and databases that grow/shrink rapidly  Lower performance, as the OS handles the I/O  Database Managed (DMS) – All Oracle table spaces works like DB2's DMS – Data stored in files or on raw devices – Storage space pre-allocated in file system, typically contiguous physically Ideal for performance-sensitive applications  Increased maintenance and monitoring CREATE TABLESPACE byeoracle MANAGED BY SYSTEM USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp') CREATE TABLESPACE byeoracle MANAGED BY DATABASE USING (FILE '/db2/file1' 1 M, FILE '/db2/file2' 1 M) AUTORESIZE YES INCREASESIZE 2 M MAXSIZE 100 M DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2 自动存储表空间  Automatic Storage Table Space – DBM creates and extends containers as needed up the limits imposed by the storage paths associated with the database – Automatically handles resizing table spaces – Creates a DMS table space for regular/large table spaces – Creates a SMS table space for user or system temporary table spaces db2 create database MYDB on /ASPATH dbpath on /DBPATH New DB & TBSP are handled by automatic storage by DEFAULT DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 Let’s walk through the following : set DB2INSTANCE=MYINST;db2start db2 create database MYDB on /ASPATH dbpath on /DBPATH db2 create tablespace TS1 managed by system using ('/MYDIR1', '/MYDIR2') extentsize 4 An Illustrative Walkthrough DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 Let’s walk through the following : set DB2INSTANCE=MYINST ; db2start db2 create database MYDB on /ASPATH dbpath on /DBPATH db2 create tablespace TS1 managed by system using ('/MYDIR1', '/MYDIR2') extentsize 4 An Illustrative Walkthrough DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 What Gets Created On Disk ? / DBPATH / MYINST / NODE0000 / SQL00001 / SQLBP.1/2 // bufferpool info (*) SQLSPCS.1/2 // tablespace info (*) SQLSGF.1/2 // automatic storage path information (*) SQLDBCONF/SQLDBCONF // database configuration (*) db2rhist.asc/bak // recovery history file (*) SQLOGCTL.LFH.1/2 // log control file (*) SQLOGMIR.LFH // used with log mirroring DB2TSCHG.HIS // tablespace log history file SQLOGDIR / // log directory (**) SQL00001.LOG // log files go here event / // default path for event monitor output / ASPATH / MYINST / NODE0000 / MYDB / T0000000 / C0000000.CAT // initial catalog tablespace container T0000001 / C0000000.TMP / // initial system temp tablespace container T0000002 / C0000000.USR // initial user tablespace container set DB2INSTANCE=MYINST; db2start db2 create database MYDB on /ASPATH dbpath on /DBPATH DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 Let’s walk through the following : set DB2INSTANCE=MYINST; db2start db2 create database MYDB on /ASPATH dbpath on /DBPATH db2 create tablespace TS1 managed by system using ('/MYDIR1', '/MYDIR2') extentsize 4 An Illustrative Walkthrough DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 What Gets Created On Disk ? db2 create tablespace TS1 managed by system using ('/MYDIR1', '/MYDIR2') extentsize 4 ./SQL00002.DAT ./SQL00003.DAT ./SQL00002.DAT ./SQL00003.DAT /MYDIR1/. /MYDIR2/. T1.1 T1.3 T1.5 T1.7 T1.9 T1.0 T1.2 T1.4 T1.6 T1.8 T2.1 T2.3 T2.5 T2.7 T2.9 T2.0 T2.2 T2.4 T2.6 T2.8 First Extent of Data Pages for T1 Second Extent of Data Pages for T1 DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2缓冲池  Area of main memory used to cache table and index data  Each database must have at least one buffer pool – By default IBMDEFAULTBP is used – Buffer pools can be created, dropped or altered – SYSCAT.BUFFERPOOLS catalog view accesses the information for the buffer pools defined in the database  Every table space associates a specific buffer pool of the same page size – Match buffer pool size with purpose of table to increase hit ratio  Self-Tuning Memory Manager (STMM) available CREATE BUFFERPOOL bp4k PAGESIZE 4K CREATE TABLESPACE tbsp1 PAGESIZE 4K BUFFERPOOL bp4k DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2配置顾问  DB2 Configuration Advisor uses expert heuristics to tune performance and to balance memory requirements – Automatic environment characteristic detection – User description of basic operational questions – Combined with mathematical model of each configuration parameter, based on expert heuristics  To use the Configuration Advisor, specify the AUTOCONFIGURE command for an existing database, The AUTOCONFIGURE is run by default when you issue the CREATE DATABASE command. Oracle Database Configuration Assistant – has only a few database templates and won't help you to set the performance parameters and cache settings all at once, in one tool – DBCA Steps: Inside DB2 Configuration Model User specification of the database environment Autonomically sensed system characteristics Expert heuristics Configuratio n settings Oracle has no real equivalent! 1 – create a parameter and password files. 2- Use the parameter file to build an instance. 3- Issue the CREATE DATABASE command. 4- Run SQL scripts to generate the data dictionary. 5- Run SQL scripts to generate the Enterprise Manager Database Control that database will require Too many steps! DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2设计顾问  Tool that suggests modifications to the database's physical design to improve performance  Allows to display, edit, or save the new database object creation recommendation set  Invoked with the db2advis command  Implementation of the recommendations by creating: – Indexes on the base tables – Materialized Query Tables (MQT) – Indexes on the Materialized Query Tables – Converting regular tables into Multi-Dimensional Clustering (MDC) tables – Re-partitioning (DPF) existing tables with a new set of partitioning key and/or in a new table space. DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2自动存储管理  Create a database with automatic storage ON  Create a tablespace with automatic storage ON CREATE DATABASE AUTOMATIC STORAGE YES ON /data/storagePath1, /data/storagePath2 DB2 will create the containers inside these paths CREATE TABLESPACE TS2 MANAGED BY AUTOMATIC STORAGE INITIALSIZE 500 K INCREASESIZE 100 K MAXSIZE 100 M Automatic table space management Automatic container management Automatic resize of DMS table spaces DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 自动对象维护  Database maintenance is essential for ensuring optimized performance and recoverability – Therefore, setting up automatic maintenance removes burden from DBA to have to manually do these tasks  AUTO_MAINT parameter is the master on/off switch – Individual Child parameters can be set to ON/OFF and the settings are persisted in the database configuration file.  Enabled/disabled through database configuration parameter hierarchy Automatic maintenance Automatic database backup Automatic table maintenance Automatic runstats Automatic statistics profiling Automatic runstats Automatic statement statistics Automatic profile updates Automatic reorganization DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 DB2自动内存调优(STMM)  Self Tuning Memory Manager (STMM) simplifies the task of memory configuration  Optimizes the performance of your database by automatically adjusting the values of: – Memory configuration parameters in instance configuration • Total instance memory – Memory configuration parameters in database configuration • e.g. Sort heap, lock list, package cache, and total DB memory – Size of buffer pools  Hands-off online memory tuning – Requires no DBA intervention – Senses the underlying workload and tunes the memory based on need – Can adapt quickly to workload shifts that require memory redistribution DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM配置  If upgrading from older version of DB2 or previously turned off STMM: 1) Turn on the master switch: • update db cfg for using SELF_TUNING_MEM ON 2) Set each parameter that you wish to tune to AUTOMATIC • update db cfg for using AUTOMATIC  For buffer pools to be tuned by STMM: – All newly created buffer pools default to AUTOMATIC – For migrated database's bufferpools • alter buffer pool size AUTOMATIC  To query whether or not STMM is tuning your DB: 1) Connect to the database 2) Get Database Configuration with details • get db cfg for show detail 3) Check the value for SELF_TUNING_MEM • If set to ON (ACTIVE) then system is being tuned • If set to OFF, or ON (INACTIVE) then system is not being tuned DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM操作模式  STMM works in two modes depending on the configuration of the DATABASE_MEMORY parameter ■ DATABASE_MEMORY = AUTOMATIC –Total amount of memory used by a particular DB2 database can grow or shrink over time –Takes from, and returns memory to, the OS as necessary ■ DATABASE_MEMORY = COMPUTED or –Memory tuning still occurs but total memory used by database is constant –For one heap to grow another heap must shrink DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM操作模式  DATABASE_MEMORY = AUTOMATIC Memory used by other applications Other DB memory consumers Total System Memory 1)Scenario involves a change in workload that now requires more memory for sorts Free memory in system DB2 database memory use Database sort heap DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM操作模式  DATABASE_MEMORY = AUTOMATIC Memory used by other applications Other DB memory consumers Total System Memory 1)Scenario involves a change in workload that now requires more memory for sorts 2)DB2 requests and gets more memory from the OS, therefore shrinking the free memory in the system Free memory in system DB2 database memory use Database sort heap DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM操作模式  DATABASE_MEMORY = AUTOMATIC Memory used by other applications Other DB memory consumers Total System Memory 1)Scenario involves a change in workload that now requires more memory for sorts 2)DB2 requests and gets more memory from the OS, therefore shrinking the free memory in the system 3)DB2 uses newly acquired memory in sort heap DB2 database memory use Database sort heap Free memory in system DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM操作模式  DATABASE_MEMORY = COMPUTED or Memory used by other applications Other DB memory consumers Total System Memory 1)Scenario involves a change in workload that now requires more memory for sorts Free memory in system DB2 database memory use Database sort heap Database lock list DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM操作模式  DATABASE_MEMORY = COMPUTED or Memory used by other applications Other DB memory consumers Total System Memory 1)Scenario involves a change in workload that now requires more memory for sorts 2)DB2 database is set at fixed memory usage, and thus cannot take memory from OS. Therefore identifies another memory consumer in the database that does not need its memory anymore (e.g. lock list) Free memory in system DB2 database memory use Database sort heap Database lock list DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM操作模式  DATABASE_MEMORY = COMPUTED or Memory used by other applications Other DB memory consumers Total System Memory 1)Scenario involves a change in workload that now requires more memory for sorts 2)DB2 database is set at fixed memory usage, and thus cannot take memory from OS. Therefore identifies another memory consumer in the database that does not need its memory anymore (e.g. lock list) 3)The memory is transferred between the memory consumers. The overall memory usage for this DB2 database stays the same. Free memory in system DB2 database memory use Database sort heap Database lock list DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM迭代机制  Constantly monitors system to make use of or return any free memory to OS (only if DATABASE_MEMORY is set to AUTOMATIC)  Works iteratively to determine an optimal memory configuration for all heaps – Iterative approach prevents instability  Control algorithms help determine interval length and prevent oscillations  In each interval each heap can only grow by 50% or decrease by 20% NODoes any heap need more memory Can memory be taken from OS Determine tuning frequency Take memory from OS and give to heap Find another heap to donate memory to first heap NO YES YES Wake up Go to sleep DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM – 删掉重要索引  10 agents executing queries with “order by” clause  At first, queries use indexes to avoid sorting  After several iterations some of the indexes are dropped – Simulates DBA error in dropping vital indexes  Lack of indexes forces sorts to be performed  Dramatically increases the demand on the sort memory  With only manual tuning, workload will likely get much slower  Lets see what STMM does… DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 STMM – 删掉重要索引 TPCH Query 21 - After drop index - Average times for the 10 streams 0 1000 2000 3000 4000 5000 6000 7000 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Order of execution Time in seconds avg = 959 avg = 2285 avg = 6206 Reduce 63% Some indexes dropped TPCH Query 21 - After drop index - Average times for the 10 streams 0 1000 2000 3000 4000 5000 6000 7000 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Order of execution Time in seconds avg = 959 avg = 2285 avg = 6206 Reduce 63% Some indexes dropped  Query time is reduced by 63% in a few cycles through STMM reacting to environment changes DB2设计与性能优化 讲师 王飞鹏 DATAGURU专业数据分析社区 炼数成金逆向收费式网络课程  Dataguru(炼数成金)是专业数据分析网站,提供教育,媒体,内容,社区,出版, 数据分析业务等服务。我们的课程采用新兴的互联网教育形式,独创地发展了逆向收 费式网络培训课程模式。既继承传统教育重学习氛围,重竞争压力的特点,同时又发 挥互联网的威力打破时空限制,把天南地北志同道合的朋友组织在一起交流学习,使 到原先孤立的学习个体组合成有组织的探索力量。并且把原先动辄成千上万的学习成 本,直线下降至百元范围,造福大众。我们的目标是:低成本传播高价值知识,构架 中国第一的网上知识流转阵地。  关于逆向收费式网络的详情,请看我们的培训网站 http://edu.dataguru.cn DATAGURU专业数据分析网站 FAQ时间 62
还剩61页未读

继续阅读

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

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

需要 10 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

qinyingfei

贡献于2016-11-05

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