The Question is:
Respecting FDLs for indexed RMS files, I have been instructed for years that the
bucket sizes for all AREAs, once the bucket size for the primary key has been
selected, should be the same. Is there a performance advantage or disadvantage
to this "rule"? Is there an alternative rule that should be considered?
Also regarding FDLs for indexed RMS files, I have been instructed that the FILL
percentages should be the same for all KEYs. Is this true? Is there any
advantage to setting them differently, and if so, what rule should be followed?
(I have not been able to locate this kind of advice in VMS manuals; although the
File Definition Language Facility section of Record Management Utilities manual
has reasonable definitions of the elements of an FDL, it does not offer advice
on how to set t hese particular elements...)
The Answer is :
An often overlooked book is the "Guide to OpenVMS File Applications",
a manual in the OpenVMS documentation set. Chapter 10 in particular
provides for for a basic understanding of RMS Indexed files and tuning.
This background will quickly permit you to draw your own conclusions
towards the validity of the 'general' tuning rules.
- concentrate on the "top 5 hot files" -- as judged by IO rates
first, then by record operations.
- bucket sizes do NOT have to be the same. Specifically, you may
want smaller buckets for random access, non-deferred write
buffers, and larger buckets for sequential reads. Large index
buckets for fixed length keys (no compression), and smaller for
variable keys. Larger buckets for duplicate chains.
Similar sized buckets DO optimize VIRTUAL memory usage. Because
of the Alpha pages size, this may impact physical memory usage
on OpenVMS Alpha as the buffer pool maximizes the size of all
buffers. (The OpenVMS Wizard expects that having a primary data
buckets value of 8 -- holding a few random access records, rarely
cached, primary index of 24 -- binary lookup, mostly cached,
and an alternate index 16 -- some sequential, some duplicates,
some caching, would be appropriate.
- FILL factor do not have to be the same.
70% could mean 1 or 2 data record on data level
where 90% could mean 10 or 20 fresh data buckets on index level.
The OpenVMS Wizard often uses the formula:
60 < data-fill < index-fill < 95
For "hopeless" data buckets -- localized random inserts, as in an
order header plus 20 order lines, filling more than a bucket, the
OpenVMS Wizard might use 100% for data -- the first new record
_will_ cause a split, and 80% for Index -- several data bucket
splits before an index split is needed.