Improving Loading Mechanism using Fastload

Tools & Utilities
Enthusiast

Improving Loading Mechanism using Fastload

Hi all!
I am new to Teradata and this is my first topic.
first of all, i like to share my problem (perhaps a challenge for those who advance in Teradata :) ).
Okay!

i have to load a lot of file from A system to teradata.
A system produces 40.000 files per day.
each file will consist of 40.000 records delimited by "|" and consist of 250 column.
and each file is in gz compression format (so it have to be gunzip first to read the file).

the goal is to load 40.000 file in less than a day.

what i have done are:
- gunzip -c per 15 file > to "special" mkfifo
- run fastload from A system that read "special" mkfifo
- run bteq to summarizing the data in to new table (the FACT table) using merge into statement.

so far the performance is around 12 minutes per 15 files.
i am using 36 amp teradata and using 32 session fastload.
with this performance i can only load 1.800 files in a day MAX.

the question is:
- is there any configuration that i need to set to make it run faster?
- is it posible to use more than 1 fastload in 1 system?
let say the performance is 1.800 file per fastload, so i can load it using 30 fastload to different table.
then insert it to just 1 single table from 30 fastload table. then just summmarize from that single table into the fact.

thanks in advance.
btw, ask me if the illustration is not clear enaough.
20 REPLIES
Junior Contributor

Re: Improving Loading Mechanism using Fastload

40.000*40.000 = 1.600.000.000 rows/day
That's a lot for a small 32 AMP system: 50.000.000/AMP/day

Hopefully "summarizing" reduces that number significantly, otherwise you run out of space within a few weeks.

32 session is far too much for 36 AMPs, try a multiple of the number of nodes.

What's the size of the target table, is it partitioned?

What's the timing for:
FastLoad Phase 1 (is it constrained by load server CPU/network/Teradata IO?)
FastLoad Phase 2
BTEQ Time for standalone Select vs. Insert/Select

"12 minutes per 15 files" -> 50.000/min seems to be quite slow.

You should probably increase the number of files per load and then might run 2 jobs rotational:
job 1 -> FastLoad -> BTEQ -> FastLoad -> BTEQ -> ....
job 2 -> FastLoad -> BTEQ -> FastLoad -> ....

This way there's always a FastLoad and an insert/select at the same time.

Dieter
Enthusiast

Re: Improving Loading Mechanism using Fastload

since the source file keep coming to landing point.
---
so, instead of increasing the session, wiser to increase number of fastload node.
for that reason, i come out with this scenario.

(1)FASTLOAD scenario:
1. using gunzip -c (number of file) > mkfifo -m.
2. fastload to target(n) table where the target is not partitioned and no index (since i can determine the index, but is it possible to have NUPI in fastload?).
3. run BTEQ to put to a single point of temporary_detail table that optimized for summary (have NUPI and NUSI).
4. add column date_load on temporary_detail table.
5. this scenario will run every 5 minute (but will not run if the same process already run).
6. this scenario will run in parallel node.

(2)SUMMARY scenario
1. run bteq to summarize from temporary_detail table for record where date_load between 2 hours before and 1 hour before.
2. run bteq to delete record where date_load more the 3 hours before.
3. this scenario will run every 1 hour.
4. this scenario will not be run in parallel.

40.000 files -> (1) fastload_1, fastload_2, ..., fastload_20 table -> temporary_detail table -> (2)fact table

run 20 fastload scenario and 1 summary scenario

so, with this fastload scenario, i can use around 20 fastload node. so that i can keep up with the 40.000 files in a day loading.
summary scenario is not a problem anymore if all the a day record has been loaded to teradata.

actually, the slow loading is not because the fastload configuration, but the gunzip process take a time to finish.
i am interesting with your statement that said
so, what the better number of session should i use for 36 amp if i were use 20 fastload node?

---
fyi, the summarizing time for about 2 million records is about 2 minutes.
and this is still not good because the source table is not using any index.
so it will take only 1 AMP in spool space to run this query.
i am hoping with the new scenario i can make the table structure for summarizing source is optimize.
so it can distribute the AMP evenly in spool space.

thanks Dieter
Junior Contributor

Re: Improving Loading Mechanism using Fastload

"2. fastload to target(n) table where the target is not partitioned and no index (since i can determine the index, but is it possible to have NUPI in fastload?)."

Each and every table in Teradata always has a Primary Index, if *you* don't specify it, the system will.
What's the PI of the target table and how the stage table will be summarized?
You should choose that PI to improve the following aggregate.

"3. run BTEQ to put to a single point of temporary_detail table that optimized for summary (have NUPI and NUSI)."

Instead of running 20 FastLoads just run 1 directly into that stage table, so you can skip that.
Btw, the number of parallel FastLoad jobs is usually limited to less than 20.
Depending on your release that number might be 5, but dba might incease it to 15 (or beyond).

"4. add column date_load on temporary_detail table."

No. Just define column date_load with a default current_date and you can skip that step, too.

"5. this scenario will run every 5 minute (but will not run if the same process already run)."

Better increase that interval

"6. this scenario will run in parallel node."

Does that mean you don't have load server and run that process on a Teradata node?
This is bad, gunzip using a lot of cpu will slow down the system.

"(2)SUMMARY scenario
1. run bteq to summarize from temporary_detail table for record where date_load between 2 hours before and 1 hour before."

Do you need that previously added column just for that purpose? Then it's better to use distinct stage tables for each FrastLoad, and you can drop that column, because there's no WHERE-condition.

"2. run bteq to delete record where date_load more the 3 hours before."

No. Use different stage tables.

"3. this scenario will run every 1 hour."

Then this is approx. the time for that single concurrent FastLoad.

"4. this scenario will not be run in parallel."

If a BTEQ job has finished you

"actually, the slow loading is not because the fastload configuration, but the gunzip process take a time to finish."

So you have to increase the cpu power of the load system until that bottleneck is removed.
If the network is the next bottleneck you have to increase it's speed until finally Teradata is the bottleneck.
And that bottleneck is easily removed by adding some Teradata nodes ;-)

"fyi, the summarizing time for about 2 million records is about 2 minutes.
and this is still not good because the source table is not using any index.
so it will take only 1 AMP in spool space to run this query.
i am hoping with the new scenario i can make the table structure for summarizing source is optimize.
so it can distribute the AMP evenly in spool space."

Probably the automatically created PI (most likely the first column) is totally skewed.

Could you provide some additional information?
- target table DDL (at least info about SET/MULTISET, PI, partitioning), table size/rowcount and cardinality of the PI
- # of nodes/AMPs of your system

Dieter
Enthusiast

Re: Improving Loading Mechanism using Fastload

i am still using the previous mentioned scenario with some changes:

1. Add Primary Index to fastload target table

- i have put primary index in fastload target table using multiple column that use in group by statement in summary scenario as your recommendation.

the result is AMP distribution is better than before, although it is not distributed equally, but it use all AMPs.

2. Use multiple staging table

- instead of using single temporary table, i use (n) temporary table where (n) is #fastload table used.

I am still using date_load column as a mentioned previously.

this strategy is done because using single temporary table have issue in inserting performance.

and some information what the table look like.

- (n) fastload target table will consist column name FIELD1, ..., FIELD235 where their type are varchar(200).

- (n) staging table will consist of call_time, subscriber_number, subscriber_target_number, call_location, call_service, call_charge, call_duration, date_load

- the fact table is consist of call_time, subscriber_number, subscriber_target_number, call_location, call_service, total_call_charge, total_call_duration, total_trx

btw, i still cant figure out on using only 1 fastload.

it slow and only load 15 files per 5 minute max.

so, what i have done is using 15 fastload in paralel.

right now the loading speed increase from 15 file per ~10 minutes to 15 files per 5 minute * 15 paralel fastload.

it should be around 225 per 15 minutes, 900 files per hour, 21600 files per day

but it still far from the target where 40.000 files per day.

the loading server is in another box.
Junior Contributor

Re: Improving Loading Mechanism using Fastload

"- (n) fastload target table will consist column name FIELD1, ..., FIELD235 where their type are varchar(200)."

1. Are those fields really 200 byte each or is it just "i don't know/care about the actual size"?
This calculates as 235*200 = 47000 bytes and FastLoad uses the maximum possible size to calculate the number of records per message, so this results in exactly 1 record per message sent to Teradata.

2. In your input file there are 235 fields, but your target table only got 8 colums?
If this is true, then *all* fields are sent across the network, but only those 8 are really needed.
Try to get rid of the unused fields (e.g. awk/sed after gunzip) or switch to MLoad which allows FILLER (but probably uses more resources).

Could you post the output of a FastLoad and the DDL of the target/stage tables?

Dieter
Enthusiast

Re: Improving Loading Mechanism using Fastload

is not that i don't care of how much each column length and type.
it is because the content will vary and depend on the record profile

let say there are 3 types of record profile with:

Type 1
column 1: value A
column 2: value B
column 3: value C
Column 4: value D

Type 2
Column 1: value A
column 2: value C
column 3: value D
column 4: value nulll

type 3
column 1: value A
column 2: value null
column 3: value E
column 4: value F

the profile determined by column 1.
and value A through F are different in type.

that is why i can't put exact type for a column.
for profile type 1 it might integer but for type 2 it might date with format YY-MM-DD HH:mm:ss.

i have try to only send column that i need.
but it not affect the performance.

please find the attachment for ddl
Enthusiast

Re: Improving Loading Mechanism using Fastload

please find the attachment for fastload and inserting to stage table log.
Enthusiast

Re: Improving Loading Mechanism using Fastload

okay.
i think i know the problem is

i set the wrong argument for SET SESSION
i use
SESSION 4 8;
it has to be
SESSION 8 4;

right now i can process 20 files per 5 minute.
and set the session to
SESSION 12 8;

but if there is another performance tips i can use, feel free to share :)
Junior Contributor

Re: Improving Loading Mechanism using Fastload

**** 00:35:21 Number of recs/msg: 1
**** 00:35:21 Starting to send to RDBMS with record 1
**** 00:36:05 Sending row 100000
**** 00:40:50 Sending row 200000
**** 00:43:51 Sending row 300000
**** 00:45:43 Sending row 375291
**** 00:45:43 Finished sending rows to the RDBMS

There's the bottleneck:
10+ minutes for 375.000 rows

"i have try to only send column that i need.
but it not affect the performance."

Because FastLoad still sends all the fields even if they're not used for insert (just don't ask me why).
You need about 15 fields, but send 235. As i already wrote you have to remove those unnecessary fields *before* FastLoading. This results in about 20 recs/msg. And if you modify that VARCHAR(200) to the actual max size for each field
it's probably much more (max size is COSTBAND VARCHAR(40)). Finally this will improve performance for FastLoad's insert phase more than 20x.

But i'd switch to MultiLoad and use FILLER for those fields, easier to maintain and no more FastLoad table:
Insert/Select -> Insert directly into stage table in MLoad LABEL
UNION ALL -> 2 APPLY ... WHERE ...
TIMESTAMP '2008-10-22 00:45:52' -> SYSDATE4/SYSTIME

Dieter