DB2调优

–操作系统级别也需要调整一些内核参数

–cost一般小于2000

–获取sql的执行时间
–编写脚本,如下:

[db2inst1@localhost ~]$ cat timesql.sql
values current timestamp@
select * from act@ –这里写sql语句
values current timestamp@
–执行:
[db2inst1@localhost ~]$ db2 -td@ -vf timesql.sql
–or
[db2inst1@localhost ~]$ time db2 “select * from syscat.tables” > temp

——————-
–db2expln
——————-
具体的查看infocenter:数据库基础-》性能调整-》影响性能的因素-》查询优化-》说明工具-》SQL 和 XQuery 说明工具-》db2expln 输出的描述
[db2inst1@localhost ~]$ db2expln -d sample -f timesql.sql -t -i -z @ -g > timesql.exp –用db2expln查看访问计划
[db2inst1@localhost ~]$ db2expln -d sample -statement “select * from l2 where id=4” -t -i -z @ -g > ooo

——————-
–db2exfmt
——————-
[db2inst1@localhost ~]$ db2 -tvf ~/sqllib/misc/EXPLAIN.DDL –生成用于存放数据的表
[db2inst1@localhost ~]$ db2 set current explain mode explain –打开explain模式。这之后所有的语句都只会的给个执行过程,不会实际执行
[db2inst1@localhost ~]$ db2 set current explain snapshot explain
[db2inst1@localhost ~]$ db2 “select * from act,emp order by actkwd desc” –执行sql语句
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
[db2inst1@localhost ~]$ db2 select \* from act
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
[db2inst1@localhost ~]$ db2 set current explain mode no –关闭explain模式
[db2inst1@localhost ~]$ db2 set current explain snapshot no
[db2inst1@localhost ~]$ db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -o exfmt_output.out –用db2exfmt查看访问计划,运行的目录需要有写的权限

Access Plan:
———–
Total Cost: 13.8193
Query Degree: 1

Rows ———》》》》这几行列出了下面数据的说明
RETURN
( 1)
Cost
I/O
|
756
NLJOIN
( 2)
13.8193
2
/—+—\
18 42
TBSCAN TBSCAN
( 3) ( 6)
6.81829 6.82266
1 1
| |
18 42
SORT TABLE: DB2INST1
( 4) EMPLOYEE
6.8181 Q1
1
|
18
TBSCAN
( 5)
6.81681
1
|
18
TABLE: DB2INST1
ACT
Q2

–join的几个符号
Operator Symbols :
——————

Symbol Description
——— ——————————————
>JOIN : Left outer join
JOIN< : Right outer join >JOIN< : Full outer join xJOIN : Left antijoin JOINx : Right antijoin ^JOIN : Left early out JOIN^ : Right early out early out 一般在exists的时候会的出现。具体的可以参考(说白了就是exists转成join了以后,join的两张表只要有一行符合条件就能结束join了,而不用像一般的join那样得所有的行都扫一遍): Early out indicator. LEFT indicates that each row from the outer table only needs to be joined with at most one row from the inner table. LEFT (REMOVE INNER DUPLICATES) indicates that an attempt to remove some duplicate rows from the inner table has taken place. RIGHT indicates that each row from the inner table only needs to be joined with at most one row from the outer table. NONE indicates no early out processing. GROUPBY indicates that early out processing is allowed because of a group by operation. --从CACHE中找Access Plan SELECT SECTION_TYPE, CASE WHEN SUM(NUM_COORD_EXEC_WITH_METRICS) > 0 THEN
SUM(TOTAL_CPU_TIME)/SUM(NUM_COORD_EXEC_WITH_METRICS)
ELSE
0
END as AVG_CPU_TIME,
EXECUTABLE_ID,
VARCHAR(STMT_TEXT, 200) AS TEXT
FROM TABLE(MON_GET_PKG_CACHE_STMT ( ‘D’, NULL, NULL, -2)) as T
WHERE T.NUM_EXEC_WITH_METRICS <> 0 AND STMT_TYPE_ID LIKE ‘DML%’
GROUP BY EXECUTABLE_ID, VARCHAR(STMT_TEXT, 200)
ORDER BY AVG_CPU_TIME DESC
得到EXECUTABLE_ID,然后执行:
CALL EXPLAIN_FROM_SECTION (x’01000000000000005F0000000000000000000000020020101108135629359000′ ,’M’, NULL, 0, NULL, ?, ?, ?, ?, ? )
再:
db2exfmt -d gsdb -e db2docs -w 2010-11-08-13.57.52.984001 -n SQLC2H21 -s NULLID -t -#0

–基本概念
IOPS:每秒进行多少次IO。计算方法:IOPS = 1000 ms/ (Tseek + Troatation)
寻道时间Tseek是指将读写磁头移动至正确的磁道上所需要的时间。寻道时间越短,I/O操作越快,目前磁盘的平均寻道时间一般在3-15ms。
旋转延迟Trotation是指盘片旋转将请求数据所在扇区移至读写磁头下方所需要的时间。旋转延迟取决于磁盘转速,通常使用磁盘旋转一周所需时间的1/2表示。比如,7200 rpm的磁盘平均旋转延迟大约为60*1000/7200/2 = 4.17ms,而转速为15000 rpm的磁盘其平均旋转延迟约为2ms。
数据传输时间Ttransfer是指完成传输所请求的数据所需要的时间,它取决于数据传输率,其值等于数据大小除以数据传输率。目前IDE/ATA能达到133MB/s,SATA II可达到300MB/s的接口数据传输率,数据传输时间通常远小于前两部分时间。

直接IO(DIO):不走文件系统的缓存,直接读
并发IO(CIO):在CIO的基础上,不使用索引节点锁

同步IO:请求发出后,应用程序阻塞,直到请求有回复
异步IO:发出请求后,不等待,继续处理。比如排序,如果数据很多,那么异步IO可以将数据分成很多份,先读取一份,然后对这一份进行处理,处理的同时,异步IO会的读取其它的部分,这样IO就和CPU处理同时发生

条带深度:一个条带单元的大小
条带宽度:一个条带集中的驱动数

–参数调优
1.MAXAGENTS(9.5后不再使用):一般是NUMDB x MAXAPPLS。可以通过:
[db2inst1@localhost ~]$ db2 get snapshot for dbm | grep “Agents stolen from another application”
看看是不是0,不是0的话需要调大。

2.NUM_POOLAGENTS:用于控制代理池中的空闲代理的数量。不过如果存在连接集中器,那么可能空闲的数量会的大于这个参数。
NUM_INITAGENTS:实例启动的时候生成的代理数量。
MAX_CORRDAGENTS:决定了实例中同一时刻最大的协调代理的数目(如果是多分区,那么指的是一个节点上的最大协调代理数目)
MAX_CONNECTIONS:决定了允许连接到一个实例的最大的连接数(在多分区环境指的是一个节点上的最大的连接数)

3.NUM_IOSERVERS:建议automatic

4.NUM_IOCLEANERS:建议automatic,对于频繁更新的事务,或者想缩短恢复时间的数据库,可以加大点。

5.CHNGPGS_THRESH:缓冲池中被更改的页所占的比例达到这个值的时候,启动异步页面清楚程序。对于较多Insert或Update操作的库,一般在20~40,否则一般60就行了。

6.MAXAPPLS:指定允许连接到数据库的并发应用程序的最大值。

7.LOCKLIST:库中所有锁的数据结构的容量
MAXLOCKS:每个应用程序允许使用的所的最大百分比
LOCKTIMEOUT:指定应用程序为获取某个锁等待的最长秒数,如果获取失败,返回911错误。

估计的时候按照每个锁32B~64B,每个应用平均512把锁来估计:
512*32*MAXAPPLS/4096 ~ 512*64*MAXAPPLS/4096

8.注册变量DB2_PARALLEL_IO

–======================================
–======================================
–======================================
–======================================
–======================================

–基本要求
1.每个CPU6~10个磁盘。每个表空间均匀的分布在这些磁盘上。(或者如果做了RAID可能可以改一下)
2.OLTP的系统bufferpool用75%的内存,OLAP用50%的内存。
3.定期在所有表上做runstats(包括系统编目表)
4.学会使用Advisor
5.日志应该写在额外的磁盘上。
6.SQL要及时COMMIT,这样可以增加并行性。
7.SORTHEAP要适当的增加。

–建立数据库
一般可以把TEMPSPACE1和USERSPACE删了,然后按照需求重新建立在更加快速的容器上。SYSCATSPACE一般不需要调优。

–缓冲池
IBMDEFAULTBP一般是给系统编目用的。
由于DB2对Bufferpool的自调优很不错,所以一般对一个页大小的Bufferpool只要建立一个就可以了。

Bufferpool的一个重要思想就是让经常被访问的row长期的放在内存中。所以尽量让那些含有经常被随机访问的表的表空间和那些含有长期被访问相同行的表空间使用不同的bufferpool。

对于一些效率要求比价高的系统,一般可以有四个Bufferpool(当然,还得考虑页大小):
1.一个用于临时表空间的中等大小的Bufferpool。
2.一个用于索引的大Bufferpool。(所有的索引都扔在这里好了,反正索引这玩意都是随机的)
3.一个用于包含含有经常被访问的表的表空间的大Bufferpool。(这个大点好了,效率肯定提升的多)
4.一个用于包含含有很少被访问的、随机的或顺序访问的表的表空间的小的Bufferpool。(这个大了也没用)

–表空间
表空间的大小选取很有讲究。比如对于OLTP系统,缓冲池很重要,那么较小的页就能让缓冲池中放入更多的随机数据。不过一般么都是8K啦。对于OLAP,一般越大约好,这样可以减少数据库的IO次数。

一般一页上只能用255行,所以一行的大小如果小于:页大小/255,那么会有浪费。这个时候可以考虑用小点的数据页,但是呢如果你的表又很大,那么就不得不用大点的数据页了。

一个表空间需要有一个对应大小的临时表空间,这个临时表空间在诸如排序、reorg的时候会很有用。一般对于每个页大小,可以(这个其实是和Bufferpool对应的):
1.创建一个系统临时表空间。
2.创建一个对应的表空间来放索引。
3.创建一个对应的表空间来放经常访问的数据。
4.创建一个对应的表空间来放不经常访问、随机访问或顺序访问的数据。
5.创建一个表空间来存放LOB。

–Extent大小
这玩意的目的是为了增加并行性,所以小表用小点,大表用大点。
一般按照表的大小,可以:
Less than 25 MB, use an Extent Size of 8
Between 25 and 250 MB, use an Extent Size of 16
Between 250 MB and 2 GB, use an Extent Size of 32
Greater than 2 GB, use an Extent Size of 64

这个要和RAID比较着来。

–Prefetch Size
对于没有RAID的数据库:
Prefetch Size = (# Containers of the table space on different physical disks) * Extent Size
对于建立在磁盘阵列上的数据库:
PREFETCH SIZE = EXTENT SIZE * (# of non-parity disks in array)

–关于建立Table
1.对于小雨30bytes的column,不要用VARCHAR。
2.对于IDENTITY或SEQUENCE,至少使用cache为20.

–关于建立索引:
1.如果select的时间合理,不要建立索引。
2.不要超过5个column。
3.对于多个column的索引,是有顺序的。
4.不要再已经有索引的column上再建立索引。
5.尽量使用ALLOW REVERSE SCANS,这个对性能是没有影响的。
6.考虑使用INCLUDE。
7.还有很多。。。

–关于环境变量
DB2_PARALLEL_IO:在RAID上请考虑设置下

–Snapshot monitoring
写个脚本,每60s抓一次数据。这个脚本在snapshot这里应该是:db2 -v get snapshot for all on $dbname >> snap_$i2,然后脚本里还可以有很多其它的东西,比如执行vmstat和iostat的输出。

对于输出的脚本,配合grep之类的工具,查找需要的东西,比如:
1.执行次数最多的sql(越大一般意味着这个sql越重要)
2.rows read最多的sql(如果比较大,很可能意味着没有走索引)
3.total execution time最多的sql(一般可以除以执行次数。如果得出的数值比较大,可能是由于全表扫描或锁等引起的,这两个都能通过索引解决。对于锁等,可以考虑经常commit)
4.Bufferpool的命中率:BPHR (%) = (1 – ((“Buffer pool data physical reads” + “Buffer pool index physical reads”) / (“Buffer pool data logical reads” + “Buffer pool index logical reads”))) * 100。如果这个比较低,先考虑看看这个Bufferpool多大:Current size X Page size,如果加大后还是没用,那么估计要重新考虑Bufferpool和Tablespace的设计了。
5.如果”Dirty page steal cleaner triggers” 大于10,或者”Buffer pool data writes”很高并且 “Asynchronous pool data page writes”很低,考虑降低CHNGPGS_THRESH。一般可以设置成20~40。20的话对经常update的情形来说是很有用的。
6.如果”Secondary logs allocated currently”很高,可以考虑加大LOGFILSIZ或LOGPRIMARY,毕竟SECONDLOG的分配需要额外的时间。
7.如果”Log pages read”一直大于3,可以考虑加大LOGBUFSZ。换句话说,系统有时候会的读取一些老的日志来进行自己的运行,一般可以直接从LOG BUFF里读,但是如果不够了,那么会从磁盘读。
5.很多很多其它的。。。

–locks
On 32-bit platforms, the first lock on an object requires 72 bytes while each additional requires 36 bytes. On 64-bit platforms, the first lock requires 112 bytes while additional require 56 bytes.

LOCKLIST and MAXLOCKS

对于LOCK TIMEOUT:
OLAP一般设为60,OLTP一般设为10,不过一开始开发的时候可以设为-1,这样可以发现锁等。如果OLTP的事务量很大,那么可以考虑大这个数值来避免ROLLBACK。

–系统相关
CPU一般要求在80%以下。
CPU过高一般是由于发生了全表扫描或者在大表上发生了索引扫描。这个时候看一下有没有rows read过高的SQL。一般锁升级的时候也会CPU暴增(反正在内存中进行计算的时候都会CPU上升啦)

磁盘一般要求Busy在45%以下。

内存的话一般不能有换页发生。

–手工指定Access Plan,可以看下一个相关的网页,这个网页也保存了
1.嵌入式的方法
先设置一个实例变量:
db2set DB2_OPTPROFILE=YES
然后重启实例:
db2stop
db2start
然后执行语句的时候后面用下面的语法:
db2 “SELECT * FROM E2 as E22 ORDER BY ID /**/”
这个时候去查看访问计划发现它就走索引了(不然select *一般是走全表扫描的)。

2.设置opt_profiles

–获取表的统计信息
[db2inst1@HADR01 ~]$ db2cat -d test -s db2inst1 -n tableXXX -o XXX.txt

[db2inst1@HADR01 ~]$ db2look -d test -z db2inst1 -t tableXXX -m -o XXX.txt

–统计信息的表
syscat.tables/sysstat.tables –表的信息
syscat.columns/sysstat.columns –列的信息
syscat.indexes/sysstat.indexes –索引的信息
syscat.coldist/sysstat.coldist –列的分布信息
syscat.colgroups/sysstat.colgroups –列组的信息

1.表的基本统计信息
CARD –表中的行数(集数)
FPAGES –当前使用的页面数
NPAGES –包含记录行的页面数,这个和上面的fpages相差太大的话可以reorg啦
OVERFLOW –溢出的行数,这个太大的话可以reorg啦
AVGROWSIZE –行的平均长度
ACTIVE_BLOCKS –表示MDC包含的数据的块数

2.列的基本统计信息
COLCARD –列上的不同值的数目
NUMNULLS –列上的空值的数目
HIGH2KEY –列上的第二大值,之所以是第二大,是因为使用第一大、第一小有时候会的有误差
LOW2KEY –列上的第二小值,之所以是第二大,是因为使用第一大、第一小有时候会的有误差

3.索引的基本统计信息
NLEAF –叶子节点的页面数
NLEVELS –索引的层级
INDCARD –索引项的数目,这个数目和表的那个CARD不同,因为索引项和数据行并不一一对应
FIRSTKEYCARD –组合索引上第一个列的不同数值数目
FIRST2KEYCARD/FIRST3KEYCARD/FIRST4KEYCARD –组合索引中前2、3、4个列的基数,当这些项不适用于该索引时(比如一个列的索引),那么就都为-1
FULLKEYCARD –索引全部列的不同值的数目

4.列组统计信息
比如一个表,其A和B列的数值有一对一的关系,对于A=1 and B=2这样的过滤,如果不收集列组统计,那么过滤的ff为card(T) *ffA*ffB,这个是不准确的。但是如果收集了runstats on table XXX on all comuns ann columns((A,B)),就可以得到更加准确的信息啦。

–相关计算
ff:过滤因子
1.只有基本统计信息的情况下,优化器认为所有的数据都是分布均匀的,于是:
对于C1=7,ff = 1/colcard(C1)
对于key1

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*