第 1 章 性能优化方法学

第 1 章 性能优化方法学

我们经常遇到这样的情况:数据库应用系统上线运行一段时间后,就会出现性能问题,例如处理相同的查询比刚上线要长很多,在业务繁忙时事务吞吐量无法满足实际需要,有时候严重的性能问题甚至导致数据库宕机。往往在这个时候,客户才意识到,在设计开发阶段,数据库系统的性能需求没有得到应有的重视。

当出现这样的性能问题时,有的DBA在慌乱之中对数据库进行盲目的参数调整,这可能让情况更加糟糕;也有的DBA,要求将运行数据库的现有硬件替换成高档硬件,这很可能是一种资源的浪费,因为有时候即使换了高档硬件,性能问题还是依旧存在。

归根结底,他们急需一种满足实战要求的性能优化方法学。

1.1 什么是性能问题

从大的方面来看,性能问题往往分为两大类:影响了整个系统的问题和影响了部分应用的问题。影响了整个系统的问题,例如整个系统的I/O瓶颈;影响了部分应用的情况,例如某一特定应用或SQL语句。

性能问题比功能性问题难解决,最首要的原因在于,性能问题往往是断断续续发生的,不容易监控和捕捉。其次,根据症状不容易找到问题的真正根源。例如有时候发现数据库系统有大量的锁超时症状,真正根源却不是锁的问题,而是由于过时的统计信息导致优化器选择了非最优的访问计划导致的,这对DBA来说往往是很难定位的。

本节将首先探讨如何发现系统的性能问题,随后讲述衡量性能的关键指标,如何进行性能基准测试,最后探讨如何制定优化目标。

1.1.1 如何发现性能问题

性能问题的背后实际上是业务驱动的。所以,可以从业务角度确定问题,设定每个问题的优化目标,设定每个问题的优先级,随后从系统角度确定原因,弄清楚时间花在何处;资源是如何被消耗的。

具体来说,可以先从下面的问题入手,来判断系统是否存在性能问题。

1. 是否有性能降低,与什么相关?“基准”是什么?

2. 一个系统的性能看起来在随时间流逝下降了?还是不同的系统性能比较,发现有一个系统性能下降了?

3. 性能下降是什么时候发生的?与某个工作负载的运行相关吗?性能下降周期性地发生吗?

4. 性能下降的前后系统有什么变化吗?例如添加了新硬件、或应用程序被更改了、大量数据被加载、或者更多的用户访问这个系统?

5. 能不能向数据库管理员、应用程序开发人员以及架构师多了解一些业务和系统情况?因为DB2服务器几乎总是硬件、中间件和应用程序这样复杂环境的一部分。

1.1.2 衡量性能的关键指标

一个系统的资源例如存储、CPU和内存等越多,资源被调度的越有效,能处理的负载就越大。换句话说,性能受两个关键因素的影响。其一是系统可用资源,其二是这些资源是如何被使用和共享的。

从量化指标来看,性能的好坏可以从以下三方面来衡量:

1. 响应时间:应用向数据库服务器发起请求,服务器完成处理并返回结果给应用,中间总共消耗的时间,它反映了数据库服务器的处理速度。

2. 事务吞吐量:通常用每分钟处理的事务数(TPM)或者每秒钟处理的事务数(TPS)来计算,这个指标反映了系统的事务处理能力。

3. 资源利用率:数据库服务器在处理事务或者查询的过程中,系统资源包括CPU、I/O以及磁盘的使用情况,这个指标反映了系统资源是否被有效利用。

1.1.3 性能基准测试

制定性能优化目标之前,首先需要对系统做基准测试(Benchmark Test,即BMT)。基准测试是一种测量和评估软件性能指标的活动,它通过科学的测试方法、测试工具和测试系统,实现对某项性能指标进行定量和可对比的测试。

基准测试能帮助用户理解数据库在不同条件下的性能情况。例如通过基准测试建立一个已知的性能水平(称为基准线),当系统的软硬件环境发生变化之后再进行一次基准测试以确定那些变化对性能的影响,这是基准测试最常见的用途。

基准测试的时机很多,可能是数据库配置参数更改,也可以是应用部署发现变化,或者是数据版本升级了。这个时候可以将再次测试的性能值和变化前进行对比,并做定量分析以确认性能变化情况。例如在开发阶段,使用基准测试来确定应用程序是否出现性能倒退;在运维阶段,使用基准测试来对调优前后的结果进行对比。

注意:基准测试不仅仅是DBA的工作

基准测试是数据库应用生命周期的一部分,不仅需要DBA的参与,也需要设计人员和应用开发人员的积极参与和配合。

1.1.4 制定优化目标

制定性能优化目标的关键在于量化。不论是整个系统的问题,还是只影响了部分应用的问题,首先都要确定响应时间能比基准值减少多少,其次要确定事务吞吐量能比基准值提升多少。

根据系统性能的基准值以及系统现状,就可以灵活制定性能优化目标了,比如:

1. 现有系统性或者部分应用存在性能问题,制定优化目标。例如要求系统优化后,响应时间要比基准值减少20%,事务吞吐量则要在基准值的基础上增加20%以上。

2. 现有系统或者部分应用发生大的变更后,制定优化目标。例如新上线了一个模块,系统的负载变重,但是硬件资源没有发生变化,这时的性能目标可以定义为:新模块上线后响应时间要和基准值相似,可接受的幅度在5%左右,但是总的事务吞吐量要求在基准值的基础上增加20%。

3. 对现有系统或者部分应用进行优化,到了一定阶段后,制定后续目标。例如,目前性能已经达到基准值的150%了,考虑到边际效应,以后的优化只能产生越来越少的效益。优化无止境,当DBA考虑需要多少时间和金钱用于性能优化时,要评估一下所花费的时间成本和金钱成本。

1.2 性能优化方法学

我和很多DBA交流过,有些人认为性能优化很高深,需要了解DB2的配置参数、锁、交易日志等技术细节,通常持有这种观点的人都是数据库高手,而且他们也以掌握了这些细节而自豪。

真实情况是,性能优化没有那么神秘,未必一定要掌握那么深入的技术细节。大家知道,性能问题的解决是找到瓶颈和解决瓶颈的过程,只要在应用层和数据库层找到了真正的瓶颈,随后使用正确的方法学加以优化,不用完全掌握那些技术细节,也能有效地解决问题。

本节首先谈谈几种常见的性能优化误区,随后探讨一下两种性能优化方法学,即自上而下方法学和自下而上方法学,最后阐述如何在性能优化时灵活选择它们。

1.2.1 几种常见误区

如果没有掌握正确的方法学,在实际生产环境中定位和解决性能问题不是一件容易的事。我们接触了很多客户,在性能优化时存在下面三种典型的误区。

1. 性能优化可以通过参数调整来解决所有问题

客户通常都热切希望不要动他们的硬件和应用,DB2有什么内部参数调整一下,就可以解决所有的性能问题。这种观点是不正确的,数据库参数要调整,但即使调整好了也并不能保证解决所有的性能问题。

2. 性能优化是DBA的工作,不需要设计人员和开发人员参与

这个观点认为,在项目进行当中,设计人员只关注数据模型,不用关心逻辑设计和物理设计;开发人员只关注业务功能,不用关心SQL语句的性能问题。但是,这种观点是十分错误的,大多数系统的性能问题还是由于设计和应用导致的,所以在优化工作中,需要设计人员和开发人员的积极参与。

3. 用更快的硬件可以解决所有的性能问题

持有这种观点的人很多,他们认为出现性能问题后,用更快的CPU、更多的内存和更快的I/O设备就能解决。但是,这种说法是不全面的,例如对于锁问题造成的懒惰系统,增加硬件资源是不能解决的。实际上,需要找到性能问题的真正原因,再采取恰当的措施。

注意:什么时候选择硬件扩容?

如果确认系统在应用或者数据库层面已经没有优化空间了,这时可以考虑硬件扩容的方式。例如针对性地增加存储,使用更快的CPU、更多的内存、更快的网络连接,或者上面三者的组合。

1.2.2 自上而下(From Top To Down)方法学

一个系统进入正式运维阶段前,通常会经历设计、开发和上线等阶段,自上而下方法学是一种理想的方法论,要求在各个阶段不仅要考虑业务功能的实现,而且要同时考虑性能。例如,在设计阶段考虑数据库逻辑设计、数据库物理设计、应用设计等;在开发阶段,考虑如何编写高效的SQL语句,如何减少死锁等;在上线阶段,考虑内存和CPU的优化,考虑磁盘I/O设计和调整等。

下面几点也是自上而下方法学重点强调的:

1. 优化工作一定要贯彻始终,越早越好,因为开始的越早,其付出的成本也就越小。

2. 系统上线前,应尽可能的解决好所有的性能问题,否则上线后出现问题,往往是灾难性的。

3. 设计和开发人员需要认真对待设计和开发阶段的性能问题,不能完全依靠性能更强的硬件解决。

1.2.3 自下而上(From Down To Top)方法学

与自上而下不同,自下而上方法学是从硬件和操作系统开始,接着是数据库,随后是应用服务器,再到应用这样的顺序去诊断问题,根本目标是找到性能瓶颈。很多DBA都有这样的感觉,当出现性能问题时,找设计和开发人员帮助,有时候很难,因为这些人在正式上线后大部分已经退出项目组了,但是找系统管理员和网络管理员帮助是非常容易的,在大部分单位都可以做到。所以,这种方法学对DBA来说容易上手,其难点在于找性能瓶颈的过程。

该方法学在解决性能问题时,共分为四步:

1. 第一步,问题监控:弄清楚系统的整体运行情况,包括硬件、操作系统、数据库、中间件和业务,确定性能瓶颈到底在哪一个层面。

2. 第二步,参数调整:找到性能瓶颈后,如果发现可以通过参数调整来解决,那么优先通过参数调整来低成本地解决问题,通常这些参数包括硬件参数、操作系统参数、DB2参数、中间件参数等。

3. 第三步,数据库优化:如果性能瓶颈在数据库层面,并且参数调整无法解决问题,这时就要根据具体的瓶颈种类进行灵活处理,具体的处理办法见本章1.4节。

4. 第四步,性能验证:优化完毕后,将优化指标和优化前的基准值进行对比,没有必要片面追求最高性能,只要当前性能指标满足应用的真正需要即可。

1.2.4 自上而下和自下而上,如何选择?

从技术上来看,这两个方法学的目的都是为了解决性能问题,只是顺序不同,一个是自上而下,一个是自下而上。自上而下是理想的方法,自下而上通常是不得已而为之。如果多用自上而下,需要救火的情况就会少,否则不可避免地要会采用自下而上的方法来救火。不过,在具体的项目中,技术角度只是考虑的一个层面,往往要根据实际情况进行选择和取舍。

1. 如果是一个新系统,那么可以采用自上而下的方式,在设计、开发和运行维护的各个阶段不仅考虑功能实现,也要考虑性能问题。

2. 如果是系统已经上线并且运行一段时间了,性能问题变得严重后才被关注,这个时候其实就是一个现场救火了,采用自上而下几乎是不可能的,客户没有时间或者也不可能把设计、开发和上线情况说清楚,所以只能采用自下而上方法学。

3. 有时候,当优化人员参与一个迭代式项目的时候,不同模块处于不同的生命周期。 例如有的模块也许刚进入设计和开发阶段,这个时候适合采用自上而下方法学;但是有的模块已经过了设计和开发阶段了,进入上线试运行阶段了,这个时候只能采用自下而上方法学。

1.3 高质量数据库设计

如果要建设一个新的数据库系统,那么必然会经历设计、开发和维护的各个阶段,按照自上而下方法学,在考虑业务功能实现的同时,性能优化应贯彻始终,越早越好,因为开始的越早,其付出的成本也就越小。

本小节将按照自上而下方法学的实践方法,深入探讨一下如何进行高质量的数据库设计。

注意:自上而下方法学施行起来不容易

自上而下方法学是性能优化理论上最好的方法,它要求在设计、开发和维护的各个阶段都要重视性能问题,在这种方法学指导下实施的数据库通常性能较高,而且在运维阶段出现性能问题的概率要小很多。

但是,这种方法学施行起来不容易,它需要设计人员、开发人员和DBA的通力协作。现实中,设计人员往往只关注数据模型;开发人员往往只关注业务功能的实现;到了上线运行后,只能靠DBA来四处救火了。

先看一下数据库的设计过程,如图1-1所示。首先需要以用户的眼光来看待现实世界的业务,做到真正理解客户的业务需求,这就是通常所说的收集需求阶段;随后业务人员根据业务需求设计概念模型,并且该模型需要和现实世界相对应,这就是概念模型设计阶段;接着进入逻辑结构设计阶段,在这个阶段数据库设计人员使用业务人员的概念设计结果,将其转换为具体数据库的对象结构,例如表、视图等;然后是物理结构设计阶段,需要为逻辑模型选取一个最适合的物理环境。

{%}

图 1-1 数据库设计的一系列阶段

设计完成后,就进入开发阶段了,这个阶段DBA会将前面物理设计的结果部署到目标机器上,开发人员开始编写应用代码去实现业务逻辑,经过测试后准备上线;最后是运行维护阶段,这个阶段主要是上线后的日常监控、维护和优化工作。

本节将从自上而下优化方法学的思路出发,谈谈在各个阶段需要特别注意的重要事项。

1.3.1 充分了解需求

这个阶段的目标是准确了解用户的需求,它是整个数据库设计过程的基础,也是最困难、最耗费时间的第一步。这个阶段主要由业务人员完成。首先调查组织机构情况、各部门的业务活动情况、协助用户明确对新系统的各种要求、确定新系统的边界。随后调查、收集与分析用户在数据管理中的信息要求、处理要求、安全性以及完整性要求,最终得到数据流图表达的数据和处理过程的关系。

除了上述功能需求外,性能需求也需要在这个阶段被定义。通常包括并发要求、响应时间要求、数据库容量要求、I/O吞吐能力要求、系统用户容量要求和系统运行时间要求,例如7×24小时不间断运行,或者可连续运行一周或一月。

在现实的设计实践中,如何才能获得真正有效的性能需求呢?这需要从以下三个方面加以考虑。

1. 性能指标必须量化

指标量化应该不难理解,但是有了数字并不代表就实现了真正的量化。例如常见的一种需求是“系统需要支持5000用户”,或者“最大在线用户数为8000”。这些有数字的需求仍然不够明确,因为还需要区分在线用户和并发用户的区别。为便于理解,先来看一个性能需求的例子。

某个金融行业交易系统的需求:

1. 系统总容量达到日委托9000万笔,成交9000万笔。

2. 系统处理速度每秒7300笔,峰值处理能力达到每秒10000笔。

3. 实际股东帐号数3000万。

这个例子中已经包括三个明确的性能需求:

1. 交易吞吐量:日委托和日成交都是9000万笔。

2. 平时处理需求:每秒7300笔;忙时处理需求:峰值达到每秒10000笔。

3. 用户容量:实际股东帐号数3000万。

2. 有实际操作意义

一般来说,性能需求要么由集成商提出,要么由客户提出。很多集成商由于项目进度的压力,往往对性能需求的定义不太严格,靠拍脑袋做决定,这是错误的,一定要确保在定义性能需求时,所使用的数据和计算公式是有根据的。对于由客户提出的情况,例如电信、银行、保险、证券以及一些其他运营商级系统的客户,他们和集成商不一样,他们往往对性能需求非常认真,但是这又是另外一个极端,经常提出超过实际需要的性能需求,这个时候,需要耐心做好说服工作,让客户明白性能是有成本的,没必要追求超过实际需要的性能。

3. 对性能需求的理解具有一致性

相关人员对性能需求的理解不一致通常是由于参考对象的不同导致的。例如有的人员根据客户以往的业务情况来分析客户的业务量;有的人员参考其他规模类似客户的情况;有的人员参考其他同行企业公布出来的数据;有的人员参考类似行业的应用。这就需要相关人员使用相同的参考对象来定义性能需求,否则很难达成一致。

1.3.2 设计概念模型

概念模型是按用户的观点来对数据和信息建模,通常用实体-关系图(E-R图)表示。通过对用户需求进行综合、归纳与抽象,形成一个不依赖于具体数据库管理系统(DBMS)的概念数据模型,但可以转换为某一DBMS支持的特定数据模型。概念模型具有较强的语义表达能力,能够方便、直接地表达应用中的各种语义知识。除此之外,概念模型清晰、易于用户理解,是用户与业务人员之间进行交流的语言。

1. 定义实体

实体都有一个共同的特征和属性集,可以从需求收集阶段收集的基本数据资料表中直接或间接标识出大部分实体。例如客户代码、代理商代码、产品代码等将其名词部分代表的实体标识出来,从而找出潜在的实体,形成实体表。

2. 定义关系

关系模型中只允许二元关系,n元关系必须定义为n个二元关系。根据实际的业务需求和规则,使用实体联系矩阵来标识实体间的二元关系,然后根据实际情况确定出关系名和说明。这里的关系类型需要明确,是标识关系还是非标识关系?如果是非标识关系,是强制的还是非强制?如果子实体的每个实例都需要通过和父实体的关系来标识,则为标识关系,否则为非标识关系。非标识关系中,如果每个子实体的实例都与而且只与一个父实体关联,则为强制的,否则为非强制的。

3. 定义码

通过引入交叉实体除去上一阶段产生的非确定关系,然后从非交叉实体和独立实体开始标识候选码属性,以便唯一识别每个实体的实例,再从候选码中确定主码。为了确定主码和关系的有效性,通过非空规则和非多值规则来保证,即一个实体实例的一个属性不能是空值,也不能在同一时刻有一个以上的值。

4. 定义属性

从基本数据表中抽取说明性的名词开发出属性表,确定属性的所有者。定义非主码属性,检查属性的非空及非多值规则。此外,还要检查完全依赖函数规则和非传递依赖规则,保证一个非主码属性必须依赖于主码。以此得到了至少符合关系理论第三范式的改进的全属性视图。

5. 定义其他对象和规则

定义属性的数据类型、长度、精度、非空、缺省值、约束规则等。

1.3.3 设计逻辑结构

本书第4章和第5章将深入探讨如何设计高质量的逻辑结构。简而言之,设计逻辑结构首先选择最适合描述相应概念结构的数据模型。数据模型是数据库系统的核心,主要包括网状模型、层次模型、关系模型等,DB2就是基于关系型数据模型实现的。

数据模型优化的关键在于确定数据依赖,消除冗余的联系,利用范式对数据模型进行规范化。数据库应用的类型不同,所采用的范式也会不同。

数据库系统从应用上分为事务型的(On-line Transaction Processing,简写为OLTP)和分析型的(On-line Analysis Processing,简写为OLAP)。

由于OLAP应用和OLTP应用有本质差异,所以在逻辑设计上也不同。OLTP应用通常需要尽量减少冗余,所以采用第三范式;OLAP应用中的表结构采用星型或雪花型模型,以方便查询和多维分析,例如业务数据(例如成本、收入)存储在事实表中,而这些事实数据所依赖的属性(例如时间、地区、产品、客户)存储在维表中。

比较:OLTP应用和OLAP应用的区别

OLTP应用的用户是业务人员,要求非常快地处理查询和事务,ERP和CRM系统是典型的OLTP系统;OLAP应用的用户是决策管理人员,要求处理复杂查询以及报表等。关于它们之间的区别如表1-1所示。

表 1-1 OLTP和OLAP应用之间的主要区别

特点

OLTP应用

OLAP应用

负载特点

日常业务操作

查询、分析、报表和批量数据加载

SQL语句种类

SQL语句比较简单,主要是插入和修改操作

SQL语句比较复杂,主要是查询操作

事务吞吐量

非常高

不高

单笔事务的资源利用率

索引

少量索引

索引比较多

数据规范化

第三范式

星型或雪花模型

分区技术

表分区使用较多

DPF、表分区和MDC结合使用

物化视图

很少

很多

逻辑结构的设计过程就是将E-R图转换为关系模型,即将实体、实体的属性和实体之间的联系进行转化。这种转换一般遵循如下原则:一个实体转换为一个关系模式,实体的属性就是关系的属性,实体的码就是关系的键值。最后,这些关系模型体现在对具体DBMS的表设计上。逻辑设计的过程可分为下面几个步骤:

1. 表设计

在关系表中,表中的每个数据行都是相关数据值的集合。一个表的每一列都必须具有对于该表唯一的名称。数据类型和长度指定对该列有效的数据类型和最大长度。主键值是唯一的,一个表上只能有一个主键。

2. 表之间的关系

主要包括一对一、一对多和多对多。一对一关系在两个方向都是单值的,例如一个经理管理一个部门,一个部门只有一个经理。一对多关系中,一个职员只能在一个部门工作,对于职员,此关系是单值的;另一方面,一个部门可有许多职员,对于部门,此关系是多值的。两个方向都是多值的关系是多对多关系。一个职员可以处理多个项目,而一个项目可以有多个职员。

3. 数据完整性设计

约束使DBMS能够防止不正确的或意外的数据输入表中,从而确保表中数据的完整性。DB2有下面几种类型的约束。

  • 唯一约束(unique constraint)防止一个值在表中的特定列里出现不止一次。必须将唯一约束中所引用的列定义为非空(NOT NULL)。

  • 引用约束(referential constraint)用来在表之间建立引用关系,这两个表通常称为子表和父表。当在父表或子表中插入/删除/更新的数据满足预定义的条件时,就执行这个操作。

  • 表检查约束(table check constraint)针对一个或多个表列定义检查约束,它们可以对列实施指定的规则,使这些列中插入或更新的数据满足检查约束中预先定义的条件。

  • 信息约束(informational constraint)是SQL编译器的规则,依赖于具体的DBMS厂商的实现,通常不需要设计开发人员干预。

4. 索引设计

针对数据的查询和应用需要,在表的某些列上建立索引或组合索引。

5. 数据组织方案

DB2提供了一种三级数据组织方案,这些组织方案单独使用,也可以同时使用。如下所示:

  • 数据库分区(DPF):数据均匀地分布在数据库分区上,以启用查询内并行性并平衡每个数据库分区上的负载。

  • 表分区(Table Partition):同一个表中的数据可以根据键值的范围放到不同的表分区中。

  • 多维集群(MDC):将多个维上具有相同值的行放到同一个物理块中。

1.3.4 设计物理结构

逻辑数据模型是数据的理想蓝图,物理数据模型才是对数据的物理实现。物理结构设计中需要重点考虑的问题,本书第3章将进行深入探讨。

在数据库系统建设初期,系统架构师需要根据当前的业务情况、对未来发展的规划以及建设经费等情况进行评估。在硬件选型时,不能盲目选择高档硬件,要根据评估结果科学选择,具体硬件选型请参考本书第3章的有关内容。

完成硬件选型后,接下来就是根据DB2的特点和处理的需要,进行存储规划、缓冲池规划和表空间规划等。

1. 存储规划:对于数据库系统来说,存储规划至关重要,首先要设计合理的RAID级别,这样可以实现磁盘阵列的高效读写。随后在磁盘阵列上规划好这几个重要的存储空间,包括实例目录、数据库目录、数据、活动日志、归档日志、备份以及临时数据等。

2. 内存设置和规划:主要完成实例共享内存、数据库共享内存、应用程序全局内存和代理私有内存的设置,除此以外,要规划好缓冲池,它对性能影响最大。当然完全靠DBA来设置内存比较复杂,所以可以结合DB2提供的自调优内存管理(STMM),这样可以大大提高效率。

3. 表空间规划:DB2中有两种类型的表空间,分别是系统管理的表空间(SMS)和数据库管理的表空间(DMS)。它们之间的区别在于,DMS 表空间所需要的空间是在创建时分配的;SMS表空间是根据需要分配的。在创建表空间时,需要指定存放容器,这里容器可以是目录名、设备名或者文件名。为了提高性能和更灵活地配置,单个表空间可跨多个容器。由于DMS表空间性能更高,所以大部分情况下使用DMS表空间是一种最佳选择。

1.3.5 应用开发阶段

设计完成后,就进入开发阶段了,这个阶段DBA会将物理设计的结果部署到目标开发机器上,随后开发人员开始编写应用代码以实现业务逻辑。

很多开发人员都遇到过这样的情况:在应用系统开发初期,由于开发数据库中的数据比较少,对于查询语句和存储过程等的编写体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交上线使用后,随着数据库中数据的增加,SQL语句的优劣很容易就体现出来了。特别是面对海量数据,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,可见对于一个系统不是简单地能实现其功能即可,而是要写出高质量的SQL语句,提高系统的可用性。

所以,在这个阶段,不仅要做好功能测试,还要模拟生产环境的数据量做好集成测试和性能测试,直到所有测试完成后才能正式上线。

1.3.6 运行维护阶段

这个阶段主要是完成数据库和应用的部署工作,随后上线试运行,通过试运行来验证设计和开发是否满足了需求,并做必要的调整。经过试运行后,数据库应用系统即可投入正式运行。在数据库系统运行过程中还需要持续对系统以及工作负载进行性能监控、诊断与优化。

1.4 性能调整与优化

如果数据库系统已经处于维护阶段了,这时遇到了性能问题,使用自上而下方法学肯定是来不及了,只能采用自下而上方法学,使用这种方法学,其目标是找到性能瓶颈并加以解决。

如图1-2所示,生产环境中遇到的性能瓶颈可以归结为下面五种类型:

{%}

图 1-2 性能瓶颈种类

1. 磁盘瓶颈:磁盘I/O是数据库性能的瓶颈。

2. CPU瓶颈:CPU是数据库性能的瓶颈。

3. 内存瓶颈:内存是数据库性能的瓶颈。

4. 网络瓶颈:客户端和数据库服务器之间,或者DB2 pureScale集群各个成员和CF之间,或者数据仓库DPF节点之间存在网络瓶颈,就会导致数据库性能变慢。

5. “懒惰系统”:数据库运行变慢,但从监控来看,磁盘、CPU和内存都很空闲。

1.4.1 磁盘瓶颈

磁盘瓶颈的基本症状是:在vmstat或iostat结果中,出现较长的I/O等待时间,较低的CPU利用率(25%~50%),磁盘较高的繁忙程度(80%)。对于磁盘瓶颈,首先需要找到繁忙设备所在的文件系统路径,接下来,查看在这些文件系统路径上都驻留了哪些数据库对象,例如热表、热索引、临时表、日志。最后,根据这些数据库对象的不同进行针对性的分析。

1. 热表导致的瓶颈

检查繁忙设备所在的文件路径上都有哪些数据表空间,这些表空间上都有哪些热表,找到热表后进一步向下钻取,找出什么造成了这些表的高度活跃。是动态SQL语句造成的高度活跃?还是静态SQL语句导致的高度活跃?一旦确定一个或多个SQL语句导致了I/O 瓶颈,下一步需要确认这个语句是否可以被优化以降低I/O。例如,这个语句是否发起了一个针对大表的表扫描?是否可以通过增加缓冲池大小来消除I/O瓶颈?整个过程如图1-3所示。

{%}

图 1-3 数据表空间性能优化树

2. 热索引导致的瓶颈

如图1-4所示的分析过程,检查繁忙设备所在的文件路径上都有哪些索引表空间,这些索引表空间中都有什么热索引,其中哪些索引是高度活跃的。在OLTP系统中,可以尝试下面的方法,例如增加缓冲池的大小,或为索引指定专门的缓冲池,从而降低I/O来消除瓶颈。但在OLAP环境中,索引通常非常大,分配足够的缓冲池来消除I/O不大可能,这种情况下,通过增加额外的容器以提高磁盘I/O并行度来消除瓶颈或许更加有效。

{%}

图 1-4 索引表空间优化树

3. 用户临时表的频繁读写导致的瓶颈

检查繁忙设备所在的文件路径上都有哪些用户临时表空间,这些用户临时表空间中都有什么频繁访问的临时表。由于排序溢出或者庞大的中间结果,导致对用户临时表的频繁读写进而造成磁盘瓶颈,分析诊断过程如图1-5所示。

{%}

图 1-5 临时表空间容器优化树

4. 日志设备的频繁读写导致的瓶颈

检查繁忙设备所在的文件路径上是否用于存放事务日志。因为日志的读写速度能影响系统中所有的INSERT/UPDATE/DELETE语句,所以日志设备的读写速度对数据库性能有重大影响。建议日志和数据要放在不同的物理盘上,避免出现磁盘争用。关于日志设备瓶颈的分析诊断过程如图1-6所示。

{%}

图 1-6 日志存储问题优化树

注意:db2diag.log的频繁写入也会导致IO瓶颈

db2diag.log的频繁写入也会造成IO瓶颈,特别是在多分区环境中,所有分区通过NFS 文件系统共享诊断日志文件,这对性能的影响更大。解决这个问题最简单的办法是为每个分区指定一个单独的诊断日志路径和一个单独的诊断日志文件。

1.4.2 CPU瓶颈

CPU瓶颈表现在两个方面:用户态CPU瓶颈和系统态CPU瓶颈。运行操作系统内核以外的软件时导致的瓶颈为用户态CPU瓶颈,运行操作系统内核的时候导致的瓶颈为系统态CPU瓶颈。用户态CPU和系统态CPU时间比率在3:1到4:1之间是正常的。如果在有瓶颈的系统中,用户和系统时间比率高于这个区间,就应该分析用户态CPU时间增加的原因。

注意:CPU并不是利用率越低越好

很多读者觉得CPU利用率越低越好,这是不完全正确的,CPU利用率应在一个合理范围内。在OLTP应用中,CPU利用率越低,说明CPU有浪费,如果设计合理,应该能充分利用硬件资源,CPU利用率越高就说明资源得到了最充分的利用,反而是件好事了。

1. 用户态CPU瓶颈

对用户态CPU瓶颈的诊断分析,如图1-7所示,主要从以下四个方面去分析。

{%}

图 1-7 用户态CPU瓶颈优化树

1. 首先通过监控工具找到对应的动态SQL语句和静态SQL语句,随后从以下方面进行优化:

  • 避免对大表进行频繁的表扫描,这样会消耗大量的CPU时间,建议创建合理的索引。

  • 许多应用程序通过字符串拼接SQL语句查询条件,这会消耗CPU时间,建议使用参数标记。

  • 如果应用程序执行一个SQL语句却只会用到一小部分产生的数据,建议使用OPTIMIZE FOR n ROWS(OFnR)和FETCH FIRST n ROWS ONLY(FFnRO)子句,这样可以减少CPU消耗。

2. 如果对表启用压缩,那么会消耗CPU时间。

3. 在一个实用工具正在运行时,CPU使用率会有显著的增加。Load和runstats就是很好的例子,它们经常导致CPU利用率变高。

4. 当删除临时表的时候,DB2必须从缓冲池中移除不再需要的页面。如果此操作经常发生,并且临时表和普通用户数据共享缓冲池,会耗费额外的CPU时钟周期来解决冲突。

2. 系统态CPU瓶颈

在大多数CPU受限的环境中用户CPU往往是决定因素,不过系统CPU有时也能成为决定因素。如图1-8所示,系统CPU瓶颈可能由以下几方面造成:

1. 操作系统上下文切换率过高。当上下文切换得太频繁,会消耗大量CPU时间。导致上下文切换率高的原因可能是有大量的DB2连接。

2. 设备中断也会导致CPU时间高。一次中断的成本并不高,然而如果中断的频率太高,总的负载会非常高。

3. DB2数据库占可用内存的50%左右通常比较合适,如果分配了过多的内存给DB2从而导致操作系统可用内存不足,这样页清除操作将造成系统CPU开销。

{%}

图 1-8 系统态CPU瓶颈优化树

1.4.3 内存瓶颈

如果没有充足的内存,数据在填满缓冲后就将转向I/O,在这个过程中常常也会造成磁盘瓶颈。另外,内存也被用来存储元数据和运算结果,比如SQL查询计划和锁。如果缺少内存,系统不得不取消或销毁那些重要信息,并在稍后需要时重新计算,从而增加了CPU的开销。因此,有时候一个内存瓶颈的表现形式可能是磁盘或CPU问题。

为了分析和诊断内存瓶颈,可用从以下方面入手,如图1-9所示:

{%}

图 1-9 内存瓶颈优化树

1. 实例级和数据库级:主要是共享内存,包括缓冲池、排序堆、锁列表、包缓存等。共享内存受到INSTANCE_MEMORY和DATABASE_MEMORY配置参数的限制,它和数据库连接数没有关系。

2. 连接层面或应用程序级分配:包括应用程序堆和语句堆,内存消耗明显依赖于连接数。

无论是否启用STMM,判断DB2内存实际用量的最好办法就是查看数据库管理器、数据库和应用程序快照的内存使用部分,它们提供了实例、数据库和应用各自的内存配置大小和当前实际内存大小。

1.4.4 网络瓶颈

如图1-10所示,通常网络方面的性能问题主要表现在以下三个方面:

图 1-10 网络瓶颈种类

1. 客户端和服务器之间网络问题。例如业务数据量达到网络所能允许的峰值,或者服务器端返回客户端并不需要的大量数据等。

2. 在DPF多分区环境下,不同节点之间通信的问题。例如,不同节点之间通信数据量过大,导致网络延时,响应时间变慢等。

3. 在DB2 pureScale集群环境中,CF节点和成员节点之间需要交互大量数据,所以推荐使用Infiniband网络或者万兆网,如果网络存在性能瓶颈,就会导致严重的性能问题。

1.4.5 懒惰系统

还有一种“懒惰系统”的性能问题,没有明显的CPU、磁盘、内存或网络瓶颈,系统也不忙。如图1-11所示导致懒惰系统的几种可能性,现在分别叙述一下。

{%}

图 1-11 懒惰系统优化树

1. 在“懒惰系统”中一个最常见的原因是锁等待。锁等待是很容易在快照数据中被检查到的。查询管理视图sysibmadm.snapdb的字段lock_wait_time 和locks_waiting,可以看到锁等待时间和当前正在等待锁的代理进程数。

2. 为了防止请求锁的应用程序无限期等待,DB2提供了LOCKTIMEOUT数据库参数,当锁等待的时间超过这个参数的设定值,就会返回出错消息,并回滚事务,这就是锁超时。当LOCKTIMEOUT的值设置为﹣1(这也是缺省值)时,请求锁的应用程序会无限期等待。

3. 锁升级也是“懒惰系统”中一个常见现象,锁升级导致的块或表级别的锁会产生严重的性能问题。

4. 另外还有死锁也会导致“懒惰系统”。死锁和应用程序有很大关系,从数据库层面监控和诊断,最终结合应用程序来解决。

5. DB2预取器、页清除器数目过小,也是导致懒惰系统的一个重要原因。

6. DB2服务器运行正常,但是由于客户端应用程序编写的质量问题,有时候也会导致懒惰系统。

从上面看出,大部分时候是由于锁问题导致的,关于这块具体内容请参考本书第8章相关章节。

1.4.6 关键SQL语句优化

通过对上述性能瓶颈的深入分析,很多读者会发现应用中包含的一些关键SQL语句往往是性能瓶颈的根源。下面我们来简单探讨一下如何对关键SQL语句进行优化。

注意:优化SQL语句,关键是分析其访问计划

很多读者可能会认为,优化SQL语句的性能,就是如何编写SQL语句,这种理解不全面。因为优化SQL语句,修改语句只是其中一种方式,优化的关键是分析和调整其访问计划。

如图1-12所示,关键SQL语句包括动态语句和静态语句,其优化方法包括手工方式和Design Advisor工具方式。手工方式需要靠DBA自己分析查询计划,自己使用索引或者表分区技术优化SQL语句;Design Advisor工具方式的好处在于方便快捷,它可以自动给出专家建议,包括设计索引、设计表分区、设计MQT或者设计MDC。

{%}

图 1-12 关键SQL语句优化树

关于SQL语句优化的技术,请参阅本书第9章的相关内容。

1.5 性能优化基本准则

让DB2数据库保持良好的性能,这并不是一件容易的事情。根据我们的优化经验,特给出以下基本准则,供读者参考。

1. 20/80原则也适用于性能优化

随着应用和数据库越来越大,全面调整应用变得代价高昂,所以,20/80原则的运用显得尤为重要。这是因为,80%的数据库性能问题是由20%的应用导致的,换句话说,只要优化这20%的关键应用部分,就能解决80%的问题。

2. 对OLTP应用和OLAP应用要区别对待

性能优化目标和应用的种类是紧密相关的,不同的应用种类要采用针对性的技术运用策略。针对OLTP应用,尽最大努力提升系统的响应速度是首要的优化目标,而对OLAP应用,则以整体数据吞吐量为目标。

3. 数据库的设计对数据库性能有至关重要的影响

在逻辑设计过程中,一定要确保用最合理的数据库技术来实现业务逻辑,例如表、索引、表分区、数据库分区、压缩等。在物理设计时,要合理地利用硬件资源,这就要求要有良好的存储设计、缓冲池设计、表空间设计等。

4. 应用开发的质量也会对数据库性能产生重要影响

一定要重视应用开发的质量,要确保应用持有锁的时间尽量短;要对DB2优化器有一定程度的了解,这样可以通过分析SQL语句的访问计划来判断SQL语句性能是否高效;要特别重视索引的运用,因为有时候一个合理的索引能对查询语句有极大的性能提升。

5. 合理配置系统参数和数据库参数

合理配置参数是性能提升的必要条件,所以要务必保证参数得到合理配置。

6. 运用维护窗口做好数据库维护工作

生产库在运维中,都会有维护窗口供使用。所以,在维护窗口期间,要及时执行runstats,以使得优化器有最新的统计信息;也要及时执行reorg,这样当有频繁的插入、删除和更新操作后,数据能得到重新整理从而提高性能。

1.6 小结

本章首先提出了性能问题,随后基于笔者在实际案例中的性能优化实践,提出了性能优化方法学,即自上而下方法学和自下而上方法学。

如果要建设一个新的数据库系统,那么必然会经历设计、开发和维护的各个阶段,按照自上而下方法学,在考虑业务功能实现的同时,性能优化应贯彻始终,越早越好,因为开始的越早,其付出的成本也就越小。

如果数据库系统已经处于维护阶段了,这时遇到了性能问题,那么使用自上而下方法学肯定是来不及了,只能采用自下而上方法学。按照自下而上方法学,本章讲解了5种类型的性能瓶颈,即磁盘瓶颈、CPU瓶颈、内存瓶颈、网络瓶颈和懒惰系统,并给出了相应的监控和分析方法。最后,与大家分享了性能优化的基本准则。

目录

  • 序一
  • 序二
  • 序三
  • 序四
  • 前言
  • 第 1 章 性能优化方法学
  • 第 2 章 实战案例研究与分享
  • 第 3 章 高质量物理设计
  • 第 4 章 经典逻辑设计
  • 第 5 章 高级逻辑设计
  • 第 6 章 系统监控
  • 第 7 章 配置参数与运维工具优化
  • 第 8 章 锁和日志优化
  • 第 9 章 SQL语句优化实战
  • 第 10 章 DB2数据仓库设计与优化
  • 第 11 章 DB2 pureScale集群数据库
  • 后记 信念的奇迹
  • 缩略语
  • 参考文献