Prologue:
DataFlex for Btrieve (originally sold under the name FLEXtrieve) is a version of the DataFlex runtime system which allows DataFlex applications to transparently access Novell Btrieve format files in a Client/Server design. This article is one in a series of customer experiences with the product.
Household Mortgage Services, a division of Household International Inc. * has never regretted their decision to move their large 400 user system to a PC LAN, and their recent migration to a DataFlex for Btrieve Client/Server solution has further strengthened the reliability of the system.
Household is a large user of the DataFlex based application Mortgageflex, which they use to process one of the countries largest home lending programs. Although satisfied with the abilities of the Mortgageflex application, the DataFlex file system with 280 or more simultaneous users was causing some difficulties.
First and foremost the file level locking implemented by DataFlex was causing increasing delays in system response due to the transaction loads on a small number of key transaction files.
The file locking method also created problems for the entire network when any single users' machine would lock-up with any type of error, it would leave the files locked and many terminals in a frozen state waiting for the lock to be released.
This would cause "downtime" of 15-20 minutes or more while the system administrators would find the problem machine and clear it from the system, then wait for possible index write errors or endless loops which would indicate index file corruption (caused by an incomplete write) occured during the user lockup. If it had occured, the system would be reindexed that evening with various third party tools.
The users would also cause the system to lock by using IRMA terminal emulators and other TSR's during the "pauses" in the system to check their e-mail. The pauses were during the locks for record updates.
Paul Sellnow, Senior Analyst for Household then discovered the FLEXtrieve system in FlexLines magazine, which later became DataFlex for Btrieve. Intrigued by its claims of never reindexing again, along with the additional benefits of Client/Server architecture, record locking and industry standard file formats, he ordered the package. Ten months of thorough full system tests were performed to insure that the system could handle the heavy workload and the number of users on the network.
First, they installed the system and began to get familiar with Btrieve on their test network of 150 users. Then tests were run to insure compatability with DataFlex programs. This was made more complex because the authors of Mortgageflex were not using the product and could not assist in the testing.
The goal was to change the Morgageflex system as little as possible so as to allow upgrades to happen easily. Fortunately, they had the source code to the system, and a very small number of changes were actually required. These primarily included:
They were impressed with DataFlex for Btrieve from the first installation, where it correctly ran nearly all of the Mortgageflex applications tested without change, although they did note that more memory was required, and began to plan for an upgrade to MS-DOS 6 and QEMM to make more memory available.
A full test was run on the test system over the weekend with 120 users on the Mortageflex system, and the system performed well in terms of performance and reliability. When it came time for the full installation however, some unexpected issues came up.
First of all, the system performed much slower under Btrieve than standard DataFlex files. It seemed that the linear progression of performance they expected from the test of 120 users did not match what was actually happening with 260 users. The slowest areas were in program chaining and reports.
Chaining between the 3000 Mortageflex programs (which is done very frequently) and 150 files was triggering anywhere between 10 and 80 open commands, which was putting a great load on the Btrieve NLM, which in turn was driving the 486/50mhz based file server to 100% utilization for most of the day.
Since many SPX requests for Btrieve file operations were being returned unanswered, it was clear that the file server was overloaded. It was also determined that, although the save's were happening faster than DataFlex, the OPEN commands were much slower. Novell was contacted about this, and began to work on a solution. Analysis with a Btrieve TSR debugger also found an extra Btrieve "STAT" call during each open command, which we quickly eliminated from DataFlex for Btrieve.
The nightly reports were running much slower compared to comparable DataFlex times, and this was at a time when there were very few users on the network. Since it appeared to run slower than the times from the test system, further research found that the full system PC's had much slower CPU speeds than the machines on the test system. In addition, data purges to bring the file size down to normal levels were not performed, causing much longer search times.
The next step was to upgrade the file server to a Compaq Prolinea Pentium 60mhz based machine with 140 megs of memory and 4 gigs of disk space. This change brought the average utilization to around 80%, but it still became overloaded at certain times during the day, and was not running very much faster.
Novell then provided a patched version of the Btrieve NLM which produced a noticable change in the speed of the open command, and lowered the server utilization a bit further, but the server overload continued.
Using a program called StatNLM they were able to determine that the requests for Btrieve services were coming in faster than they could be processed because of the high utilization, and they were able to correlate the utilization figures directly with the SPX requests to Btrieve. It was also found that the cache buffers were not performing as well as their usual 98% hit rate.
They then decided to add 2 additional Compaq Pentium machines with duplexed 2 gig drives in order to increase the speed by splitting the data across all three servers, allowing each Btrieve NLM to perform 33% of the requests originally required of the single NLM, and to allow for future growth.
They decided to split the data files across the servers based on usage, with the most used and highly linked files being split onto different machines in order to divide up as much of the Btrieve work as evenly as possible.
They then saw the clear performance improvement they had been hoping for, and the entire system worked overall faster than it had under standard DataFlex, except for a certain number of reports.
It was also determined that with 3 times the previous amount of cache memory, they had recovered the 98% cache hit rate, and their review of the console statistics showed that disk reads and writes were much smaller as a total of all three machines than it was on a single machine because of the ability to cache more disk. All of the 3 servers now ran at an average of 15-25% utilization, even during peak periods.
Since certain reports still run slower, they are re-writing some of them in C, using the direct Btrieve interface, and the ability to find multiple qualified records in each call. This is an ability that will be found in an upcoming release of DataFlex for Btrieve.
The key reason some reports may run slower under Btrieve is because of two factors, both related to the Client/Server design.
First, DataFlex is able to cache part of the index file in memory, where Btrieve, running on the server cannot.
In addition, Btrieve cannot tell which record you last requested, so it must traverse the index on every find, wheras DataFlex can hold it's place in the index for the next find command.
Although this causes the performance of the FIND command to be slightly slower, when it becomes the only operation, and is multipled by the RELATE command, it is a clearer difference than when it is combined with other normal processing operations.
Other reports are being run using the Crystal Pro report writer with NetWare SQL. They have found that using the NetWare SQL NLM to filter, select and sort data rather than using straight Btrieve has provided report times up to 30 times faster with Crystal Pro.
Crystal Pro was the users choice among the various report writers tested. It performed well, had an excellent Windows GUI user interface, used Btrieve files as a native file format, could schedule reports at night, and required no knowledge of SQL commands.
They had run tests on several report writers including IQ, Forest and Trees, FlexQL and R&R report writer, but liked WinQL the best. When they were unable to eliminate problems with date fields, they went to Crystal, which WinQL was based on, and found that the problem did not occur, and that they were also able to use NetWare SQL in addition to Btrieve, which made it perform better than the others.
They also run most user-query reports on a seperate server, onto which the entire database is written each evening. This copy is then written to tape. This unloads many reports from the main servers, as well as providing a measure of fault tolerance, since this backup server could be used in case of machine failure in place of any or all of the other 3.
Every few weeks, the network manager was required to increase the maximum records in a number of files, and then to reindex them, which took quite a while on the larger files. Since Btrieve does not require pre-allocation of the index, this chore is no longer required.
Household has been happy with the system since the server upgrades, and there have been no problems with file corruptions since the conversion. They have also found that data intensive operations such as batch purges are 5-6 times faster under Btrieve than under DataFlex when the DataFlex indexes were turned off. This allowed them for the first time to perform a recent purge while users were still in the system adding data.
They are next going to work directly with Novell to plan out a strategy for a multi-site shared database, where updates made at both sites can be transmitted via communication links. Although not exactly a distributed database, this will allow them to have a remote site kept up to date and to make occasional changes to certain files. They also plan to expand the use of NetWare SQL to speed up reports and mass update programs running locally and from remote offices.
Epilogue:
It may seem by reading this article that there are many difficulties in tuning the performance of Btrieve for large systems, but I would submit to the reader that most of our customers have very few problems in this area, and this article was written to show an extreme example.
The developers of DataFlex for Btrieve and Data Access are learning more every day about how to improve the speed of Btrieve, but it is helpful to remember that DataFlex itself is still one of the faster database systems available, and in a few areas it may out-perform Btrieve on certain hardware.
Brad Waddell
FLEXquarters Software
602-732-9217
info@FLEXquarters.com
http://www.flexquarters.com/main
Copyright © Madis Kaal 2000-