Improving DBO/VERIFY Performance (DBMS only)
TECHNICAL ARTICLES -> MAINTENANCE ARTICLES
[ Next ] [ Printer Friendly Version ]

DBO/VERIFY - It's the tool we love to hate! We love it because it gives us confidence that our database is free of internal corruption. We hate it because it is a strain on our system resources.

THE CASE FOR RUNNING DBO/VERIFY

DBMS maintains integrity controls across all VMS environments using automatic detection, rollback and recovery of incomplete transactions with recovery unit journals, the distributed lock manager and database recovery processes if there is a complete system failure.

Despite the integrity mechanisms built into DBMS to safeguard your database, corruption is still possible. Database corruption can result from hardware failures, media failures, software failures or user actions.

RECOVERING FROM CORRUPTION

Although the focus of this article is not about "recovering from corruption," a few words on this topic are worth mentioning. The most critical aspect of recovering from database corruption is quick identification of the corruption, access to valid backups and the use of After Image Journaling (AIJ). The performance impact of AIJs is negligible when compared to the cost and risk associated with manually reentering transactions (since the last valid backup) or low-level patching of corrupted databases.

Verifying your DBMS database is NOT AN OPTION. It must be done! However, performing a complete verification of your database using the "/SETS" option can prove to be a time and resource drain on your system. Therefore, it seems only reasonable that besides optimizing our application, we must also optimize the database verifications.

IMPROVING VERIFICATION PERFORMANCE

On the surface, DBO/VERIFY does not offer any tuning or optimization parameters. You ask, "How can we optimize DBO/VERIFY performance?" This is the topic of this article.

If you are running DBMS V6.1 or higher then you are in luck (if not, consider an upgrade). In DBMS V6.1, the DBMS engineers implemented a verification algorithm developed by Bryan Holland of Software Concepts Int'l that dramatically improved the quality and speed of verifying DBMS databases. The new algorithm performs a full set-verification by sequentially reading the storage areas rather than physically walking each set. Typical results of implementing SCI’s algorithm show a 10 times improvement in verification performance – while performing a more thorough verification! To gain the benefits of this feature, you will need plenty of disk space…and will have to add the “/OPTIMIZE” qualifier to your DBO/VERIFY command.

NOT YET ON DBMS v6.1 (or higher)?

If you haven’t upgraded to v6.1 or higher, there are still some things you can do to improve the performance of your DBMS verification.

Step 1

The first step in tuning DBO/VERIFY is to make certain that you start with a well-tuned database. If your database is performing poorly, and it is inefficient to access the data in your database, DBO/VERIFY will perform poorly. That is not to say that you cannot improve the performance of DBO/VERIFY for a poorly tuned database, but you will never achieve an optimal level of performance.

INCREASE BUFFERING

Verification is generally I/O intensive. One of the easiest ways to improve the performance of DBO/VERIFY is to use a large number of buffers. Increasing the number of buffers, increases the likelihood that a page will already be in the buffer pool if it is accessed a second time.

Although there are many ways to increase the number of buffers for verification, the preferable method for increasing buffers (for verification) is to define the process logical name DBM$BIND_BUFFERS. The goal is to verify the database with the fewest I/Os using the fewest buffers.

A second aspect to buffering, is modifying the length of the buffers used by verification. Increasing the buffer length will cause more database pages to be read into the buffer pool with each I/O, and can significantly improve the performance of large sequential reads. Depending on your database environment, increasing the size of buffers using DBO/MODIFY/LENGTH can improve database performance.

Unfortunately, there is not a process logical name that will allow you to modify the buffer length at bind-time. Therefore, unless you can run your verifications standalone, you should set the value of LENGTH to that will optimize the overall performance of your database application.

MULTITHREAD VERIFICATION

Partitioning the verifications by area and performing the verifications in parallel, can yield significant performance improvements. This allows you to tailor the performance of verifications to fully use the computing power and bandwidth available. In general, you will want to submit additional verifications (in parallel) until you saturate the I/O bandwidth or the CPU resources across your cluster. Submit parallel verifications in descending order of the time required to verify the area.

SYSTEM AND PROCESS QUOTAS

Improving performance of any application, is really a matter of efficiently using all available resources. This typically means using more memory and CPU to minimize I/O. To fully realize these benefits, we must first make certain that our process can use the available memory.

System (SYSGEN) parameters

GBLPAGCNT

GBLPAGFIL

VIRTUALPAGECNT

WSMAX

Process (UAF) parameters

ASTLM

BYTLM

DIOLM

ENQLM

PGFLQUOTA

WSEXTENT

WSQUOTA

BOOST PROCESS PRIORITY

Thus far, we have looked at ways to improve verification performance by reducing I/Os at the expense of memory and CPU usage. If we accomplished our previous objectives, we should begin reaching a CPU bottleneck. To give verification an edge over other processes on the system, increase the priority of the verification process to use a larger portion of the CPU cycles.

ONLINE VERIFICATION

If your verifications still run too long, or if you require 7-by-24 up time, ONLINE verifications allow you to verify your database concurrently with other processes accessing the database.

REDUCE VERIFICATION LEVEL

As a last resort, you can try reducing the integrity level of the verification. By verifying only the PAGE or SEGMENT structures, you can significantly reduce the overhead associated with verification. However, PAGE and SEGMENT verifications also significantly reduce the integrity level of verification by completely bypassing the validation of the set-pointers. Although your verifications will complete faster, you failed to check the most critical aspect of a DBMS database – the set pointers.

INCREMENTAL VERIFICATIONS

Incremental verifications are a quick and efficient way to verify those database pages that have changed since the last full or incremental verification. Incremental verifications take considerably less time than a full verification, allowing you to perform incremental verifications more frequently than you would full verification. If possible, it makes sense to perform incremental verification daily, and full verifications before your full backups.

MONITORING VERIFICATION PERFORMANCE

It is possible to monitor the performance of DBO/VERIFY with such tools as:

• VMS monitor to determine possible CPU bottlenecks.

• DCL SHOW PROCESS/MEMORY to monitor memory usage

• DBO/SHOW STATS to analyze data file I/O operations and stalls to determine possible disk bottlenecks.

Using these tools, it is possible to determine what are the bottlenecks when running DBO/VERIFY. You can then take corrective action to improve the performance of verification. As in any tuning exercise, this requires that you make changes incrementally, monitor and record the results.

STILL NOT ENOUGH...

At some point in the process, you may determine that you are fully using all CPU cycles all available I/O bandwidth. If you still require greater throughput for your verifications, you will need to consider buying more memory and CPU cycles. As your local Compaq/HP sales representative will tell you, memory and CPU cycles are cheap.

SUMMARY

Although DBO/VERIFY does not offer any tuning parameters, you still have significant control over the performance of your verifications, including:

• Start with a well-tuned database.

• Increase the number of buffers – DBM$BIND_BUFFERS

• Increase the size of the buffers DBO/MODIFY LEN=n

• Multi-threaded VERIFY.

• Tune system and process quotas for optimal efficiency.

• Increase the priority of the verification process.

• Perform incremental verifications.

• Use /ONLINE verifications to allow concurrent access to the database during verification.

• Reduce the level of verification and perform "/SET" verification only on those pages with checksum or segment errors.

Although DBO/VERIFY does not offer any tuning "parameters" (other than the “/OPTIMIZE” qualifier added in v6.1), knowing how to exploit the general tuning capabilities of DBMS gives you tremendous control over DBO/VERIFY performance.