何文平：数据库架构设计与面向DB2的工程实践

数据库 架构 酒店人事架构图下载公司架构图下载企业应用架构模式pdf监理组织架构图免费下载银行管理与it架构pdf 设计与面向DB2的工程实践 文平 / Wenping 数据库平台技术研究与应用 DB2/Oracle/Sybase/UNIX DTCC2011DTCC2011 Page 2 演讲人介绍  数据库资深技术顾问，从1995年起开始数据库领域工作  职业历程 – 程序员=> – 分析员=> – 管理员=> – 优化顾问  历年工程实践：应用开发、系统设计、系统构建、系统优化、用户服务  历年出版技术专著 – 《PowerBuilder 开发中的数据库设计》 汕头大学出版社 – 《Sybase数据库在UNIX、Windows上的实施和管理》 电子工业出版社 – 《Oracle大型数据库在AIX / UNIX上的实战详解》电子工业出版社 – 《构建高可用AIX UNIX系统》 机械工业出版社（待出版） DTCC2011DTCC2011 Page 3 今日演讲主题  DB2数据库服务，是一个整体服务的系统工程 – 操作系统构架和服务 – 存储系统部署和服务 – 网络系统信息的传输 – 应用系统的数据访问  系统构架中要恰当定义结构、优化资源使用 – 计算资源的使用方面 – I/O资源的使用方面 – 网络通信的使用方面 DTCC2011DTCC2011 Page 4 DTCC2011DTCC2011 Page 5  出现在系统资源提供与数据库资 源使用方面的矛盾 – 文件系统资源、存储资源、网 络资源 – 计算资源、缓存资源  DB2系统：向系统申请资源，向 用户表现效率 DB2系统工程：资源使用上的固有矛盾 DTCC2011DTCC2011 Page 6 DB2系统工程：资源使用上的固有矛盾 出现在系统资源提供与数据库资源使用方面的矛盾 – 文件系统资源 – 计算资源 – 缓存资源  DB2系统：以资源流的方式向系统申请着资源，向用户 表现着效率 – SQL执行需要CPU资源用于合并、排序、计算... – SQL执行需要存储系统的I/O，以提取数据和落实数据 – SQL执行需要网络通信能力，为客户端提供服务 构架中应注意这些矛盾，减小瓶颈出现的可能性 DTCC2011DTCC2011 Page 7 何为DB2系统工程？服务器底层资源  CPU计算能力  内存缓存能力  磁盘数据能力  网络通信能力  OS：调度能力 – ioo – vmo – no – schedo – ........... – /etc/system – /etc/sysctl.conf – ........... CPUs Memo PROCs DTCC2011DTCC2011 Page 8 何为DB2系统工程？数据库资源使用  CPU时间分片 – SQL代码执行 – 数据排序合并 – 磁盘网络IO – 系统状态维护  内存使用 – 数据库缓存（BP） – 文件系统访问  IO数据库读写 – 数据读写 – 日志写（读） – 网络 CPUs Memo PROCs DTCC2011DTCC2011 Page 9 操作系统资源：可用的CPU 提供进程的处理 – DB2守护类进程调度和执行 – SQL操作中的运算、函数调用、排序等操作  CPU的主频、数目因素 – 足够的“POWER”，PC-Server也渐成气候 – 规划未来的Scale-up、Scale-out策略 不同应用类型的特征 – 联机应用应倾向于单分区数据库，侧重于系统垂直扩展能力 – 数据分析应倾向于多分区数据库，侧重于系统水平扩展能力 DTCC2011DTCC2011 Page 10 操作系统资源：可用的内存  操作系统内存将用于： – 操作系统内核、各种服务进程 – 文件系统所需的各种缓存结构 – DB2系统的Buffer等 – 空闲的物理内存  内存大小取决于 – 服务器并发用户数 – 工作负载性质 – 是否有其他应用共享内存  基本原则 – 数据库系统缓存要有较好的命中率 – 少有交换操作的产生  不同的平台有不同的经验值（如AIX的 1:4 原则）  使用64位系统，避免32位问题 DTCC2011DTCC2011 Page 11 操作系统资源：可用的磁盘存储  用于： – 操作系统二进制文件、库文件、其他文件等 – DB2系统的“安装部分” – DB2系统的“数据部分” – 数据表空间、临时表空间、索引表空间、工作区域等  磁盘来源和设定 – RAID 5 – RAID 10  数据存放要考虑 – 操作系统、DB2系统、DB2数据、备份数据的分离 – 多换页空间之间的位置分离 – 数据库数据、数据库日志之间的位置分离 – 数据库表、数据库索引针对于数据库容器的存储位置分离 DTCC2011DTCC2011 Page 14 今日演讲主题  DB2数据库服务，是一个整体服务的系统工程 – 操作系统构架和服务 – 存储系统部署和服务 – 网络系统信息的传输 – 应用系统的数据访问  系统构架中要恰当定义结构、优化资源使用 – I/O资源的使用方面 – 计算资源的使用方面 – 网络通信的使用方面 DTCC2011DTCC2011 Page 15 系统构架：结点所在与我们的关注目标 结果 数据源 处理 路径 流量 •其他制约要素 •带宽 •并发 •队列 •许可 •优先 •........ DTCC2011DTCC2011 Page 16 系统构架：资源使用上的系统工程  系统构架中要恰当定义结构、优化资源使用 – 计算资源的使用方面 – I/O资源的使用方面 – 内存资源的使用方面 – 缓存的作用 – 网络通信的使用方面 DTCC2011DTCC2011 Page 17 构架设计：I/O资源的使用 尽量减少系统的I/O量以节省存储资源的调度 – 文件系统的优化配置 – 缓存资源的配置以优化I/O – 存储子系统的配置优化以提高I/O效率 – 数据库对象的存储设计优化 – SQL执行路径优化 – .............................. DTCC2011DTCC2011 Page 18  这需要进行操作系统范围的调整  例如，对于AIX而言： – 缓存参数调整： – vmo -o minfree=8000 -o maxfree=8512 – vmo -o minperm%=3 -o maxperm%=90 -o maxclient%=90 – 替换策略参数： – vmo -o lru_file_repage=0 – 换页空间参数： – vmo -o scrubclean=1 – LVM IO参数 – ioo -p -o pv_min_pbuf=1024、ioo -p -o lvm_bufcnt=64 – 文件系统参数 – ioo -p -o j2_minPageReadAhead=32 -o j2_maxPageReadAhead=512 – ioo -p -o j2_nBufferPerPagerDevice=2048 – 其他参数等（参见手册说明） I/O资源的使用：文件系统相关 DTCC2011DTCC2011 Page 19 适配卡、存储设备应具有最佳性能和可恢复能力 光纤通道卡（FC卡，对应性设置） 物理硬盘(hdisks) – 设置磁盘最大队列深度 – chdev -l hdiskX -a queue_depth=128 – 使用 iostat -D监控设备队列 I/O资源的使用：磁盘设备优化 DTCC2011DTCC2011 Page 20 异步I/O机制可以有效优化DB2 I/O性能 – DB2使用异步I/O来维护缓冲池页面 需要调整异步I/O服务器数目和相关参数 – （AIX） – chdev -P -l aio0 -a maxservers=16 -a maxreqs=8192 – aioo -o maxservers=16 -o maxreqs=8192 – 使用iostat -AQ 显示 AIO 统计，如果入队请求数大于AIO服务器数则增大AIO 服务器数目 – 使用下面的命令检查当前运行的AIO服务器数目 – ps -ek |grep aioserver I/O资源的使用：I/O算法优化 DTCC2011DTCC2011 Page 21 一般的网络硬件都能满足DB2访问要求 – 千兆、百兆等 常用网络调整（AIX） – RFC1323：no -p -o rfc1323=1 – IP队列长度：no -r -o ipqmaxlen=250 – TCP/UDP发/收缓冲区：no -p -o tcp_sendspace=221184 -o tcp_recvspace=221184 -o udp_sendspace=65536 -o udp_recvspace=655360 – 注：在AIX的Oracle RAC部署中，这些网络参数必须调整 I/O资源的使用：网络设置优化 DTCC2011DTCC2011 Page 22  用户进程数的调整 – （AIX）：chdev -l sys0 -a maxuproc=4096  用户的Shell限制 – 打开文件句柄数指定（nofiles参数） – 用户Shell 资源限定（对于AIX来说修改为-1） I/O资源的使用：SHELL限定相关 DTCC2011DTCC2011 Page 23  DB2系统需要恰当申请系统的存 储资源 – 数据存放 – 数据访问 – 数据缓存化  设计合理的存储使用 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 是DB2 这类存储敏感型应用的最重要考 虑要素 I/O资源的使用：数据库存储设计 DTCC2011DTCC2011 Page 24  存储设备用来存放业务数据 – 表数据、索引数据、lob数 据  数据存储上的矛盾基本点 – 连续操作和随机操作的矛盾 – 缓存化和数据可用性的矛盾 – 并发访问和一致性上的对立 各种方案都有着另一面的 问题  构架设计中这是重中之重 I/O资源的使用：数据库存储设计 DTCC2011DTCC2011 Page 25  DB2实例与DB2数据库（主要）之间可以保持1:1关系  选择使用恰当的页面大小（4K, 8K, 16K, 32K） – OLTP类型的应用 – 小页（4K、8K) – DSS – 大页（16K、32K) I/O资源的使用：数据页/块尺寸设计 DTCC2011DTCC2011 Page 26 为每种页面创建一个对应的临时表空间 表空间不必多，但要： – 分散到多个磁盘上，提高IO并发性 – 降低维护成本 – 使用RAID设备时无需考虑多容器问题 表空间创建中的数据分离考虑 – 区分数据表空间和索引表空间 – 对某个重要表空间可以使用独立的缓冲池） I/O资源的使用：数据分布与冲突规避 DTCC2011DTCC2011 Page 27 可以使用SMS（系统管理的表空间），用于： – 临时表空间的创建 – 指定在（AIX）JFS2文件系统下 – 自动扩展大小 可以使用DMS（数据库管理表空间），用于： – 常规数据和索引数据 – 指定在（AIX）JFS2文件系统下 – 使用裸设备以提高IO性能 文件系统采用直接IO、并发IO挂载（AIX： DIO、CIO） – 跳过文件系统缓存的使用，避免双缓存的情况 I/O资源的使用：表空间管理方式选择 DTCC2011DTCC2011 Page 28  DB2中是在表空间中创建表，注意表的选项： – APPEND ON：如果数据表插入操作都是追加数据，可以考虑启 用 – LOCKSIZE：如果并发访问不多，但是一次操作设计数据量很大， 可以考虑启用 – PCTFREE：用于为LOAD或者REORG保留空间，如设置为10 表到表空间的存储关系需要指定，尽可能考虑表数据间读 写的分离问题 表上创建的索引很可能造成负性能效果： – 联机业务类系统应有少的索引数，如3到5个 – 数据分析类系统应用大量的索引和维度表 I/O资源的使用：表的设计与部署选择 DTCC2011DTCC2011 Page 29 分配单独的磁盘并分散到多个物理磁盘 最好能够镜像（RAID 10）  LOGFILSIZ – 适当增大 如 5000*4K 数据页或者更多  LOGPRIMARY – 分配足够的主日志数目，确保所有的日志都存放在主日志中 – 仅在极其特殊的情况下使用LOGSECOND  LOGBUFSZ – 增加到 512 或者更大  MINCOMMIT – 保留默认值1，一般而言不要超过2-3 I/O资源的使用：数据库日志设置 DTCC2011DTCC2011 Page 30 并发IO – db2set DB2_PARALLEL_IO=*,8  DSS系统配置LOAD端口 – db2set DB2ATLD_PORTS=60000:65000 大并发量OLTP环境 – DB2_SKIPINSERTED – DB2_SKIPDELETED – DB2_EVALUNCOMMTTED I/O资源的使用：使用并发操作优势 DTCC2011DTCC2011 Page 31 对性能影响最大的参数 避免使用太多的缓冲池 – 每种页面大小2个，1个给临时表空间，1个给其他的表空间 – 考虑为索引表空间指定单独的缓冲池  SOFTMAX – 控制缓冲池中的脏页刷新如磁盘 – 取决于负荷和恢复时间， – 小值 – 更频繁的写操作 – 短的恢复时间 – 大值 – 略少的写操作 – 长的恢复时间 – 性能上的获益 内存资源的使用：DB2 数据缓存结构 DTCC2011DTCC2011 Page 32 应用要依赖OS的支撑 过量的缓存设置可能对OS运行有害： – 操作系统空闲内存缺失导致交换 过量的缓存设置对数据库也无益： – 一定的命中率后，增量缓存的边际共享率接近0  40%~~50%？缓步递增！ 内存资源的使用：必须为OS预留 DTCC2011DTCC2011 Page 33  RUNSTATS – 定期运行RUNSTATS更新统计信息 – V9.1 可以自动运行,如果没有定期批量的统计信息更新，建议启用 – 数据库配置参数AUTO_RUNSTATS  REORG – 重组表中的碎片 – RUNSTATS->REORGCHK->REORG->RUNSTATS  快照（SNAPSHOT） – 运行快照信息获取性能数据，如缓冲池统计信息  事件监控器（EVENT MONITOR） – 监控特定事件，如死锁，连接等  db2pd – 监控实时运行信息，不影响服务器运行，推荐使用 资源使用的持续性：运维管理 DTCC2011DTCC2011 Page 34 体现用户业务需求：形成逻辑设计 – 可能谁做都结果类似 体现工程解决时间：形成物理设计 – 对象的实体设计 – 对象的存储设计 – 对象的相关性设计 – 对象的约束设计 考虑编程简易型、运行高效性的再调整 – 非 规范 编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载 化设计 – 数据分布设计 应用设计和资源使用：应用结构设计 DTCC2011DTCC2011 Page 35 访问模式和应用系统构架设计的矛盾 – DB2对象设计中的表和索引配比？ – 表、索引、数据量决定了何种 SQL执行计划？ – 并发性方面导致的一致性、并发性矛盾？ 设计中关于数据量和性能的矛盾 – 分割、分区、冗余？ 设计中关于查询和更新的矛盾 – 索引、不索引、那种索引？ 规范化和非规范化 – 3NF？2NF？ 应用设计和资源使用：应用结构设计 DTCC2011DTCC2011 Page 36  数据库表对象结构将决定部分SQL工作方式 – 表现为IO扫描中的“超宽行”或海量数据 – 表现为结果集中的“无用”数据或浪费 – 表现为结果集形成中的无效排序等操作  数据库表的列类型选择将决定部分SQL工作方式 – 数据匹配中的类型转换 – 数据比对中的算法调用  数据库表对象物理部署将决定部分SQL工作方式 – 数据存储的密度 – 数据块更新后的存储密度 – 表设计中的NULL值问题 – 磁盘I/O的扫描范围 – 磁盘I/O的效率 应用设计和资源使用：应用设计常见误区 DTCC2011DTCC2011 Page 37  数据库表对象结构调整 – 超宽行必要性的存在，是否可以垂直分割 – 调整索引以引导SQL到恰当的扫描路径 – 恰当描述SQL以避免排序  数据库表的列类型选择 – 数据类型的匹配  数据库表对象物理部署将决定部分SQL工作方式 – 提高存储密度 – reorg – runstats – 垂直分割 – 磁盘I/O的扫描范围 – 文件系统整理、裸设备使用 – 磁盘I/O的效率 – IO重分布 – 异步IO – 文件系统挂载调整 应用设计和资源使用：DB2 设计误区规避 DTCC2011DTCC2011 Page 38 数据库表对象结构调整 – 超宽行，垂直分割？ – 避免超宽行、超高行 – 避免表稀疏存储 – 导致冗余增加和关系复杂 – 如利大于弊，则是很好的工 程优化手段 应用设计和资源使用：分割的使用 DTCC2011DTCC2011 Page 39 要保证表有足够的数据密度 – 通过数据库运维提高数据分布密 度 – reorg – 数据页存储效率 – 索引页存储效率 – 进而I/O效率 – runstats – 通过操作系统运维提高数据分布 密度 – reorgvg – 存储位置聚集 – 进而I/O效率 应用设计和资源使用：数据密度问题 DTCC2011DTCC2011 Page 40 应用设计和资源使用：计算资源相关 当减少系统IO资源使用的时候，计算资源也就同步减少 了； 数据库设计中的潜在计算资源消耗要素： – 排序操作，可否使用索引的已排序特性？ – 类型转换，是否通过数据库结构设计避免转换？ – 1 = ‘1’ ？ – 函数调用，仅调用必要的函数，小心函数导致索引的失败 – 数据列的业务表述、数据列的类型定义失当？ – 是否可通过设计冗余列来避免计算？ – 连接操作，是否是高效的连接方式和结果集获得路径 – 要考虑表的垂直分割和水平分割 – 反之，表的合并也是考虑的方向 DTCC2011DTCC2011 Page 41 应用设计和资源使用：SQL设计和编程误区  SQL编程中的潜在计算资源消耗要素： – 排序操作，是否可使用索引的已排序特性？ – 类型转换，是否设置了不恰当的变量类型？ – 函数调用，是否函数或表达式导致索引的失败？ – 优化器工作，统计是否正确以及优化器 – 及时完成统计 – 发现表、索引的低密度造成的IO、CPU问题  一个简单事实：大多数应用系统的性能问题来源于SQL性能。  另一个简单事实是：SQL性能取决于数据库系统设计和当前数据分 布。  再一个简单事实是：当前数据分布及其统计取决于数据库管理员的 勤与检。  需求提出者、构架师、分析员、管理员、程序员全有责任。 DTCC2011DTCC2011 Page 42 应用设计和资源使用：建议SQL编程规范 避免不必要的SQL（例：存在性测试） 减少返回的行（例：那些没有where的SQL） 仅选择必要的列（例：select *...） 单行返回和游标 避免排序（例：distinct、union） 使用索引数据排序（例：使用索引列排序或分组） 使用嵌入视图 避免数据类型转换（列和变量在类型、长度的比较） DTCC2011DTCC2011 Page 43 应用设计和资源使用：建议SQL编程规范 在索引列上进行表连接 连接查询的结果集避免在多（表）列上排序 小心使用like匹配，避免前like，可使用in替换？ 尽量使用exist 避免使用NOt 避免在对比中在列上使用表达式（可能索引失效） 限制不必要函数使用（计算资源、索引限制），设计相 关？ DTCC2011DTCC2011 Page 44 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf ：DB2构架中的角色 应用需求是要满足的，但有时需要必要的让步！ 结构设计者将是系统未来状态的主要决定者！ 系统构建、物理部署可以在一定程度上决定性能和可靠 性！ 应用开发者将是系统未来状态的主要决定者！ 系统、数据库管理员对应用运行状态有一定程度影响 DTCC2011DTCC2011 Page 45 总结：DB2构架中的工作目标  需求的产生：均衡DB2系统的资源申请和运行环境配置 – DB2需要CPU资源用于合并、排序、计算... – DB2需要存储系统的I/O，以提取数据和落实数据 – DB2需要网络通信能力，为客户端提供服务  应对的方法：均衡系统资源申请与提供方面的矛盾 – 存储资源：减少IO量、优化IO方法、优化存储和文件系统 – 计算资源：减少计算、优化设计和SQL – 缓存资源：分配恰当的缓存。提高系统、数据库命中率  阶段性任务 – 设计阶段：设计中实事求是 – 开发阶段：开发误区要规避 – 部署阶段：平台的优化部署 – 测试阶段：具有压力的实测 – 运维阶段：规范化管理任务 DTCC2011DTCC2011 Page 46 感谢大家的参与 衷心感谢那些为本讲座提供所引用技术的机构： IBM ：DB2 UDB这样“好用”的产品 Quest ：Spot系列这样的“亮点”产品 DTCC2011DTCC2011