Teradata 3.0.1 for Windows NT

Enterprise scalability for NT data warehouses

[Editor's Note: Denielle Otey of TECA helped develop the benchmarking program I used to test this product. The Windows 2000 Magazine Lab helped run the tests.]

Many companies are turning to data-warehousing and OLAP decision-support applications to gain a competitive advantage. NCR targets its Teradata 3.0.1 for Windows NT database software for this rapidly growing market. The company designed Teradata to provide enterprise-level scalability through a Massively Parallel Processor (MPP) architecture. MPP lets the product scale upward by dividing the workload among multiple processor nodes that use a shared-nothing architecture. (MPP systems are a collection of two or more loosely coupled SMP processors that use a shared SCSI disk subsystem.)

Teradata runs under NT on standard PC hardware but supports only single-node SMP scalability. On NCR's WorldMark servers, Teradata can support as many as four MPP clustered nodes under NT. The next Teradata version, which NCR plans to release in the summer of 2000, will support as many as 64 processor nodes under NT.

System Architecture
The Teradata system can perform basic online transaction processing (OLTP)-type work, but its primary strength lies in its ability to process queries and support data-warehousing and decision-support applications. The Teradata system's parallel-processing architecture lets it divide a query workload among its processing nodes, which then work independently on the query's different parts.

When the Teradata system first receives an SQL request, the Parsing Engine (PE) checks the request for valid SQL syntax. Each PE is a virtual processor that manages sessions, optimizes queries, and decomposes the incoming SQL statements into parallel steps that multiple nodes execute simultaneously. NCR has developed Teradata's PE since 1983, and it is one of the most highly developed query processors available. You can configure multiple PEs on a multinode system such as the WorldMark 4800 server. The system I tested had two PEs. After the PE optimizes the query, it sends the query instruction steps to the Access Module Processes (AMPs) through the Bynet.

The core of Teradata's database architecture, the AMP, is a virtual processor that retrieves data and performs database-management operations such as joins, locks, and aggregation. Each AMP owns a given set of data that Teradata divides among the AMPs at row level, and Teradata automatically distributes data among the AMPs as you add data to a table. A significant management benefit of the Teradata database is that you don't need to be concerned about the physical placement of data because Teradata offers no way to control data placement. The Teradata system automatically handles all data placement. NCR configured my test system to use 10 AMPs. Figure 1 illustrates Teradata's basic system architecture.

Teradata's software architecture provides enterprise-class reliability. Each AMP is a virtual processor, so you can move AMPs among the processor nodes in the event of a hardware node failure. AMPs also support a high-availability feature called fallback tables, which are basically a copy of a table that Teradata duplicates to different AMPs. Fallback tables provide protection from AMP or disk failure. For an additional level of software fault tolerance, you can configure AMPs in clusters.

Teradata is a fully relational database system that supports the standard ANSI-92 SQL syntax for performing basic SQL Data Definition Language (DDL) and Data Manipulation Language (DML) statements. The Teradata database supports multiple databases as well as views, triggers, and Referential Integrity (RI), which you enable through Referential Constraint Checks that you can define for parent and child tables. The Teradata database also provides support for international languages.

Although Teradata supports the entire range of SQL query options, including outer joins and left outer joins, it lacks support for stored procedures. This omission would be a serious limitation in an OLTP database, but NCR designed the Teradata database primarily as an OLAP decision-support database, which doesn't usually require stored procedures for query-type processing. However, NCR claims that the next release of Teradata will include support for stored procedures.

Teradata also has an unusual requirement in that each table must have a primary index that isn't a separate structure from the table. This requirement is a result of how Teradata distributes rows among different AMPs. The Teradata system uses the index to ensure row uniqueness. Table 1 lists Teradata's capacities under NT.

System Overview
I reviewed Teradata on a WorldMark 4800 system that came in a two-cabinet configuration in which one cabinet housed two 4-way Xeon 550MHz processor nodes, and the second cabinet contained the Bynet interprocessor communication network and the disk subsystem. Each processor node had 1GB of memory, four 9GB 10,000rpm drives, two 10/100 Ethernet LAN connections, and a UPS. The disk subsystem consisted of two NSC disk array controllers with eighty 9GB drives that used a RAID 1 (mirrored) implementation with a total capacity of 360GB. The WorldMark 4800 system provides several fault-tolerant hardware facilities to deliver enterprise-class availability. The system also provides dual-processing nodes, dual Bynets, redundant RAID controllers, multiple LAN connections, and redundant power supplies.

In addition to the two-cabinet server, the WorldMark 4800 includes an Administration Workstation (AWS) that you use to manage and monitor WorldMark MPP systems' processing and storage subsystems. The AWS consisted of a standard NCR PC running NT 4.0. The system had a 600MHz Pentium III processor and 128MB of RAM, one 9GB 10,000rpm drive, and a special Ethernet adapter that connects the workstation to the WorldMark 4800 server. You use a keyboard/video/mouse (KVM) switch to switch access between the AWS and each processor node on the WorldMark 4800. Figure 2 shows the graphical systems-management interface that the AWS provides.

Database Tools and Utilities
The Teradata ODBC driver provides basic client connectivity to the Teradata system. On all client systems that connect to the Teradata system, you must install the ODBC driver and configure a data source that points the client system to the Teradata server. In my testing, I found that the driver was easy to set up but it didn't support unattended installation to client systems—I had to manually configure the driver at each client system. In addition to the ODBC driver, the Teradata system supplies a Java Database Connection (JDBC) driver for Java application development and a WinCLI software development kit (SDK) for non-ODBC-database application development.

Teradata provides several client utilities that let you manage and manipulate the database. The Teradata Manager is a suite of management programs that you use to perform primary administrative tasks. WinDDI is the primary administrative tool in the suite. This tool provides a graphical interface, which Figure 3 shows, that lets you create databases, users, and tables, as well as perform administrative tasks such as setting permissions and creating triggers.

For ad hoc data queries and management tasks, the Teradata system provides the Queryman tool. Similar to SQL Server's Query Analyzer, the Queryman utility lets you enter and run SQL statements from its Query pane. The lower portion of the Queryman interface, which Figure 4 shows, displays the results of executed SQL statements. In this History pane, you can retrieve and rerun an SQL statement by double-clicking the statement.

NCR primarily designed the Teradata system for data-warehousing applications that are populated by data derived from operational OLTP applications, so the system provides several data-loading tools. Teradata's array of data-loading tools includes FastLoad, MultiLoad, and TPUMP. FastLoad quickly populates empty tables. MultiLoad loads and updates tables that contain existing data, and TPUMP trickle loads data into a table as a continuous background task. Teradata also provides the FastExport tool, which can export data from the Teradata database into a flat text file.

For workload management, the Teradata system supplies an optional Database Query Manager (DBQM) utility that governs the workload that client connections submit. This utility helps you manage server availability by limiting the amount of disk and CPU resources that a given client connection can allocate.

Scalability Testing
To test Teradata's database-growth and multiuser scalability, I conducted several tests on the Teradata database server running on the WorldMark hardware. I conducted these tests on single- and dual-node configurations to determine the scale-out capabilities of Teradata's MPP architecture.

To measure database scalability and query performance, I ran 15 decision-support queries and measured the time Teradata took to complete the entire set of queries. This set of tests provided an indication of system scalability as the volume of data increased. These queries represented a mixed system workload in which seven of the queries generated a light system workload, seven queries provided a medium system workload, and one query generated a heavy system workload. We ran the first set of queries on a 10GB database, then ran the same set of queries on a 100GB database and measured the time the system needed to complete both test sets.

In Graph 1, you can see that the single-node configuration ran the query set in 23 minutes and 33 seconds using the 10GB database and 3 hours, 57 minutes, and 55 seconds using the 100GB database. Using the 10GB database in a dual-node configuration, Teradata required 17 minutes and 58 seconds to execute the complete set of queries. Using the 100GB database in a dual-node configuration, Teradata required 2 hours and 29 minutes to execute the same set of queries. The goal in this type of test is for the database system to provide linear scalability as the database size increases. Thus, if Teradata requires 17 minutes and 58 seconds (i.e., 1078 seconds) to complete the 10GB test, ideally, the system would complete the 100GB test in 10 times that amount of time (i.e., 10,780 seconds, or 2 hours, 59 minutes, and 40 seconds). My test results show that the Teradata system approached linear scalability in a single-node configuration and provided better than linear scalability in a dual-node configuration. These tests show that adding a second node to your configuration results in a performance improvement in the system's ability to process large databases.

To test the system's ability to scale upward as you add users to the system, I ran the same set of 15 queries against the 10GB database using varying numbers of attached client systems, and I measured the time that Teradata required to complete the entire set of queries for all the client systems. To minimize the possibility that the Teradata system will cache data, each client system ran the query set in a random order. In addition, I randomly selected the values for each query from the set of all possible data values.

Graph 2 shows the results of the multiuser throughput tests. In the ideal results, the database system would provide linear scalability as the number of users increases. In other words, if the query set requires 17 minutes and 58 seconds (i.e., 1078 seconds) to execute at the one-user level, ideally the system can complete the query set for 56 users in 56 times the total amount of time required for one user (i.e., 60,368 seconds, or 16 hours, 46 minutes, and 8 seconds). In my tests, at the 56-user level on a dual-node system, the Teradata system completed the query set in 9 hours, 53 minutes, and 27 seconds. In both configurations, the Teradata system provided better than linear scalability. The Teradata system was able to make use of the overlapping database I/O that the different client systems requested.

In addition, the Teradata system's performance improved by jumping from a single-node to a dual-node configuration. However, my test results don't show a linear increase in capacity from adding a second node. The Teradata technical support personnel explained that adding a node to a single-node configuration doesn't usually result in linear scalability. However, adding two nodes to a dual-node configuration typically results in a linear increase in performance. The support person also noted that a bigger improvement would have been evident if I had run the tests against the larger 100GB database.

During the multiuser throughput testing, I ran into a couple of problems that were related to the Teradata ODBC driver. While the Teradata system provided excellent scalability as the number of users increased, I found that I was unable to run tests using multiple or virtual test applications per client system. Running multiple client applications produced an error in which the Teradata system refused additional client connections. At the time of this writing, NCR was unable to determine the cause of this error. In addition, while I was investigating this condition, I found that the Teradata ODBC driver consumed 100 percent of the CPU utilization for the client system while it was executing a query. NCR confirmed that this situation was typical and resulted from the driver periodically polling the Teradata system. This polling is designed to let the driver process asynchronous queries. The Teradata technical support contacts stated that the driver would let client applications get processor time, but I didn't measure the effect on client performance in my testing.

An Enterprising Database Server
NCR usually sells the WorldMark server and the Teradata database as a packaged solution that includes the system setup and consulting aid required to get the system up and running. In addition, NCR's Global Support Center (GSC) provides 24 * 7 system and software support. The enterprise-level performance that the Teradata system demonstrated in my testing paralleled the recent TPC-R scores that NCR posted with the Transaction Processing Performance Council (TPC). Running under Windows 2000, the Teradata system posted top TPC-R scores of 21,254 queries per hour. For more information about these scores, go to http://www.tpc.org.

I found the Teradata system to be an excellent platform for enterprise data-warehousing and decision-support applications. The WorldMark 4800 system running Teradata for NT clearly proved its ability to handle very large databases (VLDBs) and provide excellent multiuser scaling. In addition, the system's automated database-tuning and data-placement feature makes it easier to maintain than competing enterprise database solutions that require the administrator to explicitly select the data's physical placement.

Although the Teradata system provides excellent database scaling and query performance, it's not a general-purpose OLTP-type database system. NCR's omission of basic features, such as stored procedures support, underline Teradata's strong emphasis toward decision support and data-warehousing implementations.