2007年02月22日

1.      bufferpool进行监控和调优

select BP_NAME,

(

INT(1 –

(FLOAT(pool_Index_P_Reads + pool_data_P_Reads)) /

(FLOAT(Pool_Index_L_Reads+Pool_data_L_Reads))

)*100

) AS Pool_Hit_Ratio

FROM TABLE

(SNAPSHOT_BP(‘SAMPLE’,-1 ))  #-1仅获取当前partition-2获取所有partition

as SNAPSHOT_BP;

对于bufferpool1-(物理的read次数/逻辑的read次数),就是bufferpool的命中率。这个值大于80%才能说明这个bufferpool是有用的;对于OLTP环境,必须更大(特别是Index的命中率);对于DSS环境,这个值不可能要求很大。

 

2.      监视bufferpool的异步读Ratio

异步读(asynchronous read ratioRatio的计算公式:

ARR = ((Asynch Data Reads + Asynch Index Reads) /

((Data Logical Reads + Index Logical Reads)) * 100%

使用SQL Table funtion

select BP_NAME,

(INT(((FLOAT(pool_Async_data_Reads + pool_async_index_Reads)) /

(FLOAT(Pool_Index_L_Reads + Pool_data_L_Reads))) * 100))

AS Asynch_Read_Ratio

FROM TABLE(SNAPSHOT_BP(‘SAMPLE’,-1 ))

as SNAPSHOT_BP;

得到的结果就是表明prefetch的活动是否频繁。如果得到一个非常小的结果,则可能说明:

1.     事务的类型都是对单行进行读写,因此无法从prefetch中获益

2.     database配置了太少的prefetcher

3.     可能database只有一个container

对于异步读比例高的tablespace,最好是为它们各自配置单独的bufferpool

 

3.      物理读RatePhysical Read Rate

PRR = (Data Physical Reads + Index Physical Reads) /

(Time since monitor switches reset orgraphics/ccc.gif activated)

Timer的计算方法为:Snapshot timestamp – First db connect timestamp

物理读可以识别出哪个tablespace有更多的IO需求。

 

4.      Read 所消耗的Time

对于每个ReadDB2 snapshot中提供了足够的信息计算出每个Read所消耗的时间

ART = (Total Buffer Pool Read Time) / (Data Physical Reads + Index Physical Reads)

SQL Table function算法是:

select BP_NAME,

(INT(((FLOAT(pool_read_time))/

(FLOAT(Pool_Index_p_Reads+Pool_data_p_Reads))) * 100))

AS Avg_Read_Time_in_ms

FROM TABLE(SNAPSHOT_BP(‘SAMPLE’,-1 ))

as SNAPSHOT_BP;

 

5.      Page cleaner每次写入diskpage的平均值

      平均每次写入diskpages过大或者过小都将影响数据库的性能。DB2没有直接的snapshot可以获取这个值,但是可以计算出来:

APPAW =

((Asynchronous pool data page writes + Asynchronous pool index page writes) / graphics/ccc.gif(Dirty page steal cleaner triggers + Dirty page threshold cleaner triggers + LSN Gapgraphics/ccc.gif Cleaner Triggers))

通过连接两个表TABLE(SNAPSHOT_BP())TABLE(SNAPSHOT_DATABSE())可以获得这个值。得到的值(pages需要乘上pagesize)应当比较和bufferpool的比率,如果在大于1%小于5%则是比较合理的值。

1.      Page Cleaning

agent要求DB2disk上读取databufferpool中,而此时bufferpool已满的时候,DB2需要选择出victim page,如果victim page已经dirty,那么DB2首先需要将dirty page写入到disk中,这时agent需要等待。

为了避免agent等待,DB2page被选择位victim之前,就将其asynchronously写入到disk,使用page cleaner来完成任务(异步缓冲区写程序)。

每个bufferpool等维护着一个dirty list。当page cleaner被触发时,它们将list中的dirty page写入到各个对应的表空间。

 

2.      触发Page Cleaner的三种途径

1.     Dirty list threshold

参数CHNGPGS_THRESH所设定的,dirty pagebufferpool总页数的百分比

2.     LGN Gap

DB崩溃之后恢复时,需要从日志中重放数据记录,需要重放的日志量是:最新的日志记录,和日志中记录的对bufferpoolpage所做的最早更改开始的日志。

触发PageCleaner的条件是当需要重放的日志记录大小超过:logfilsiz*softmax时,即触发清理动作。其中,softmax的含义是:如果配置了520,那么值就是logfilsiz*5.2。(windows下默认有13log file

3.     Dirty page steals

DB2为一个agent选择了若干个dirty page达到一定的数量之后,则启动page cleaner进行清清除。这是同步写入到disk,需要agent等待。

三种情况各占的百分比可以用以下SQL Table函数获得:

SELECT DB_NAME,

POOL_LSN_GAP_CLNS,

POOL_DRTY_PG_STEAL_CLNS,

POOL_DRTY_PG_THRSH_CLNS

FROM TABLE(SNAPSHOT_DATABASE(‘SAMPLE’,-1 ))

as SNAPSHOT_DATABASE

在比较好的情况下,第三种情况应当只占总的page cleaner次数的2%以下。

如果发现第2种情况的page cleaner过少,则说明可能是logfilsiz*softmax值过大,分别查看这两个值,可以确定问题的原因。

 

3.      Page Cleaner是如何工作的?

当以上三种条件之一触发了Page Cleaner进程之后,所有的page cleaner都将开始工作。

每个page cleaner获取最多400dirty page,并且将它们一个个写入到disk。然后再查看是否还有更多的dirty page;如果没有则等待再次被触发。

 

4.      如何选择PageCleaner的个数?

过多的Page Cleaner将占用过多的系统资源(因为它们会同时被触发),所以,作为一个直观上的选择,选择与本机器CPU个数相同的Page Cleaner

 

5.      调优CHNGPGS_THRESH参数

该参数决定,当bufferpool中的脏页达到百分之多少时,进行page cleaner工作。对于一个大的bufferpool(如2GB),默认值80%过大,将导致严重的性能问题。应当适当调小。对于OLTP环境,应当进行启动page cleaner;而对于DSS环境,则没有必要。

 

1.      关于Prefetching

      预取由optimizer在生成access plan的时候指定,或者在restart recover的时候自动启用(可以用DB2_AVOID_PREFETCH来避免)。

      DB2在读取一个页时,它预计到需要读取与该页在同一个extent中的其他页时(一般来说是sequential detect),它将启动Prefetch,配置参数SEQDETECT可以不使用该功能。DB2 IO Servers参数也就是DB2 Prefetcher

      Prefetcher的工作时间与bufferpool的大小有关,较小的bufferpool无法容忍过于aggressiveprefetcher

      存在一个Prefetch Queue,由prefetch manager管理(它可以查看是否有预取请求可以合并),采用FIFO算法。队列的最大值为100

 

2.      两种Prefetching

1.     Range Prefetching

在执行sequential表扫描中,如果bufferpool中配置了block area,那么DB2将启动一个big block readvectored read,看OS是否支持;对于raw device,应当是毫无疑问支持的)将数据读入到一个private memory buffer中,再将这个buffer中内容copybufferpool中的block area

2.     List Prefetching

在扫描index的时候,产生了一个RID的结果集,需要将这些RID对应的数据预取到bufferpool中。List Prefetch可能被convertRange Prefetch

      与预取有关的环境变量:DB2_PARALLEL_IO

 

3.      选择Prefetching的大小

      对于一个拥有多个containerDB来说,算法一般如下:

prefetch size = extent size * number of containers

      如果只有一个containerRaid上,预取的大小一般为:

prefetch size = extent size * number of disks in the stripe set

      如果启用了DB2_PARALLEL_IO,那么将启动PrefetchingSize/ExtentSizePrefetcher,如果需要aggressive的能力(如在DSS系统中),扩大此值。如果没有启用DB2_PARALLEL_IO,那么,启动的Prefetcher个数等于container的个数。

 

4.      Prefetcher的其他功能

1.     Drop temporary objecsts

agentSQL请求中,可能需要建立system temporary tables来存储中间或最后结果集,为了加快agent的相应速度,将drop的工作给prefetcher来做。

2.     Add DMS container

Agent为第一个container分配空间,后面container空间由Prefetcher来分配。

3.     Resize DMS container

Add相似。

4.     TRAVERSING INDEX LEAF PAGES

在使用REORG命令的时候,需要Prefetcher来调整INDEX LEAFpages

1.      Create Bufferpools命令

                                     .-IMMEDIATE-.

>>-CREATE BUFFERPOOL– bufferpool-name –+———–+————>

                                       ‘-DEFERRED–’

   .-ALL DBPARTITIONNUMS———————————–.

>–+——————————————————-+—->

   |                           .-,———————–. |

   |                           V                         | |

   ‘-DATABASE PARTITION GROUP—- db-partition-group-name -+-’

>–SIZE– number-of-pages –  4 * ————————————->

>–+————————————+–  4 * ——————–>

   ‘-  1 | except-on-db-partitions-clause | -’

   .-  1 NUMBLOCKPAGES 0 ————————————————.

>–+—————————————————————-+–>

   ‘-  1 NUMBLOCKPAGES –  1  number-of-pages  –+—————————-+-’

                                     ‘-  1 BLOCKSIZE –  1  number-of-pages  -’

      .-PAGESIZE–4096———–.

>–*–+————————–+–*—————————>

      ‘-PAGESIZE– integer –+—+-’

                           ‘-K-’

   .-NOT EXTENDED STORAGE-.

>–+———————-+–*———————————><

   ‘-EXTENDED STORAGE—–’

except-on-db-partitions-clause:

|–EXCEPT ON–+-DBPARTITIONNUM–+——————————->

              ‘-DBPARTITIONNUMS-’

 .-,————————————————————————-.

 V                                                                           |

>–(—- db-partition-number1 –+————————–+–SIZE– number-of-pages -+–)–|

                              ‘-TO– db-partition-number2 -’

 

 

2.      一个DB需要多少个Bufferpool?

1.     Under carefully monitoring and tuning, DB2 can outperform much better than one single bufferpool.

2.     One single bufferpool needs no tuning, DB2 has a highly optimized algorithm for aging pages in bufferpools, like favoring index pages, put pages unlikely to accessed to ‘Hate Stack’.

 

3.      何时需要考虑使用多个Bufferpool?

1.     DBA wants to favor a particular application.

2.     Tables are always scanned seperately.

3.     Certain tables don’t need bufferpools.

4.     Operations like join, sort that needs large temporary tables in temp spaces.

tablespace可以使用指定的bufferpool

4.      Bufferpools带来的开销

      DB2 需要为bufferpool中的每一个page分配100 byte的描述符,占用的dbheap上的空间。也就是说,对于一个1GBbufferpool,需要消耗dbheap25MB的空间。因此,在扩展bufferpool之前,需要先扩大dbheap的大小。

 

5.      32-bit 环境下的DB2内存限制

      32-bit的环境中,每个进程的共享内存大小是有限的。DB2中,有如下参数对应的内存需要在bufferpool中分配:

1.     bufferpools

2.     locklist

3.     pckcachesz

4.     shared sortssort_heap, SHEAPTHRES_SHR

5.     dbheap (logbufsz, catalogcache_sz)

6.     util_heap_sz

AIX中,每个进程的地址空间包括了16个段,每个段256MB。在这16个段中,只有7个能够用于Shared Memory,也就是1.75GB。而在这7个段中,一个用于mapped memory I/O,另外一个用于FCM(fast communication manager, 用于partition之间的通信)

对于仅有很少文件个数的tablespacememory mapped I/O可以避免i-node的竞争。如果file个数多,可将DB2_MMAP_READ, DB2_MMAP_WIRTE设置为NODB2_FORCE_FCM_BP设置为NO可以关闭FCM

 

6.      Block-Based Bufferpool

      配置了NUMBLOCKSZBLOCKSZ两个参数之后,bufferpool中的部分区域是以block(多个连续的page)组织的。其作用在于:

      disk上预取的时候,是以extentNpage)的大小取得数据的,放到bufferpool时,可能分布在不连续的page上。在bufferpool中配置了一定比例的block area之后,预取的extent就可以放到连续的页(block)上了。

      extent的大小大于blocksz的时候,DB2将数据放到block中。当extent的大小小于blocksz的时候,DB2可能将数据放到page-based area中或block-based area中,取决于extentblocksz的差别有多大。

2007年01月31日

1. 一個表的最大列數<=1012
2. 一個視圖的最大列數<=5000
3. 一行的最大長度(字節)<=32677
4. 每個分區中表的最大尺寸(千兆字節數)<=512
5. 每個分區中索引的最大尺寸(千兆字節數)<=512
6. 每個分區中表的最大行數<=4000000000
7. 最長索引關鍵字(字節數)<=1024
8. 一個索引關鍵字中的最大列數<=16
9. 一個表的最大索引數<=32767或存儲器
10. 一個SQL語句或視圖中所引用的最大表數<=存儲器
11. 一個預編譯程序中的最大主機變量申明數<=存儲器
12. 一個SQL語句中所引用的最大主機變量數<=32767
13. 用于插入或更新的最大主機變量數(字節數)<=32767
14. 最長的SQL語句(字節數)<=65535
15. 一個選擇列表中最大的元素個數<=1012
16. 一個WHERE或HAVING子句中最多的謂詞個數<=存儲器
17. 一個GROUP BY子句中最多的列<=1012
18. 一個GROUP BY子句中所有列的最大總長度(字節數)<=32677
19. 一個ORDER BY子句中最多的列<=1012
20. 一個ORDER BY子句中所有列的最大總長度(字節數)<=32677
21. 一個SQLDA的最大規模(字節數)<=存儲器
22. 預備語句的最大個數<=存儲器
23. 一個程序中最多可申明的游標數<=存儲器
24. 一次可打開的最大的游標數<=存儲器
25. 一個SMS表空間的最多表數<=65534
26. 一個表中的最大約束數<=存儲器
27. 子查詢潛套的最高級別=存儲器
28. 一個單一查詢中的最大子查詢數=存儲器
29. 一個INSERT語句中的最多值數<=1012
30. 一個單一UPDATE語句中SET子語句的最多值數<=1012
31. 一個唯一約束中所有列的最大數(字節數)<=1024
32. 一個外鍵中所引用列的最大數<=16
33. 一個外鍵中所引用列的最大總長數(字節數)<=1024
34. 一個檢查約束說明的的最大總長數(字節數)<=65535
35. 一個分區關鍵字中列的最大個數<=500
36. 一個工作單元中可改變的行的最大個數=存儲器
37. 包的最大個數=存儲器
38. 一個語句的最大約束數=存儲器
39. 一個服務器同時存在用戶的最大個數=64000
40. 一個存儲過程參數的最大個數=32767
41. 一個用戶自定義的函數中的最大個數=32767
42. 級聯觸發器的最大運行深度=16
43. 同時激活的事件監視器的最大個數=90
44. 一個常規DMS表空間的最大規模(千兆字節數)<=512
45. 一個長DMS表空間的最大規模(千兆字節數)<=2
46. 一個臨時DMS表空間的最大規模(千兆字節數)<=2
47. 一個實例中可同時使用的最大數据庫數<=256
48. 一個實例中可同時使用的最大用戶數<=64000
49. 一個數据庫可同時存在的最大程序數<=1000
50. 最大分區數<=999
51. 一個DMS表空間的最大表對象個數<=51000
52. 最長可變索引關鍵字部分(字節數)<=255
53. 通過別稱的數據源表或視圖的最大列數<=5000
54. 一個緩沖池中最多頁數(32BIT)<=524288
55. 一個緩沖池中最多頁數(64BIT)<=2147483647
56. 一個數据庫中表空間作多個數為<=4096
57. 一個結构類型中最多屬性數<=4082

1. 一個表中的列的最大個數
4KB頁大小的限制=500
8KB頁大小的限制=1012
16KB頁大小的限制=1012
32KB頁大小的限制=1012
2. 一行的最大長度
4KB頁大小的限制=4005
8KB頁大小的限制=8101
16KB頁大小的限制=16293
32KB頁大小的限制=32677
3. 一個表的每個分區的最大規模(千兆字節)
4KB頁大小的限制=64
8KB頁大小的限制=128
16KB頁大小的限制=256
32KB頁大小的限制=512
4. 一個索引的每個分區的最大規模(千兆字節)
4KB頁大小的限制=64
8KB頁大小的限制=128
16KB頁大小的限制=256
32KB頁大小的限制=512
5. 一個選擇列表元素的最大個數
4KB頁大小的限制=500
8KB頁大小的限制=1012
16KB頁大小的限制=1012
32KB頁大小的限制=1012
6. 一個GROUP BY子語句列的最大個數
4KB頁大小的限制=500
8KB頁大小的限制=1012
16KB頁大小的限制=1012
32KB頁大小的限制=1012
7. 一個GROUP BY子語句列的最大長度(字節數)
4KB頁大小的限制=4005
8KB頁大小的限制=8101
16KB頁大小的限制=16293
32KB頁大小的限制=32677
8. 一個ORDER BY子語句列的最大個數
4KB頁大小的限制=500
8KB頁大小的限制=1012
16KB頁大小的限制=1012
32KB頁大小的限制=1012
9. 一個ORDER BY子語句列的最大長度(字節數)
4KB頁大小的限制=4005
8KB頁大小的限制=8101
16KB頁大小的限制=16293
32KB頁大小的限制=32677
10. 一個INSERT語句中數值的最大個數
4KB頁大小的限制=500
8KB頁大小的限制=1012
16KB頁大小的限制=1012
32KB頁大小的限制=1012
11. 一個UPDATE語句中數值的最大個數
4KB頁大小的限制=500
8KB頁大小的限制=1012
16KB頁大小的限制=1012
32KB頁大小的限制=1012
12. 一個常規DMS表空間的最大規模(千兆字節數)
4KB頁大小的限制=64
8KB頁大小的限制=128
16KB頁大小的限制=256
32KB頁大小的限制=512

1. 最小的 DATE 值 = 0001-01-01
2. 最大的 DATE 值 = 9999-12-31
3. 最小的 TIME 值 = 00:00:00
4. 最大的 TIME 值 = 24:00:00
5. 最小的 TIMESTAMP 值 = 0001-01-01-00:00:00:000000
6. 最大的 TIMESTAMP 值 = 9999-12-31-24:00:00:000000

1. CHAR 的最大長度(字節數)<=254
2. VARCHAR 的最大長度(字節數)<=32672
3. LONG VARCHAR 的最大長度(字節數)<=32700
4. CLOB 的最大長度(字節數)<=2147483647
5. GRAPHICCHAR 的最大長度(字節數)<=127
6. VARGRAPHIC 的最大長度(字節數)<=16336
7. LONG VARGRAPHIC 的最大長度(字節數)<=16350
8. DBCLOB 的最大長度(字節數)<=1073741823
9. BLOB 的最大長度(字節數)<=2147483647
10. 字符常量的最大長度(字節數)<=32672
11. 圖形常量的最大長度(字節數)<=16336
12. 串聯字符串的最大長度(字節數)<=2147483647
13. 串聯圖形串的最大長度(字節數)<=1073741823
14. 串聯二進制串的最大長度(字節數)<=2147483647
15. 十六進制常量數字的最大長度(字節數)<=16336
16. 分類注釋的最大長度(字節數)<=254
17. 運行時結构類型列對象的最大長度(字節數)<=1

1. 最小的 INTEGER 值 >= -2147483648
2. 最大的 INTEGER 值 <= 2147483648
3. 最小的 BIGINT 值 >= -9223372036854775808
4. 最大的 BIGINT 值 <= 9223372036854775808
5. 最小的 SMALLINT 值 >= -32768
6. 最大的 SMALLINT 值 <= 32768
7. 最小的 十進制精度 <= 31
8. 最小的 DOUBLE 值 >= -1.79769E+308
9. 最大的 DOUBLE 值 <= 1.79769E+308
10. 最小的正 DOUBLE 值 >= 2.225E-307
11. 最大的負 DOUBLE 值 <= -2.225E-307
12. 最小的 REAL 值 >= -3.402E+38
13. 最大的 REAL 值 <= 3.402E+38
14. 最小的正 REAL 值 >= 1.175E-37
15. 最大的負 REAL 值 <= -1.175E-37