Archives of the TeradataForum

Message Posted: Thu, 12 Jul 2001 @ 18:31:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

While we don't really push Teradata for OLTP alone, Teradata is extremely powerful for mixing certain types of OLTP and DSS. This is especially true when the DSS is substantial (very ad-hoc and very complex workload) and the OLTP access path is known; via a PI.

We have many customers at various stages of using Teradata for various amounts of mixed DSS / OLTP workload.

Many other databases say they mix OLTP and DSS but when you look under the covers they are really doing mostly OLTP and very little DSS and the DSS that they can do is very simple basic reporting on small amounts of data. If you try to mix this type of workload on another database, be very careful to ask the right questions on how this will work.

There are two challenges to mixing OLTP and DSS. These are approach and workload management.

The first is the OLTP approach.

There are many designers that try to simply treat Teradata like the database

that they are familiar with and use Secondary indexes. This approach will not work very well, unless the OTLP volume is very low, until some of the new features in V2 R5 or V2 R6.

The key to keeping OLTP simple on Teradata is that you have a known access path. To this end, you have a few options.

Option #1

Use a Primary Index value that is known, and access through a UPI or a NUPI with a small number of duplicate rows / NUPI value. At the largest this would fit into whatever your data block size is set to. This approach allows for a single amp operations and is truly linear scalable with the size of your system. This scalability is very powerful because few system can promise this level of scalability.

Option #2

Use a USI / NUPI combination to make this a two amp operation but with the row id from the USI to minimize overhead in the row selection on the NUPI. This also scales linearly with the size of your system but at roughly 1/2 the rate of Option #1.

Option #3

Involves some additional complexity using some newer techniques.

If you don't have an known access path, then you will either have to figure out a method of creating a known access path, For example adding data to a nearly unique Secondary index to create a USI or using some other approach (there are a few) to identify a known access path.

The second major challenge is workload management. This is faced by all systems trying to mix DSS and OLTP workload. This management must come in two forms. Technology and Process.

The Teradata Priority Scheduler is the technology piece to workload management on Teradata. This technology is very good and always improving. It needs to be planned and maintained for your specific workload on your system.

The process part needs to come from the people that maintain and run the system supporting the mixed workload. This is often the hardest part to implement because there are often so many people involved. DBA, Operations, Users (Ad-hoc, OLTP, Applications) The top things to keep in mind are having and using a true test and development environment, managing batch and ad-hoc workload, setting performance expectations, understanding your DSS and OLTP workload volumes and patterns, and finally setting the various workload priorities

Good Luck,

Eric

EJK
Eric J. Kohut
Senior Solutions Consultant - Teradata Solutions Group - Retail
Certified Teradata Master
NCR Corp.

	<Prev	Next>		<<First	<Prev	Next>	Last>>