HP 3000 Manuals HP 3000 Manuals
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:
* Operating system buffers.
* ALLBASE/SQL data buffer pool, shared by all processes accessing
the same DBEnvironment.
* ALLBASE/SQL scan buffer (or tuple buffer)--one per user process.
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.
____________________________ ____________________________
| | File 1 | | File 1
|____________________________| Data |____________________________| Data
| Tuple |79|ABCD|22|XYZZ| | Page 6 | Tuple |18|GHIJ|49|NTGH| | Page 19
|Header_|___1___|___2___|... |______ |Header_|___1___|___2___|... |______
| | | | | |
| ____________ ________| | | ____________ ________| |
| <--- |... 3 2 1| TRAILER | | | <--- |... 3 2 1| TRAILER | |
|______|_________|___________| | |______|_________|___________| |
| |
_____V_____________________ |
| I | D | D | S | D | I | D |<------------------------'
|___|_:_|___|___|___|___|_:_|
Operating System | S | : | S | I | D | D | : |
Buffers (HP-UX) |___|_:_|___|___|___|___|_:_| Data and
or memory mapped | D | : | D | D | D | D | : | Index pages
data (MPE/iX) |___|_:_|___|___|___|___|_:_|
| D | : | D | D | D | D | : |
|___|_:_|___|___|___|___|_:_|
: :
..............: :
: :
_____________________V_____________ :
| S | S | S | D | D | D | D | D | D | ................:
|___|___|___|___|___|_:_|___|___|___| :
| I | I | I | D | I | : | I | D | I | : ALLBASE/SQL
|___|___|___|___|___|_:_|___|___|___| : Data Buffer Pool
| S | S | I | D | D | : | S | D | I | : Accessible to all
|___|___|___|___|___|_:_|___|___|___| : (Number of pages set
| D | D | D | I | D | : | D | D <.......: by DBA at startup time
|___|___|___|___|___|_:_|___|_:_|___| or with SQLUtil)
| I | I | D | D | D | : | D | : | D |
|___|___|___|___|___|_:_|___|_:_|___|
| S | S | | | | : | | : | |
|___|___|_ _|___|___|_:_|_ _|_:_|___|
: :
............: :
,......:.., ,........:
__V_________V_______V________V______
|79|ABCD|22|XYZZ|18|GHIJ|49|NTGH| | Scan Buffer for User's
|___0___|___1___|___2___|___3___|..._| Application (12K)
From the scan buffer, the application fetches data into host variables,
as in the following:
__V_________V_______V________V______
|79|ABCD|22|XYZZ|18|GHIJ|49|NTGH| |
|___0___|___1___|___2___|___3___|..._|
: : : | :
:.......:.......:..|....:
: |
: | FETCH
: BULK V
: FETCH ____ ______
: | 18 | | GHIJ | Host Variables
: |____| |______| in User Application
:
: ____ ______
:........>| 79 | | ABCD |
|____| |______| Host Variable Array
| 22 | | XYZZ | in User Application
|____| |______|
| 18 | | GHIJ |
|____| |______|
| 49 | | NTGH |
|____| |______|
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. In MPE/iX, this happens only when the operating
system buffer is full.
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.