Data Buffering

ALLBASE/SQL uses a system of buffers to provide access to data and index pages by concurrent transactions. Three sets of buffers actually are used:

In what follows, the emphasis is on the latter two items, which are components of ALLBASE/SQL. Because the ALLBASE/SQL data buffer pool resides in shared memory, many users can access the same pages in memory. For example, if many users need to access information from the same system catalog pages, these pages do not need to be read into the buffer every time a transaction needs them. Provided there is enough buffer space, pages may remain in ALLBASE/SQL shared memory for long periods. A page that has not been modified at all or a page that has been modified and written to disk is considered clean, while a page that has been modified but not written out to disk is considered dirty.

If there is not enough empty space in the buffer, dirty pages are swapped out, that is, written back to disk, and clean pages are overwritten, on the basis of a least recently used (LRU) algorithm.

As the following figure shows, pages flow through the operating system's buffers into the shared ALLBASE/SQL data buffer. Then individual tuples from data pages are read into the tuple buffer associated with an application program. In the figure, D indicates user data pages, I indicates user index pages, S indicates system catalog pages. The illustration shows primarily the movement of data pages, but index pages move in and out of the data buffer in the same way.

From the scan buffer, the application fetches data into host variables, as in the following:

Individual rows are fetched (or groups of rows are BULK fetched) into host variables or arrays declared within the application program.

It is important to understand that each layer of buffering requires additional copying of data from one place to another. More significantly for performance, the movement of data from DBEFiles into the operating system's buffer pool and back requires I/O. The movement of pages between the operating system buffers and the ALLBASE/SQL data buffer also may require additional I/O.

When your applications read large numbers of data pages, they may displace other pages which are still in the buffer, though they are not being used. Dirty pages in the buffer are swapped to disk, and new pages are read in.

To maximize performance, you should define a large enough data buffer for your specific queries, and you should attempt to eliminate as much swapping to disk as possible. This topic is discussed fully in the "System Administration" chapter.

For more information about buffering, refer to the chapter on "Concurrency Control through Locks and Isolation Levels" in the ALLBASE/SQL Reference Manual. To monitor data buffer I/O, invoke the IO screen in SQLMON.