For those interested in Ken Paul's article (which appeared in the HP Chronicle, September 1996), here it is. I'll also include it in Adager's web site in a couple of days, under "Technical Papers": ------------------------------- Understanding Prefetch in IMAGE ------------------------------- By Ken Paul Adager Corporation P.O. Box 3000 Sun Valley, Idaho 83353-3000 U.S.A. [log in to unmask] http://www.adager.com Telephone (208) 726-9100, Fax (208) 726-8191 Not so long ago on the HP3000-L Internet list/newsgroup, Randy Smith posted the following questions: Subject: 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? Randy has a 958 with 128 megabytes of 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 blocks 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-intensive applications (I think they mean intensive in terms of DBPUT/DBDELETE/CriticalItemDBUPDATE). The 35% performance improvement that is mentioned in the Robelle "What's Up DOCumentation" is news to me. 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 site 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. Ken