An Object Placement Advisor for DB2 Using Solid State Storage

Mustafa Canim, Bishwaranjan Bhattacharjee, George A. Mihaila, Christian A. Lang, Kenneth A. RossPVLDB 2009 · DOI

TL;DR

This paper introduces an "Object Placement Advisor" (OPA) for database administrators to intelligently decide which database objects (tables, indexes, etc.) to place on expensive but fast Solid State Drives (SSDs). By analyzing workload performance profiles, OPA helps maximize performance gains while minimizing SSD storage costs, outperforming naive placement strategies.

What Problem Does This Paper Address?

The advent of Solid State Drives (SSDs) offers a significant performance boost for database systems due to their dramatically lower latency for random I/O operations compared to traditional Hard Disk Drives (HDDs). However, SSDs are considerably more expensive per gigabyte. This creates a challenge for database administrators (DBAs): how to strategically place database objects on SSDs to achieve the greatest performance improvement without overspending on storage. Simply placing all data on SSDs is cost-prohibitive, while a trial-and-error approach is inefficient and may not yield optimal results. DBAs need a systematic way to answer questions like: "Which specific data should go on the SSD?", "How much better will performance be?", and "Is it worth the investment?".

Methods

The core of the proposed solution is the Object Placement Advisor (OPA), an off-line tool that operates in two phases:

  1. Profiling Phase:
    • The system first creates a benchmark installation where each database object (table, index, materialized view) resides in its own tablespace on HDDs.
  2. A workload is run against this setup, and IBM DB2's snapshot utility is used to collect detailed I/O statistics. This includes physical reads/writes (sequential and random) and the time taken for these operations.
  3. Crucially, this phase allows for the measurement of average sequential and random access costs per page for each object. For SSDs, these costs are assumed to be constant and significantly lower than HDDs.
  4. Decision Phase:
    • The collected profiling data is used to estimate the gain (performance improvement) for moving each object from HDD to SSD. This gain is calculated as the difference in I/O time between HDD and SSD for each object, weighted by the number of I/O operations.
  5. The problem is then framed as a 0-1 Knapsack Problem: maximize the total gain (value) by selecting objects to place on the SSD, subject to a constraint on the total SSD storage capacity (budget).
  6. Two common algorithms are employed to solve this knapsack problem:
  7. Dynamic Programming: Provides optimal solutions but can be computationally expensive and memory-intensive for large datasets.
  8. Greedy Heuristic: Ranks objects by a "priority value" (gain per unit of storage) and selects them in descending order of priority until the budget is met. This is computationally more efficient.

The OPA also includes a mechanism to characterize the SSD device itself through microbenchmarks, providing its sequential and random read/write performance.

Datasets

The paper evaluates the Object Placement Advisor using workloads based on two well-known industry benchmarks:

  • TPC-H: Used to simulate an Operational Data Store (ODS) environment. The authors modified TPC-H queries to access smaller data fragments, making random I/O more critical.
  • TPC-C: Used to simulate an On-Line Transaction Processing (OLTP) environment, which is characterized by frequent updates and random accesses.

The authors created custom databases for these benchmarks, with a TPC-H scale factor of 30 (resulting in ~39GB of data) and 20 Warehouses for TPC-C (resulting in ~2.2GB of data).

Key Insights and Ideas

  • Strategic Placement is Key: Simply moving all data to SSDs is often overkill and cost-inefficient. The real benefit comes from intelligently identifying and moving the most impactful objects.
  • Gain vs. Cost: The "gain per unit of storage" (priority value) is a critical metric for making cost-effective placement decisions. Objects that offer a high performance improvement for a small storage footprint are prioritized.
  • Workload-Specific Optimization: The effectiveness of SSD placement is highly dependent on the specific workload. The OPA's ability to profile actual I/O behavior is crucial for tailoring recommendations.
  • Beyond Index Placement: While moving indexes to SSDs is a common naive strategy due to their random access patterns, the OPA demonstrates that data objects can also be significant candidates for SSD placement, depending on their access frequency and I/O characteristics.
  • SSD Characteristics Matter: The OPA accounts for specific SSD performance metrics (random vs. sequential, read vs. write) and compares them against HDD performance to calculate gains.

Key Results

The experiments demonstrated significant performance improvements with the Object Placement Advisor:

  • TPC-H:
    • A naive approach of placing all indexes on SSDs (5.5 GB) yielded a 16% speedup.
  • The OPA's recommended placement (1.8 GB SSD) achieved a 51% speedup, significantly outperforming the naive approach in terms of performance per gigabyte.
  • Placing all data on SSDs (39 GB) provided the fastest execution (53 minutes) but required a much larger storage footprint compared to the OPA's recommendation (68 minutes).
  • TPC-C:
    • The OPA identified specific objects (New_Order_DT, Item_IX, Item_DT, Stock_IX, Customer_IX, and Stock_DT) for SSD placement (0.5 GB).
  • This resulted in a significant increase in transactions per minute (tpmC) compared to no SSD usage or placing all indexes on SSDs. The OPA's approach achieved a substantial improvement with a much smaller SSD footprint than placing all data on SSDs.

Limitations and Future Work

  • Optimizer Awareness: The current system relies on the standard DB2 optimizer, which is not SSD-aware. This means the optimizer might generate query plans optimized for HDDs, potentially missing opportunities for further gains on SSDs. Future work includes making the optimizer SSD-aware.
  • Single SSD Device: The current work focuses on a single SSD device. Future work aims to extend the advisor to handle multiple storage tiers with different SSDs or other storage technologies.
  • Other Storage Technologies: The authors suggest their methods can be applied to other emerging storage technologies beyond SSDs.
  • MTTF and Energy Consumption: While performance is the primary focus, other factors like Mean Time To Failure (MTTF) and energy consumption are mentioned as important considerations for future work.
  • Page Granularity: The current approach places whole database objects on either HDD or SSD. Future work could explore page-level placement, though this introduces complexities like data fragmentation.

Summary updated

BibTeX
@article{canim2009object,
  author = {Mustafa Canim and Bishwaranjan Bhattacharjee and George A. Mihaila and Christian A. Lang and Kenneth A. Ross},
  title = {{An Object Placement Advisor for DB2 Using Solid State Storage}},
  journal = {Proc. VLDB Endow.},
  year = {2009},
  doi = {10.14778/1687553.1687557},
}
Figure