Has anyone experienced table corruption with 32k frame sizes.

I have a table which has 1k frames, with 9 million rows, and I am trying to copy out to a 32k table to reduce the disk IO.

When this completes the frame size is corrupt, eg set to 2million-odd… yes, that is the FRAME size it reports.

The table is then non-readable and crashes OI when attempting to read.

Any suggestions most welcome.

Any other way to copy files out/in to do this?

Using OI 8.0.8 with UD 4.7

Thanks

Colin

I can not help you with your "non-readable" issue, although I can suggest that you do not modify the "frame size" just because your table has nine million rows in it. I only adjust the frame size of a table when I have the average row size is very large. I normally set the frame size to the number of bytes your physical disk reads for each disk read or write call.

Adjusting the frame size only reduces the number of overflow frames. I have found that it really does not reduce the disk IO unless you have quite a few overflow frames.

I would figure that unless you have quite a few overflow frames the actual "hashing" that is built in to linear hashed files would still maintain a very low read count or disk IO.

If I had a table with nine million rows, I would consider possibilities to reduce the row count in the table. Either look at splitting up the table into multiple tables (not having all the information in one table), purge old data, break up the table into "years" or something. There just might be some resolve to your nine million row table if you think about it.

And unless your "selects" are readnexting through the whole table, I can not see why you would have disk IO issues. Maybe indexing should be considered if there are "selects" that are readnexting through the whole table.

Basically "hashing" and "indexing" were created so that very large tables would not have disk IO issues. Maybe your table design is not quite efficient in using these tools.

Thanks for your comments Richard.

The reason I am converting to 32k frames is that the current data produces 1400, yes 1400 disk operations per read, with expected performance consequences. Changing to 32k frame sizes on a test of 3 million rows brings this down to about 8, still not perfect, but acceptable.

The average record sizes are about 1k.

We dont do file select/readnexts as we have internal indexes, and part of the record key is a sequential numeric, so we can determine all the records from a parent.

Adding indexing wont change disk IO, as each record is known for performing the reads.

I thought of splitting the table in various ways, eg 10 sub-files (non-starter), one file per project (makes 10,000 tables in one volume). The other alternative is to create a separate folder for each 'project' which I have on another application, making 10,000 folders, with each one containing about 20 tables. This gives issues in searching some tables, as attach speeds take about 1 second.

The only other alternative, which for the effort is perhaps more viable, is to move away from the LH database in favour of SQL, which given such risk with table issues, is under consideration in due course. This would however also necessitate normalisation amongst other issues, but the performance benefits so I hear are fantastic.

Colin

The header comprises a set of attributes packed into fixed length fields. I suspect that the corruption is akin to buffer overflow - one of the attributes gets too large and overflows into the frame size. But, I wonder if your real problem is that the keys hash poorly.

In the table with the small frames - is the ov 1400 times the size of the LK?

Is the current frame size are enough to hold an average sized record?

Are all the keys hashing to a small number of groups?

I found a piece of Arev code which will show the number of records and total size and count of overflow frames for each group in a file, jazzed it up to display the results in Excel. It might help you answer the hashing question. See Get_Lh_Distribution If you have an natural key which hashes badly you might be able to calculate a key that hashes well.

Finally, if you must switch the data storage layer, before you switch to SQL you might want to take a look at other Mutlivalue databases or other NoSQL databases . Many of them are store key-value pairs like LH instead of normalized data like SQL.

Bob

We've already shown Colin that he is experiencing hashing clumping due to having a long less unique string as the first part of the key with a more unique string as the second part. We have recommended rewriting the rows using the inverted key structure (so playing to the strengths of a hashing algorithm rather than the weaknesses) and then installing an MFS to invert the key on retrieval/writing so the application does not have to change.

In the sample we were shown the average row size was around 32K - the version he is using reported average row size incorrectly. By inverting the key and using a 32K frame size we got the i/o down from 1400+ per read to less than 2 per read (and frankly I can't see that we could improve on that) HOWEVER we ran into issues with LH_VERIFY seemingly barfing on this. That said it might just have been a long delay as it eventually ran to completion.

We are currently waiting on a copy of the data so we can see if we can duplicate Colin's corruption issues as we have not been able to in the lab.

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Andrew,

<We have recommended rewriting the rows using the inverted key structure (so playing to the strengths of a hashing algorithm rather than the weaknesses) and then installing an MFS to invert the key on retrieval/writing so the application does not have to change>

That seems a great simple way to handle the hash clumping issue. I really like the idea.

and given Colin doesn't use OI indexes there are no implications there :)

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Thanks for the comments, which I was seeking whilst providing the data to Sprezz to test, which is taking some time to provide as we have to get permission from the user, as well as getting a USB drive to fit it on, as compressing is not efficient.

Indeed the keys do hash poorly.

I am running checks to see whether the reversed key still causes the frame corruption, as I see little point considering this if this also causes the same header corruption, so I need to check first, but every action takes an overnight run.

I will advise further.

Colin

Bob, further to this, and in answer to your questions, the LK = 160,025kb, and the OV =6,680,727 kb, so not 1400 times, but certainly not proportionate.

The current frame size is 1k, being the default we tended to use ages ago, and historically not considered abnormal. In hindsight it would have been better to increase it, but there we are.

The average size record is probably around 700 characters, hard to tell as the Table info says 14, which is clearly wrong, and the 32k frame table I copied to (with reversed Ids) over the weekend tells me the average is a worrying minus 302. Unfortunately a power cut terminated the copy so results may be unreliable, and only had 4mill records, but I will try again tonight.

Not 100% sure about the hashing, I have tried get_lh_distribution, but the sending to Excel with 9 million records and a modulo of 160,025 causes a lot of Excel rows, so to test I limited to the first few hundred groups. This showed a spread of full groups and empty groups with empty groups such as 3,5,7,9,10,17,18,19,20,27,34,40 etc, so very poor usage of groups.

Those with data averaged around 60 records per group, with 46000 characters per group (1k frame).

I tried it on 100,000 records with the reversed keys and the hashing was well spread with no empty groups, and good average sizes, so it is very clear the reversed key is preferable by a long way.

With 32k frame sizes, and the non-reversed key, the results were much better than the 1k frame, with a few empty groups, eg 5,18,19,43, but not as good as the reversed key spread with 32k frames.

Colin

Bob, I am considering the reversed record key, but the idea is uncomfortable with me as it means changing the live system on the user site, and going through UAT and other issues.

The solution to use an MFS is also uncomfortable with me, as I have no experience in it, and the effort of putting one together, or paying for someone to do so, is a risk, which I am edging on the side of just changing my code to suit. Still on the fence on this one.

As a minimum, just using 32k frame sizes with not changing the record key would be a suitable result for me and the client, to get the speed improvements, and no code changes, and the aim of my initial failed exercise

My major gripe here, is that whilst a good key for hashing is definitely preferable this does NOT negate the fact that the LH filing system cannot deal with copying the data from table to another table without crashing the recipient table (at least for me).

The solution to reverse the key, whilst ideal for hashing, does not justify the fact that such a problem is not considered a real risk. I am worried that at any point it could be the case that the tables cease to work.

Once I have tested that the reversed record keys copy does or does not corrupt the table, then I can make an informed decision, as I cannot decide to use this hashing if it too causes header corruption. I will advise Sprezz and post here.

Is there any info in the header I can interrogate to see.

Colin

Colin,

Just want to make a comment about frame size. I was at an enterprise Rev site where they were dealing with very large numbers of records, and large numbers ( let say upwards of 0.5 million records) being added on regular basis. They were concerned about performance - and one initiative was to run lab tests with larger frame size - in the same way as you are considering. A number of weeks later and the results were analysed; I was not entirely suprised that for them, larger frame sizes did not produce the desired result; why ? Because it would seem that other factors come into play such as network infustructure, disk / head optimisation … the list goes on.

Thus I was most impressed with Andrew / Sprezz's amazingly simple solution of the inverted key via MFS. I like using the sequential key assignment - because it is simple, however I also recognise the problem with large record numbers - the hash clumping. The inverted key method extends the simple sequential assignment - and the MFS - which would be strightforward to construct - will make the delivery very robust.

Richard Bright

What I find interesting in all the varied discussions over the years about frame size and key structure, is that everyone seems to forget the other component of the LH structure - threshold value.

By lowering the threshold value to 40 or lower, you start to create many more primary frames. The increase number of frames gives you more potential frames to hash to, which, in theory, should decrease the number of records that need to go into overflow. The clumping aspects of the keys will still occur, but the larger amount of available frames could have some effect on the amount of overflow used.

This, of course, assumes your disk space is unlimited. You will end up with many empty frames.

The idea here is to minimize not only the number of overflow frames, but the number of rows per frame as well.

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

View this thread on the Works forum...