Explaining TurboIMAGE Prefetch

By Ken Paul
The Adager Support Team
Awhile ago on the HP3000-L internet list/newsgroup, Randy Smith posted the following questions:

" Would someone explain TurboImage Prefetch?

I was reading over the latest Robelle What's Up Doc, and it talked about increasing TurboImage performance by using pre-fetch.

1) I can infer many things from the term prefetch as applied to a database, but what does it mean exactly for Image?

2) What are the benefits?

3) What are the drawbacks?

4) Are there any gotchas?

We have a 958 with 128 meg memory. With between 14%-34% disc space free with our several databases. One of the datasets has about 6,000,000 entries."

In order to answer these questions I will need to give some background.

First, Prefetch is a flag that can be ENABLED for your database via DBUTIL. It was introduced in TurboIMAGE version C.03.13 which corresponds to MPE/iX version 3.1. By default this flag is DISABLED for your TurboIMAGE databases and must be explicitly ENABLED using DBUTIL.

As Randy mentions, the term "Prefetch" can have many different meanings and this case is no exception. Usually Prefetch refers to file I/O, where the system is going out to disc to get more information before you ask for it. This type of Prefetch can greatly enhance the performance of your programs especially if the data that is being Prefetched by the system is indeed the data your application needs. This usually is very beneficial with a process that is doing serial reads of a file or dataset.

Unfortunately, this is not what is meant by the TurboIMAGE Prefetch flag.

TurboIMAGE is currently 'single threaded' when it comes to doing DBPUTS, DBDELETES and most recently Critical Item DBUPDATES. This means that only ONE person is actually adding or deleting or updating a critical item at a time. In order to accomplish this, TurboIMAGE uses a semaphore lock known as the PUT/DELETE semaphore. This lock is applied at the beginning of the transaction and released at the end while several blocks are updated within the database which ensures data integrity.

The Prefetch flag tells TurboIMAGE when to apply this semaphore lock. Before Prefetch and with Prefetch disabled, TurboIMAGE locks the PUT/DELETE semaphore before reading, updating and writing every data block which is modified by the transaction. After this is done the PUT/DELETE semaphore is unlocked.

With Prefetch enabled, TurboIMAGE reads all the data blcoks that are needed for the complete transaction before the PUT/DELETE semaphore is locked. Once the semaphore is locked, TurboIMAGE updates and writes the data blocks which are modified by the transaction before unlocking the semaphore.

It is the holding of the semaphore lock for a shorter period of time which could cause more throughput of data on your system but it is not gauranteed. There is the possibility that when the data is read or 'Prefetched' before the semaphore lock is applied that it may be flushed out of memory by another process before TurboIMAGE is able to update it. If this is the case, TurboIMAGE must go back out to disc to read in the block again which could have a negative impact on your database performance.

The Prefetch flag has always been a 'your mileage may vary' flag since its introduction. Certain conditions need to exist on your system in order to possibly gain benefit from turning on this flag.

First, the system should have 'adequate' (a wonderfully nebulous word) memory available to handle the increased data page locality as well as adequate processor capability to handle increased concurrency of processes. Second, the application should make numerous calls to DBPUT, DBDELETE or most recently DBUPDATE to critical items. Third, multiple users must be processing data before a benefit can be realized.

The HP Database Lab has said that they have seen 5-10% improvements in the number of transactions that could be processed in benchmarks as a result of enabling Prefetch. They also mention that it helps in situations where you have excess CPU time and you are running update (I think they mean DBPUT/DBDELETE/CIUDBUPDATE) intensive applications.

The 35% performance improvement that is mentioned in the Robelle What's Up Doc is news to me and is why I consider the Prefetch flag a 'try it you might like it' flag.

The only problems that I am aware of regarding Prefetch was a problem under TurboIMAGE version C.04.06 thru C.04.08 when DBPUTS to a detail dataset were failing because this flag was set and there were some uninitialized variables which caused problems. This problem, however, did NOT cause database corruption. The fix was to disable the database for Prefetch until the next version of TurboIMAGE was released via a patch.

Recently I also ran into a sight which was experiencing very bizarre messages of 'DBG disabled' while opening a database which could not be traced back to broken chains. After analyzing the database and finding no broken chains we noticed that Prefetch was enabled and upon disabling it the problem disappeared. This was on TurboIMAGE version C.04.19. This database also had 179 datasets.

My suggestion on the Prefetch flag is enable it and see if your environment changes noticably. It can always be turned off if any problems appear.

I would like to thank Marguerite Bryan of the HP Response Center and Jim Sartain formerly of the HP Database Lab for information which was used in this article.

HP's Response Center also noted in an update on IMAGE databases:

When should prefetch actually be used? As per Marguerite Bryan of the HP Response Center in issue #20 of the IMAGE Support Newsletter, you may use prefetch when

1. The system has adequate memory available to manage the increased data page locality as well as adequate processor capability to handle increased concurrency of processes.

2. The application makes numerous calls to DBPUT, DBDELETE, or most recently DBUPDATE to critical items. Other TurboIMAGE intrinsics only read or update data, not pointers or counters where multi-block access is a real issue.

3. As PREFETCH is intended to increase database concurrency, multiple users must be processing data before the benefit can be realized. Prefetching data blocks offer little or no benefit to single processes executing against a database.

The problems have been found in TurboIMAGE versions C.04.06 through C.04.08 on MPE/iX release 4.0. This problem is documented in detail in SR 5000-668673 and can be fixed by installing the patch TIXFX04 (TurboIMAGE C.04.09).