SORT:Restrict Memory Usage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
telenet_bi
Premium Member
Premium Member
Posts: 33
Joined: Wed Jul 23, 2008 7:33 am
Location: Mechelen, Belgium
Contact:

SORT:Restrict Memory Usage

Post by telenet_bi »

hi,


our production server is heavily loaded on CPU-usage and on IO, but we see still a lot of free memory. Since a lot of sort-stages are used I want to take a look at optimizing memory-usage in these.

Untill now the option for Restrict Memory Usage is not used anywhere. I understood that this limits the process to 20MB of memory(per node). I would like to get better settings for this per sort-stage, but I'm a bit in the dark about the effects.

Some questions:
- if we increase this for a stage, but no memory is available will the job then abort, Or will it just start using swap-space? (anyway if both memory and swap are full there's definately problems).

- If we use varchar-fields the scratch-disk usage during sorts handles these as chars(so a varchar(100) takes 100 bytes for every record, no matter what the real length is). Is this also the way we would need to think in memory-usage?

-what would be the approach which gets the most result the fastest:
* increase the setting for all large sorts to 100MB
* take out some jobs and get them to work completely in memory
(probably here the first remark will be that this depends on my environment and type of jobs, but I still would like to know what you would do within your environment)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Memory usage is described in the manuals. First place it uses is the scratch disk specified in the configuration file. That is, it does not go to your swap device(s).

There are unbounded and bounded VarChars, and they are handled slightly differently. But if you base your sizing on 100% fill factor, you're going to err on the side of caution.

Tune individual jobs, particularly the ones that deal with larger data volumes.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
telenet_bi
Premium Member
Premium Member
Posts: 33
Joined: Wed Jul 23, 2008 7:33 am
Location: Mechelen, Belgium
Contact:

Post by telenet_bi »

ray,

for the temp space used in sort-space I find in the manual:
The stage uses temporary disk space when performing a sort. It looks in
the following locations, in the following order, for this temporary space.
1. Scratch disks in the disk pool sort (you can create these pools in the
configuration file).
2. Scratch disks in the default disk pool (scratch disks are included here
by default).
3. The directory specified by the TMPDIR environment variable.
4. The directory /tmp.
This doesn't actually say anything about memory, but what happens when memory alone is not sufficient.
The parameter I want to use tells datastage to use more memory, I was worried what would happen if it is then not available.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

The same happens as described for temporary space. You just get more - or differently sized - temporary sort files on disk.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
rohit_mca2003
Participant
Posts: 41
Joined: Wed Oct 08, 2008 9:19 am

Post by rohit_mca2003 »

Try using variable APT_OLD_BOUNDED_LENGTH and set to 'True'.
Rohit
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

:idea: Public Service Announcement

These stupid spam posts are bringing lots of random posts up to the surface, including this 10 year old one. Not saying there's no value to adding comments or suggestions to them but do keep that in mind, please.

Hopefully someone with the power will get them cleaned up... soon.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply