Mission Critical Computing Blog
Your source for the latest insights on HP Integrity, mission critical computing, and other relevant server and technology topics from the BCS team.

Defrag NonStop Database the Easy Way and Improve Application Performance and Disk Space Utilization

You must have experienced on your Windows PC that the disks, as they get used, become fragmented over time, which also results in poor application performance. There are tools to determine when the disks are fragmented and to reorganize the data to improve disk space utilization and application performance.

 

On a database system, the same general concept applies. Over time, the data has to be reloaded to improve application performance and disk space utilization. The question is - when is the right time to reload the data?

 

On HP Integrity NonStop systems, FUP RELOAD physically reorganizes key-sequenced Enscribe files as well as SQL/MP and SQL/MX tables and indexes that are disorganized. However, FUP RELOAD is not a procedure that should be used indiscriminately. Determining when to reload is difficult.

 

Reload Analyzer (TRA) is a database management tool for NonStop systems that determines when to reload files, tables, indexes or partitions. TRA performs data-block chaining and fragmentation analysis, and generates FUP RELOAD commands, if the percentage of average blocks per chain is below a user-specified threshold (for example, if the percentage of average blocks per chain is under 5%).

 

TRA performs both vertical analysis of index blocks and horizontal analysis of data blocks, as explained later. TRA supports NonStop Enscribe, SQL/MP and SQL/MX databases. It supports all index and data block sizes, including 32kb block size tables. It can be run any time to analyze a single file, a single partition, all partitions, or a user-specified batch list of files.

 

What does Reload Analyzer compute?

Reload Analyzer computes and displays the following statistics:

  • Total number of data-block chains
  • Length of longest data-block chain
  • Length of shortest data-block chain
  • Histogram of blocks per chain
  • Average number of blocks per chain

 

Ideally, the percentage of total blocks in average chain should be 100%. One physical chain is optimum.

 

Reload Analyzer performs vertical index-block analysis

Too many vertical index-blocks cause poor random access. Reload Analyzer performs vertical index-block analysis to make the random access performance better.

 

Reload Analyzer performs horizontal data-block analysis

Too many short, broken (non-adjacent) horizontal data-block chains cause excessive physical I/O and/or seeks, and thus, very poor sequential access (up to 128 times slower). Disorganized files cannot take advantage of bulk reads for sequential pre-fetch and bulk writes for sequential updates in files. Reload Analyzer performs in-depth horizontal data-block analysis to make the sequential access performance better.

 

If you are not already using TRA, I strongly advise you to try it out, and see how it can improve your application performance and disk space utilization by determining when files and tables must be organized. 

Leave a Comment

We encourage you to share your comments on this post. Comments are moderated and will be reviewed
and posted as promptly as possible during regular business hours

To ensure your comment is published, be sure to follow the community guidelines.

Be sure to enter a unique name. You can't reuse a name that's already in use.
Be sure to enter a unique email address. You can't reuse an email address that's already in use.
Type the characters you see in the picture above.Type the words you hear.
Search
About the Author
Vinay Gupta is an HP Distinguished Technologist and the NonStop Manageability Architect. He joined Tandem in 1994 after graduating from Indi...


Follow Us
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation