Is Your Big Data Hot, Warm, or Cold?

In part one of this series, the focus was on developing a set of key performance indicators (KPIs) to better understand the “temperature” of your data. Table 1 describes the generally accepted classifications used when discussing multi-temperature data or storage.

This article provides a high-level view of how workload management (WLM) can both help and enforce the prioritization of queries based on business requirements and the “temperature” of the data being accessed.

Managing a multi-temperature workload

Let’s see if a picture is really worth a thousand words. AAs shown in Table 2, we can assume the following definitions for high, normal, and low priority as it they relates to the temperature of the data being accessed.

The workflow in Figure 1 shows management of workloads by the age (or temperature) of the data. In this example, data that is less than 4 months old when queried is high priority; data that is between 4 and 10 months old is normal priority; and data that is 10 months and older is low priority.

Figure 1: Multi-temperature workload management

Implementing concurrency controls and thresholds

Figure 2 shows concurrency controls that determine the number of concurrent queries that can be executed for a given priority.

Figure 2: Implementing queues and thresholds in a workload management workflow.

If there are more queries than the number allowed to execute concurrently, those queries are put into a queue. When the number of queries concurrently executing drops below the concurrency level specified, the queries that were queued up are automatically released to execute. Figure 2 also shows that it is possible to define thresholds, while Table 3 lists a subset of the thresholds that can be implemented along with the actions that are available for each threshold. A complete list of available thresholds and descriptions can be found in the IBM® DB2® 10.1 Information Center for Linux, UNIX, and Windows in the section called “Control of work with thresholds.”

Going global

For those familiar with workload management, Figure 2 shows queuing implemented at the service class level. You can also implement queuing at other levels in DB2 such as the workload definition level. Using the countries listed in the article “Going Global with Data Mart Consolidation,”  you can also define concurrency controls (and other WLM constructs) in which each country is an individual workload with its own set of concurrency controls. For simplicity, only the concurrency controls for China and Brazil are shown in Figure 3.

Figure 3: Defining concurrency controls for China and Brazil.

Using multi-temperature workload management with storage groups

Storage groups are an excellent fit for the implementation and management of a WLM multi-temperature storage scheme. Figure 4 shows data that is range-partitioned in table spaces defined using the storage groups HOT, WARM, and COLD. Each is defined with a different type of storage.

Figure 4: Storage group and table space configuration.

While the implementation details are not covered in this article, note that DB2 WLM can identify which table spaces any particular query is accessing and then act accordingly to prioritize and manage each query. The examples in Figures 1, 2, and 3 apply directly to the configuration depicted in Figure 4 from a workload management point of view.

In my next article, I will demonstrate how to use SQL to implement a multi-temperature workload management solution based on business requirements. In the meantime, please feel free to share your experiences with multi-temperature workload management in the comments.