zdcdcz
作者zdcdcz2021-04-15 13:52
数据库架构师, 某互联网行业

数字化转型之基于混合云的数据库性能优化思考与实践

字数 6758阅读 2625评论 1赞 0

1.背景

“加快数字化发展,协同推进数字产业化和产业数字化转型,建设数字中国。”这是李克强总理在今年政府工作报告中提到的内容。数字化转型在今年两会期间被多次提到, 可见 2021年将是重点围绕数字化转型发展的一年 。

为了给数字化转型注入新动能, 以 云计算 、大数据、人工智能、工业互联网为代表的一系列现代信息技术正在快速更迭,数字化浪潮席卷而来。 云计算 作为数字化转型的重要 基础设施底座 , 其重要性不言而喻,而作为基础设施之上的数据库在加快数字化 转型的发展 中起到重要的作用 。

2.业务需求分析

云服务一体化平台需要面向全省提供综合性服务,云平台可以根据需要灵活扩展,需要多少扩容多少,理论上不存在系统瓶颈,同时服务能力可集中管理。

云平台基于服务端,基于对强交互,数据安全,数据备份等的综合考虑,对于核心关系数据库采用Oracle数据库,Oracle可以支持复杂的数据模型,而且带有强大的数据查询功能。另外,从商务和性能角度考虑,Oracle数据库创建在本地物理机上。云主机和本地物理机通过专线连接 实现 互联互通。

在以上混合云场景下,数据库的性能存在挑战:

  1. 运维管理复杂、易出错,而且稳定性得不到保障。
  2. 使用云计算的扩展能力在应对业务高峰时是优势,但对数据库的压力也相应增大,同时需要解决多个环境间的数据一致性问题。

3.数据库体系结构选型

基于混合云平台的业务系统 中有非常多的用户,第一年大概几千用户,在两年后有可能 激增 到几万用户,对于这样的需求,通常有两个办法:

1) 一次性购买当时能购买的性能最好的机器。
2) 先购买能满足当前性能的机器,在需要的时候进行扩展。

显然从经济收益比上来说,第2种方法更优,而集群是第2种方法的具体实现。

基于高可用性和在需要的时候能够横向扩展这样的需求的考虑,集群模式能满足。同时,集群的share disk和缓存融合技术也能完美解决多个环境下数据的一致性问题。

但应注意到,与传统的认知相反,集群不一定能带来更高的性能,在极端的条件下,有可能反而使得数据库运行的更慢。比如,如果应用程序没有绑定变量导致过多的库高速缓存争用的情况,在集群RAC的环境下反而会加剧这种情况。

因此,应用系统的设计仍然需要在充分理解数据库 原理 的基础上做好优化。

4.数据库性能优化的目标

数据库性能优化的目标有两种:

一种是客观的数据库性能的数据值,另一种是主观的用户使用体验。对于性能优化而言,最终的目标是用户的体验,而不是冷冰冰的数据。从追求结果的角度,用户的使用体验也是最终的试金石,不要 追求 过度优化。实际上,优化是牵一发而动全身的,过度优化不但浪费时间精力,还有可能造成系统的不稳定。

对于用户来说,性能最直观的指标就是业务系统响应时间,Oracle也引入了时间模型的概念,体现在数据库层面就是事务平均响应时间。从这个角度来讲,我们优化的目标就是减少业务系统响应时间。

5.数据库性能优化工具包

数据库性能分析需要用到一些基本的工具包,用以调试和优化。

优化器是Oracle数据库的核心组件,优化器的目的是按照一定的规则来得到目标SQL在当前环境下的最佳执行路径,也就是说优化器的目的是为了得到目标SQL的最佳执行计划。

那什么是执行计划?为了执行SQL语句,Oracle在内部必须执行很多步骤,并且按照一定的顺序执行这些步骤,最后将执行结果返回给用户,这些执行步骤的组合就是执行计划。

优化器目前采用基于成本的优化即CBO,那么什么是成本?成本包含IO和cpu,对于数据库来说,瓶颈主要在于IO。这是因为,数据库一般把数据文件存储在磁盘上,我们需要检索某些记录而这些记录在内存中搜索不到时,就需要从磁盘加载,这个加载过程的成本就是IO成本。

查看执行计划是分析数据库SQL性能的重要步骤,那么如何查看SQL的执行计划呢? 有以下几种方式:

Explain plan命令:

Explain plan是优化器通过读取数据字典的统计信息做出'最佳'访问路径判断,并没有真正去执行语句,所以Explain plan查看的执行计划并不是真实的执行计划,因为此时目标SQL并没有真实执行, 由此 产生的执行计划有可能不准,因为:

1)当前的环境可能和执行计划生成时的环境不同;
2)不会考虑绑定变量的数据类型;
3)不进行变量窥视。

AUTO TRACE :

AUTO TRACE则是 真实 执行了SQL语句,同时 会 把访问记录数、执行计划、统计信息等打印出来。set autotrace是sqlplus一个功能,需要在通过sqlplus连接的session中使用,它非常适合在开发时测试SQL语句的性能,它的优点在于简单明了。

10046事件 :

SQL_TRACE可以在实例级别启用,也可以在会话级别启用,在实例级别启用SQL_TRACE会导致所有进程的活动被跟踪,包括后台进程及所有用户进程,这通常会导致比较严重的性能问题,所以在一般情况下,我们使用sql_trace跟踪当前进程,方法如下:

SQL>alter session set sql_trace=true; ...SQL语句... SQL>alter session set sql_trace=false;

10046事件是SQL_TRACE的一个升级版,它也是追踪会话,生成Trace文件,10046生成的跟踪文件信息更加丰富,这些信息包括了Parse/Fetch/Execute三个阶段中遇到的等待事件、消耗的物理和逻辑读、CPU时间 ,同时也能看到执行计划 。

10046事件的设置方法如下:

session 级别:
alter session set events ’10046 trace name context forever,level X’;

system 级别 :
alter system set events ’10046 trace name context forever,level X’;

生成trace文件后,可以用tkprof 工具将sql trace 生成的跟踪文件转换成易读的格式,语法如下: tkprof inputfile outputfile

以下是一个格式化后的10046的trace文件的示例:

10053事件 :

10053 事件可以作为我们解析优化器为什么选择某个执行计划辅助工具,但 10053 并不告诉我们这个执行计划到底运行地 效果如何 。

而10046 并不告诉你 优化器 为什么这么选择执行计划 ,但它 告诉你 在SQL解析parse阶段所遇到的等待事件和所消耗的CPU等资源,以及Execute执行和Fetch阶段的各项指标。

总之,区别就在于10046 告诉我们 SQL 运行地如何,10053告诉我们优化器为什么这个SQL选择某个执行计划。

所以, 两者在做SQL性能优化的时候,需要配合使用。

10053事件的设置办法:

启用10053事件:
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';

关闭10053事件:
ALTER SESSION SET EVENTS '10053 trace name context off';

6.性能是设计出来的而不是调整出来的

我们经常 在系统上线后, 遇到开发人员来抱怨:“系统页面出来很慢,有没有什么好的办法 ? ”在系统设计初期,我们就应该有针对性的去处理存储数据的方式,而不是等到系统出了问题才去调整。以下通过一个实际当中遇到的例子来说明 :

我们从用户那边获得反馈,访问某个页面的时候特别慢,要好几分钟后才能出来,而这个页面正是调用了后台数据库,数据能显示但是慢,这很显然是一个性能的问题。

首先 我们 检查数据库所在操作系统资源,发现CPU,内存资源都很充足,IO也很正常。

通过获取缓慢时间段的AWR报告,发现占据大部分等待时间的top事件是read by other session。查询资料得知这个等待事件是指两个或者多个会话同时需要把硬盘中的对象装载到data buffer中,当其中一个会话把对象装入后,其他会话就处于read by other session等待状态 , 这是oracle 10g 从oracle 9i的buffer busy waits中分离出来的等待事件。 简单的说,这个等待事件产生的原因是系统比较繁忙,并发的请求过多的扫描热块引起的等待。

通过脚本抓取到read by other session关联的SQL,sql_id是 suisxcvyhsn0ssd56qd. 。

通过以下语句查看运行时间最长的SQL语句:

SQL> select *
2 from (select v.sql_id,
3 v.child_number,
4 v.sql_text,
5 v.elapsed_time,
6 v.cpu_time,
7 v.disk_reads,
8 rank() over(order by v.elapsed_time desc) elapsed_rank
9 from v$sql v) a
10 where elapsed_rank <= 10;

发现运行时间最长的SQL也是指向 这个 sql :

SQL> select count(*) from r
2 where id like '12541,13467%'
3 and createdate >= to_date('XXXX-XX-XX','yyyy-MM-dd')
4 and createdate < to_date('XXXX-XX-XX','yyyy-MM-dd');

通过AUTO TRACE 查看该语句的执行计划:

SQL> set autotrace trace exp stat
Execution Plan
----------------------------------------------------------
Plan hash value: 383627788
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 39998 (2)| 00:08:00 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| R  | 16636 | 406K| 39998 (2)| 00:08:00 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CREATEDATE">=TO_DATE('xxxx-xx-xx 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "ID" LIKE '12541,13467%' AND
"CREATEDATE" ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

Session altered.
SQL> explain plan for select count(*) from r_browser
2 where areaid like '12617,13479%'
3 and createdate >= to_date('xxxx-xx-xx','yyyy-MM-dd')
4 and createdate < to_date('xxxx-xx-xx','yyyy-MM-dd');
Explained.
SQL> alter session set events '10053 trace name context off';
Session altered.

我们分析一下 生成的 trace文件:

可以看到CBO在基于成本的分析下,认为全表扫描的cost要小于走索引的cost,所以选择了全表扫描,但是对于一个500多万行的数据表来说,这显然也不是一个效率高的办法。

在讲解决办法的时候, 这里 先 补充一下关于索引的知识:

索引并不总是有益的,除非以下几种情况:

  1. 当我们只需访问表中很少一部分行的时候;
  2. 当查询的结果都可以在索引获取到,不需要从硬盘读取,这可以避免回表带来的额外的物理IO;

我们查看一下研发创建的这个索引为什么cost这么高?

选择率和聚簇因子是Oracle 统计信息中在CBO优化器模式下用于计算cost的两个重要参数,它决定了当前的SQL语句是否走索引,还是全表扫描。

为了评估选择率以便优化器计算出最优执行计划,CBO会使用各种形式的统计信息,配置参数等.以表中列的角度来说,CBO会收集以下统计信息: 列中不同值的数量也就是NDV 列中的最小值/最大值 列中null值的数量 数据分布或直方图信息

而聚簇因子则用于描述索引块上与表块上存储数据在顺序上的相似程度,也就说表上的数据行的存储顺序与索引列上顺序是否一致。

在这个例子中, 单独走ID或者createdate索引都没有一个很好的选择率和聚簇因子,走单独索引的成本远高于全表扫描 ,从这个意义上来说,优化器的选择并没有错。就如我们前面说的,优化的目标是减少业务系统响应时间,那还有什么办法能减少SQL运行时间呢?

我们注意到,select count(*)语句实际上并不需要 返回 数据行的具体内容, 它 只是一个计数的需求。从这里我们看到了一缕曙光,于是,我们在ID列上创建 一个 索引。

再运行一遍sql,果然,运行速度提升了上百倍。

再 设置 一次10053事件,我们看一下到底发生了什么。

可以看到,单独使用一个索引走的rangescan,cost都高达191870或者204711。

而创建了两个索引 后,索引 进行indexjoin,cost立马降低到只有3000多,效果是立竿见影的。这样的执行计划不需要回表 取数据 ,直接在索引里就可以完成 计数 ,自然是cost最低的,于是优化器选择了这个执行计划,执行成本最低,执行时间也是最短的。 我们前面说过,两种情况下走索引是有益的,这里就属于第二种,返回的结果都在索引里完成。

7.经验总结

1.在项目中遇到数据库问题,最常见的原因之一就是对数据库了解不足,开发团队缺乏对所用数据库的基本了解。开发人员把主要精力放在业务开发上,对数据库原理不清楚,因此容易出现不可避免的性能问题。

2.目前对于应用系统的故障定位缺乏有效的工具,但是数据库本身却 有很多可量化的工具 ,本文介绍的性能工具软件能够帮助定位问题所在,而且这种测量是可打开,可关闭的,应用系统不具备这种能力。

3.从上面的例子来看,开发人员由于对于数据库存储数据的原理不了解,把索引创建在了不合适的列上,从而让CBO选择了全表扫描的方式,造成了业务系统上线后的性能问题。在系统上线前可能不明显,但在系统上线后,特别是云计算易于扩展的 环境下 方式下,逐渐增多的用户 并发 量会 遇到 在测试期间所没有遇到的性能问题。因此,在应用设计初期就应该考虑到数据库级别的性能。

4.经验表明,80-90%的性能调优都是SQL语句,而目前大部分的SQL都是由应用开发编写的。不在设计阶段对应用程序进行 优化 ,而是在系统上线后去调整数据库,是得不偿失的。

5.数据库是应用的基础,应该尽量发挥数据库本身的性能,而不是自己在业务逻辑层去实现。

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

0

添加新评论1 条评论

zhuqibszhuqibs软件开发工程师, Adidas
2021-06-07 14:44
我说话比较尖锐,请不要介意,这文章就是挂羊头卖狗肉,混合云压根就是浮光掠影,而实质就是oracle调优文章截取下来的一段,而又限于篇幅没有说清楚
Ctrl+Enter 发表

相关文章

相关问题

相关资料

X社区推广