Release 10.1A: OpenEdge Data Management:
Database Administration

> ## Database I/O

> Database I/O occurs when the database engine reads and writes blocks containing records to and from disk into memory. To minimize database disk I/O, the database engine tries to keep a block in memory after it reads the block the first time. The next time the engine needs that block, it can access it from memory rather than reading it from disk.

> To eliminate database I/O bottlenecks, you can:

> - Increase the number of database buffers.

> - Change the number and structure of database storage areas.

> - Use private read-only buffers.

> - Use asynchronous page writers (APWs)3.

> ##### Storage areas

> Storage areas are the largest physical unit of a database. Storage areas consist of one or more extents that are either operating system files, or some other operating system level device that is addressed randomly. A storage area is a distinct address space, and any physical address stored inside the area is generally stored relative to the beginning of the storage area.

> Storage areas give you physical control over the location of specific database objects. You can place each database object in its own storage area or place many database objects in a single storage area. Storage areas can contain database objects of one type or of many types. For example, to achieve load balancing, you can place a particularly active table in a separate storage area, then place the most active index for that table in its own storage area. Then, in a third storage area, place all the remaining tables and indexes. You cannot split a table or index across storage areas.

> However, you can improve performance by moving tables and indexes to an application data storage area on a faster disk, while the database remains online. For a description of how to move tables and indexes while the database remains online, see Chapter 14, " Maintaining Database Structure."

> ##### Database buffers

> A database buffer is a temporary storage area in memory used to hold a copy of a database block. When the database engine reads a database record, it stores the block that contains that record in a database buffer. Database buffers are grouped in an area of memory called the buffer pool. Figure 13–1 illustrates database disk I/O.

> Figure 13–1: Database I/O

> Database I/O works as follows:

> 1. When a process needs to read a database record, it requests access to the record.

> 2. The database engine searches the buffer pool for the requested record.

> 3. If the block that holds the record is already stored in a buffer, the engine reads the record from the buffer. This is called a buffer hit. When tuned correctly, the engine should achieve a buffer hit most of the time.

> 4. If the record is not found in any buffer, the engine must read the record from disk into a buffer. If an empty buffer is available, the engine reads the record into that buffer.

> 5. If no empty buffer is available, the engine must replace another buffer to make room for it.

> 6. If the block that will be evicted has been modified, the engine must write the block to disk to save the changes. This is known as an eviction. While the eviction takes place, the process that requested the record in Step 1 must wait. For this reason, performance is improved if empty buffers are always available. See the "How the database engine writes modified buffers" section for detailed steps.

> Figure 13–2 illustrates how the engine reads a database record into a buffer.

> Figure 13–2: Reads and writes to database buffers

> ##### How the database engine writes modified buffers

> When a process requires access to a database block that is not in the buffer pool, the database engine must replace another buffer to make room for it. The server searches for a buffer to replace.

> The ideal replacement candidate is a buffer that is unlocked and unmodified. Replacing an unmodified buffer requires only one step: writing the new contents into the buffer. If a buffer contains modified data, it must first be evicted before it can be replaced. Evicting the buffer requires two steps: writing the buffer’s contents to disk, then writing new contents into the buffer. It is therefore slower and requires more overhead as shown in Figure 13–3.

> Figure 13–3: Evicting buffers

> When searching for a replacement candidate, the server searches a maximum of ten buffers. If the server fails to find an unlocked, unmodified buffer, the server evicts the first unlocked, modified buffer that it finds.

> ##### Monitoring database buffer activity

> A buffer hit occurs when the database engine locates a record in the buffer pool and does not have to read the record from disk. See the "Database buffers" section for an explanation of buffer hits and how they improve performance by reducing overhead. When tuned correctly, the engine should achieve a buffer hit most of the time.

> To determine the efficiency of database buffer activity, check the Buffer Hits field of the PROMON Activity option. For best performance, increase the Blocks in Database Buffers (-`B`) parameter until the buffer hits percentage exceeds 95 percent, or until your system starts paging. Figure 13–4 shows the Buffer Hits field in a sample Activity display.

> Figure 13–4: Monitoring buffer activity

> ##### Tuning database buffers

> If database buffer monitoring indicates that buffer hits are below 85 percent, you can increase the number of database buffers so more records are kept in memory.

> To increase the number of buffer hits, increase the -`B` startup parameter to allocate more buffers. Increase the -`B` parameter until the buffer hits percentage exceeds 95 percent, or until your system starts paging.

> The database engine uses a hash table to reduce the time it takes to locate a database buffer. The Hash Table Entries (-`hash`) startup parameter controls the number of hash table entries in the buffer pool. The database engine sets this parameter to approximately 25 percent of the number of database buffers (specified by the -`B` parameter). In most cases, the default value is adequate. However, increasing this parameter might slightly reduce the time required to find a block in the buffer pool.

> ##### Using private read-only buffers

> The buffer pool is a mechanism that conserves I/O when multiple users are accessing information from the same disk blocks. The buffer pool has a predefined size. Once the buffer pool becomes full, buffers are replaced on a least recently used (LRU) basis. Since sequential readers of the database access so many different buffers, they sometimes monopolize the buffer pool. That is, sequential readers of the database cause many shared buffers to be replaced by the buffers most recently used by the sequential reader.

> Consequently, you can request some number of buffers in the buffer pool to be private read-only buffers. Private read-only buffers do not participate in the LRU replacement algorithm of the general shared buffer pool.

> Applications that read many records in a short time, such as applications that generate reports or lists, should use private read-only buffers. Private read-only buffers prevent applications from quickly using all the public buffers and depriving buffers from other users. When an application is using private read-only buffers, updates are performed correctly using the public buffers. Therefore, an application performing many read operations but only a modest amount of updates might also benefit from using private read only buffers.

> When a sequential reader is using private read-only buffers and needs a buffer to perform a read operation, and the buffer is already in the private read-only buffer pool, the database engine marks the buffer as most recently used (MRU) and uses it. If the buffer is not already in the private read-only buffer pool, the sequential reader takes a buffer from the LRU chain and puts it in the private read-only buffer pool. If the sequential reader has exhausted its quota of private read-only buffers, a private read-only buffer is replaced. The sequential reader maintains a list or chain of all its private buffers and uses a private LRU replacement mechanism identical to the public-shared buffer pool LRU replacement algorithm.

> All users, regular and sequential, have access to all buffers in the buffer pool (public or private). If a regular user needs a block found in a private buffer pool, the buffer is removed from the sequential readers list of private buffers and is put back into the LRU chain as the most recently used buffer. In addition, if a sequential read user needs to update a private read-only buffer, it is removed from the sequential reader’s private buffer pool and put into the general shared buffer pool as most recently used.

> Sequential reads use an index and require that index blocks be available in memory because they are used repeatedly. Therefore, you want to request enough private read-only buffers to hold all of the index blocks needed to retrieve a record. To determine how many private read-only buffers to set, count the number of tables that you read and determine the indexes you use. Then, determine the number of levels in the B-tree (balance tree) of each index and add 1 (for the record blocks). For example, request at least five private read-only buffers if you have a report that reads the `Customer` table using the `Cust-Name` index, and the `Cust-Name` index has four B-tree levels.

> If you do not know the number of levels in your index, you can generally request six private read-only buffers and get a good result. If you perform a join and are reading from two tables simultaneously, request 12. If the system is unable to allocate the requested number of private read-only buffers, a message is written to the database log.

> You can request a number of private read-only buffers using the Private Buffers (-`Bp`) startup parameter. When you use the -`Bp` startup parameter the request remains active for the entire session unless it is changed or disabled by an application. Each user of private read-only buffers reduces the number of public buffers (-`B`).

> Note: The total number of private read-only buffers for all simultaneous users is limited to 25% of the total blocks in database buffers. This value is set by the -`B` startup parameter. See Chapter 18 "Database Startup Parameters" for information on setting `-B`.

> You can also request a number of private read-only buffers from within a 4GL or SQL application by setting a value in the `_MyConn-NumSeqBuffers` field of the `_MyConnection` virtual system table (VST). Since `_MyConnection` is an updatable virtual system table, private read-only buffers can be dynamically requested and released in the application. For a description of the `_MyConnection` VST, see Chapter 25, " Virtual System Tables."

> The following 4GL code example demonstrates how to turn private read-only buffers on and off:

>     /*Get 6 private read-only buffers for my application*/
>     FIND _MyConnection.
>     _MyConnection._MyConn-NumSeqBuffers = 6.
>     /**** Report using private read only buffers ***/
>     /* Turn off private read only buffers of my application */
>     FIND _MyConnection.
>     _MyConnection._MyConn-NumSeqBuffers = 0.

> The following example demonstrates how to turn private read-only buffers on and off using an SQL statement:

>     UPDATE pub."_MyConnection" SET "_MyConn-NumSeqBuffers" = 6.
>     UPDATE pub."_MyConnection" SET "_MyConn-NumSeqBuffers" = 0.

> ##### Using APWs to improve performance

> APWs are optional, and require an Enterprise database license. APWs are highly recommended because they improve performance in the following ways:

> - They ensure that a supply of empty buffers is available so the database engine does not have to wait for database buffers to be written to disk.

> - They reduce the number of buffers that the engine must examine before writing a modified database buffer to disk. To keep the most active buffers in memory, the engine writes the least recently used buffers to disk; the engine must search buffers to determine which one is least recently used.

> - They reduce overhead associated with checkpointing because fewer modified buffers have to be written to disk when a checkpoint occurs.

> You must manually start APWs. You can start and stop APWs at any time without shutting down the database. See Chapter 3, "Starting Up and Shutting Down," for instructions on starting and stopping an APW.

> A database can have zero, one, or more APWs running simultaneously. The optimal number is highly dependent on your application and environment. Start two APWs and monitor their performance with PROMON. If there are buffers being flushed at checkpoints, add an additional APW and recheck. Applications that perform fewer changes to a database require fewer APWs.

> Note: If you perform no updates, no page writers are required.

> APWs are self-tuning. Once you determine how many APWs to run, you do not have to adjust any startup parameters specific to APWs. However, you might want to increase the BI cluster size to allow them to perform at an optimal rate. The PROUTIL TRUNCATE BI qualifier lets you create a BI cluster of a specific size. For more information, see Chapter 20, " PROUTIL Utility."

> APWs continually write modified buffers to disk, making it more likely the server will find an unmodified buffer without having to wait. To find modified buffers, an APW scans the Block Table (BKTBL) chain. The BKTBL chain is a linked list of BKTBL structures, each associated with a database buffer. Each BKTBL structure contains a flag indicating whether the associated buffer is modified. When an APW finds a modified buffer, it immediately writes the buffer to disk. Figure 13–5 illustrates how an APW scans the BLKTBL chain.

> Figure 13–5: Block Table (BLKTBL) chain

> The APW scans in cycles. After completing a cycle, the APW goes to sleep. When the APW begins its next scanning cycle, it picks up where it left off. For example, if the APW scanned buffers 1 to 10 during its first cycle, it would start at buffer 11 to begin its next cycle.

> When the database engine writes modified buffers to disk, it replaces the buffers in a least-to-most-recently-used order. This is beneficial because you are less likely to need older data.

> To find least recently used buffers, an APW scans the least recently used (LRU) chain. The least recently used chain is a doubly linked list in shared memory that the engine uses to access database buffers. The LRU chain is anchored by a data structure that points to the head and tail of the chain. Whenever a process accesses a database buffer, the server must lock and update the LRU anchor, moving the accessed buffer to the tail of the chain. Figure 13–6 illustrates the LRU chain.

> Figure 13–6: APWs and the least recently used chain

> Since all processes must lock the LRU anchor whenever they have to access a buffer, long buffer replacement searches create contention for all processes accessing the database buffer pool. This can have a debilitating effect on performance, especially on heavily loaded systems. APWs reduce contention for the LRU anchor by periodically clearing out modified buffers. When buffer replacement is required, the database engine can find an unmodified buffer quickly.

> A third way that APWs improve performance is by minimizing the overhead associated with before-image checkpointing.

> The before-image file is divided into clusters. A checkpoint occurs when a BI cluster becomes full. When a cluster becomes full, the database engine reuses the cluster if the information stored in it is no longer required. By reusing clusters, the engine minimizes the amount of disk space required for the BI file.

> Checkpoints ensure that clusters can be reused and that the database can be recovered in a reasonable amount of time. During a checkpoint, the engine writes all modified database buffers associated with the current cluster to disk. This is a substantial overhead, especially if you have large BI clusters and a large buffer pool. APWs minimize this overhead by periodically writing modified buffers to disk. When a checkpoint occurs, fewer buffers must be written.

> ##### Monitoring APWs

> The PROMON R&D option Page Writers Activity display shows statistics about APWs running on your system. Figure 13–7 shows a sample display.

>     01/25/00      Activity: Page Writers
>     16:29         from 01/25/00 13:56 to 01/26/00 11:23 (21 hrs 27 min)
>                                  Total   Per Min   Per Sec     Per Tx
>     Total DB writes              3       0         0.00        0.00
>     APW DB writes                0       0         0.00        0.00
>         scan writes              0       0         0.00        0.00
>         APW queue writes         0       0         0.00        0.00
>         ckp queue writes         0       0         0.00        0.00
>         scan cycles              0       0         0.00        0.00
>         buffers scanned          0       0         0.00        0.00
>         bfs checkpointed         173     0         0.11        0.14
>     Checkpoints                  82110   0         5.22        6.79
>     Marked at checkpoint         0       0         0.00        0.00
>     Flushed at checkpoint        0       0         0.00        0.00
>     Number of APWs:                           1

> Figure 13–7: PROMON Page Writers Activity display

> Note: Nonzero numbers in the Flushed at Checkpoint row indicates that the APW was unable to write buffers fast enough to prevent a memory flush. Increase the number of APWs and/or increase the cluster size to eliminate the flush.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095