Performance Tips for Teradata Datamover 13.01

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Performance Tips for Teradata Datamover 13.01

Teradata Data Mover makes moving tables and other objects between Teradata systems easier than ever. However, the initial release, 13.01, does require manual tuning and configuration in order to get the best performance results. Here are some tips and recommendations to improving Data Mover performance.

Maximizing Network Throughput

Teradata Data Mover comes pre-installed on a Teradata Managed Server, however it is up to the user to properly configure the network to maximize Data Mover performance. When configuring the network, we suggest the following:

  • Use as many of the Teradata Managed Server's ethernet ports as possible.
  • Assign COP entries to specific ports to distribute session connectivity between the Teradata Managed Server and the source and target Teradata Databases.
  • Use bonding if available.

The DM Agent is the Data Mover component that requires the heaviest network usage. If adding additional DM Agents on separate Teradata Managed Servers, make sure the network is properly configured for those machines as well.

Optimizing Data Mover Throughput

There are three main factors for optimizing how much work Data Mover will process at any given time: 1) the maximum number of tasks that each DM Agent will process concurrently, 2) the number of DM Agents available to execute tasks, and 3)  the maximum number of jobs that Data Mover will run concurrently. Depending on the user's environment, increasing these factors may increase Data Mover's overall throughput while in other cases these factors may need to be decreased to more efficiently process work.

Modifying a DM Agent's Concurrent Task Limit

Each Data Mover job is composed of one or more steps with each step containing one or more tasks. When it comes to executing a job's step, all tasks within that step are sent to a queue where they wait to be picked up by the DM Agent and executed. The DM Agent is in turn capable of executing multiple tasks at the same time.

In cases where a lot of tasks need to be executed, increasing the DM Agent's concurrent task limit can increase Data Mover's overall throughput. This includes scenarios such as running multiple jobs concurrently or when using TPTAPI or JDBC to move multiple tables (jobs that use ARC use a single task to move all the tables in the job whereas jobs using TPTAPI or JDBC have separate tasks for each table).

The DM Agent's concurrent job limit is controlled by the agent.maxConcurrentJobs (a misnomer) setting in the DM Agent's agent.properties file. To change the limit, stop the DM Agent, modify the agent.properties file, and then restart the DM Agent.

Adding Additional DM Agents

Increasing the DM Agent's concurrent task limit allows more tasks to be executed at the same time but it also means that more tasks are competing for the DM Agent's resources. Continuing to increase the concurrent task limit will eventually result in an overload of the DM Agent's resources and individual task performance will suffer. Adding additional DM Agents provides a way to increase the number of tasks being executed concurrently while also providing additional resources for those tasks to use.

Adding additional DM Agents can help improve performance for the following scenarios:

  • Running multiple concurrent Data Mover jobs
  • Moving lots of tables in a single Data Mover job that uses TPTAPI or JDBC

For Data Mover 13.01, adding additional DM Agents will not help performance in the following scenarios:

  • Moving lots of tables in a single Data Mover job that uses ARC
  • Moving large tables

Note: In Data Mover 13.01, there is no automatic load balancing when using multiple DM Agents. There is no awareness as to how much work is involved for each task nor is there a guarantee that the tasks needing to be executed will be split up equally amongst the available DM Agents. The DM Agents pull tasks from the queue on a first-come-first-serve basis and each DM Agent will continue to consume tasks until it has reached its concurrent task limit. For example, if there are five tasks in the queue and there are two DM Agents each with a concurrent task limit of five, it is possible that all five of those tasks will be executed on just one of the DM Agents.

Modifying Concurrent Job Limit

The maximum concurrent job limit is the first limitation enforced by Data Mover. This limit determines the number of jobs that Data Mover will run at any given time. Modifying this limit alone will not likely result in an increase in throughput as, once a job is running, it is competing with all running jobs for space on the DM Agent(s) to run its tasks. When looking to increase throughput, it is suggested that you first try one of the two above methods first before increasing the concurrent job limit.

Data Mover's concurrent job limit is controlled by the jobExecutionCoordinator.maxConcurrentJobs setting in the DM Daemon's daemon.properties file. To change the limit, stop the DM Daemon, modify the daemon.properties file, and then restart the DM Daemon.

Load Slot Limit Considerations

The load slot limit needs to be taken into consideration when optimizing Data Mover throughput. The Teradata database restricts how many jobs using the FastLoad or MultiLoad protocols can run at any given time. This restriction is controlled by the Teradata database's MaxLoadTask setting. For Data Mover, this limit applies to any job using TPTAPI Load or TPTAPI Update. To avoid hitting the database's limit, Data Mover provides its own limit referred to as the load slot limit. Data Mover's load slot limit is applied at the task level. For example, if the Data Mover load slot limit is set to 5 and there are 5 Data Mover jobs running, each with 2 tasks that use either TPTAPI Load or TPTAPI Update, then Data Mover will only allow 5 of the total 10 tasks to run at the same time even if the DM Agent(s) is capable of executing more tasks. Therefore, when considering increasing the DM Agent's concurrent task limit or adding additional DM Agents, you should first analyze your workload to see if there is even room within the load slot limit to run additional concurrent tasks.

When setting Data Mover's load slot limit, we recommend setting the limit at or below the Teradata database's MaxLoadTask limit. Allowing Data Mover to manage the load slots is more efficient than relying on the Teradata database's limit.

Tuning Individual Data Mover Jobs

If you are running Data Mover jobs without providing values for key performance settings like number of sessions and streams, then you are likely not getting the best performance for your jobs. A properly tuned Data Mover job can run anywhere from 50% to 1000% faster than a Data Mover job using the default performance settings. Here are some recommendations based on which underlying utility is being used by Data Mover:

Tuning Data Mover Jobs Using ARC

Factor Recommendation
Number of streams Using multiple streams helps on a per table basis. Work for each table is spread across the multiple parallel streams. Streams only work on one table at a time however so there is no added benefit for jobs with multiple tables. Recommended to use 2 streams for tables in the 1 to 100 GB range, 4 streams for tables in the 100 to 500 GB range, and 8 streams for tables larger than 500 GB. Only use 1 stream for small tables (< 1 GB) as multiple streams provide no benefit in that case.
Number of source sessions Recommended to use 1 source session for every 2 target sessions.
Number of target sessions For tables larger than 1 GB, recommended to use 10 to 20 target sessions depending on size of target system.
Number of jobs Splitting up tables amongst several DM jobs can significantly improve performance particularly when moving large tables. The drawbacks are that each DM job in this case requires a unique target Teradata user as an ARC COPY job will fail if the same Teradata user is already logged on. For moving multiple small tables, this method is not recommended as the efficiency of a DM job can be lowered when competing with other concurrent DM jobs.

 

Tuning Data Mover Jobs Using TPTAPI Load

Factor Recommendation
Number of streams N/A. Multiple streams only available for ARC in DM 13.01. For TPTAPI, each table is moved using a single stream on a single agent.
Number of source sessions In test environment with 2 node and 4 node systems, 3 to 6 sessions produced best results. Range of optimal sessions will likely be higher on bigger systems. Note though that using too many sessions will lead to performance degradation.
Number of target sessions In test environment with 2 node and 4 node systems, 5 to 10 sessions produced best results. Range of optimal sessions will likely be higher on bigger systems. Not though that using too many sessions will lead to performance degradation.
Number of jobs If table DDL is a bottleneck, such as in a case where moving multiple small tables, splitting up tables amongst several DM jobs can provide performance benefit as DDL will get run in parallel in that case versus being run sequentially in a single DM job. Otherwise using multiple DM jobs with TPTAPI versus a single DM job with TPTAPI does not provide any additional benefit as single DM jobs with TPTAPI already provided data movement parallelism for multiple tables.

 

Tuning Data Mover Jobs Using TPTAPI Update

Factor Recommendation
Number of streams N/A. Multiple streams only available for ARC in DM 13.01. For TPTAPI, each table is moved using a single stream on a single agent.
Number of source sessions In test environment with 2 node and 4 node systems, 3 to 6 sessions produced best results. Range of optimal sessions will likely be higher on bigger systems. Note though that using too many sessions will lead to performance degradation.
Number of target sessions In test environment with 2 node and 4 node systems, 5 to 10 sessions produced best results. Range of optimal sessions will likely be higher on bigger systems. Not though that using too many sessions will lead to performance degradation.
Number of jobs If table DDL is a bottleneck, such as in a case where moving multiple small tables, splitting up tables amongst several DM jobs can provide performance benefit as DDL will get run in parallel in that case versus being run sequentially in a single DM job. Otherwise using multiple DM jobs with TPTAPI versus a single DM job with TPTAPI does not provide any additional benefit as single DM jobs with TPTAPI already provided data movement parallelism for multiple tables.

 

Tuning Data Mover Jobs Using TPTAPI Stream

Factor Recommendation
Number of streams N/A. Multiple streams only available for ARC in DM 13.01. For TPTAPI, each table is moved using a single stream on a single agent.
Number of source sessions In test environment with 2 node and 4 node systems, 3 to 6 sessions produced best results. Range of optimal sessions will likely be higher on bigger systems. Note though that using too many sessions will lead to performance degradation.
Number of target sessions In test environment with 2 node and 4 node systems, 5 to 10 sessions produced best results. Range of optimal sessions will likely be higher on bigger systems. Not though that using too many sessions will lead to performance degradation.
Number of jobs If table DDL is a bottleneck, such as in a case where moving multiple small tables, splitting up tables amongst several DM jobs can provide performance benefit as DDL will get run in parallel in that case versus being run sequentially in a single DM job. Otherwise using multiple DM jobs with TPTAPI versus a single DM job with TPTAPI does not provide any additional benefit as single DM jobs with TPTAPI already provided data movement parallelism for multiple tables.

 

Tuning Data Mover Jobs Using JDBC

Factor Recommendation
Factor Recommendation Number of streams N/A. Multiple streams only available for ARC in DM 13.01. For JDBC, each table is moved using a single stream on a single agent.
Number of source sessions Recommended to use 3 to 6 sessions. Using too many sessions can lead to performance degradation.
Number of target sessions Recommended to use 5 to 10 sessions. Using too many sessions can lead to performance degradation.
Number of jobs If table DDL is a bottleneck, such as in a case where moving multiple small tables, splitting up tables amongst several DM jobs can provide performance benefit as DDL will get run in parallel in that case versus being run sequentially in a single DM job. Otherwise using multiple DM jobs with JDBC versus a single DM job with JDBC does not provide any additional benefit as single DM jobs with JDBC already provided data movement parallelism for multiple tables.

 

Tuning Data Mover Jobs With Unknown Utility

In many cases, you may wish to leave it up to Data Mover to choose the utility to use to perform the job. In these cases, you may not be aware ahead of time which utility Data Mover will choose to use. The safest route in these cases is to set the number of sessions recommended to use with jobs using TPTAPI Load and to set the number of streams recommended to use with jobs using ARC. Setting the number of streams to use does not cause any problems if ARC is not chosen to run the job. If ARC is not chosen, the number of streams specified is ignored.

Limiting the Impact of Progress Reporting and Logging

Getting information on how and what a Data Mover job is doing is obviously necessary but gathering this information can also negatively impact a Data Mover job's performance. Here are a few recommendations to limit the impact of gathering this information:

Set TMSM Reporting Frequency as High as Possible

Data Mover is designed to report certain information to TMSM for each Data Mover job running. Included in this is reporting progress information for each job with row and byte counts. This information is useful particularly if using TMSM to follow a job's progress. However, if this information is reported too often then the job's performance can be negatively impacted. To prevent this, the TMSM reporting frequency should set as high as possible.

The TMSM reporting frequency is controlled by the tmsm.frequency.bytes setting. This setting can be modified by using the list_configuration and save_configuration commands.

Only Use Log Level 99 for Debugging

Data Move 13.01 provides three logging levels for individual jobs: 0,1, and 99. Log levels 0 and 1 have the exact impact on a Data Mover job's performance. Log level 0 simply means that the normal logging gather for the job will not be stored once the job finishes unless an error occurs while log level 1 means that the normal logging will always be stored. Log level 99, on the other hand, causes Data Mover to increase the information being logged. Log level 99 also causes jobs using TPTAPI Load, Update, or Stream, to output special TPTAPI operator logs. All of this extra logging has a negative impact to performance so log level 99 should only be used in situations where you are debugging a problem.

Tags (2)
1 REPLY

Re: Performance Tips for Teradata Datamover 13.01

Hi i am getting the following error while moving data from 1 environment to another...
could you please help me resolve it??

ERROR:com.teradata.jdbc.jdbc_4.util.jdbcexception:
[Teradata Database] [TeraJDBC 13.10.00.03] [Error 3134] [SQLState HY008] The request was aborted by abort session command.