Hashed File Creation Bogging Down Server

casedwgroup · Post by **casedwgroup** » Tue Mar 16, 2010 2:20 pm

We have a UNIX server split into zones (all on Solaris 10). On one zone is DataStage, on the other is our reporting application. I've found that when creating large hashed files (>20 MB or so), the performance on the entire server suffers (the reporting app slows down, it takes significantly longer for Director to list the jobs in a category, and even doing a simple 'pwd' on the server takes 5-10 seconds instead of being instantaneous).

Judging by the server statistics, it appears to be a disk I/O limitation. We are willing to upgrade the hardware, but are wondering how much it needs to be upgraded.

Is there some kind of benchmark we can use to figure out how many bytes per second need to be written to the disk without crippling the server?

Also, is there some way to limit how much DataStage can write at one time so that performance on the rest of the server is not impacted?

Thanks for any insight that can be provided.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 16, 2010 7:38 pm

Much of this is automatic table space management within the dynamic hashed file structure. When you create the hashed file (as opposed to populating it) pre-size it according to the expected data volume - you can use Hashed File Calculator to achieve the calculation, and the MINIMUM.MODULUS keyword/option in the creation dialog.

A static hashed file of the correct size would be even better, but these are difficult to keep well tuned if the volume of data varies.

casedwgroup · Post by **casedwgroup** » Wed Mar 17, 2010 8:59 am

Thanks for the suggestion, Ray. I was able to increase the performance while loading the hashed files, but the server is still experiencing the lagging issues while they are loading.

Any other thoughts?

chulett · Post by **chulett** » Wed Mar 17, 2010 9:12 am

I've seen something like this. At my last client's site, one particular job that populated a rather large hashed file would bring the server to its knees when it ran, right down to that same painful 15 second pwd. This wasn't a generic issue but was (thankfully) isolated to this one particular job. This was a Linux system with a fairly crappy disk subsystem but we never really did find out the root cause of this - DataStage, Linux, disk system, some combination... no clue.

Not really all that helpful for you but it turned out our hashed file was no longer needed by the process it was built to support so our ultimate solution was to... stop running the job.

casedwgroup · Post by **casedwgroup** » Wed Mar 17, 2010 9:26 am

Hashed files are overrated anyway, right?

If you have any suggestions for how you would go about fixing this if not running the job was not an option, please let me know. Thanks!

chulett · Post by **chulett** » Wed Mar 17, 2010 9:45 am

casedwgroup wrote:Hashed files are overrated anyway, right?

Overrated? Are you kidding? They are the heart and soul of server jobs, something you will (and should be) leveraging to the hilt in whatever Server based solutions you build. Learn them. Love them.

My issue was an aberration. Yours... disk, I assume, but not being a hardware guy can't really speak to the gory details. Have you had your SysAdmins monitor the system while the problematic hashed writes are happening? When I've had systems where 'tuning' of writes needed to be done, that could provide invaluable information they could use to improve performance. May be as simple as making config changes to how the 'journaling' functions (if such a thing applies) or it may be something it takes better hardware to improve.

casedwgroup · Post by **casedwgroup** » Wed Mar 17, 2010 9:56 am

To answer your question: yes, I was kidding. Fear not, Craig. I already love hashed files. I've got pictures of them in my house and everything.

I suppose I should use emoticons next time I attempt a facetious remark, eh?

chulett · Post by **chulett** » Wed Mar 17, 2010 9:58 am

It would have helped head off that first "wtf?" reaction from me, yes.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 17, 2010 7:26 pm

What's the structure of the key? What hashing algorithm have you chosen?

casedwgroup · Post by **casedwgroup** » Thu Mar 18, 2010 9:13 am

The command used to create one of the files, based on the output from the HFC, was:

CREATE.FILE DEBUG 16 50321 1

Key structure is 4 decimal keys with an average length of 4 or 5, then 1 character key of length 5.

chulett · Post by **chulett** » Thu Mar 18, 2010 9:52 am

DEBUG? What dat do?

In other words, never seen it before and never used it. Wondering how much it is contributing to your issue... perhaps that is the sum total of it.

casedwgroup · Post by **casedwgroup** » Thu Mar 18, 2010 9:54 am

That's just the name of the file.

chulett · Post by **chulett** » Thu Mar 18, 2010 10:10 am

LMFAO

ray.wurlod · Post by **ray.wurlod** » Thu Mar 18, 2010 3:41 pm

Try using a separation of 4 or even 8, rather than 1. You'll need to enter this manually into the Hashed File Calculator.