topic Re: Improving performance on indexed file. in Operating System - OpenVMS

Improving performance on indexed file.

yaron1 — Wed, 16 May 2007 11:59:00 GMT

Hi,

I need to improve the performance (time) of operations done on an indexed file. I’m going to paste the FDL and the SHOW RMS_DEFAULT command at the end of my problem description. The operations are put, update and read, mainly random. This file is created empty once a year with initial allocation that is sufficient for the whole year, in other words no extends seems to happen later as records are added. At the beginning of this year an additional new (alternate) key was added, that makes it 4 keys including the primary key. Many records in the forth, new, key have space (bad, it makes them duplicate). Once a week there is a long job that adds and updates records in that file. Recently, every week the job takes longer and longer to complete. Records are never deleted from this file (once a year the file is replaced). The machine is Vax 4500A. I think of doing this, but this might be wrong or not enough:
1) Bucket fills and splits. If the problem might be related to many bucket splits that now occur so converting the file even with same FDL will probably give immediate (temporary) improvement, because buckets will again be partially free. Should I consider also decreasing the DATA_FILL and the INDEX_FILL in the FDL?
2) Should I consider adding the CONNECT attribute MULTIBUFFER_COUNT to the FDL? This might speed up the operations. If yes waht values?

Thanks for the answers. Here is the FDL and the SHOW RMS_DEFAULT command output:
IDENT "20-OCT-2000 13:04:49 VAX-11 FDL Editor"

SYSTEM
SOURCE "VAX/VMS"

FILE
ORGANIZATION indexed

RECORD
CARRIAGE_CONTROL carriage_return
FORMAT fixed
SIZE 281

AREA 0
ALLOCATION 1500003
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 9
EXTENSION 65535

AREA 1
ALLOCATION 31041
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 9
EXTENSION 7767

AREA 2
ALLOCATION 243504
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 6
EXTENSION 60876

KEY 0
CHANGES no
DATA_AREA 0
DATA_FILL 75
DATA_KEY_COMPRESSION yes
DATA_RECORD_COMPRESSION yes
DUPLICATES no
INDEX_AREA 1
INDEX_COMPRESSION no
INDEX_FILL 75
LEVEL1_INDEX_AREA 1
NAME "PRIME_KEY"
PROLOG 3
SEG0_LENGTH 54
SEG0_POSITION 0
TYPE string

KEY 1
CHANGES no
DATA_AREA 2
DATA_FILL 75
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION no
INDEX_FILL 75
LEVEL1_INDEX_AREA 2
NAME "key2"
SEG0_LENGTH 8
SEG0_POSITION 54
TYPE string

KEY 2
CHANGES no
DATA_AREA 2
DATA_FILL 75
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION no
INDEX_FILL 75
LEVEL1_INDEX_AREA 2
NAME "key3"
SEG0_LENGTH 8
SEG0_POSITION 62
TYPE string

KEY 3
CHANGES yes
DATA_AREA 2
DATA_FILL 75
DATA_KEY_COMPRESSION yes
DUPLICATES yes
INDEX_AREA 2
INDEX_COMPRESSION no
INDEX_FILL 75
LEVEL1_INDEX_AREA 2
NAME "key4"
SEG0_LENGTH 3
SEG0_POSITION 70
TYPE string

SHOW RMS
MULTI- | MULTIBUFFER COUNTS
BLOCK | Indexed Relative Sequential
COUNT | Disk Magtape Unit Record
Process 0 | 0 0 0 0 0
System 16 | 0 0 0 0 0

Prolog Extend Quantity
Process 0 0
System 0 80

Re: Improving performance on indexed file.

Hein van den Heuvel — Wed, 16 May 2007 12:18:02 GMT

>> I'm going to paste the FDL

Good, but not good enough...

We need ANAL/RMS/FDL output, for the file after a year, or at least after a few months.

You may also want to try my 'tune_check' on the live file.

>> and the SHOW RMS_DEFAULT command

Good, but bad data.

I hope to program which adds record to this file will set a fgood few (20+) local buffers. Or uses SET RMS/PROC/IND/BUF=20

>> in other words no extends seems to happen

On the outside it may look clean. On the inside it might be a mess. Are teh records mainly added in ever ascending primary key order or not? You may need a monthly convert to keep it humming.

>> Many records in the forth, new, key have space (bad, it makes them duplicate).

So create a NULL_KEY attribute = YES and NULL_VALUE = ' '

>> Should I consider also decreasing the DATA_FILL and the INDEX_FILL in the FDL?

Possibly. Depends on the load pattern.
DATA_FILL only helps if the load is pretty much random. For localized inserts (for a warehouse, for a city, for a ..) then you might just as well pack them up.
Index_fill of 80 or 90 is probably usefull.

>> 2) Should I consider adding the CONNECT attribute MULTIBUFFER_COUNT to

NO. That is NOT stored in the file.
Is it only valid for the process parsing the FDL (convert!?).
You need the SET RMS/BUF=nn as mentioned earlier.

What programming language?
In COBOL you can use APPLY nn AREAS
IN Macro or C use RAB$B_MBF = nn
If you had nothing, then try 20 or 30

I'll gladly do some consulting to figure out the truly optimal value for you!

The above is all I can offer for free.
Please feel free contact me if further professional help is needed.

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

Re: Improving performance on indexed file.

Hoff — Wed, 16 May 2007 12:34:15 GMT

I expect you're building up deleted records in the file, and some cases of duplicate keys can certainly lead to massive I/O increases. Try a CONVERT/RECLAIM off to the side, and run some tests to see if this picks up performance. (This assumes no RFA access.)

Here are the generic steps:

http://h71000.www7.hp.com/DOC/731FINAL/4506/4506pro_030.html

As for the hardware, what disk(s)? What is the I/O bandwidth?

What queue depth on the disk(s)?

Stuff of this vintage tended to be DSSI, and that's about the speed of SCSI-1. Real Slow.

What are the chances of replacing the VAX 4000 model 500 with something faster? The VAX 4000 Model 505A and up, VAXstation 4000 model 90 and up, MicroVAX 3100 model 95 and up, or otherwise.

And most any Alpha or Integrity would massively exceed the speed of this box. If you have the code, you may get a better and bigger payoff by porting forward.

Throwing hardware at the problem -- disk or new box -- is often the most expeditious, assuming it's not something like duplicate keys that is slamming performance.

Hein (who will likely comment here Real Soon Now) has various presentations on the topic of tuning RMS indexed file access, as well. And as Hein has cited, duplicate keys can nail you. Start with the following article that Hein wrote a while back:

http://h71000.www7.hp.com/openvms/journal/v2/articles/rms.html

As part of examining this, I would encourage you to focus first on the indexed file; to look wider. I'd tend to look wider first and see if there's an underlying limit or brute-force fix. This might well be an RMS update, but you can push a SCSI-1-class disk only so fast... Then work your way down to this indexed file, and tuning it. (If you've already done the wider look, please ignore this.)

Stephen Hoffman
HoffmanLabs.com

Re: Improving performance on indexed file.

Robert Gezelter — Wed, 16 May 2007 13:03:50 GMT

Yaron,

I agree with both Hein and Hoff.

While the FDL is helpful, what is truly needed are statistics that show where the actual performance is a problem.

ANALYZE/RMS is a good start, multiple ANALYZE/RMS operations separated by regular intervals so that some trend analysis can be done is even better.

It would also be a good idea to do a series of MONITOR runs during peak load with the results going to a file for later processing using the T4 suite.

I always recommend that clients "do science" rather than presume the nature of the problem. Sometimes the results can be surprising. If not surprising, at least the science allows the rationale for changes to be on a solid footing.

- Bob Gezelter, http://www.rlgsc.com

Re: Improving performance on indexed file.

Wim Van den Wyngaert — Mon, 21 May 2007 06:27:06 GMT

Also check if the Extended File Cache (sysgen vcc_flags=2) gets enough memory (sh mem/cac). VCC_MAX_CACHE may be limiting the cache.

The caching result for each file can be viewed with
$ sh mem /cac=file=xxx.

If you're on 6.2 or so, consider increasing the VIOC (vcc_maxsize).

Wim