Mload Performance Issue

Tools
Enthusiast

Mload Performance Issue

I have an Informatica mapping trying to load data from a flatfile to a table. The target table uses MLOAD to load the table in one of the database in teradata development database.

Till yesterday the mapping was taking 2 minutes and from today the mapping was running for more than 2 hours with the same source file. I tried to compare the MLOAD loader log and this is what i found out

Below is the output from mload loader log from yesterday:

**** 11:09:11 UTY0826 A checkpoint has been taken, recording that end of file

     has been reached for IMPORT 1 of this MultiLoad Import task.

**** 11:09:12 UTY1812 A checkpoint is being initiated because 10000 output

     records have been sent to the RDBMS.

**** 11:09:12 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 1;

**** 11:09:13 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST

            (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,       

     MiscInt1,MiscInt2,MiscInt3,MiscInt4,       

     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,byteflag,MLoadCkpt)      

      VALUES (110, 1, 1, 1, 10000, 10000, 0, 10000, 0, 0, 0, 0, 0, 1, 1,

     :Ckpt);

**** 11:09:13 UTY0827 A checkpoint has been taken, recording that input record

     10000 has been processed for IMPORT 1 of this MultiLoad Import task.

**** 11:09:13 UTY1812 A checkpoint is being initiated because 20000 output

     records have been sent to the RDBMS.

**** 11:09:13 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 2;

**** 11:09:14 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST

            (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,       

     MiscInt1,MiscInt2,MiscInt3,MiscInt4,       

     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,byteflag,MLoadCkpt)      

      VALUES (110, 1, 2, 1, 20000, 20000, 0, 20000, 0, 0, 0, 0, 0, 2, 1,

     :Ckpt);

Below is the output from mload loader log from today:

**** 09:38:15 UTY1812 A checkpoint is being initiated because 10000 output

     records have been sent to the RDBMS.

**** 09:38:22 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 1;

**** 09:38:23 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST

            (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,       

     MiscInt1,MiscInt2,MiscInt3,MiscInt4,       

     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,byteflag,MLoadCkpt)      

      VALUES (110, 1, 1, 1, 10000, 10000, 0, 10000, 0, 0, 0, 0, 0, 1, 1,

     :Ckpt);

**** 09:38:23 UTY0827 A checkpoint has been taken, recording that input record

     10000 has been processed for IMPORT 1 of this MultiLoad Import task.

**** 09:43:42 UTY1812 A checkpoint is being initiated because 20000 output

     records have been sent to the RDBMS.

**** 09:43:49 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 2;

**** 09:43:50 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST

            (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,       

     MiscInt1,MiscInt2,MiscInt3,MiscInt4,       

     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,byteflag,MLoadCkpt)      

      VALUES (110, 1, 2, 1, 20000, 20000, 0, 20000, 0, 0, 0, 0, 0, 2, 1,

     :Ckpt);

I am not sure what changed from yesterday to today. Can someone help me to resolve it?

Tags (1)
4 REPLIES
Enthusiast

Re: Mload Performance Issue

I have an Informatica mapping trying to load data from a flatfile to a table. The target table uses MLOAD to load the table in one of the database in teradata development database.

Till yesterday the mapping was taking 2 minutes and from today the mapping was running for more than 2 hours with the same source file. I tried to compare the MLOAD loader log and this is what i found out

Below is the output from mload loader log from yesterday:

**** 11:09:11 UTY0826 A checkpoint has been taken, recording that end of file

has been reached for IMPORT 1 of this MultiLoad Import task.

**** 11:09:12 UTY1812 A checkpoint is being initiated because 10000 output

records have been sent to the RDBMS.

**** 11:09:12 UTY0817 MultiLoad submitting the following request:

CHECKPOINT LOADING INTERVAL 1;

**** 11:09:13 UTY0817 MultiLoad submitting the following request:

USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST

(Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,

MiscInt1,MiscInt2,MiscInt3,MiscInt4,

MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)

VALUES (110, 1, 1, 1, 10000, 10000, 0, 10000, 0, 0, 0, 0, 0, 1, 1,

:Ckpt);

**** 11:09:13 UTY0827 A checkpoint has been taken, recording that input record

10000 has been processed for IMPORT 1 of this MultiLoad Import task.

**** 11:09:13 UTY1812 A checkpoint is being initiated because 20000 output

records have been sent to the RDBMS.

**** 11:09:13 UTY0817 MultiLoad submitting the following request:

CHECKPOINT LOADING INTERVAL 2;

**** 11:09:14 UTY0817 MultiLoad submitting the following request:

USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST

(Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,

MiscInt1,MiscInt2,MiscInt3,MiscInt4,

MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)

VALUES (110, 1, 2, 1, 20000, 20000, 0, 20000, 0, 0, 0, 0, 0, 2, 1,

:Ckpt);

Below is the output from mload loader log from today:

**** 09:38:15 UTY1812 A checkpoint is being initiated because 10000 output

records have been sent to the RDBMS.

**** 09:38:22 UTY0817 MultiLoad submitting the following request:

CHECKPOINT LOADING INTERVAL 1;

**** 09:38:23 UTY0817 MultiLoad submitting the following request:

USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST

(Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,

MiscInt1,MiscInt2,MiscInt3,MiscInt4,

MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)

VALUES (110, 1, 1, 1, 10000, 10000, 0, 10000, 0, 0, 0, 0, 0, 1, 1,

:Ckpt);

**** 09:38:23 UTY0827 A checkpoint has been taken, recording that input record

10000 has been processed for IMPORT 1 of this MultiLoad Import task.

**** 09:43:42 UTY1812 A checkpoint is being initiated because 20000 output

records have been sent to the RDBMS.

**** 09:43:49 UTY0817 MultiLoad submitting the following request:

CHECKPOINT LOADING INTERVAL 2;

**** 09:43:50 UTY0817 MultiLoad submitting the following request:

USING Ckpt(VARBYTE(1024)) INSERT DB_T_STAG_MEMBXREF_DEV.ML_POL_CLAIMS_LIST

(Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,

MiscInt1,MiscInt2,MiscInt3,MiscInt4,

MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,bytef lag,MLoadCkpt)

VALUES (110, 1, 2, 1, 20000, 20000, 0, 20000, 0, 0, 0, 0, 0, 2, 1,

:Ckpt);

I am not sure what changed from yesterday to today. Can someone help me to resolve it?

Enthusiast

Re: Mload Performance Issue

Did you talk to your DBA, if he sets priorities? It may be the size has increased by leaps and bounds.....How about network..... my initial thought
Enthusiast

Re: Mload Performance Issue

Yes I Spoke with my DBA and he said that he has changed nothing on the settings.

I think my DBA would have installed the MLOAD utility in informatica server and i want to see the settings of the MLOAD loader. I am not sure where to see those settings?

Teradata Employee

Re: Mload Performance Issue

Comparing with the old output, this part looks suspicious:

**** 09:38:23 UTY0827 A checkpoint has been taken, recording that input record

     10000 has been processed for IMPORT 1 of this MultiLoad Import task.

**** 09:43:42 UTY1812 A checkpoint is being initiated because 20000 output

     records have been sent to the RDBMS.

Was  the data fed in the same way? This could be checked by reviewing the "IMPORT" command in the mload script.

--Ivy.