Download (direct link):
For example, if the configuration is adequate from an I/O perspective but the CPU is at maximum capacity, adding a node tends to alleviate the problem.
If you need more nodes but not storage space, determine if you should:
• Add nodes to existing cliques to share existing disk arrays.
• Add nodes plus disk arrays to maintain the current ratio.
• Add nodes plus AMPs and redistribute data to reduce the amount of storage managed by each AMP.
Reconfiguring Your Teradata RDBMS
When you add nodes, AMPs, or disks to a Teradata RDBMS configuration, you must reconfigure your TPA. The Reconfiguration utility can do most things automatically, such as partitioning the disks and redistributing your data rows.
However, it is good practice to first archive your data as a hedge against errors. Also, some types of expansion, such as adding a disk array, still require row redistribution, so if your tables are very large, you may find it faster to archive and restore.
You can use the Reconfig Estimator utility to obtain an estimate of elapsed time needed for reconfiguration, based on the number and size of the data tables on your current system. The Reconfig Estimator prompts you for information about the planned upgrade and provides estimates for the following phases:
• Rebuilding secondary indexes
12 - 28 Teradata RDBMS Database Administration
Chapter 12: Troubleshooting
Solving Bottlenecks by Expanding Your Teradata RDBMS Configuration
If you have questions about possible procedures or the reported time estimates, contact the TSC. For an overview of the utilities, issues, and procedures involved in a TPA reconfiguration, see "Viewing or Modifying the Teradata RDBMS Configuration" on page A-13.
Scaling Your Applications
Your user applications should be scalable.
For example, assume that table BigT is a very large table but its rows are hash distributed based on only 16 unique values. Applications using BigT perform well and with high parallelism on a system with 1 or 2 nodes and 8 to 16 AMPs.
If you then expand the system to 128 AMPs, the rows of BigT still hash to only 16 unique values, and so are still distributed among only 16 AMPs. Thus, applications will not perform any better, and perhaps not as well.
To ensure scalability of your applications, try to make your primary index a unique index. If a single column does not provide uniqueness, combine two or more columns. You can define up to 16 columns for one primary index.
The result is many more unique hash combinations, and your applications should continue to perform well as your system expands.
12 - 28
Teradata RDBMS Database AdministrationChapter 13:
Teradata provides a variety of tools with which you can manage your resources and develop applications. The table below offers suggestions on their use.
IF you want to . THEN .
improve application performance use COLLECT STATISTICS (Optimizer form), QCF, TLE, Baseline Profiling, and DBQL to determine whether and how you should alter your Teradata RDBMS: • Application design • Physical database design • Tuning parameters • Configuration balance
improve query throughput use INSERT EXPLAIN .WITH STATISTICS or COLLECT STATISTICS (QCD form), DBQL, Teradata Visual Explain, and the Index Wizard to analyze planned and existing indexes. Use the Index Wizard recommendations to develop indexes that provide the most efficient data distribution and retrieval.
manage resource utilization through priorities and limits use TDQM and Priority Scheduling to: • Manage workload throughput • Customize the delegation of resources among the various workload processes • Achieve optimum balance of workload size and density
identify and eliminate the reasons for performance anomalies use DBQL, Teradata Manager, Teradata Performance Monitor, and Teradata Visual Explain to: • Monitor performance in near-real time • Compare performance results, based on query structure and processing behavior
plan capacity needs relative to workload growth use DBC.AMPUsage, DBQL, ResUsage, and PMON to understand current and projected: • Resource usage • Performance trends for the purpose of accurately: • Predicting the need for expansion • Sizing additional hardware
Teradata RDBMS Database Administration
13 - 1Chapter 13: Database Analysis
The rest of this chapter introduces the tools you can use to take best advantage
of the query analysis capabilities of Teradata RDBMS, including:
• "Target Level Emulation (TLE)" to replicate your production configuration in a safe test environment
• "Query Capture Facility (QCF)" for index analysis using an SQL interface to capture data demographics, collect statistics, and implement the results
• "Teradata Index Wizard" for SI analysis and recommendations, using data captured via QCF and/or DBQL capabilities
• "Teradata Visual Explain Utility" to compare results from a query run at different times, on different releases, or with different syntax