让DB2跑得更快——DB2内部解析与性能优化

让DB2跑得更快——DB2内部解析与性能优化

（DB2数据库领域的精彩强音，DB2技巧精髓的热心分享，资深数据库专家牛新庄、干毅民、成孜论、唐志刚联袂推荐！）

 洪烨著

2013年10月出版

定价：79.00元

 

编辑推荐

    本书作者在DB2China数据库论坛担任热点讨论版块版主，主持多次热点讨论以及专家现场诊断，擅长DB2数据库及相关产品的性能调优及故障分析，对DB2技能及实践经验有多年积累，并且近年来多位业界专家一直在积极推动DB2领域的技术交流，真正理解DB2技术人员真正的需求与痛楚，是DB2系统知识及技巧精髓的热心分享者及贡献者。

    作者本人出于对DB2的狂热与追求，通过长期的凝练与汇聚，将DB2知识系统化，把DB2数据库调优技巧的精髓热心地分享给广大读者，并且凭借深厚而扎实的理论及经验，对DB2数据库的内部进行了深入解析，这是对数据库领域所做出的重要贡献与精彩强音！

    单看“内部解析”四个字，就已经能体现本书的宝贵价值，在“内部解析”的基础上进行“性能优化”，定会让您的DB2“跑得更快”！

内容提要

    本书以优化为主题，根据数据库内部原理将DB2数据库对SQL语句及其他操作的内部机制进行详细剖析，并将RDS、DMS、IXM、BPS等DB2内部组件不为人知的一面展现给大家，以期做到对数据库的调优过程知其然并知其所以然。同时本书结合响应时间与资源瓶颈两种性能问题的现象，对数据库调优的整体思路进行详细讲解，对原来老式的调优思路进行整理和改动，结合了DB2 V10.1版本的一些新的监控工具及特性，以一种全新的方式阐述DB2数据库性能调优的基本思路及实践方法。

    本书适合DB2数据库管理员、数据库相关应用程序开发人员、系统管理员、系统架构师及有一定数据库基础的用户自学和参考，也可作为DB2培训的参考用书。

目录

第1篇  性能定义及整体架构

第1章  DB2性能优化概述.... 2

1.1  性能目标.... 2

1.1.1  响应时间... 4

1.1.2  吞吐量... 5

1.2  工作负载类型.... 6

1.2.1  联机事务处理（OLTP）... 6

1.2.2  联机分析处理（OLAP）... 7

1.2.3  决策支持系统（DSS）... 8

1.2.4  企业资源规划（ERP）... 9

1.3  影响性能的因素.... 10

1.3.1  软件代码编写对性能的影响... 12

1.3.2  应用程序架构设计对性能的影响... 14

1.3.3  数据库设计对性能的影响... 14

1.3.4  系统设计对性能的影响... 27

1.4  本章小结.... 34

第2章  DB2架构介绍.... 36

2.1  DB2整体概况.... 36

2.1.1  DB2进程/线程体系简介... 37

2.1.2  DB2内存体系简介... 41

2.1.3  DB2相关文件简介... 42

2.2  DB2组件介绍.... 51

2.2.1  操作系统服务... 52

2.2.2  基本系统调度... 53

2.2.3  关系数据服务... 58

2.2.4  数据管理服务... 59

2.2.5  缓冲池服务... 60

2.2.6  数据保护服务... 63

2.3  SQL语句处理过程.... 65

2.3.1  数据查询语言（DQL）... 66

2.3.2  数据操作语言（DML）... 68

2.3.3  事务处理语言（TPL）... 69

2.4  本章小结.... 70

第2篇  性能监控工具及监控技巧

第3章  性能监控工具.... 72

3.1  实时监控工具.... 72

3.1.1  db2trc. 72

3.1.2  db2top. 75

3.1.3  db2pd. 77

3.2  历史监控工具.... 81

3.2.1  快照... 81

3.2.2  快照视图及快照函数... 83

3.2.3  事件监视器... 86

3.3  DB2工作负载管理（DB2 Workload Manager）.... 87

3.3.1  标识阶段（Identification Stage）... 88

3.3.2  管理阶段（Management Stage）... 89

3.3.3  监控阶段（Monitoring Stage）... 90

3.4  语句解释说明工具.... 94

3.4.1  db2exfmt 94

3.4.2  db2expln. 102

3.4.3  语句解释说明工具对比... 105

3.5  监控技巧.... 105

3.5.1  查找数据库中耗时最长的语句... 107

3.5.2  分析特定语句的时间分布... 108

3.5.3  捕获所有的SQL语句... 111

3.6  本章小结.... 113

第3篇  性能分析及内部原理剖析

第4章  深入探讨优化器.... 116

4.1  语法语义分析.... 117

4.1.1  查询解析... 117

4.1.2  语义检查... 117

4.2  SQL语句重写.... 118

4.2.1  谓词简介... 119

4.2.2  扫描方式... 122

4.2.3  连接运算... 125

4.2.4  查询重写... 130

4.3  优化器编译.... 135

4.3.1  生成备选执行计划... 135

4.3.2  基数评估... 137

4.3.3  成本计算公式... 156

4.3.4  生成可执行的代码... 157

4.4  基数评估检查.... 157

4.4.1  通过COUNT语句检查基数评估... 157

4.4.2  使用Section Actuals分析执行计划... 162

4.5  本章小结.... 164

第5章  SQL语句性能优化之7种武器.... 165

5.1  长生剑——基本统计信息.... 165

5.1.1  统计信息收集方法... 170

5.1.2  统计信息收集策略... 171

5.2  碧玉刀——分布统计信息.... 172

5.3  孔雀翎——列组统计信息.... 177

5.4  离别钩——REOPT.. 182

5.4.1  REOPT处理机制... 184

5.4.2  REOPT的启用方式及监控... 187

5.5  多情环——静态视图.... 188

5.6  霸王枪——优化概要文件.... 193

5.6.1  优化概要文件的使用方法... 194

5.6.2  优化概要文件规则... 196

5.7  拳头——语句优化.... 199

5.8  本章小结.... 202

第6章  数据对象存储设计.... 203

6.1  表类型及设计方法.... 204

6.1.1  常规表... 204

6.1.2  MDC表... 205

6.1.3  分区表... 212

6.1.4  MQT. 214

6.1.5  表设计原则... 217

6.2  索引类型及设计方法.... 220

6.2.1  索引的作用... 220

6.2.2  索引创建原则... 224

6.2.3  索引键顺序的选择... 226

6.2.4  索引设计性能考虑... 227

6.3  DB2设计顾问程序.... 228

6.3.1  战略性的索引创建... 229

6.3.2  战略性的表类型选择... 230

6.4  本章小结.... 232

第7章  DB2物理结构深入解析.... 233

7.1  表空间结构剖析.... 233

7.1.1  SMS（系统管理表空间）结构剖析... 238

7.1.2  DMS（数据库管理表空间）结构剖析... 240

7.1.3  高水位对于性能的影响... 243

7.1.4  对容器进行重新平衡对性能的影响.... 246

7.2  数据页详解.... 247

7.2.1  数据页结构剖析... 247

7.2.2  字段类型与行迁移... 251

7.2.3  页重组... 259

7.3  索引页详解.... 263

7.3.1  索引内部结构剖析... 263

7.3.2  索引的分裂... 266

7.3.3  索引维护和清除... 269

7.4  日志文件结构剖析.... 273

7.5  本章小结.... 275

第8章  I/O管理及优化.... 276

8.1  数据I/O管理.... 277

8.1.1  缓冲池I/O原理... 278

8.1.2  缓冲池逻辑读取... 282

8.1.3  缓冲池物理读取... 285

8.1.4  缓冲池写入操作... 292

8.1.5  基于块的缓冲池I/O.. 297

8.1.6  缓冲池I/O监控... 298

8.1.7  直接I/O管理... 305

8.2  日志I/O管理.... 307

8.2.1  日志读取... 308

8.2.2  日志写入... 309

8.2.3  日志I/O原理... 310

8.2.4  日志文件I/O相关调优参数... 314

8.2.5  归档日志对I/O的影响... 315

8.3  本章小结.... 319

第9章  内存管理.... 320

9.1  内存模型.... 320

9.1.1  实例共享内存... 326

9.1.2  数据库共享内存... 328

9.1.3  应用程序全局内存... 335

9.1.4  代理程序私有内存... 338

9.1.5  排序堆... 339

9.1.6  其他内存区域... 341

9.2  STMM... 345

9.2.1  STMM运行机制... 345

9.2.2  STMM监控... 347

9.3  如何定位及修复内存泄漏.... 348

9.3.1  内存泄漏诊断方法... 348

9.3.2  内存泄漏的处理方法... 350

9.4  本章小结.... 352

第10章  DB2等待事件.... 353

10.1  锁对象及兼容性.... 354

10.1.1  锁对象及锁模式... 355

10.1.2  锁兼容性及锁转换... 360

10.2  锁问题的监控与解决.... 361

10.2.1  锁事件监控... 365

10.2.2  锁问题解决方法... 370

10.2.3  锁案例分享... 377

10.3  latch事件.... 381

10.3.1  latch监控... 382

10.3.2  案例分析... 383

10.4  本章小结.... 384

第4篇  实用工具调优及操作系统优化

第11章  实用工具调优.... 386

11.1  备份恢复工具.... 386

11.1.1  backup. 386

11.1.2  restore. 391

11.2  数据移动.... 392

11.2.1  export 392

11.2.2  import 394

11.2.3  load. 396

11.3  其他管理工具.... 401

11.3.1  reorg. 401

11.3.2  runstats. 407

11.4  本章小结.... 409

第12章  操作系统相关问题.... 410

12.1  AIX.. 411

12.1.1  虚拟内存管理... 411

12.1.2  磁盘及文件系统管理... 417

12.1.3  网络调优参数... 422

12.1.4  操作系统相关参数... 423

12.1.5  系统监控工具... 425

12.2  Windows. 429

12.2.1  内存管理... 429

12.2.2  磁盘及文件系统相关参数... 432

12.2.3  系统监控工具... 432

12.3  本章小结.... 435

第5篇  性能分析思路及优化总结

第13章  性能问题分析思路.... 438

13.1  响应时间问题.... 439

13.1.1  响应时间总结... 439

13.1.2  通过快照进行分析... 442

13.1.3  通过快照函数进行分析... 444

13.2  资源占用问题.... 447

13.2.1  磁盘瓶颈... 447

13.2.2  CPU瓶颈... 450

13.2.3  内存瓶颈... 455

13.3  本章小结.... 457

 

精彩节摘

9.3  如何定位及修复内存泄漏

在怀疑遇到内存泄漏的问题之前，必须谨慎地判断当前数据库中是否真实存在内存泄漏。判断数据库中存在内存泄漏时需要注意以下几点：

—   系统中的内存在逐渐减少，甚至导致换页（paging）；

—   SQL语句越来越慢（经常是换页导致）；

—   随着时间增长，由DB2分配的内存在不断增加。

9.3.1  内存泄漏诊断方法

    正如之前介绍的，从DB2 V9.5版本开始，可以很轻松地检查当前各个内存集的使用情况。每个数据库分区都会保留当前分区中内存使用信息的实时状态。其中包括所有的私有内存、应用内存、数据库共享内存、数据库管理器共享内存及分区间共享的FCM内存。我们可以通过db2pd -dbptnmem命令观察当前数据库中各部分内存的使用情况。输出结果如下：

>>>> Database Partition 0 Memory Statistics <<<<

Controller Enabled:   Y

Controller Automatic: Y

Memory Limit:         28609436 KB  内存限制（INSTANCE_MEMORY的值）

Current usage:        898240 KB       当前内存的使用量

HWM usage:            898240 KB 内存最高使用量

Cached memory:        271424 KB   总共的缓存大小

 

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Mem Cached (KB)

============================================================

APPL-LIAM               160000        160000          159616

DBMS-lfinnie             36608         36608            3648

FMP_RESOURCES            22528         22528               0

PRIVATE                  12032         12032             256

FCM_RESOURCES            10048         10048               0

LCL-p1130882               128           128               0

DB-LIAM                 656896        656896          107904

    可以看到当前分区中INSTANCE_MEMORY的限制值大约为28GB，当前内存总共使用了898MB大小。其中271MB作为申请后并未使用的内存，意味着当前已经提交的内存大约为627MB。如果将这些内存按照内存集分开观察，可以分为以下几个部分：Mem Used代表向操作系统中已经申请的内存部分，DB2会提前向操作系统申请部分多余内存；Mem Used区域是目前可以被DB2分配给各个内存池的总共大小；CACHED部分是还没有被任何内存池占用的内存数量，同时也代表当操作系统中内存不足时，可以释放的最大内存数量。

    对于单次调用db2pd -dbptnmem命令所得到的输出结果，很难从中发现当前是否存在潜在的内存泄漏问题。还需要了解以下情况来证实我们的猜测：

—   如果没有任何激活的应用程序，实际提交的应用共享内存应该非常小（Used -Cached）；

—   如果没有任何监控开关打开，则实例共享内存的值应该非常小；

—   私有内存的大小应该根据线程数量浮动。如果发现每个线程需要消耗1～2MB的内存，代表可能会发生内存泄漏（除非启用私有排序，如果启用私有排序，则每个线程消耗的内存应该不大于SORTHEAP定义的值）；

—   对于单分区实例，FCM_RESOURCES应该是不变的。对于多分区实例，如果存在大量跨节点连接的操作，则FCM_RESOURCES会占用相当大一部分内存；

—   对于数据库共享内存，其内存集大小应该等于内部所有内存池之和（缓冲池、锁列表、Package cache等）。

如果有缓慢的内存泄露发生，仅仅祈祷是不起作用的，需要定期检查当前正在使用的内存量。在开发环境中，以下几个关键点在对内存泄露检查时非常有用。例如：

—   数据库激活之后，还没有任何应用程序连接时；

—   应用程序准备执行前；

—   应用程序执行过程中；

—   应用程序执行后。

    判断内存泄漏需要定期地对比当前数据库中的内存情况。在应用程序数量、执行语句类型及数据量变化不大的情况下，内存的使用应该也会保持恒定。如果发现内存在缓慢增长，则可以认为是内存出现泄漏。但是有一点例外，当启用STMM功能且数据库参数DATABASE_MEMORY是自动调整的情况下，可能会出现数据库共享内存会基于数据库中的空闲内存数量增加或减少，但是其他内存集不会受到任何影响。

作者简介

    洪烨，毕业于湖南大学，曾在IBM担任数据库现场技术支持，为北京移动、北京联通、中国银联、中国银行、中信银行、联想集团、国家气象局、中华联合保险、唐山商业银行等多家企业进行DB2故障诊断、技术支持以及技术培训工作，拥有DB2开发、高级管理以及AIX管理等多项国际认证。

    并在DB2China数据库论坛担任热点讨论版块版主，主持多次热点讨论以及专家现场诊断，擅长DB2数据库及相关产品的性能调优及故障分析，对DB2技能及实践经验有多年积累。

    近年来与多位业界专家一直在积极推动DB2领域的技术交流，真正理解DB2技术人员真正的需求与痛楚，是DB2系统知识及技巧精髓的热心分享者及贡献者。

 

前言

写作背景

    作为最早出现的关系型数据库，DB2提供了高层次的数据利用性、完整性、安全性、可恢复性，以及小规模到大规模应用程序的执行能力。并且DB2以拥有一个非常完备的查询优化器而著称，其外部连接改善了查询性能，并支持多任务并行查询。近几年来，为了应对爆炸式增长的数据量，DB2也在进行推陈出新，不断推出完美应对大数据的技术，并持续增强DB2的性能以及可用性。到本书出版之时，DB2已经推出了V10.5版本，并在该版本中提供了列式存储功能及更加强大的性能提升。

    DB2随着一代代版本的发展，已经成为一个庞然大物，其内部组件众多，对于SQL语句的处理有时甚至需要调动数十个内部组件进行相互协同。当遇到性能问题时更需要对内部原理有详细的了解才能进行有效的处理以及应对，否则就会让人感到无从下手。与Oracle数据库相比，DB2数据库的资料显得相对匮乏，其中鲜有对其内部运行机制进行详细剖析的资料，这也给DB2的传播发展带来了一定的弊端。因此对于这种情况，尽可能地将自己所知分享出来的想法从我脑中涌现，尽可能地对DB2处理过程在流程上进行阐述的念头一直支持着我完成本书的写作。

    在常见的数据库问题中，性能问题不仅出现的频率较高并且很多生产系统中并不存在一个对性能问题进行隔离的高可用机制，正因为如此，在很多关键行业的系统中，性能问题往往成为影响生产系统正常运行的最大因素。而性能问题的影响时间有时长达数小时，这样不仅给生产系统带来了极大的负面影响，也使业务很难正常进行。

    由于性能问题的特殊性，很多情况下问题发生过后并没有留下任何有效的故障信息，这又给之后的分析处理带来了一定的难度和困扰，往往并无相关线索可以对其进行求证。而对于性能问题的信息收集及调优方法，目前关于DB2性能调优的资料过于分散，并没有提供一个很完善的性能解决体系，甚至有些资料中仅仅在套用各个KPI指标对性能问题进行调节，虽然这种方式有时能够解决一些问题，但在更多时候会显得无能为力。

    在本书中，通过对DB2数据库的内部机制进行探讨，希望能够让读者更好地理解数据库的运行机制，当性能问题发生时能够在脑海中对数据库的整体运作机制有个清晰的认识，这样才能够直接有效地对数据库进行分析与优化。

    感谢读者选择本书，笔者水平有限，书中错漏在所难免，敬请读者朋友谅解，并期望读者朋友进行指正。

本书结构

    全书分为5大篇共13章。第1篇主要对性能问题的定义、影响性能问题的因素、DB2的整体组件结构，以及对于各种类型语句的处理机制进行详细的探讨；第2篇主要针对DB2提供的各个监控工具进行阐述，并提供了一些监控建议；第3篇主要阐述DB2的内部运行机制及各个组件的原理；第4篇包含DB2中内部工具的优化与运行机制，以及DB2在各个平台中需要注意的性能参数；第5篇对性能优化思路进行了概括性的总结。

第1篇  性能定义及整体架构

第 1 章主要对性能问题的目标进行了阐述和定义，并描述了可能影响各个工作负载的特征，以及可能对其产生性能影响的因素。

第2章对DB2的体系结构进行了基本介绍，并描述了DB2各个组件处理SQL语句的基本原理与机制。

第2篇  性能监控工具及监控技巧

第3章按照监控特性对DB2提供的监控工具进行了基本介绍，并介绍了一些基本的监控技巧。

第3篇  性能分析及内部原理剖析

第4章对优化器的原理进行了探讨，阐述了优化器的重写机制、优化原理及编译原理，并介绍了如何检查优化器的估算结果的两种方法。

第5章介绍了解决优化器编译问题的的7种性能优化武器，以及何时且如何才能有效地使用这些武器解决实际问题。

第6章描述了为了避免性能问题应该如何对数据库表及索引进行有效设计，针对合适的场景使用适合的技术才能更有效地避免性能问题的发生。

第7章详细描述了DB2数据库的I/O原理，I/O性能通常是数据库运行过程中最为耗时的一环，本章详细介绍了DB2相关I/O情景，以及如何有效地提高I/O性能。

第8章详细介绍了DB2中各个内存池的分配以及作用，并讲述了怎样定位及修复内存泄露的方法。

第9章对数据库的物理结构进行了详细剖析，并讲解了各种情况下物理结构对于数据库性能的负面影响及避免方法。

第10章对DB2中锁及latch等待事件进行了描述与分析，并分享部分等待事件解决案例及心得。

第4篇  实用工具调优及操作系统优化

第11章讲述了backup、restore、export、import、load、reorg、runstats等DB2提供的多种实用工具的执行原理以及性能调优方法。

第12章介绍了AIX及Windows平台上CPU、内存、磁盘I/O及网络等方面的相关优化参数。

第5篇  性能分析思路及优化总结

第13章对性能分析思路进行了归纳与总结，并按照资源占用问题及响应时间缓慢的问题对数据库性能问题提供了整体分析的思路与解决方案。