ITPUB论坛－专业的IT技术社区

标题: DB2数据库预取功能 [打印本页]

作者: tom_fans 时间: 2010-5-7 18:24
标题: DB2数据库预取功能
关于这个功能我不是太明白，DB2是如何预取的？

一条SQL进入DB2，生成计划，然后去取数据，请问DB2如何达到预取？之前根本就不知道需要哪些块，等知道了需要哪些，这个时候也已经开始查询了。

作者: tom_fans 时间: 2010-5-7 19:20
看来星期五是个好日子，全部出去了

作者: biti.vector 时间: 2010-5-7 22:48
http://publib.boulder.ibm.com/in ... c/doc/c0005400.html

作者: tom_fans 时间: 2010-5-8 14:42

原帖由 biti.vector 于 2010-5-7 22:48 发表
 http://publib.boulder.ibm.com/in ... c/doc/c0005400.html

我的意思是既然要预取，那么自然应该知道需要什么块(这个时候通过顺序块还是单块暂时先认为无所谓），既然知道了，其实按理来说SQL已经开始查询了。预取又有什么用呢？

如果说这个时候不知道要什么块，又如何预取呢？

如果知道了要什么块，但是SQL还在生成计划阶段，或者说还没正式的开始查询，请问DB2如何实现这个功能的呢？查询没开始怎么知道要什么块？

这是我想知道。毕竟其他数据库并没有这种所谓的预取功能。

作者: wangzhonnew 时间: 2010-5-8 21:14
一般prefetch发生在table scan和index scan。
对于一般的index scan主要还是走sync read，但是如果访问计划里面的路径有ixscan->sort(rid)->ridscan->fetch，那么这就是说对于从index scan里面拿到的rid作排序，然后对于排序好的rid进行prefetch。
所谓prefecth就是一次读超过一个page（比如一个或者若干个extent）。

LZ应该是oracle过来的，应该比较熟悉oltp系统。对于oltp，大部分情况是每一个查询会得到1，2条记录。这几条记录可能在同一个或者不同的page里面。这样，数据库需要对于这几个rid所对应的page一个一个读。在结果集小的时候还可以，但是考虑一下如果结果集超过100万，对于每一个记录一条一条地做I/O是不大现实的。
因此，当这种情况发生的时候，db2首先对结果集里面所有的rid进行排序，然后一次读入很多个page，这样这些page里面很可能一下就包含几百上千个结果，比单个I/O要快很多。

作者: tom_fans 时间: 2010-5-8 23:12

原帖由 wangzhonnew 于 2010-5-8 21:14 发表
一般prefetch发生在table scan和index scan。
对于一般的index scan主要还是走sync read，但是如果访问计划里面的路径有ixscan->sort(rid)->ridscan->fetch，那么这就是说对于从index scan里面拿到的rid作排序，然后对于排序好的rid进行prefetch。
所谓prefecth就是一次读超过一个page（比如一个或者若干个extent）。

LZ应该是oracle过来的，应该比较熟悉oltp系统。对于oltp，大部分情况是每一个查询会得到1，2条记录。这几条记录可能在同一个或者不同的page里面。这样，数据库需要对于这几个rid所对应的page一个一个读。在结果集小的时候还可以，但是考虑一下如果结果集超过100万，对于每一个记录一条一条地做I/O是不大现实的。
因此，当这种情况发生的时候，db2首先对结果集里面所有的rid进行排序，然后一次读入很多个page，这样这些page里面很可能一下就包含几百上千个结果，比单个I/O要快很多。

如果是这样，我基本算明白了DB2的预取是怎么的说法。

从访问路径来看也就分为：全表扫描，索引扫描（当然索引扫描又分为几种情况），如果是索引扫描，自然通过得到的RID直接可以定位到page，（从ORACLE角度来讲，除了FFS其他的索引扫描都是单块扫描，不知道要拿哪些BLOCK，自然不能通过多块，否者会拿很多无用的块）或许DB2在这个地方相对ORACLE来说做了改进，并不使用ORACLE所谓的单块来取，当然也避免不了取出无用的page。但不管如何，如果取很多数据这么做应该效率会高，但是如果按你说的数据少的话，这种做法未必是个好事。

对于全表扫描来说，ORACLE自然是通过多块来扫，一般是16×8=128K数据。可能DB2里并没有这种多块和单块的概念，那么这里所谓的预取功能，应该和ORACLE的多块是一个概念。

不知道我对DB2的预期功能理解的对不对？

作者: wangzhonnew 时间: 2010-5-8 23:30

原帖由 tom_fans 于 2010-5-9 00:12 发表

如果是这样，我基本算明白了DB2的预取是怎么的说法。

从访问路径来看也就分为：全表扫描，索引扫描（当然索引扫描又分为几种情况），如果是索引扫描，自然通过得到的RID直接可以定位到page，（从ORACLE角度来讲，除了FFS其他的索引扫描都是单块扫描，不知道要拿哪些BLOCK，自然不能通过多块，否者会拿很多无用的块）或许DB2在这个地方相对ORACLE来说做了改进，并不使用ORACLE所谓的单块来取，当然也避免不了取出无用的page。但不管如何，如果取很多数据这么做应该效率会高，但是如果按你说的数据少的话，这种做法未必是个好事。

对于全表扫描来说，ORACLE自然是通过多块来扫，一般是16×8=128K数据。可能DB2里并没有这种多块和单块的概念，那么这里所谓的预取功能，应该和ORACLE的多块是一个概念。

不知道我对DB2的预期功能理解的对不对？

在db2里面可以定义extent size 和prefetch size。
比如说如果你的tablespace有3个container，然后extent size定义为4，prefetch size定义的是24那么每次I/O的时候db2最多会从每个容器里面取2个extent，也就是8个数据页。你可以通过定义dft_prefetch_sz或者每个tablespace自己的参数来修改最大的prefetch size。

当数据量少的时候，一般优化器会选择同步读，也就是对每一个rid读一行。具体的算法在优化其内部对每种路径的开销进行预测，然后选择一个开销小的

作者: tom_fans 时间: 2010-5-9 00:20
恩，这个明白。但是应该还要考虑系统I/O的最大值，ORACLE在多块读取的时候受系统IO的影响，我相信DB2在这方面也一样。应该不是你设置多大值，就一定能拿那么大。

至于你提到的当存在多个容器的时候，会条带式的获得page，感觉比ORACLE的多块读取好像强一些。毕竟ORACLE一次最大IO是一个extent，而DB2可以获得多个extent。

我常听人说，DB2的查询速度大于ORACLE，虽然我没做实验，但是从这个方面来说，感觉的确会快些。不知道斑竹怎么看这个问题？

作者: wangzhonnew 时间: 2010-5-9 01:03
i've never touched oracle before, can't give any comment :-)

作者: tom_fans 时间: 2010-5-9 01:44
哈哈，你这么晚也没睡觉啊。关于这个快慢的问题，我也用理论来推导过，我就说说我自己的看法，仅仅是客观的描述，没有感情色彩。

在谈这个问题之前，需要排除一些干扰，尤其是算法的干扰，比如定位BLOCK的HASH算法，B树索引的效率，以及生成计划过程的一些开销，仅仅是谈获得一切条件之后开始查询后的比较。

1. 锁的问题
ORACLE通过UNDO来解决select锁的问题，但是额外的开销就是构造前映像，这种构造的BLOCK可能非常多，也可能非常少，典型的就是仓库和OLTP，一些类似OLAP的报表系统算是中间的。如果排除极端情况，从经验来看，ORACLE需要20%额外的构造前映像的开销，虽然说在内存里面构造这种前映像非常的快（我测试过我solaris系统，平均1秒可以获得30W-35W的buffer gets，一个block 8k），但是每个SQL都需要这么做，必然或拖慢ORACLE的速度。但是在更新频繁的系统，UNDO的作用毋庸置疑。

但是DB2在9.7之前select是要加锁，这必然会在并发性上比较差，但是也正是因为select加锁，不需要获取额外的block开销，如果锁资源竞争少的情况下，DB2应该会跑的比ORACLE块。但是一旦并发高，我可以好不断言的说DB2在并发上要差至少一个档次。

2.最大IO以及多块及单块读问题（相对DB2就是预取功能了）

首先我不确定DB2有最大IO能力限制（我认为有，毕竟DB2也是构建在操作系统上），所以这个我就不说了，但是ORACLE肯定有，一次IO最多1个extent。这个extent同样要受系统IO限制。

按照开始你的说法，以及我个人的思考，DB2的预取功能上要胜过ORACLE的多块读，在数据量少的情况下应该性能一样，毕竟DB2的预取功能没发挥效率，但是数据量稍微大点，不管是通过索引还是全表扫描，DB2都胜过ORACLE。
全表扫描DB2可以获得多个extent，而且可以条带式获得，但是ORACLE一次最多在一个数据文件获取一个extent。
索引扫描，ORACLE除了FFS使用多块，也就是一次扫一个extent，其他的一定是单块，也就是一个一个叶子的找，毕竟索引物理存储上并不是像索引逻辑概念上一样有序的。而DB2通过预取功能不做单个IO获得单个BLOCK，而是获取多个BLOCK，这必然加快查询速度。当然这时候IO能力很重要。尤其是磁盘的能力。假设排除这个可能性。
DB2要胜过ORACLE。

3.重用计划问题

2者都有这个功能，所以没办法说谁好谁差。

如果从上面几个方面讲，并发性不用比了，DB2肯定差点，所以如果OLTP使用DB2，就我个人来说，这种系统我真不敢去碰。那个锁竞争我不清楚会什么样子，select都要被堵塞，这是什么概念。除非你用脏读。但是在OLAP系统来看，DB2应该是稍微胜过ORACLE。

呵呵，这是我自己的想法，见笑了。

作者: mdkii 时间: 2010-5-10 10:43
又来一个db2与oracle比较的。
我的感觉是，不论什么数据库，
只要用在合适的场景并发挥了它的最大性能，都一样跑得飞快

作者: tom_fans 时间: 2010-5-10 11:15
你这样说也不是没道理，但是东西自然有好有差。不要说什么用在合适的场景。

DB2的锁机制本类就糟糕，糟糕也就算了，还美其名曰：拿当前数据，说人家ORACLE不过是拿before image。

一个新插入，更新的但是没有提交的数据也叫当前数据？这只是一种借口罢了。

为什么9.7以后就实现了查前映像呢？

差要承认，当然好的地方也要说出来。

作者: Pythagoras 时间: 2010-5-11 16:48
Prefetch is asynchronous read I/O.
For large volume read, asynchronous I/O is more effective than synchronous, that is more pages been get by one I/O operation, less CPU been held during I/O suspension.

作者: tom_fans 时间: 2010-5-11 16:54
你说是异步？请问如何异步？

查询没开始，就知道要什么page了？查询开始了再去获取又怎么能说是异步呢？

作者: wangzhonnew 时间: 2010-5-11 19:37
query already started... as i remember when it's doing async read, it pass a list of read request (not just one) to prefetcher queue.
there could be multiple prefetcher threads reading the queue, when they see something arrives, they dispatch the request and pass the requests into readv() system call (or some similar OS call, can't remember clearly).

so for example if you are DB2, and you want extent 1,2,3,4,5.....100
so what do you do? you simply pass your request to a queue and then wait for notification.
after a while someone knock your door and says "hey, they data is out there and you can start using it" (at this moment, it's not necessarily all data are there, maybe only extent 1 and 2 in bufferpool, but you can already proceed your job).
then you start working, and while you work someone send you notification says "extent 3 and 4 there"; "extent 6,7,9,20 avaliable"...
maybe after work for 50 extents, you find the queue is empty and there's still 50 extents wait to proceed, then you sit down to drink tea, and later on someone will come to tell you "extent 79,80 are read, go ahead to use them"...

作者: tom_fans 时间: 2010-5-11 20:58
这样的定义也能叫异步？

你要是说多线程也就罢了，说异步这简直是自欺欺人。 IBM向来喜欢玩这种文字游戏，我也知道。

作者: wangzhonnew 时间: 2010-5-11 22:25
well, that is async, compare with sync, which is reading one block, do fetch, and then take another I/O to read next block...
if you think it doesn't help, it's up to you, but in reality it really improve performance compare with sync read when reading big block...

[ 本帖最后由 wangzhonnew 于 2010-5-11 23:29 编辑 ]

作者: tom_fans 时间: 2010-5-11 23:03
哈哈哈哈，这么做可以提高性能，但是绝对不是异步。你要说相对来说，我也不赞同。 ORACLE的术语多块读定义就比较好，不像IBM要说自己是异步，不就是一次取多个page，而不是拿一行取一个page。

多谢版主指点了，从ORACLE一下转到DB2真是不习惯，术语也不习惯，什么page,page，BLOCK多好听，UNIX本身也是说BLOCK。

作者: wangzhonnew 时间: 2010-5-11 23:06
it's different than "不就是一次取多个page"
when you said "一次取多个page", it means
you send request -> someone read multiple pages -> you process

but async read means
you send request -> someone read page, and at the same time you process.
The main difference is, you do NOT wait for all pages read into bufferpool then process.

and note the definition of async (regardless in TCPIP or database or I/O), it simply means "do things at same time"~~~

作者: tom_fans 时间: 2010-5-11 23:12
it's different than "不就是一次取多个page" ===》是different from 不是than

作者: wangzhonnew 时间: 2010-5-11 23:13
as long as you can understand what i mean~~~ we are not discussing english gramma here~~~

typing chinese is too slow than english..~~

for async I/O definition you can refer wikipedia
http://en.wikipedia.org/wiki/Asynchronous_I/O

Asynchronous I/O, or non-blocking I/O, is a form of input/output processing that permits other processing to continue before the transmission has finished.

作者: tom_fans 时间: 2010-5-11 23:14
那我还有一个问题不明白：

既然你说的异步是这个意思，那么为什么要定义预取的page大小的？ DB2直接按照自己的IO步骤去做不就得了。反正是变做变拿，何必需要定义大小呢？

作者: wangzhonnew 时间: 2010-5-11 23:17
when db2 ask prefetcher to read something, it must be read block by block (not oracle block here, it just means "some data").
the block size must be provided to system call so that OS knows how much data you want.

So how to define how much data each readv() want? Whether it's 4096 bytes, or 200MB?
That is the thing we defined for prefetch size. So basically prefetch size is the upper limit for the size that each prefetcher read from disk every time.
DB2 may want to read 10000 pages, and if we have 10 prefetchers are idle, each prefetcher will get those requests and break it into multiple reads, the maximum size of data to read for each I/O will be prefetching size~~~

作者: Pythagoras 时间: 2010-5-11 23:28
What is sync read I/O?
--CPU: Hi, IO. I need page No.1000 for DB2, please get this page, I will idle here until you give it to me.
--IO: OK. I will search the buffer pool, if not found I will then search the disk cache, if not found I will then search the disk. Please wait at most 5 ms.
(5 ms later)
--IO: CPU, you are not a lucky guy. I finally find this page in disk, I mean I have to spin the magnetic head. Here is the page.
--CPU: Thanks. I will check it to find any rows qualified the DB2's request. BTW, I think I do the work in so-called synchronous mode, when you spin the disk, I can do nothing but wait for your reply. It waste my time, which is expensive. Next time, I need 100 pages, what a nightmare!

作者: Pythagoras 时间: 2010-5-11 23:29
Next time, what is async read I/O?
--CPU: Hi, IO. This time I need 100 pages, No.1000-1099 for DB2. Please get these pages. I do not like the sync mode, when you spin the disk I can do nothing. This way, you give the first page (#1000) to me, I will check it to find any rows qualified the DB2's request, when I do my checking work, please don't be lazy, get the next 16 contiguous pages (#1001-1016) , then I will check these 16 pages to find any rows qualified the DB2's request, at the same time you get the next 16 contiguous pages(#1017-1032) again, and so on. Let's do it.
--IO: OK. For the first page, please wait for at most 5 ms. For the second and third and forth and ... 16 contiguous pages, please wai for at most 10 ms. Because get more pages in one I/O is more effective than get one page in one I/O.
--CPU: Of course, I know. I think 10 ms is enough for me to check 16 pages carefully. BTW, I think we do the work in so-called asynchronous mode, when you spin the disk, I am also busy, I do not have to wait for your reply. It maximize utilization of me. I like it.

[ 本帖最后由 Pythagoras 于 2010-5-11 23:48 编辑 ]

作者: tom_fans 时间: 2010-5-11 23:39
按这么说是否要操作系统启动异步IO呢？

作者: tom_fans 时间: 2010-5-11 23:39
好像LINUX，solaris，就算AIX也要配置异步IO才行啊。

作者: wangzhonnew 时间: 2010-5-12 00:05
well, if you already read infocenter you'll see it's part of db2 installation requirement:

http://publib.boulder.ibm.com/in ... c/doc/t0050571.html
# On AIX® Version 5.3, Asynchronous I/O (AIO) must be enabled. It is strongly recommended the system has I/O Completion Ports (IOCP) enabled.

but again, db2 prefetcher is on different layer compare with aio from OS level. AIO on OS level is only used by pagecleaner, not by prefetcher...

作者: jamaica123 时间: 2010-6-30 15:20
好帖子，收藏。。

作者: sdusun 时间: 2010-7-7 16:37

原帖由 tom_fans 于 2010-5-9 01:44 发表
哈哈，你这么晚也没睡觉啊。关于这个快慢的问题，我也用理论来推导过，我就说说我自己的看法，仅仅是客观的描述，没有感情色彩。

在谈这个问题之前，需要排除一些干扰，尤其是算法的干扰，比如定位BLOCK的HASH算法，B树索引的效率，以及生成计划过程的一些开销，仅仅是谈获得一切条件之后开始查询后的比较。

1. 锁的问题
ORACLE通过UNDO来解决select锁的问题，但是额外的开销就是构造前映像，这种构造的BLOCK可能非常多，也可能非常少，典型的就是仓库和OLTP，一些类似OLAP的报表系统算是中间的。如果排除极端情况，从经验来看，ORACLE需要20%额外的构造前映像的开销，虽然说在内存里面构造这种前映像非常的快（我测试过我solaris系统，平均1秒可以获得30W-35W的buffer gets，一个block 8k），但是每个SQL都需要这么做，必然或拖慢ORACLE的速度。但是在更新频繁的系统，UNDO的作用毋庸置疑。

但是DB2在9.7之前select是要加锁，这必然会在并发性上比较差，但是也正是因为select加锁，不需要获取额外的block开销，如果锁资源竞争少的情况下，DB2应该会跑的比ORACLE块。但是一旦并发高，我可以好不断言的说DB2在并发上要差至少一个档次。

2.最大IO以及多块及单块读问题（相对DB2就是预取功能了）

首先我不确定DB2有最大IO能力限制（我认为有，毕竟DB2也是构建在操作系统上），所以这个我就不说了，但是ORACLE肯定有，一次IO最多1个extent。这个extent同样要受系统IO限制。

按照开始你的说法，以及我个人的思考，DB2的预取功能上要胜过ORACLE的多块读，在数据量少的情况下应该性能一样，毕竟DB2的预取功能没发挥效率，但是数据量稍微大点，不管是通过索引还是全表扫描，DB2都胜过ORACLE。
全表扫描DB2可以获得多个extent，而且可以条带式获得，但是ORACLE一次最多在一个数据文件获取一个extent。
索引扫描，ORACLE除了FFS使用多块，也就是一次扫一个extent，其他的一定是单块，也就是一个一个叶子的找，毕竟索引物理存储上并不是像索引逻辑概念上一样有序的。而DB2通过预取功能不做单个IO获得单个BLOCK，而是获取多个BLOCK，这必然加快查询速度。当然这时候IO能力很重要。尤其是磁盘的能力。假设排除这个可能性。
DB2要胜过ORACLE。

3.重用计划问题

2者都有这个功能，所以没办法说谁好谁差。

如果从上面几个方面讲，并发性不用比了，DB2肯定差点，所以如果OLTP使用DB2，就我个人来说，这种系统我真不敢去碰。那个锁竞争我不清楚会什么样子，select都要被堵塞，这是什么概念。除非你用脏读。但是在OLAP系统来看，DB2应该是稍微胜过ORACLE。

呵呵，这是我自己的想法，见笑了。

分析的不错，我再补充一下。

1
对于这里的第一个问题(1. 锁的问题)
要知道在DB2中SELECT加锁，虽然不需要获取额外的block开销，但是会占用locklist，尤其当数据量大的时候。
而Oracle并没有使用内存管理行锁，只是一条记录中的一个状态位而已。

Oracle因为有Undo，所有原理上并发应该能更高一些。

2 最大IO以及多块及单块读问题
我也基本赞成你这里关于IO读取的分析。
但是不要忘了，Oracle有Index fast full scan和Index skip scan，
这是DB2所不具备的。
所以单从I/O读取上来判断执行计划的优劣有失偏波。

作者: sdusun 时间: 2010-7-7 17:06
标题: 回复 #25 Pythagoras 的帖子
赞！

欢迎光临 ITPUB论坛－专业的IT技术社区 (http://www.itpub.net/)