Problems creating large hash files

[Thomas.Nel]

Hi Guru's,

I'm having trouble creating a LARGE Dynamic hash file (approx 50 bytes / record, 67 000 000 records) that reads data from and Oracle DB. The first error I got was an "unable to allocate memory error", which I got around by disabling the cache loading for the file. Now I'm running into an "ds_uvput() - Write failed for record" error for the same job.

From what I can gather, it looks like I'm running out of disk space as I estimate that the file should be +- 4 GB on disk when fully created. So, can someone help me answer the following?

• Firstly, should I create this hash file "manually" instead of letting DS create it for me? I've used the HFC for help and the command that DS runs is not the same as what the HFC suggests.
  What directories should I look at to make sure there is enough space available, as they all seem to have sufficient space?
  What entries in the uvconfig file would come into play in this case that I should be aware of (like UVTEMP,SCRMIN etc)?
  Also, the DS server has a lot of free memory available, can I consider loading this file into memory (i.e. turning caching on)?

Any suggestions will be greatly appreciated.

Thanks.

[ray.wurlod]

As soon as your data looks like getting near to 2GB (even 1.5GB for a safety margin), you can't readily use the default dynamic hashed files; you must switch to 64-bit addressing (or Distributed files, which is an entirely different topic, and you don't get memory caching).

Ideally, then, you should create the hashed file manually; the hashed file stage does not include a "64 bit" switch. The HFC does, you can determine its effect by toggling between 32-bit and 64-bit. Storage overheads are increased (there are three pointers per record).

So long as there's enough free disk space where you're placing the hashed file, there is nothing else that you need to consider.

Similarly there's nothing you need to change in uvconfig for this.

As for enabling memory caching, how far can you crank up the size of the read cache is limited by the spin button in the Administrator client (to three digits, therefore 999MB, which won't be enough for you).
There are some commands associate with file caching, but primarily with write caching.
These, and everything else you can learn about hashed file disk caching (including shared cache) is in the manual Hash Stage Disk Caching(dsdskche.pdf).

[kduke]

If you are setting at 4gb then you are close to the limit of a 64bit file. You can do a search for multiple partition hash file or distributed hahs file viewtopic.php?t=86504 .

Creating the metadata for these files is not that hard. It was covered in viewtopic.php?t=84954 but there was an issue with the code. Create a job and put this in the job control. Now you can import this metadata.

Code: Select all

open 'DICT', 'MyHashFile' to DictFilePtr else 
  print 'Unable to open MyHashFile' 
  stop 
end 
* --------------------------------------------------------------- 
* Dictionary records 
* Field 1 = I or D for formulas and direct fields 
* Field 2 = formulas or field number 
* Field 3 = oconv() 
* Field 4 = Column heading 
* Field 5 = Length : Justification ex. 20L 
* Field 6 = S or M for single or multivalued 
* Field 7 = Association if multilvalued 
* Field 8 = Metadata type or column type like varchar
* --------------------------------------------------------------- 
Key1='MyFirstKeyField' 
DictRec='' 
DictRec<1>='I' 
DictRec<2>='field(@ID, @TM, 1)' 
DictRec<4>=Key1 
DictRec<5>='20L' 
DictRec<6>='S' 
write DictRec on DictFilePtr, Key1 

Key2='Key2' 
DictRec='' 
DictRec<1>='I' 
DictRec<2>='field(@ID, @TM, 2)' 
DictRec<4>=Key2 
DictRec<5>='10L' 
DictRec<6>='S' 
write DictRec on DictFilePtr, Key2 

Key3='Key3' 
DictRec='' 
DictRec<1>='I' 
DictRec<2>='field(@ID, @TM, 3)' 
DictRec<4>=Key3 
DictRec<5>='10L' 
DictRec<6>='S' 
write DictRec on DictFilePtr, Key3 

AtKey='@KEY' 
DictRec='' 
DictRec<1>='PH' 
DictRec<2>='Key1 Key2 Key3' 
write DictRec on DictFilePtr, AtKey

Field1='Field1' 
DictRec='' 
DictRec<1>='D' 
DictRec<2>=1 
DictRec<4>=Field1 
DictRec<5>='99L' 
DictRec<6>='S' 
write DictRec on DictFilePtr, Field1 

Field1='Field2' 
DictRec='' 
DictRec<1>='D' 
DictRec<2>=2 
DictRec<4>=Field2 
DictRec<5>='88L' 
DictRec<6>='S' 
write DictRec on DictFilePtr, Field2 

AtPhrase='@' 
DictRec='' 
DictRec<1>='PH' 
DictRec<2>='Field1 Field2' 
write DictRec on DictFilePtr, AtPhrase

end 

I usually do not test this code but it should be close. Ask if it does not work. Do not use the names Key1 or Field1.

[ray.wurlod]

Nah. Kim's not correct in stating an upper limit of 4GB for 64-bit addressing in hashed files.

64-bit addressing allows a theoretical upper limit of 19 million TB for a hashed file. Some operating systems, however, restrict (!) you to one million TB.

Thomas, RESIZE filename * * * 64BIT [ USING pathname ] is the correct method for converting to 64-bit addressing.

However, I believe that, if the file is larger than the cache size (which can be determined from the file header and fstat() calls), none of it is cached. Instead you get a message logged indicating that cache is disabled because the file is too large. Doesn't stop you using the hashed file; just the memory cache.

If it's a dynamic hashed file you can pre-allocate disk space using the MINIMUM.MODULUS parameter. If it's a static hashed file you necessarily pre-allocate disk space.

CREATE TABLE does indeed create the metadata. It's CREATE.FILE that does not.

[kduke]

Ray

I did not know that.

Thanks.

[kcbland]

I'm not even going to ask why you're putting 67 million rows into a hash file. I'm not even going to get on my soap box and talk about no read/write caching available, inefficiencies, etc. However, if you're going to use a single job to load this hash file, let's say from a sequential file, you're bottlenecking behind a single-threaded job, singly loading the file.

If you persist in your efforts, I would recommend to simply partition the data yourself and stick it in N hashfiles. You will want to run multiple loading jobs to populate this hash file. Instead of one job taking all day to load into the hash file, you have N jobs doing it in 1/N the amount of time.

For example, if you have an orders fact data stream referencing a customer dimension hash reference file, you could create 10 hash files of the customer information. Use a last-digit of a column value type operation to put into a hash file CUSTOMER_REF_0 all CUSTOMER_KEYs ending in 0. Put into CUSTOMER_REF_1 all CUSTOMER_KEYs ending in 1, and so on. You will have 10 hash files that can load simultaneously.

Now, your orders transformation job needs to be instantiated to 10 also. Each job only processes the facts with respective CUSTOMER_KEYs ending in 0..9, and each job references the associated hash file. To make this really easy, you could actually use a job parameter in the hash file name (CUSTOMER_REF_#PartitionNumber#) and feed the appropriate value. This is really slick and easy to do. If you don't have 10 cpus to sustain this degree of parallelism at the job level, you could switch to using MOD(CUSTOMER_KEY, your desired DOP) = 0..your desired DOP-1. This allows you to use a DOP of like 4, so MOD always gives you answers of 0 thru 3.

This way you have 10 hash files, all probably squeezing under the 2.2 gigabyte limitation , 32-bit not 64-bit , all read cacheable , and able to simultaneously load.

[roy]

Hi,
actually the 999MB limit applies to a single hash file.
what you could try to do is split it to several files, in case the total memory you need is in the boundries of the resources available.
you can split the file in round robin and perform several lookups to get the data.
in this way you can also use multiple job instances or several jobs in parallel to get the data from DB to hash faster.
IHTH,

[ray.wurlod]

Should we give up on the whole idea of having a single large hash table available to us and just create say hlp_dim_keys_hash_001/002/003 etc so we break the hash table up into smaller parts and let DS deal with these smaller parts?

If memory caching is important to you, that's what I'd advise. Although I'd recommend meaningfully named hashed files.

[peternolan9]

Hi All,
just by way of sharing findings from performance testing...

As we noted we have some large hash files. We have now broken our largest hash file into 30+ pieces, one for each dimension table.

But we still have some hash files greater than 1GB.

One unexepected finding was that if a hash file is 1GB+ and we 'enable' load into memory DS tries to load it, figures out it can't and then proceeds with the data still 'on disk'. However, a test job with the memory load enabled ran about 30% slower than a job with memory load disabled.

It would seem that there is some extra processing internally in DS to process hash files where the file was too large to load. And a 30% difference is enough to be careful as to know which files can be loaded and which cannot...

[kcbland]

DS uses a paging technique, so therefore there is some effort to pmove data in and out. You probably know this, but for the newer users reading this post, you should only preload to memory for really one reason:

A row will be accessed multiple times. If each row is only accessed once, then it is a waste of time for the job to sit there loading the rows into memory, only to have it hit once for a reference. You incur more time this way because it is accessed twice, once to read from disk and again for reference. If didn't preload, it would simply read from disk, but you save the time of loading the hash file into memory and then the memory scan for reference.

If you have a large hash file, but only a small percentage of the rows will be accessed by the job stream, don't preload to memory. You'll find that the frequently accessed rows will benefit from OS caching. Again, you don't have to incur the overhead of hash files preloading, and, you don't have all that memory used (again under the constraints of paging).

Watch the job log when the job starts. You will notice that each hash file in the design set to preload will generate a message about preloading. You will notice that it loads one at a time. This is added runtime straight to the top. Even if your row count on your primary input stream is 1, you will incur all of this runtime. This is a sequential preloading processes, so really use your judgment if it's beneficial or not.

[peternolan9]

Hi All,
us and our large hash files again....

We are taking the approach of squeezing our lookup tables in under the 999MB limit for each table so we are doing our best to take everything out of hash tables that we can to minimise the #bytes.

We thought we had got there. For example I have now a 21M row table with just a varchar string for the key and an integer for the lookup key. The varchar string averages about 12 bytes and the integer I thought should fit into 4 bytes.... so this is about 340MB data. However, when I look at the hash file size on disk it is 1.2GB.

Two questions:
1. When the size of file on disk says 1.2GB is this the actual amount of data it will try and load into memory or is there stuff on the disk that will not be loaded? (I'll be giving it a try anyway...)

2. Given there seems to be a 4x multipler between data/disk is there any option that we should be looking out for that we may have specified that will cause extra disk (then memory) usage?


Thanks

[kcbland]

Remember, a hash file is a presized parking lot where the stalls or spots are big enough for a couple of cars each. With a minimum modulus the hash file will always be a certain size when empty.

You don't know when it's filling up because the data is being distributed within that parking lot. This special parking lot allows multiple cars within the same spot, called a group. But, when a car needs to park in its group but there's no room it goes to the overflow file. When enough cars are in the overflow, the parking lot resizes and redistributes the cars within the lot, and most groups go back to having just one car in the spot.

1. If you remember, I said earlier that it pages the hash file, so that the maximum cache size is how much is in memory, the rest is on disk. The most commonly used data stays in memory, and pages the least referenced back to disk.

2. Not really.

[peternolan9]

Remember, a hash file is a presized parking lot where the stalls or spots are big enough for a couple of cars each. With a minimum modulus the hash file will always be a certain size when empty.

1. If you remember, I said earlier that it pages the hash file, so that the maximum cache size is how much is in memory, the rest is on disk. The most commonly used data stays in memory, and pages the least referenced back to disk.

2. Not really.

Hi Kenneth,
thanks for this....I didn't know that internally it allocated extra disk even though the slots were empty, though I should have guessed that......I'd also guess that that would only be on the disk, that when the file was in memory such empty slots would be gone because of the read only nature of the hash file for lookups. (or am I being too hopefull?)

After a few days of looking at this large hash file problem again I believe I've figured out an application dependent way of splitting them that will give us a few years before we hit the 999MB limit again...now I just have to test it and see if my theory is correct. (I'm taking your advice on figuring out how to split hash files so they all fit into memory...)

Even when the hash file is paged by the OS the performance we are getting is well too low for what we are doing.......I really want all the hash files in memory to be able to be used by all the fact table processing jobs at the same time. We made sure we bought enough memory to do it, it's just a case of making it work...

Thanks very much for all your good advice. It is very much appreciated...

[ray.wurlod]

Hi All,
us and our large hash files again....

We are taking the approach of squeezing our lookup tables in under the 999MB limit for each table so we are doing our best to take everything out of hash tables that we can to minimise the #bytes.

We thought we had got there. For example I have now a 21M row table with just a varchar string for the key and an integer for the lookup key. The varchar string averages about 12 bytes and the integer I thought should fit into 4 bytes.... so this is about 340MB data. However, when I look at the hash file size on disk it is 1.2GB.

Two questions:
1. When the size of file on disk says 1.2GB is this the actual amount of data it will try and load into memory or is there stuff on the disk that will not be loaded? (I'll be giving it a try anyway...)

2. Given there seems to be a 4x multipler between data/disk is there any option that we should be looking out for that we may have specified that will cause extra disk (then memory) usage?


Thanks

There are no data types in hashed files. Since integers can have up to 11 characters (including sign), you need to allow 10 bytes, not 4, for a positive integer. This will explain some of the variation.

The remainder is storage overhead, as other posters have described. There is also a small amount of storage overhead (three pointers and one or more pad bytes to achieve word boundaries) in every record. This, combined with the fact that not every group will be 100% full, explains the remainder of the variation.

DataStage needs to load the entire structure (1.2GB) into memory. It uses the same hashing algorithm in memory as it does on disk, and therefore requires that all data be at the same offset (from beginning of file) whether in memory or on disk.

[peternolan9]

There are no data types in hashed files. Since integers can have up to 11 characters (including sign), you need to allow 10 bytes, not 4, for a positive integer. This will explain some of the variation.

.

Hi Ray,
thanks for this...I had not guessed that integers were stored as strings inside hash files.... ..I'll be careful to reset my integer keys to 1 when we start the system for real!!!!