Think big, start small: a good initiative to design green query optimizers

摘要

Recently scientists, politicians, students, associations and actors are sounded the alarm to save our planet. The slogan of Greta Thunberg "Our house is on fire" urges any person to act on the climate. As researchers in the field of databases, one of the most active research communities, we are compelled to propose little and big steps to save our planet. It should be noticed that DBMSs are one of the main energy consumers, as responsible to store and efficiently process data. In data stores, research on energy consumption has been mainly focused on some specific types of stores: data centers, database clusters, known as big infrastructures. These stores are computer warehouses dedicated to store and process in a parallel manner a large amount of data. They include different servers and network infrastructures. Energy consumption in traditional DBMSs got less attention compared to data centers, and at the same time, they are widely used in the actual applications. In DB-Engine (https://db-engines.com/en/ranking) ranking DBMSs according to their popularity, traditional DBMSs (Oracle, MySQL, SQL Server, PostgreSQL, DB2) are the top 5 of the most popular systems. This motivates us to integrate energy consumption in the components of these DBMSs. Query optimizers are one of the energy consumer's components. The actual studies were focused on integrating energy in query optimization in the mono-core processor architecture. Recently, thanks to multi-core, these studies have to be revisited. In this paper, we propose a new approach to integrate the energy dimension into query optimizers in the multi-core processor architecture. Firstly, we present a rich state of the art on energy consumption in the context of traditional databases. Secondly, a crossing from sequential query processing mode to parallel mode is given. Thirdly, we propose a cost model capturing energy in a multicore architecture. Its parameter values are obtained by using non-linear regression and neural network techniques. Finally, our cost model is integrated into the query optimizer in PostgreSQL on which several experiments were conducted showing the efficiency and effectiveness of our proposal.