Improving the energy efficiency of relational and NoSQL databases via query optimizations

• Published in: Sustainable computing informatics and systems 2019-06, Vol.22, p.120-133
• Main Authors: Mahajan, Divya, Blakeney, Cody, Zong, Ziliang
• Format: Article
• Language: English
• Subjects: Cassandra; DVFS; Energy-efficiency; MongoDB; MySQL; NoSQL databases; Relational databases
• ISSN: 2210-5379
• DOI: 10.1016/j.suscom.2019.01.017

•We conduct a comprehensive study (first of its kind to the best of our knowledge) on the impact of numerous query optimization techniques for MySQL, MongoDB, and Cassandra on performance, power, and energy consumption.•We develop an easy-to-use power measurement tool that can accurately measure the fine- grained real-time power consumption of various queries running on MySQL, MongoDB, and Cassandra.•We present a methodology using Speedup, Powerup, and Greenup to reveal the correlations between performance, power, and energy efficiency of relational and NoSQL databases.•We prove that in some scenarios energy efficiency optimization is neither merely a byproduct nor a conflicting goal of performance optimization. There are optimization techniques that can help energy more than performance.•We compare the performance and energy characteristics of relational and NoSQL databases using the Yahoo! Cloud Server Benchmark (YCSB) and ˜100GB of customized Twitter data.•We reveal the impact of different DVFS policies on the performance and energy efficiency of MySQL, MongoDB and Cassandra. As big data becomes the norm of various industrial applications, the complexity of database workloads and database system design has increased significantly. To address these challenges, conventional relational databases have been constantly improved and NoSQL databases such as MongoDB and Cassandra have been proposed and implemented to compete with SQL databases. In addition to traditional metrics such as response time, throughput, and capacity, modern database systems are posing higher requirements on energy efficiency due to the large volume of data that need to be stored, queried, updated, and analyzed. While decades of research in the database and data processing communities has produced a wealth of literature that optimize for performance, research on optimizations for energy efficiency has been historically overlooked and only a few studies have investigated the energy efficiency of database systems. To the best our knowledge, there are currently no comprehensive studies that analyze the impact of query optimizations on performance and energy efficiency across both relational and NoSQL databases. In fact, the energy behavior of many basic database operations (e.g. insertion, deletion, searching, update, indexing, etc) remains largely unknown due to the lack of accurate power measurement methodologies for various databases and queries. In this paper, we investigate a series o