|
||||||
| DATABASE ADMINISTRATION |
Improving DBO/VERIFY Performance (DBMS only)
TECHNICAL
ARTICLES -> MAINTENANCE
ARTICLES
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.