In this case study, we look at tactical workloads on a Teradata system. While Teradata has received features over the years that support tactical workloads, it is still a category of workload for which the physical design must be perfectly chosen to meet user expectations for query speed.
There is nothing more frustrating than designing a tactical workload on a test environment, only to find out on the production system that it does not come close to meeting user expectations.
Over the years, I have often seen applications that execute tactical queries fail to meet user expectations because they were implemented as if they were implementing a data warehouse for a strategic workload.
Let’s take as an example a web application in which the user has the possibility to retrieve information about individual customers. This is actually a classic OLTP task and not necessarily a suitable workload for a data warehouse system. The user will usually expect to be able to click through the customer’s data in real-time. Usability falls and stands with performance.
As you can imagine, we need stable query runtimes to have satisfied users. No user will find it satisfying if one and the same query takes 2 seconds and then again several minutes. But this is exactly what I have experienced many times because these specifics of tactical workload were not taken into account. The application is developed on an unloaded test system, the bad surprise comes when the application is put into production.
The cause of the performance problem
Teradata is a parallel database system and designed to read large amounts of data as efficiently as possible. Unfortunately, this is not what we need for a tactical workload. Here it is about accessing individual physical rows on the disk as efficiently as possible (reading and writing).
The big problem with tactical queries is full table scans, as their runtime is very dependent on the overall load of the Teradata system. Of course, the runtimes can theoretically be improved by increasing the size of the system (by adding new nodes); but first, this is usually just not feasible, and second, the query result can be there a little faster, but the runtimes are still not stable. I have seen projects where a dedicated Teradata system was bought just to run the application isolated on it. This will usually not be possible for various reasons, and then the question arises: Why does it have to be on a Teradata system? Why not on a system that is specialized for the tactical workload?
The optimization goal of tactical query tuning is to minimize the number of I/Os needed to access one or a few rows from a single AMP, as shown in the below image. In an optimal case, a tactical query execution plan localizes the work in only one AMP and requires just one data block transfer from disk to memory.
Teradata Workload Management supports tactical queries (which have the above characteristics) with their own workload group, in which AWTs are reserved and only used for single-AMP accesses. This guarantees that tactical queries get their turn immediately and do not have to wait for AWT availability.
However, a query is immediately moved to a lower priority workload group should the query not behave as expected to protect valuable resources.
A query that relies on single AMP access ensures almost the same execution time for each run, which is our goal. Of course, only as long as the execution plan stays the same.
How to design tactical workload
We need to distinguish between retrieve steps and join steps.
With retrieve steps it is relatively simple: we need a primary index (UPI, NUPI) or similar access (for example, single table join index). Primary index access ensures that data rows can be localized within one or a few data blocks and access time is guaranteed to be stable.
Joins are a different kind of workload as they usually involve most or all AMPs. Still, we can design the workload in a way to make use of the nested join and merge joins involving only a few AMPs – the only two join methods which can be restricted to one or a few AMPs. The best option we have is the nested join as it can be used in an initial step to restrict for example to exactly one customer.
Another advantage of single-AMP operations is that the parsing engine uses ROWHASH-level locking for the retrieving step, and a separate locking step is not needed. ROWHASH-level locking allows for greater concurrency as many tactical queries can access the same table at the same time.
So, as we have seen, our main task in designing tactical workloads is to obtain an execution plan that does not involve full table scans but predominantly single-AMP or group-AMP accesses.
As we mentioned, the primary index access in each step is our most important tool. Therefore, it is often advantageous to use a database dedicated to the application submitting tactical queries. Because then we are free in our decision to model the indexes as we need them. Unfortunately, this is not always possible. From my experience, the same database is often used for tactical and strategic workload, and the free choice of the primary index is not given. Then there is still the possibility to design direct access using a Join Index.
A special trick is to create a single table join index that acts like a “hashed” NUSI. I.e. we have the advantages of direct access combined with the advantage that the index does not have to be unique.
For row partitioned tables, the partition columns should always be part of the primary index, if possible, since this ensures that a row can only be located in exactly one partition.
Find more information about tactical workload in out book which is available on Amazon: https://www.amazon.com/gp/product/B08KHP87JB/ref=as_li_tl?ie=UTF8&tag=dwhpro-20&camp=1789&creative=9325&linkCode=as2&creativeASIN=B08KHP87JB&linkId=faa17919f70aff354fc3d36998354779