how to select sessions,tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities

Tools
Enthusiast

how to select sessions,tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities

My Teradata Database is 255 AMP and 16 node based.

I want to know what will be the sessions, tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities to be selected while loading 1 million record file,10 million or 100 million record file.

Means how to choose the values for parameters like sessions, tenacity,sleep,checkpoint etc so that performance will be good.

What is the max session value i can use. Does increasing the session value increases the performance.

Looking for valuable suggestion.

Thanks
7 REPLIES
Enthusiast

Re: how to select sessions,tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities

increasiing session value doesnot increase performance. if ur practicing in ur house . just keep sessions max 4 . other tan tat its waste .
Senior Apprentice

Re: how to select sessions,tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities

Of course does increasing the number of sessions also increase performance.
This mainly depends on the size of the system.

As a general rule of thumb you define the number of sessions as a multiple of your node number: Sessions are distributed round robin across nodes, so this results in the same number of sessions per node.

In your case this will be 16/32/... sessions.

But for a small number of rows (like 1 million) even 16 may be too much in FL/ML.
For larger imports you usually run some tests with each tool to find out which is the optimium number on your system.

For TPump it depends on many factors, a single sessions with a large PACK plus ARRAYSUPPORT might be faster than multiple session with small pack and withour ARRAYSUPPORT.

Checkpoints should be as high as possible, lower checkpoints are only usefull, when you have a lot of script restarts.
Use a few minutes in MLoad.
For FastLoad it's not as easy to define, because 1 million rows might be 100MB for 100 byte rows or 10GB for 10,000 byte rows.
For TPump it depends e.g. on the ROBUST setting, if it's ON then every PACK will insert a new row into the logtable, which is cleaned at a checkpoint.

It's all down to test, test, test.

Dieter
Enthusiast

Re: how to select sessions,tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities

Hi Dieter,

Thanks first of all. Lets say till date my system is 16 node based. but later say 1 year after it can be 32 node based if nodes get added. So do i need to change my script accordingly to set the session value if number of nodes get changed.

Thanks

Re: how to select sessions,tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities


Hi Dieter,

My requirement is to load 100 millions of data from oracle source to a teradata table.

For this am using an informatica interface which is loading data using fastload external loader.

I wanted to know, what should be the sessions, tenacity,sleep,checkpoint and error limit to load 100 millions records in one shot.

Thanks!!

Teradata Employee

Re: how to select sessions,tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities

As was previously answered, just indicating 100 million rows does not tell us the entire story.

How large are the rows?

What is your network bandwidth?

What is your CPU availablity.

Since you are pulling data from Oracle, the FastLoad loader will outperform the extract from Oracle.

In most cases (no matter how many nodes you have) I have not seen much need for more than 32-40 sessions.

However, it depends on your row size.

As for checkpointing, the rule of thumb is this: "how much time are you willing to lose?"

For example, if you do not want to lose more than 15 minutes worth of data loading, then set checkpoint to an appropriate value for the utility that would correspond to 15 minutes. (MultiLoad can accept a time value for checkpoint; FastLoad is row-based, so you will have to determine how many rows you can load in 15 minutes.)

Tenacity/sleep is irrelevant unless you expect to run your job at a time when a lot of other FastLoad/MultiLoad jobs are being run at the same time and you may get locked out due to the load limit. The amount of time to wait for the next logon attempt it up to you.

You can also consult with Informatica.

They have used our tools for a long time and may be able to help here.

-- SteveF
Enthusiast

Re: how to select sessions,tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities

Hello,

We implemented this new architecture in our system :

Powercenter Realtime module received messages from MQ and each message is load in a TERADATA table using TPT STREAM operator.

My question concerns the right number of Session to set. We have 2 parameters : Min sessions and Max sessions.

Min sessions =1

Max sessions = ?

We think that Max sessions=1 is enough because we load (TPT STREAM) only one short message at a time.

 




can you tell me if this is a good choice or not ?

 

Thanks

Teradata Employee

Re: how to select sessions,tenacity,sleep,checkpoint etc for fastload,multiload,tpump utilities

If you are only loading 1 message at a time (and I am assuming that 1 message means 1 row), then 1 session is fine, and you should set the pack factor to 1 so that the row is sent immediately (and not waiting for a buffer of rows to fill up).

-- SteveF