Performance Enhancement

The transactional interface provides the following features for enhancing performance:

•

System Transactions

•

•

•

To gain better performance and to aid data recovery, the transactional interface includes one or more committed operations (both transactional and non-transactional) into a bundle of operations called a system transaction. The transactional database engine creates a system transaction bundle for each file. A system transaction can contain operations and user transactions from one or more clients running on the same engine.

Note: Do not confuse system transactions with exclusive or concurrent transactions. Throughout this manual, the term transaction refers to an exclusive or concurrent transaction (also known as a user transaction). User transactions affect how changes become incorporated into the pages in cache, while system transactions affect how dirty pages in cache become a part of the files on disk. The transactional database engine controls the initiation and process of system transactions.

Both user transactions and system transactions are atomic. In other words, they happen in such a way that either all or none of the changes occur. If a system failure occurs, the transactional database engine recovers all files involved in the failed system transaction as it reopens the files. All changes made during a failed system transaction (that is, all operations to a file by all clients on that engine since the last completed system transaction) are lost; however, the file is restored to a consistent state, enabling the operations to be attempted again after resolving the cause of the system failure.

Pervasive PSQL guarantees transaction durability for all loggable files except those opened in Accelerated mode. (A file can be logged if it contains at least one unique, or non-duplicatable, key. The key can be system-defined.) Transaction durability is the assurance that before the transactional database engine returns a successful status code to the client for an End Transaction it finishes writing to the transaction log. When you open a file in Accelerated mode, the transactional interface does not log the file; therefore, the transactional database engine does not log entries to the file. Therefore, the transactional database engine cannot guarantee transaction durability for that file.

After the transactional database engine rolls back a file’s system transaction, it replays the log when it next opens the file. Doing so restores those committed operations that were stored in the log but were not written to the file because of the system transaction rollback.

Each system transaction consists of two phases: preparation and writing.

Preparation Phase

During the preparation phase, the transactional database engine executes all operations in the current system transaction, but writes no pages to the files. The transactional database engine reads uncached pages from the files as needed and creates new page images only in cache.

Any of the following actions can trigger the end of the preparation phase, which marks the beginning of the writing phase:

•

The transactional database engine reaches the Operation Bundle Limit.

•

The transactional database engine reaches the Initiation Time Limit.

•

The ratio of pages prepared for writing to the total number of cache pages reaches a system threshold.

Note: Generally, the preparation phase ends after a completed transactional interface operation. However, it is possible that the time limit or the cache threshold could be reached during an incomplete user transaction; the transactional interface switches to the writing phase, regardless.

Writing Phase

During the writing phase, the transactional database engine writes to disk all pages prepared in the preparation phase. It first writes all data, index, and variable pages. These are actually shadow pages. While these are being written, the consistency of the files on disk remains the same.

However, the critical part of the system transaction occurs while the PAT pages are being written, because they point to the shadow page as the current page. To protect this phase, the transactional database engine writes a flag in the FCR. When all PAT pages are written, the final FCR is written and the file is now consistent. If a system failure happens during this phase, the transactional database engine recognizes it the next time the file is opened and rolls the file back to the previous state. Then, all durable user transactions in the transaction log file will be implemented in the file.

Frequency of System Transactions

Less Frequent

Doing system transactions less often provides a performance benefit to most configurations. These include client/server, single engine workstation and multi-engine workstation environments where files are opened exclusively.

When the transactional database engine initiates system transactions less often, dirty pages, or pages that need to be written, stay in memory longer. If the application is doing a lot of change operations, these pages might get updated multiple times before being written to disk. This means fewer disk writes. In fact, the most efficient engine is one that writes only when it must.

There are three limits that, when reached, can cause the system transactions to be initiated: Operation Bundle Limit, Cache Size, and Initiation Time Limit. When any of these limits are reached, the transactional database engine initiates a system transaction. See Advanced Operations Guide for more information about these settings.

The best way to do system transactions less often is to set the Operation Bundle Limit and Initiation Time Limit to higher values. You can also increase the size of the cache.

More Frequent

A disadvantage of causing the transactional database engine to perform fewer system transactions is more data in the machine’s memory at any point in time that needs to be written to disk. If a system failure occurs, such as a power outage, more data is lost. Although the transactional database engine is designed to keep files in a consistent usable state, that state may not include the most recent changes. Of course, the use of user transactions with transaction durability will minimize this risk. You should carefully consider the risks of decreasing the frequency of system transactions versus the performance gains.

For example, if your application is using a workstation engine to update a remote file over a slow or non-reliable network connection, you should perform system transactions often so that changes are put onto disk at soon as possible.

Memory Management

The cache is an area of memory the transactional database engine reserves for buffering the pages that it reads. When an application requests a record, the transactional database engine first checks the cache to see if the page containing that record is already in memory. If so, the transactional database engine transfers the record from the cache to the application’s data buffer. If the page is not in the cache, the transactional database engine reads the page from the disk into a cache buffer before transferring the requested record to the application. The transactional database engine cache is shared by local clients and used across multiple operations.

If every cache buffer is full when the transactional database engine attempts to transfer a new page into memory, a leastrecentlyused (LRU) algorithm determines which page in the cache the otransactional veinterfacerwrites. The LRU algorithm reduces processing time by keeping the most recently referenced pages in memory.

When the application inserts or updates a record, the transactional database engine first makes a shadow image of the corresponding page, modifies the page in the cache, and then writes that page to disk. The modified page remains in the cache until the LRU algorithm determines that the transactional database engine can overwrite the image of that page in cache with a new page.

Generally, a larger cache improves performance because it enables more pages to be in memory at a given time. The transactional database engine enables you to specify the amount of memory to reserve for the I/O cache buffers. To determine this amount, consider the application’s memory requirements, the total memory installed on your computer, and the combined size of all files that all concurrent Pervasive PSQL applications will access. The configuration setting for this cache is “Cache Allocation Size.”

In Pervasive PSQL V8 and later releases, a secondary dynamic L2 cache is also available. The configuration setting for this dynamic cache is “Max transactional database engine Memory Usage.” See Advanced Operations Guide for more information on configuring these settings.

Note: Increasing cache above the available physical memory can actually cause a significant performance decrease because part of the cache memory in virtual memory will be swapped out onto disk. It is recommended that you set the transactional database engine cache to about 60 percent of available physical memory after the operating system is loaded.

To find this value on Windows NT, for example, right click on the clock in the taskbar and select Task Manager. Select the Performance tab to see the available physical memory near the bottom right of the dialog box.

Page Preallocation

Page pre allocation guarantees that disk space is available when the transactional database engine needs it. You can enhance the speed of file operations if a data file occupies a contiguous area on the disk. The increase in speed is most noticeable on very large files. For more information about this feature, refer to Page Preallocation.

Extended Operations

Using the extended operations—Get Next Extended (36), Get Previous Extended (37), Step Next Extended (38), Step Previous Extended (39), and Insert Extended (40)—can greatly improve performance. Extended operations can reduce the number of transactional interface requests by 100 to 1 or more, depending on the application. These operations have the ability to filter the records returned so that records not needed by the application are not sent to it. This optimization technique has the best results in client/server environments that have to send data back and forth over a network.

See Multi-Record Operations for detailed information on these operations.