nxdy
作者nxdy2018-05-31 10:02
其它, nj

Db2数据库SQL语句的性能调优

字数 3218阅读 4018评论 1赞 3

数据库优化是DB2数据库运维工作中较为重要的部分,也是比较难的部分。数据库优化工作的内容很多,包括存储、操作系统、数据库系统、应用程序等等多个层面。

在日常运维的过程中,我们最常遇到的问题是应用程序层面的问题,其中包括了SQL语句的性能问题。

常见的SQL语句性能问题表现为运行时间超长,消耗大量CPU和内存,占用大量临时表空间,造成大量锁等待、超时、死锁、锁升级等等。

应用维护和开发人员往往会首先要求系统管理员和数据库管理员排查操作系统、存储、数据库参数和网络等方面的问题。

其实,在多数情况下,问题出在应用程序、SQL语句、表的索引设置等方面,通过调整系统配置和数据库参数并不能解决根本问题。

例如,江苏农信某系统经常发生锁升级和锁等待事件,应用开发人员多次要求数据库管理员调高LOCKLIST和MAXLOCK这两个数据库参数,但通过多次调高,都解决不了问题。抓取动态SQL语句,我们发现,提交该SQL请求的应用程序设置的隔离级别是RS,导致在运行的时候获取了大量的锁造成了锁升级,调整隔离级别后,便再也没有发生类似问题。

本篇文章梳理总结了“Db2数据库SQL语句的性能调优在线答疑”活动的问答内容,让大家能够更直观了解这方面的常见问题及解决方法。

一、专家能整理下DB2数据库常用的、需人工配置的参数给大家分享下吗?

DB2的参数比较多,分DB2SET参数、DB2 DBM CFG参数以及DB2 DB CFG参数等几个层面。建库时,一般需要考虑的参数有各种上限类的参数或者指定某个内存池大小的参数(MAX开头的参数),比如MAXAPPLS,需要根据应用需要去设置,在系统资源允许的情况下,我们这边多数设置为AUTOMATIC。还有锁相关的参数(比如超时时间LOCKTIMEOUT、死锁探测周期DLCHKTIME等)需要结合应用程序的特性来设置。在日常运维中,要基于发生的问题来调整参数。在发生问题时,需要进行深入分析,按照分析结果去修改参数。例如某数据库曾经发生过交易取sequence慢的问题,经过一系列分析发现跟page latch有关,最终调整了NUM_IOCLEANERS和NUM_IOSERVERS这两个参数,使问题得以优化。如果不分析具体问题,很难去凭空评判哪个参数应该怎样去调整。

二、除了DB2ADVIS,还有什么办法来根据SQL语句建立索引,通过系统视图syscat.columns, syscat.coldist如何来查看字段分布和建立索引?为什么有些直接把column放在index里,有些放在include的条件里?

创建索引有些基本的原则,大体如下:
1、在适当地方定义主键和唯一索引。
2、创建关于查询用来连接表(Join)的任何列的索引。
3、创建关于基于常规基础从中搜索特定值的任何列的索引。
4、创建关于通常用在 ORDER BY 子句中的列的索引。
5、确保使用了仅检索您需要的数据的谓词。
6、当创建多列索引时,索引的第一列应该为查询中的谓词最常用的那一列。
7、确保索引造成的磁盘和更新维护的开销不会太高

在对表做runstats的时候 需要加上with distribution 选项,这样数据库就记录统计分布信息。
查看字段分布的语句如下:
select colname,colcard from syscat.columns where tabname='表名'
select colvalue,valcount,distcount from syscat.coldist where tabname='表名' and colname='字段名' and type='F'

创建索引是指定include的作用在于将非索引键存储在非聚集索引的叶级索引页面上,这样作的好处是不增加索引大小(level)的情况下,增大索引的覆盖范围。另外如果列过长,索引的效率也 是极低的,include能用到的场景不多。因为include列只能放在唯一索引的后面,所以,在查询语句当中选择的字段,除了出现在唯一索引中的以外,还有那么几个字段,就可以放在include里面 了,实现纯索引的扫描可以看出,这个要求是非常高的,所以只建议对查询量很大的语句进行这种优化。

三、如何抓取静态语句?

通过监控工具或快照找出相应的package name
然后
db2expln -d 数据库名 -c package模式名 -p package名 -g -i -o 输出文件名
在输出文件中 可以搜索section number 找到相应的sql语句和执行计划

四、在并发系统里,如何避免死锁?

对于死锁,问题的源头在于合理的应用程序设计,可以通过event monitor for locking或者查看db2diag.log找到发生死锁的应用,然后修改相应的code。修改程序逻辑,尽量避免ABC和BAC的情况。优化程序、SQL语句、索引等,减小单个事务拿锁的数量和时间。

五、reorg之后,为什么需要rebind?如何rebind?

reorg,runstats之后catalog table中存储的统计信息发生变更,对于dynamic SQL执行计划都是动态生成的,只用static SQL才需要rebind。因为static SQL的执行计划是在rebind时生成。只有静态语句需要rebind,也就是预先绑定的程序包。因为数据库对象发生了变化,db2优化器可能产生更优的查询计划,只有rebind才能用上最新的信息。
先找到涉及做过runstats的表的package名称,比如某个存储过程AAA的package这样找:
select lib_id from syscat.routines where routinename='AAA'
得出lib_id
select pkgschema,pkgname from syscat.packages where pkgname like '%刚才的lib_id值'
就能找出package模式名和名称
然后 db2 rebind package 模式名.名称

六、执行runstats收集统计信息操作会避开业务繁忙时段,但在无法避开表操作的时候,runstats有什么影响?

在db2中做查询的时候,以什么样的方式访问数据是由优化器来决定的。一般来说,优化器会采用最优的方式,选择最有效率的方式。选择的依据就是统计信息。所以统计信息不准了,优化器的决策自然也就有问题了。可能造成查询的缓慢,影响性能和效率。
runstats就是用来收集统计信息的。runstats可以基于频率或百分比进行数据分布采集。一般采取默认值即可。
一般还是建议在空闲时执行runstats。但如果有特殊情况,如窗口避不开,且表特别大,可以考虑抽样统计。或者使用允许写访问的选项在线收集,但在线收集表上会加in锁,如果期间表更改过多,也有可能导致收集的统计信息有偏差。

七、把DB2的监视开关都打开的话,会不会很消耗系统资源?

基于快照的那些开关对性能影响不大。在服务器资源没有瓶颈的情况下最好打开,在日常运维中,监视开关提供的快照数据很有用。

八、关于如何提高索引缓冲池命中率的问题?

索引缓冲池命中率低是不是只是从缓冲池这个维度去查看的结果?如果是这样的话不太准确,因为我们最终想看的是哪个表的哪个索引命中率低,这样才知道是不是有问题。这个可以从表空间的缓冲池命中率和相关sql的缓冲池命中率去确定。如果最终确定了是表的索引命中率低,有两个方向去调整。一个是增加buffer的大小。这种在buffer原先设置过小的情况下是很有效的。第二个是查还有谁占用了缓冲池,例如全表扫的大表,临时表等,把这些占buffer的东西都弄到其他表空间去占用单独的bufferpool。这样就能减少影响。

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

3

添加新评论1 条评论

#vpsuperman数据库管理员, vp
2019-08-19 15:19
很有用的经验
Ctrl+Enter 发表

本文隶属于专栏

活动总结
活动总结是社区交流活动内容的总结及延伸,为大家提供了社区专家们丰富且高水平的理论知识、实践经验以及常见问题的最佳解决方法,非常值得大家收藏学习。

关于TWT  使用指南  社区专家合作  厂商入驻社区  企业招聘  投诉建议  版权与免责声明  联系我们
© 2019  talkwithtrend — talk with trend,talk with technologist 京ICP备09031017号-30