【好文转载】What Is Prefetching?

When an agent acting on behalf of an application needs to access table or index pages, it will first look for the pages in the database buffer pool area. If the page cannot be found in the buffer pool area, it will be read from disk into the buffer pool. I/O is very expensive, and in this case, the agent cannot do anything but wait for the read request to finish before it can access the page. These page reads are typically done one page at a time, and if the application also needs to access subsequent pages within the table or index, this is not an efficient method for reading the pages into the buffer pool.

[@more@]

In many situations, DB2 UDB can anticipate the pages that will be requested by an application and read them into the buffer pool before the agent actually attempts to access them. This is referred to as prefetching. Prefetching can improve the database performance because the pages will be found in the buffer pool when the agent accesses them, reducing or eliminating the time the application must wait for the page to be read from disk into the buffer pool. This is more relevant to DSS-type workloads that scan large indexes and tables than for OLTP-type workloads that involve less scanning and more random insert/update/delete activity.

Prefetching can be enabled by the DB2 optimizer when it is building the access plan for a statement and determines that it will be scanning a large portion of a table or index. It can also be enabled or triggered when DB2 is executing an access plan and detects that it has read a number of pages in sequence and will likely continue to do so. This is known as sequential detection and can be enabled or disabled, using the database configuration parameter SEQDETECT.

Restart recovery will automatically use the prefetchers to improve restart time; however, prefetching can be disabled during restart recovery by setting the DB2 registry variable DB2_AVOID_PREFETCH to ON.

How Does Prefetching Work?

Prefetching will be requested by a DB2 agent if the access plan has specified that prefetching should be done or if sequential detection is enabled and a number of pages are read in sequence. The DB2 agent will make prefetch requests that will be examined by the prefetch manager and assigned to the DB2 I/O servers (also known as prefetchers).

The prefetch requests are sent to a queue where they are examined by the prefetch manager. The prefetch manager examines the requests to determine whether any of the prefetch requests from different applications can be combined and handled more efficiently in one prefetch operation. If no prefetch requests can be combined, the queue acts in a first in-first out (FIFO) manner.

To ensure that the prefetchers do not work too aggressively, reading data into the buffer pool and overwriting pages before they can be accessed, the amount of prefetching is controlled by the size of the buffer pool, as well as the prefetch size and the access plan generated by the optimizer. For smaller buffer pools, prefetching may be scaled back to ensure that prefetched pages do not flood the buffer pool and kick out pages that are currently being used or have been prefetched into the buffer pool and have not been accessed.

Within DB2 UDB, there are two main types of data and index prefetch requests handled by the prefetchers: range and list prefetching.

Range prefetching is used to prefetch a sequential range of pages from a database object and is used during table scans and when triggered by sequential detection. If a block-based area is set aside in the buffer pool, DB2 will perform a "big block" read to read a contiguous series of pages into a single private memory buffer, then will copy the block of pages to the block-based area of the buffer pool with one memory copy operation.

Internally, the mechanism used is big block read or "vectored" read (on platforms that support an efficient vectored read API). A big block read involves reading a contiguous block of pages into a single, private memory buffer. Each individual page is then copied into its own buffer pool slot (and all of these slots are likely to be scattered throughout the buffer pool). A vectored read API will read a contiguous block of pages from disk into different memory buffers (a different memory buffer for each page of the block). In this case, those memory buffers can actually be the individual buffer pool slots, and the overhead of the copy from one memory buffer to another is avoided (hence, an efficient vectored read API is preferred over big block read). The block-based area of the buffer pool overcomes this by setting aside contiguous blocks of the buffer pool equal in size to the extent size to make prefetching more efficient, especially on operating systems that do not support vectored reads.

List prefetching is used to prefetch a list of pages into the buffer pool area when an index scan produces a list of record IDs (RIDs) that must be prefetched into the buffer pool. List prefetching is normally decided on and specified in the access plan when the SQL statement is optimized. A list prefetch request may get converted to a range prefetch request if the pages are found to be sequential.

There is one prefetch queue that is monitored by the prefetch manager and shared between all of the configured prefetchers. The prefetch queue can handle up to 100 prefetch requests.

When a prefetch request is made, the request will be broken up into a number of smaller I/O requests. By default, the number of I/O requests will be determined by the number of containers that are in the table space. However, if the DB2_PARALLEL_IO registry variable is set, DB2 will start up a number of prefetchers equal to the prefetch size divided by the extent size. This allows multiple prefetchers to be working on the requests in parallel.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7437037/viewspace-914510/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7437037/viewspace-914510/