Towards Eco-friendly Database Management Systems W. Lang, J. M. Patel (U Wisconsin), CIDR 2009

1. Towards Eco-friendly Database Management SystemsW. Lang, J. M. Patel (U Wisconsin), CIDR 2009 Shimin Chen Big Data Reading Group

2. Introduction • Energy consumption is important for data centers: • 2005: 1.2% of total US energy consumption is attributed to powering and cooling servers, ~ $2.7B • If current methods for powering data centers continue, the consumption will nearly double by 2011 • For DBMS: • Previously large ignored energy efficiency • Must start considering energy as a critical metric

3. This paper: ecoDB • New project: energy efficient data processing techniques • Two broad classes of techniques: • “global”: change how entire system is managed or used • E.g. job scheduling • “local”: improve methods of processing data at individual nodes (focus of the paper)

4. Idea

5. Two Questions • (1) “How does a system generate graphs as shown in Figure 1?” • DMBS must know HW capabilities and operating characteristics • Accurately estimate / continuously measure energy consumption • (2) “How can such a graph be used?” • Systematic method to change settings • Service level agreements (SLAs) • This paper focuses on mechanisms for creating graphs

6. Outline • Introduction • Processor Voltage/Frequency Control (PVC) • Improved Query Energy-efficiency by Introducing Explicit Delays (QED) • Opportunities for Energy Efficiency • Summary

7. Techniques • CPU freq = front side bus (FSB) freq * CPU multiplier • DVFS (dynamic voltage and frequency scaling) • Each p-state defines a CPU multiplier • CPU voltage is based on CPU multiplier • Under-clocking (Focus of this paper) • Reduce FSB freq • Finer granularity • Also changes RAM freq

8. System Under Test • System components: • ASUS P5Q3 Deluxe Wifi-AP motherboard • Intel Core2-Duo E8500 • 2×1GKingston DDR3 main memory • ASUS GeForce 8400GS 256M • Western Digital Caviar SE16 320G SATA disk • Power supply unit (PSU): a Corsair VX450W PSU • System power draw measured by a Yokogawa WT210 unit (suggested by SPEC Power benchmark) • MS Windows Server 2008 • JDBC (Java 1.6)

9. Power • CPU power sensors on motherboard: • ASUS motherboard has an EPU processor that directly measures the CPU power. • ASUS P5Q3 Deluxe 6-Engine software displays information gathered from this hardware sensor. • Current CPU wattage displayed in GUI: • The authors sample the GUI every second • Compute CPU joules using the average CPU wattage and the execution time of a workload

10. Component powers • No hard disk, no operating system • Focusing on CPU power: • CPU power consumption is often about 25% of the total system power consumption in the experiments

11. DB test • Workload • Use a commercial DBMS and MySQL 5.1.28 • TPC-H (ad-hoc decision support), scale factor 1.0 (1GB data) • Only run Query 5: six table join and a group by • A run consists of ten queries with different parameters • FSB underclocking (allowed by ASUS 6-engine software) • Stock (normal) • Reduce FSB freq by 5%, 10%, and 15% • CPU voltage downgrade • “small” and “medium” downgrade • 7 settings: • Stock, 3 FSB freq reductions X 2 CPU voltage downgrades

12. Equal Energy delay product

13. Equal Energy delay product With the same voltage level, larger frequency the better EDP

14. Theoretical Modeling • EDP= joules x times = power x time2= power / freq2 • Power=CV2F • EDP = CV2/F

15. Disk Energy • Measured separately for stock setting • Warm database • CPU: 1228.7 Joules • Disk: 214.7 Joules • Cold database • CPU: 2146.0 Joules • Disk: 1135.4 Joules

16. Outline • Introduction • Processor Voltage/Frequency Control (PVC) • Improved Query Energy-efficiency by Introducing Explicit Delays (QED) • Opportunities for Energy Efficiency • Summary

17. Idea • Explicitly delay queries • look for commonalities among multiple queries • Group multiple queries into a single query • After execution, split query results

18. Setting • DB clients repeatedly issue single table select queries with different selection predicate. • For example: SELECT *FROM lineitemWHERE l_quantity=X • DBMS processes one query at a time • QED: buffer queries in a queue, merge them, send the merged query, split results • In the experiments, X is different for the queries, so no overlaps

19. As batch size increases, diminishing decrease in energy consumption.

20. Outline • Introduction • Processor Voltage/Frequency Control (PVC) • Improved Query Energy-efficiency by Introducing Explicit Delays (QED) • Opportunities for Energy Efficiency • Summary

21. Opportunities in (DBMS) Software • Traditional DB investigations into improving query response times • Energy vs. performance tradeoffs • Operator-level: rethink join algorithms • Query-level: energy-efficient query plans • Workload management per server • Workload management for the entire collection of servers: scheduling and using techniques to turn entire servers off

22. Summary • Energy-efficient data processing • Studied two techniques • Processor Voltage/Frequency Control (PVC) • Improved Query Energy-efficiency by Introducing Explicit Delays (QED) • Designing a DBMS to balance the response time vs. energy consumption opens a wide range of research issues