Review by John Burke

When I was a developer and programmer of application systems, we used to comment somewhat wryly that the design of the database and access methods was more art than science. Experience was critical because most design “rules” were based upon anecdotal evidence at best and urban legend at worst. When performance problems developed in production that were not evidenced during testing we were often reduced to desk-checking code and access algorithms looking for potential bottlenecks. Sometimes, we even wrote little capture/debug routines to test what was going on. But what we really needed was a general-purpose tool that would allow us to create performance snapshots and allow us to view the inner workings of our access algorithms.

When I arrived at my current career stop, I found homegrown systems employing dozens of databases and probably millions of lines of code all developed over a 15-year span. If we experienced performance problems I was flat out of luck. We had no baseline data. We had no tools to even develop any baseline data. All we had to go on were the user complaints that the system was running “slow”. I then had to relay this “information” to those developers still with the company. At which point, what we mostly did was stand around and scratch our heads a lot. Again, what we needed was a general-purpose tool that would allow us to look at the inner workings of our systems.

That tool is here now, and it is called DBGauge.

What Does It Do?

DBGauge works by capturing all user database subroutine calls (DBINFO, DBOPEN, DBGET, DBUPDATE, DBPUT, etc.) and recording in a message file the amount of CPU and wall time elapsed between the initial call and its completion. The reporting function displays for each intrinsic/mode/dataset/database combination the number of times it was called, total CPU and wall time, average CPU and wall time and shortest and longest CPU and wall time during the test period. Instead of a printed report, you can get a semi-colon delimited, quoted string output file of data suitable for import directly into your favorite spreadsheet program.

How Does It Work?

DBGauge uses two XL files that must be added to a program’s XL list (alternatively, the product comes with the object files for the two XLs allowing you to incorporate them into your existing XLs). The first XL captures all of the database calls made by the application and any XL subprograms immediately preceding it in the XL list. The placement of this XL in the application’s XL list determines which database calls are reported and which are not providing some granularity in complex systems. The DBGauge XLs must be placed in the XL list before any special capability XLs (i.e. PM XLs, 3rd-party XLs, etc.). The second XL basically completes the trap cycle preventing looping. The DBGauge trap routines write timing transactions to a message file which is then processed by a program (either manually or automatically) into a cumulative statistical file. Utility programs then format the information into printed reports or files suitable for import into spreadsheet programs. The existence of a flag file can be used to control the stopping and starting of the timing transaction.

Installation and Documentation

DBGauge is supported on MPE/iX 5.0 and all later versions. My test machine was on the 6.0 PP1 release of MPE/iX (TurboIMAGE C.07.21). The product that is delivered supports Native Mode (NM) programs only. [Support for Compatibility Mode (CM) is said to be available on request, but was not tested.] No Privileged Mode (PM) code is required.

Installation was a snap and only took a few minutes. It is the usual RESTORE from tape (the only way the product is currently delivered) an install job to PUB.SYS which is STREAMed as MANAGER.SYS, creates the appropriate account structure and RESTOREs everything else. T.RenzSoft has added a few nice touches to this common scenario. The install job contains several CI variables you can set that control how the product is installed:

• Not only is installation on the system volume set NOT required, but all you need do is set a CI variable with the name of the desired user volume set and the install job does all the rest.

• If your site’s standards specify something other than “MGR” for the account manager user of the TRENZSFT account, just set the appropriate CI variable to whatever you want and the install job will take care of the rest.

• There are CI variables for all passwords.

• And, a flag can be set in the install job that will cause the job file to be purged upon successful completion of the install job.

I wish more vendors supplied such a flexible installation script.

The “DBGauge for the HP 3000” reference manual and the “Installation guide for your T.RenzSoft Products” are on the installation tape in MS Word 6.0/95 format (a hard copy of the installation guide is delivered with the product tape). They are RESTOREd into the DOC.TRENZSFT group by the installation job. Here is another neat touch: if you have Samba running on your target system, you can directly open both files into MS Word. If not, simply transfer the files in BINARY format to your PC using your favorite terminal emulator.

The reference manual is complete in that it shows you all you need to use the product. However, in a future version of the reference manual, I would like to see some examples of statistics from test runs and an interpretation of the values.

A Simple Test Drive

One thing I tried was a simple little test with QUERY. First I had to build three files and add an entry in one for each database I wanted to monitor. Then I loaded a fairly good-sized database (93 sets), selected a dataset, did a “find all” and finished up with a little report that just counts the number of entries.

:run query.pub.sys;xl=”gaugexl1.dbgauge.trenzsft, gaugexl2.dbgauge.trenzsft”

HP32216N.03.14 QUERY/NM
WED, APR 26, 2000, 6:25 AM

>b=ledger.pub.money
PASSWORD = >>
MODE = >>1
>s=hazardous-part
>f all part
USING SERIAL READ
646 ENTRIES QUALIFIED
>r
>>e1,”zzz9”
>>r1,add,”1”
>>tf,r1,10,e1
>>end

646
>exit

END OF PROGRAM

Not much, right? To gather the statistics, you first run a collector program. It reads the raw data deposited by the trap routines in a message file, organizes it and writes it to an intermediate file for further processing. At this point you may choose to generate a printed report, or create a semi colon delimited, quoted string file for import into your favorite spreadsheet. The intermediate file may be either purged and re-created for the next test run, or left to accumulate running totals over numerous test runs. Figure 1 shows the statistics generated by DBGauge for my simple test. Note the output has been truncated and reformatted slightly to better fit the page.

                        For File Name:
"/SYSMGR/PUB/GAUGENUM" as of WED, APR 26, 2000,  6:30 AM;
   ===>>[ # of Unique Processes ==>>0; # of DB Calls
received ==>>0]
   **Note: The above numbers may be approximate if the job was not
shutdown properly!!!

For database "LEDGER";

   DBCLOSE(1) :   1 CT: 0.070 CA: 0.070 CS: 0.070 CL: 0.070 WT:
0.144 WA: 0.144 WS: 0.144 WL: 0.14
   DBINFO     :  12 CT: 0.027 CA: 0.002 CS: 0.000 CL: 0.011 WT:
0.026 WA: 0.002 WS: 0.000 WL: 0.01
   DBOPEN(1)  :   1 CT: 0.435 CA: 0.435 CS: 0.435 CL: 0.435 WT:
0.689 WA: 0.689 WS: 0.689 WL: 0.68
   DBUNLOCK   :   1 CT: 0.001 CA: 0.001 CS: 0.001 CL: 0.001 WT:
0.001 WA: 0.001 WS: 0.001 WL: 0.00

*****For database.data-set "LEDGER. (#0)";

   DBLOCK(6)  :   1 CT: 0.001 CA: 0.001 CS: 0.001 CL: 0.001 WT:
0.002 WA: 0.002 WS: 0.002 WL: 0.00

*****For database.data-set "LEDGER.HAZARDOUS-PART (#62)";

   DBCLOSE(3) :   1 CT: 0.001 CA: 0.001 CS: 0.001 CL: 0.001 WT:
0.001 WA: 0.001 WS: 0.001 WL: 0.00
   DBGET(2)   : 647 CT: 0.733 CA: 0.001 CS: 0.001 CL: 0.004 WT:
0.660 WA: 0.001 WS: 0.000 WL: 0.00
   DBGET(4)   : 647 CT: 0.715 CA: 0.001 CS: 0.001 CL: 0.003 WT:
0.653 WA: 0.001 WS: 0.000 WL: 0.02

For DBINFO, mode #101 for each database;

   LEDGER     :   1 CT: 0.002 CA: 0.002 CS: 0.002 CL: 0.002 WT:
0.002 WA: 0.002 WS: 0.002 WL: 0.00

For DBINFO, mode #113 for each database;

   LEDGER     :   1 CT: 0.001 CA: 0.001 CS: 0.001 CL: 0.001 WT:
0.000 WA: 0.000 WS: 0.000 WL: 0.00

For DBINFO, mode #201 for each database;

   LEDGER     :   1 CT: 0.001 CA: 0.001 CS: 0.001 CL: 0.001 WT:
0.002 WA: 0.002 WS: 0.002 WL: 0.00

For DBINFO, mode #204 for each database;

   LEDGER     :   1 CT: 0.003 CA: 0.003 CS: 0.003 CL: 0.003 WT:
0.003 WA: 0.003 WS: 0.003 WL: 0.00

Each intrinsic called (with mode value if appropriate) for each set/database is listed with a count of the number of times called, followed by timing statistics:

CT = Total CPU accumulated for all of the calls

CA = Average CPU usage per call

CS = Shortest CPU time

CL = Longest CPU time

WT = Total wall time accumulated for all the calls

WA = Average wall time for each call

WS = Shortest wall time

WL = Longest wall time

Many of the statistics, particularly short and long times, are meaningless in this example because of the trivialness of the task. There is another example report from a more meaningful run (Amisys) at the T.RenzSoft Web site, www.trenzsoft.com.

Q&A with the Developer, Tom Renz

Q: Does DBGauge play well in a Netbase/Shareplex configuration?

A: Yes. To use DBGauge, you put the DBGauge XLs in your program’s XL list. The Netbase XL routines reside in XL.PUB.SYS, which in turn call the actual system intrinsics in NL.PUB.SYS. I designed DBGauge with Netbase environments in mind — this is why we do not install anything in the system XLs.

Q: How much overhead does DBGauge add?

A: We have seen it add around 10 percent to the application. We expect to improve on this in the next release.

It is interesting to see how fast the collector file (a message file) will grow depending on the program used. You can actually see the activity the program is creating while it is running. QUERY, for example, will issue a lot of DBINFO calls, especially when you do a FORM command. Even when you do not expect it (note: see the example and figure 1). DBGauge tracks all of the IMAGE intrinsics calls and their modes. I built into the program a checkpoint if the collector file becomes 75 percent full. DBGauge will stop writing records to the file until the reader process has read and processed enough records. Generally, when you are using the DBGauge collector intrinsics you will also run the reader program, either as a child process or a stand-alone program to read the message file and keep up with application. General-purpose, do-all programs (Powerhouse, Speedware, QUERY, etc.) will generate a lot more calls than the typical special-purpose program written by an internal staff or third-party developers. DBGauge will show you exactly what the program/application is doing, not just track update transactions as might be available by monitoring a database log file. Generally, applications spend most of their time (80-90 percent) doing read and info type transactions as opposed to update transactions.

Q: I understand how DBGauge can really help in the development and troubleshooting of systems, but how does it help with third-party applications that the user has little control over?

A: DBGauge can help third-party application providers improve their code and database design. When put into a user site, DBGauge can help identify the inevitable problem areas that crop up in heavily stressed production environments that do not show up in test environments. Also, some systems, such as AMISYS, are highly configurable/customizable. These systems can only be “clocked” in a user production environment. AMISYS generally works well with the simple, straightforward configuration that drives the main data. As more and more configuration is done, the system may slow down to the point that performance is unacceptable. If DBGauge is used to take before and after snapshots around major configuration changes, the cause of the slowness can be pinpointed and corrective action taken.

In any development/QA cycle, the use of DBGauge snapshots will greatly speed up the process of isolating any performance issues. Other uses include clocking how long it takes the system to create a new “chunk” when a database uses DDX or MDX, or, the impact of adding TPI or, the impact of sorted paths and long sort chains.

DBGauge can potentially save you hundreds of thousands of dollars in hardware upgrade charges by identifying correctable design and coding problems that are adversely affecting performance. With DBGauge you can quantitatively evaluate the effect of repacking a detail dataset or adding additional indexes.

Conclusion