We have some slow file access on large files. 13 million records in one and 3 million in one. Looking into these issues has raised several questions I can't answer.

A) What is the upper limit on LH Groups?

B) Why would 40% of my frames be empty and the rest have 40 overflows?

c) Why is our free frame list so long?

D) What problems cause the free frame list to not be used?

E) Is SYSTEMP the only place FIX will put records, even though there's lots of other stuff in there?

F) Even though we've deleted millions of records, why won't my file shrink?

Thanks for your timely response. Feel free to email me at dte@iainet.com.

Hey Dan, long time…

A) What is the upper limit on LH Groups?

Depends - are you using the NPP? If so, which version?

B) Why would 40% of my frames be empty and the rest have 40 overflows?

Could be related to overflow/sizelock - see later

c) Why is our free frame list so long?

See above

D) What problems cause the free frame list to not be used?

See above

E) Is SYSTEMP the only place FIX will put records, even though there's lots of other stuff in there?

Depends on version

F) Even though we've deleted millions of records, why won't my file shrink?

Meat - OK if the Sizelock is over ]= 2 the file will neither grow nor shrink. This is caused by aborted selects etc etc etc. Dump the file and press - (minus) to decrement the sizelock to 0. The Do a Ctrl R then a Ctrl C. Backup first.

amcauley@sprezzatura.com

Sprezzatura Ltd

World Leaders in all things RevSoft

Forget the sizelock issues, it is 0 and has been. They are using NLM v1.10. See "Novell Server Abend" message on Network Product discussion for all the stats. We do get a group 0 GFE, type 12, unsorted free frame list.

More I can tell you?

Any reason why they're not at 1.5?

amcauley@sprezzatura.com

Sprezzatura Ltd

World Leaders in all things RevSoft

We have some slow file access on large files. 13 million records in one and 3 million in one. Looking into these issues has raised several questions I can't answer. A) What is the upper limit on LH Groups?

The maximum modulo is 16M (256*256*256) minus 1. Using the default frame size, that gives you a base file size of 16 Gig. The linear hash algorithm, however, starts losing resolution before that.

B) Why would 40% of my frames be empty and the rest have 40 overflows?

This is a consequence of losing resolution with a large modulo. To avoid this, you can rebuild your file with a different frame size. However, there are consequences to doing this (do a search for frame size for articles on this topic).

c) Why is our free frame list so long? Because of deletes. Every time you delete a record that empties out a frame, that frame is added to the free frame list. When new overflow needs to be allocated, it is allocated from this free list. The freelist is cleared if you use it up with overflow frames or if you issue a compress from DUMP (this would take a long time with the size file you are talking about) or if you remake the file. Also, if you do a tablecopy, the destination file will have no frames in the overflow free list. D) What problems cause the free frame list to not be used? I'm not aware of any problems causing the free frame list to not be used. However, there are situations that cause the free frame list to be truncated, making free frames inaccessible. Fixing GFEs is one such situation. E) Is SYSTEMP the only place FIX will put records, even though there's lots of other stuff in there? I don't think I understand the question. F) Even though we've deleted millions of records, why won't my file shrink? Generally, your .LK file WILL shrink, but your .OV file will not shrink. This is because rather than shrinking, unused frames are added to the overflow free list. You can recover these by issuing a compress in the DUMP utility. Remember also that when you are deleting records, the overflow frames for those records will be in random (well at least random enough to consider them as such for the sake of this argument) locations within the overflow file. So when you delete the record, the frames being freed up will be somewhere in the middle of the file. There is still data after that location, so the file cannot shrink. This is the .OV file I'm referring to here. The .LK file should shrink. </QUOTE> —- === At 04 JUN 1998 02:43PM Dan Ellis wrote: === <QUOTE>Umm…Oversight and tardiness? </QUOTE> —- === At 04 JUN 1998 02:57PM Dan Ellis wrote: === <QUOTE>Victor, thank you for your informative response. It does raise others questions . 1) If 16mil minus some=practical size limits, why are we seeing the same problem with a 3mil row file? The problem being 40% of LK frames empty, others in long overflow? How early is resolution lost? OR could this be part of a Group 0 GFE, Code 12 (free frame list problem)? See question 3 for more on this. 2) In DUMP, why does the modulo change as we scroll through frames? I thought that came from frame 0. 3) How can I fix a Code 12 GFE in Frame 0. VerifyLH reports this but FixLH appears to run fine, but leaves this GFE. 4) Is it true that I cannot stop a DUMP-COMPRESS once it starts? Because of file size, I need to do it in multiple sessions. It will probably take 30 hours or more to run. 5) Do you think clearing the free frame list and compressing will solve the Code 12 GFE? 6) How can I find the modulo of a file if DUMP only shows 5 characters and VerifyLH stops short because of a Code 12 GFE? My 13mil row file shows mod 16002 in DUMP, which is probably 16 mil. Another file with 11mil rows ALSO shows 16002 mod and the 3mil row file shows 15999 mod in DUMP. ! Again, thanks for your info - I think it is helping get closer to solutions for us. Dan Ellis Information Architects Inc Poulsbo WA USA </QUOTE> —- === At 04 JUN 1998 04:28PM Victor Engel wrote: === <QUOTE>Whoops. I think I may have screwed up. I keep thinking the modulo is determined by 3 bytes, making it be limited to 16 million, but I think it's actually 4 digits, which would put the limit at 4 billion frames. I don't have the layout handy at the moment, but I'm pretty sure it's the latter. 1) If 16mil minus some=practical size limits, why are we seeing the same problem with a 3mil row file? The problem being 40% of LK frames empty, others in long overflow? How early is resolution lost? OR could this be part of a Group 0 GFE, Code 12 (free frame list problem)? See question 3 for more on this. There are two issues here. One is an absolute maximum modulo. The other is a point at which distribution of the keys starts missing certain groups. I don't know much about the latter situation, but others have posted here that this does happen at some point before the previous limit (which I think is actually 4 Gig) is reached. That information is in the archives somewhere, but I wasn't able to find it quickly. 2) In DUMP, why does the modulo change as we scroll through frames? I thought that came from frame 0. What you are seeing is the group modulo. Frame 0 has the file modulo. The manuals for the version 1.1 release (I think) of Arev illustrated how the linear hash system worked very well, complete with diagrams. I don't remember how the group modulo is used. 3) How can I fix a Code 12 GFE in Frame 0. VerifyLH reports this but FixLH appears to run fine, but leaves this GFE. I don't know what a code 12 GFE is, but if you have a problem with the free frame list, you can reset it in DUMP. This will result in inaccessibility of unused frames. To recover these you must compress the file or remake the table. 4) Is it true that I cannot stop a DUMP-COMPRESS once it starts? Because of file size, I need to do it in multiple sessions. It will probably take 30 hours or more to run. Yes, I think it is true. And, unfortunately, you don't get a status bar, so you can't tell how far along it is. For large files I usually prefer to create a new file and do a recordcopy or else to a tablecopy, which should be faster. 5) Do you think clearing the free frame list and compressing will solve the Code 12 GFE? Yes. 6) How can I find the modulo of a file if DUMP only shows 5 characters and VerifyLH stops short because of a Code 12 GFE? My 13mil row file shows mod 16002 in DUMP, which is probably 16 mil. Another file with 11mil rows ALSO shows 16002 mod and the 3mil row file shows 15999 mod in DUMP. ! Probably the easiest way is to DUMP the file and note the DOS file name and frame size (probably 1024). Do a directory listing of the LK portion of the file to find its size. Divide this by the frame size to get the modulo. To find out what Arev thinks the modulo is, DUMP the file and press CTRL-H. This displays the hex values of each byte at the bottom. Count over from the left to the 10th character. This is the least significant digit of the modulo in hexadecimal. The next digit is the next most significant digit. Continue on until you have 4 digits. Now convert from hexadecimal. </QUOTE> —- === At 05 JUN 1998 02:37PM Dan Ellis wrote: === <QUOTE>Thanks John, 1) You didn't really address the issue of why would I lose resolution on a file with only 3mil rows? 2) If not resolution loss, what else can cause 40% of primary (lk) frames to be empty while other in massive overflow? 3) Were you able to find any more on when resolution loss begins. Does the lab have a graph or anything? 4) According to the Arev 3.12 Reference, Appendix 3-24, a Code 12 GFE is Free frame numbers in the free frame list are not sorted. 5) What would you recommend that I do to fix my bad files, given their extreme sizes. 6) Larges sizes are unavoidable in this application right now. Because of this I really need all the info I can get on resolution loss and other issues related to LARGE files. Thanks again, Dan Ellis dte@iainet.com </QUOTE> —- === At 05 JUN 1998 02:48PM Dan Ellis wrote: === <QUOTE>Oops, Sorry Victor that I called you John in that last message. I thought of something else that might be an element here. The keys are fairly long in these files, say, between 15 and 20 bytes. They look like 1*1*123456789*324. Maybe resolution is lost earlier with this kind of key. ALL 13 million keys start with 1*1. Part 3 is an SSN. Part 4 is a sequential number per SSN. So hundreds will have only the last part different, and 10s of thousands will have only part 3 different. In other words every SSN (10s of thousands) will have sequences starting with 1 to 1000 or more. These are transactions on an automated loan payment system. Dan </QUOTE> —- === At 13 JUN 1998 01:40PM Aaron Kaplan wrote: === <QUOTE>Hashing, while a logical, mathamatical model, is still more of a black art than anything else. We could get heavily into hashing theory here, which I don't really want to do, but simply put, the LH algorith looks at the file, determines how many groups there are, and decides where this record key fits in the algorithm. The best algorithm hashes one key to one group. LH really won't do that since the files are dynamic. As you add and delete records, it has to shuffle records around. For speed sake it only (potentially) reallocates the records in one or two frames. For reliability sake, it has to be able to find the records again. With that, you end up with clumping occuring as more and more records are added to a file. On a different note, the nature of the algorithms is such that more clumping occurs with sequential keys than with random generated alpha-numerics. Remaking the file will smooth out the rough edges. It will also help in the long run since an evenly spaced file can expand more readily since more primary space will be filled for the threshold to reached. Threshold values only care about LK not OV, so as more and more records go into overflow, you're not chaning your threshold, or your hashing values, thus condeming more records to overflow. Your best bet for a rebuild is create the new file pre-sized. Set the sizelock to 1, then copy the records in. It'll go pretty fast even with 13 million records. Doesn't have to resize, and just jams in data. Creating the file will take a hefty amount of time, but you can do this during the day on a spare workstation. For an immediate fix, you could clear the free frame list (Ctrl-D). Compression is up to you. akaplan@sprezzatura.com Sprezzatura, Inc. </QUOTE> View this thread on the forum...