一个复杂的数据需求的创新优化(r12笔记第96天))

今天处理了一个蛮有意思的案例,正如我给开发同学所说的那样,方案有很多,但是我们需要明确需求之后,找到一个最合适的需求。

业务同学反馈,数据库中有一个表数据量很大,因为要做一期活动,需要近期的数据,以前的旧数据可以考虑清理。清理多少旧数据呢,差不多是99%的量,数据量有多大呢,差不多两个亿。所以这个需求听起来蛮简单,但是业务同学明确希望能够保持业务的可持续性,这样一来就对实现方案有了一些选择。

这个看起来简单的需求,我的总结如下:

总结下来,要做4件事情:

  1. 优化查询,目前是基于时间范围来查询,经过评估需要给这个表添加索引
  2. 清理数据,表里有两亿数据,但是要清理绝大部分数据。
  3. 保证业务的可持续性,每10分钟会做一次统计分析,数据会实时录入系统
  4. 把表修改为分区表,把旧数据放入一个分区,新数据放入另一个分区,变更之后删除就分区即可。

如此一来,在线重定义这个方案就是我评估之后的首选方案。

但是我发现,尽管我信息满满,但是在实践的时候是困难重重,过程还是略有坎坷,碰到了很多细小的问题,碰到了低版本的bug,能够峰会陆欢,化险为夷,看起来要做得事情还真不少。

在线重定义碰到的坑

在线重定义的过程步骤其实不难,但是比较纠结的是在这个过程中碰到了很多的问题,有些竟然是低版本的bug,这个时候我是深深怀念11g,平时感觉不到明显的好,但是这种问题面前就会感觉11g更加亲切。

除了时间和空间的代价较大之外,碰到了一些bug会让人实在有些无奈。

比如进行到90%左右的时候,估计到了最后的索引rebuild的时候去,抛出了temp空间不足的错误,之前的准备都白费了,重头开始。

在取消在线重定义的过程中,碰到了10g中的bug,导致abort的过程没有响应,系统CPU消耗很高,最后手工清理,杀掉会话解决。

在继续尝试在线重定义的过程中,碰到了10g中的bug,最后发现其中一个原因是由于回收站的影响,清理回收站里的对象继续。

再一次尝试,在临近尾声的时候抛出了ORA-00600的错误,毫无疑问这又是一个10g的bug.

Errors in file /U01/app/oracle/admin/xxx/udump/xxx_ora_25657.trc: ORA-00600: internal error code, arguments: [kcbnew_3], [0], [4], [293213], [], [], [], []

问题到了这个时候,连创建一个要在线重定义的分区表都会抛出上面的错误,经过查询,这个bug的临时解决方案就是杀心bufer cache,听起来容易操作起来难,至少给业务同学解释这个风险点的时候,他们还是有顾虑,所以这个方案有待商榷。

问题总得解决,临时解决方案

问题到了这个时候,我们算是给业务同学报忧不报喜了。在线重定义的方案不太可行,至少目前是碰到了一些问题,要临时解决还是存在风险点。

业务同学觉得我这个需求也不过分啊。能不能想想办法,或者这么来做,我就需要最近几天的新数据,做完活动之后就不用了,对数据权限没有要求,只需要查,压根不修改和删除。

于是有了这么一个设想,我们创建一个物化视图,然后增量刷新,commit后自动同步,这样一来就是一个影子表的感觉,在新的表上我们可以创建索引,这样查询的效率也可以提高。如下图所示。

这样一个方案好不好做呢,其实如果细究起来还是有一些难度,我们需要创建一个prebuilt的物化视图,然后选择性同步里面的部分数据。

而另一方面业务同学如果要查询之前的那个大表,性能又很差,所以两者综合起来有什么改进方案呢,其中一个方案就是创建物化视图,全量刷新后,增量刷新,这样一来这个物化视图表就是源表的一个影子表,查询完全可以在这个表上来进行。如此一来业务放也就不着急去申请维护窗口去添加索引了。问题这样可以过渡一段时间。

所以方案就成了这样了。

创建物化视图的过程当然也不是一帆风顺,花了些功夫,碰到了一些小问题,总算是给了业务同学一个基本满意的交代,原本的查询需要1分多钟,现在不到1秒钟就可以搞定,性能差异非常大。

高手过招,就是不断优化

当然这样一个解决方案,虽然能够交代了,其实我心里还是很遗憾的,因为最大的问题没有解决,那就是旧数据还是在那里。虽然查询效率提高了,但是从问题的本质来说,还是没有解决,只是缓解了。

这个时候有一个好消息是,因为这个表的数据量比较大,已经刷新了buffer cache,所以就不需要我们手工来刷了。一个检查点就是创建分区表没有问题了,只是一个好消息。

这个时候能不能考虑对源表进行在线重定义呢,显然不行,因为源表已经有了物化视图日志,在线重定义的基本条件就不满足了。

为此我就想了下面的方案。可以实现清理数据的这个需求,那就是偷天换日。

首先从物化视图中根据时间条件(有索引,所以性能高)把要保留的数据查出来,放入分区表SERVERLOG_PAR_OLD

我们使用exchange partition,把 SERVERLOG_PAR_OLD里的分区数据和SERVERLOG做交换,这样2个亿的数据就和分区的数据做了交换,然后可以把近期的增量数据通过物化视图的形式插入临时表serverlog_hot里面,最后把数据补入serverlog,这样就是一个完整的数据流了,而后期添加索引的操作这个时候影响面就很小了,可以使用在线重定义来完成,或者直接添加也可以(因为数据量小很多,速度很快)

整个过程也算是有惊无险,还是充满挑战的。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-06-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏一名叫大蕉的程序员

一场关于逻辑应该写在哪里的争论No.93

先说结论,我支持将逻辑写在 Java 等应用系统中。 观察了一下,传统企业以及绝大部分转型中的企业的 Java 应用中,很神奇的是,他们的开发人员包括我自己以...

5338
来自专栏PHP在线

MYSQL数据库设计的一些小技巧[整理] 有感

选表类型: mysql的myisam表适合读操作大,写操作少;表级锁表 innodb表正好相反;行级锁表 互联网服务,不算支付性的服务外,互动产品,新闻系统等等...

3014
来自专栏大魏分享(微信公众号:david-share)

爆款:K8S原生应用管理平台

首先,需要说明的是:Openshift是K8S集群,但K8S集群不是Openshift集群。K8S集群是Openshift集群的真子集。

3263
来自专栏数据和云

【MySQL 5.7.17】从主从复制到Group Replication

时值双十二之际,MySQL官方献上了大礼,Group Replication(后文简称GR)终于正式宣布GA,组合在MySQL 5.7.17版本内部发布出来。 ...

3598
来自专栏架构师之路

互联网公司为啥不使用mysql分区表?(一分钟系列)

缘起:有个朋友问我分区表在58的应用,我回答不出来,在我印象中,百度、58都没有听说有分区表相关的应用,业内进行一些技术交流的时候也更多的是自己分库分表,而不是...

55711
来自专栏CSDN技术头条

漫谈千亿级数据优化实践:一次数据优化实录

即使没有数据倾斜,千亿级的数据查询对于系统也是一种巨大负担,对于数据开发来说,如何来优化它,既是挑战,也是机遇!

45310
来自专栏逸鹏说道

我是如何在SQLServer中处理每天四亿三千万记录的

首先声明,我只是个程序员,不是专业的DBA,以下这篇文章是从一个问题的解决过程去写的,而不是一开始就给大家一个正确的结果,如果文中有不对的地方,请各位数据库大牛...

38713
来自专栏CSDN技术头条

那些高级运维工程师,都是怎么给公司省机器的?

随着项目用户量的快速增长,前期可能由于应用程序设计、数据库设计及架构不当,大多项目会在用户量百万、日志/流水等表过千万、乃至过亿时,出现写入卡顿、查询缓慢、各种...

1172
来自专栏即时通讯技术

优秀后端架构师必会知识:史上最全MySQL大表优化方案总结

本文原作者“ manong”,原创发表于segmentfault,原文链接:segmentfault.com/a/1190000006158186

1363
来自专栏数据和云

罗海雄:仅仅使用AWR做报告? 性能优化还未入门(含PPT)

编辑手记:祝贺罗海雄老师加入Oracle ACE社区,他是数据库SQL开发和性能优化专家,也是ITPUB论坛的资深版主,我们整理了罗老师一篇AWR裸数据分析的文...

1092

扫码关注云+社区