Keeping a Lid on Concurrency within the Vantage Platform

I took my mother to the traveling Van Gogh art exhibit when it stopped by Los Angeles a few years back. The museum had strict enforcement in place on how many visitors could be inside viewing Van Gogh paintings at the same time. The staff kept track of how many people they let in, and how many were exiting, so the total number of visitors inside never exceeded a self-imposed limit. By correlating entrances to exits they could ensure that all visitors, once inside, ended up with a quality viewing experience without anyone needing to elbow their way to the front of an over-crowded room to get a brief glimpse of the sunflowers or the self-portraits.

In a similar manner, a quality experience for end users of the Vantage platform can be assured by smart use of workload management rules known as “throttles”. Throttles are the gate-keepers that control the flow of requests that are allowed to enter the Advanced SQL Engine and begin execution. There are wide variety of different types of these throttles, and they can be combined or set up in a hierarchy for more granular control.

Enterprise Vantage platforms often support explorative or analytic types of requests that can require unusually high levels of resource. At certain times of the day, requests can pile up from users trying to create models or find answers to complex business questions, just as more art lovers may line up to view masterpieces during the lunch hour.

Throttles are effective at alleviating congestion and blocking in the database because they even out the level of work that is active on the platform. As a result, throttles can provide more predictable response times for the most important work and keep overall demand for platform resources steady.

The original type of throttle, introduced 15 years go and still in wide use today, is the “system throttle.” This all-purpose type of throttle can be used to manage all queries that enter the system or can be laser-beamed to cover a particular type of work.

Many customers use system throttles to put a ceiling on the number of requests that are performing sophisticated in-database analytics, such as requests using the SCRIPT table operator. You can also use system throttles to keep the number of queries submitted by one particular user, or a defined group of users, to one or two at a time. Another popular use of system throttles is to restrict concurrency of queries that are estimated to take, for example, over an hour to run. Data Lab users, who tend to run a lot of exploratory queries, can be limited in how many requests in combination they can execute by means of a system throttle.
Creating a throttle rule is actually simpler than managing the flow of visitors at an art show. The administrator gives the throttle rule a name, provides a query limit and adds classification criteria that describes the types of requests that the rule will manage. Once the rule is activated, the specified limit (for example 5 queries at a time) will set the upper threshold for how many requests that match the throttle’s classification criteria will be allowed to execute concurrently.

Workload management code keeps a counter of active queries that are under the control of each active throttle rule, just like the counter used by the museum staff. Every time a new query under the control of a throttle begins to run, the counter is incremented. Queries that would cause the limit to be exceeded are placed in a delay queue. When one of the managed queries completes, the counter is decremented and the first query in the delay queue is released.

Throttles have become so popular among Teradata customers that over time different variations evolved from the original system throttle. Here are a few of the specialized types of throttles available on the Vantage platforms today, and their uses:

Utility throttles:

Allows limits to be placed on the number of load or export utility jobs that will be allowed to run concurrently. At certain times of day when online queries and daytime activities are more numerous, there is an advantage in restricting load utilities, which often use large amounts of resource. Holding back the number of load utility jobs that run during the day allows more resources to be available for things such as dashboards, customer portal applications, or end user business queries and reports.

Workload throttles:

Allows you to hold down concurrency for just a single workload (a workload represents a priority grouping). Workload throttles are most useful on low priority workloads that have an erratic, sometimes extreme demand on resources. But they can also be useful for medium and even high priority workloads as a mechanism to reign in unexpected peaks in demand within those more critical types of work.

Virtual partition throttles:

Opportunity to limit on concurrency on an entire sub-division of work running on the platform. Virtual partitions are a method of dividing platform resources by higher-level groupings such as business units, tenant groups or geographic areas. During setup, virtual partitions are given an allocation percent that defines their share of CPU and I/O. Placing a throttle on a virtual partition provides a greater degree of control over how much work can get started in each.
One nice thing about throttles is that they can be programmed to automate concurrency limit changes by time of day, or day of week, or month of year.

My mother and I did not really mind waiting a few minutes to enter the Van Gogh exhibit. Because once we were admitted, the experience of unobstructed, up-close viewing of these much-admired paintings was profound and unforgettable. Taking steps to manage congestion before it happens, whether on the freeway, at the airport, in a museum or on a database platform improves the overall experience when you get the green light. In Teradata Vantage, throttles are one of the many capabilities in place to ensure that every query that runs in the database has a quality execution.
 
To read about other capabilities that enable quality experiences for all active requests in Vantage, read my white paper titled Born to Be Parallel and Beyond.