Skip to main content

Advertisement

SpringerLink
Log in

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

  • Published:
Cluster Computing Aims and scope Submit manuscript

Abstract

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.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9
Fig. 10
Fig. 11
Fig. 12
Fig. 13
Fig. 14
Fig. 15
Fig. 16
Fig. 17
Fig. 18
Fig. 19
Fig. 20
Fig. 21
Fig. 22
Fig. 23
Fig. 24
Fig. 25
Fig. 26
Fig. 27
Fig. 28
Fig. 29
Fig. 30

Similar content being viewed by others

Notes

  1. https://cop23.com.fj/

  2. https://cop24.gov.pl

  3. https://db-engines.com/en/ranking

  4. Solid State Drives

  5. https://forge.lias-lab.fr/projects/ecoprod

  6. https://www.postgresql.org/docs/10/static/how-parallel-query-works.html

  7. http://www.tpc.org/tpch

  8. https://www.powermeterstore.com/p1206/watts_up_pro.php

  9. https://docs.oracle.com/cd/B10501_01/server.920/a96533/ex_plan.htm

  10. https://www.oracle.com/technetwork/database/manageability/owp-sql-monitoring-128746.pdf

  11. Giga Byte

References

  1. Jin, Z., Xu, G., Li, Y., Liu, P.: A novel cloud scheduling algorithm optimization for energy consumption of data centres based on user qos priori knowledge under the background of WSN and mobile communication. Clust. Comput. 20(2), 1587–1597 (2017)

    Article  Google Scholar 

  2. Itani, W., Ghali, C., Kayssi, A.I., Chehab, A., Elhajj, I.H.: G-route: an energy-aware service routing protocol for green cloud computing. Clust. Comput. 18(2), 889–908 (2015)

    Article  Google Scholar 

  3. Mastelic, T., Oleksiak, A., Claussen, H., Brandic, I., Pierson, J.-M., Vasilakos, A.V.: Cloud computing: survey on energy efficiency. ACM Comput. Surv. 47(2), 33:1–33:36 (2014)

    Google Scholar 

  4. Liebert, E.: Five strategies for cutting data center energy costs through enhanced cooling efficiency, White Paper. http://www.emersonnetworkpower.com/documentation/en-us/brands/liebert/documents/white%20papers/data-center-energy-efficiency_151-47.pdf (2007). Accessed 15 Mar 2018

  5. Tsirogiannis, D., Harizopoulos, S., Shah, M.A.: Analyzing the energy efficiency of a database server, In: Proceedings of the sigmod, pp. 231–242 (2010)

  6. Associates, C.R.: Energy efficiency policy options for Australian and New Zealand data centres: E3 equipment energy efficiency, ENERGY RATING, Report. http://energyrating.gov.au/document/report-energy-efficiency-policy-options-australian-and-new-zealand-data-centres (2014). Accessed 22 Jan 2019

  7. Info-Tech, Top 10 energy-saving tips for a greener data center, Tech. Rep. http://static.infotech.com/downloads/samples/070411_premium_oo_greendc_top_10.pdf (2007). Accessed 19 Dec 2018

  8. Roukh, A., Bellatreche, L., Ordonez, C.: Enerquery: Energy-aware query processing, In: Proceedings of the 25th ACM International Conference on Information and Knowledge Management (ACM CIKM), pp. 2465–2468 (2016)

  9. T.N. R.D. Council: “America’s data centers consuming and wasting growing amounts of energy,”. http://www.nrdc.org/energy/files/data-center-efficiency-assessment-IB.pdf (2014). Accessed 10 Apr 2018

  10. Shehabi, A., Smith, S.J., Sartor, D.A., Brown, R.E., Herrlin, M., Koomey, J.G., Masanet, E.R., Horner, N., Azevedo, I.L., Lintner, W.: “United states data center energy usage report,” Energy Technology Area, Report. https://eta.lbl.gov/publications/united-states-data-center-energy (2016). Accessed 21 Jan 2019

  11. Sadler, R.: Video demand drives up global co2 emissions. Climate news network. https://climatenewsnetwork.net/video-demand-drives-global-co2-emissions/ (2017). Accessed 2 Feb 2019

  12. Moore, F.: Data center energy consumption: Enormousdata centers creating a hyperscale heat wave, White Paper. https://www.fujifilmusa.com/products/tape_data_storage/case_studies/pdf/Hyperscale_Heat_Wave.pdf, (2016). Accessed 21 Jan 2019

  13. Graefe, G.: Database servers tailored to improve energy efficiency, In: Proceedings of the EDBT, pp. 24–28. ACM (2008)

  14. Harizopoulos, S., Shah, M., Meza, J., Ranganathan, P.: Energy efficiency: the new holy grail of data management systems research. arXiv:0909.1784 (2009)

  15. Poess, M., Nambiar, R.O.: Energy cost, the key challenge of today’s data centers: a power consumption analysis of TPC-C results. PVLDB 1(2), 1229–1240 (2008)

    Google Scholar 

  16. Roukh, A., Bellatreche, L., Boukorca, A., Bouarar, S.: Eco-physic: eco-physical design initiative for very large databases. Inf. Syst. 68, 44–63 (2017)

    Article  Google Scholar 

  17. Guo, B., Yu, J., Liao, B., Yang, D., Lu, L.: A green framework for DBMS based on energy-aware query optimization and energy-efficient query processing. J. Netw. Comput. Appl. 84, 118–130 (2017)

    Article  Google Scholar 

  18. Xu, Z., Tu, Y.-C., Wang, X.: “Exploring power-performance tradeoffs in database systems,” In: Proceedings of the ICDE, pp. 485–496 (2010)

  19. Ouared, A., Ouhammou, Y., Bellatreche, L.: Costdl: a cost models description language for performance metrics in database, In: Proceedings of the ICECCS, , pp. 187–190. IEEE Computer Society (2016)

  20. Oracle: Parallel execution with oracle database 18c fundamentals. Oracle Database. https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf (2018). Accessed 22 Jan 2019

  21. N. SCIENCE: Anatomy of an electromagnetic wave. NASA. Tour of the Electromagnetic Spectrum (2018)

  22. Venkatachalam, V., Franz, M.: Power reduction techniques for microprocessor systems. ACM Comput. Surv. (CSUR) 37(3), 195–237 (2005)

    Article  Google Scholar 

  23. Mittal, S.: A survey of techniques for improving energy efficiency in embedded computing systems, CoRR. arXiv:abs/1401.0765 (2014)

  24. Kim, N., Austin, T., Blaauw, D., Mudge, T., Flautner, K., Hu, J., Jane Irwin, M., Kandemir, M., Narayanan, V.: Leakage current: Moore’s law meets static power. Computer 12, 68–75 (2003)

    Google Scholar 

  25. Ge, R., Feng, X., Cameron, K.W.: Performance-constrained distributed DVS scheduling for scientific applications on power-aware clusters, p. 34. https://doi.org/10.1109/SC.2005.57 (2005)

  26. Moldovan, D.I.: Parallel Processing: From Applications to Systems, 1st edn. Morgan Kaufmann Publishers Inc., San Francisco (1992)

    Google Scholar 

  27. El-Rewini, H., Abd-El-Barr, M.: Advanced Computer Architecture and Parallel Processing (Wiley Series on Parallel and Distributed Computing). Wiley, New York (2005)

    Google Scholar 

  28. Multiprocessors, multicomputers, and clusters. http://www.edwardbosworth.com/My5155_Slides/Chapter13/Multiprocessors_01.pdf. Accessed 22 Feb 2019

  29. Blake, G., Dreslinski, R., Mudge, T.: A survey of multicore processors. Signal Process. Mag. IEEE 26(6), 26–37 (2009)

    Article  Google Scholar 

  30. Rakhee Chhibber, D.R.: Multicore processor, parallelism and their performance analysis, Int. J. Adv. Res. Comput. Sci. Technol. IJARCST. (2014)

  31. Ge, R., Feng, X., Cameron, K.W.: Performance-constrained distributed dvs scheduling for scientific applications on power-aware clusters, In: Proceedings of the 2005 ACM/IEEE Conference on Supercomputing, ser. SC ’05, p. 34, IEEE Computer Society, Washington, DC, USA. https://doi.org/10.1109/SC.2005.57 (2005)

  32. Lang, W., Patel, J.: Towards eco-friendly database management systems. arXiv:0909.1767 (2009)

  33. Tu, Y.-C., Wang, X., Zeng, B., Xu, Z.: A system for energy-efficient data management. ACM SIGMOD Rec. 43(1), 21–26 (2014)

    Article  Google Scholar 

  34. Xu, Z., Wang, X., Tu, Y.-C.: Power-aware throughput control for database management systems. In: Proceedings of the ICAC, pp. 315–324 (2013)

  35. Intel and Oracle, “Oracle exadata on intel® xeon® processors: extreme performance for enterprise computing,” White Paper. https://goo.gl/eSyrGw (2011). Accessed 20 Mar 2018

  36. Hayamizu, Y., Goda, K., Nakano, M., Kitsuregawa, M.: Application-aware power saving for online transaction processing using dynamic voltage and frequency scaling in a multicore environment. In: Berekovic, M., Fornaciari, W., Brinkschulte, U., Silvano, C. (eds.) Architecture of Computing Systems–ARCS 2011, pp. 50–61. Springer, Berlin (2011)

    Chapter  Google Scholar 

  37. Korkmaz, M., Karsten, M., Salem, K., Salihoglu, S.: Workload-aware CPU performance scaling for transactional database systems, In: Proceedings of the 2018 International Conference on Management of Data, , pp. 291–306. ACM (2018)

  38. Woods, L., István, Z., Alonso, G.: Ibex: an intelligent storage engine with support for advanced SQL offloading. Proc. VLDB Endow. 7(11), 963–974 (2014)

    Article  Google Scholar 

  39. Poon, K.K.W., Wilton, S.J.E., Yan, A.: A detailed power model for field-programmable gate arrays. ACM Trans. Des. Autom. Electron. Syst. 10(2), 279–302 (2005)

    Article  Google Scholar 

  40. Hurson, A., Azad, H.: Energy Efficiency in Data Centers and Clouds. Academic Press, Cambridge (2016)

    Google Scholar 

  41. Breß, S., Siegmund, N., Heimel, M., Saecker, M., Lauer, T., Bellatreche, L., Saake, G.: Load-aware inter-co-processor parallelism in database query processing. Data Knowl. Eng. 93, 60–79 (2014)

    Article  Google Scholar 

  42. Cheng, X., He, B., Lau, C.T.: Energy-efficient query processing on embedded CPU-GPU architectures, In: Proceedings of the 11th International Workshop on Data Management on New Hardware, p. 10. ACM (2015)

  43. Meza, J., Shah, M.A., Ranganathan, P., Fitzner, M., Veazey, J.: Tracking the power in an enterprise decision support system, In: Proceedings of the ISLPED, pp. 261–266. ACM (2009)

  44. Schall, D., Hudlet, V., Härder, T.: Enhancing energy efficiency of database applications using SSDS. In: Proceedings of the Third C* Conference on Computer Science and Software Engineering, pp. 1–9. ACM (2010)

  45. Chandrasekharan, S., Gniady, C., Qamem: Query aware memory energy management, In: Proceedings of the 2018 18th IEEE/ACM International Symposium on Cluster, Cloud and Grid Computing (CCGRID), IEEE (2018)

  46. Xu, Z., Tu, Y., Wang, X.: Dynamic energy estimation of query plans in database systems, In: Proceedings of the IEEE 33rd International Conference on Distributed Computing Systems, , pp. 83–92. ICDCS 2013, 8-11 July, 2013, Philadelphia, Pennsylvania, USA. https://doi.org/10.1109/ICDCS.2013.21 (2013)

  47. Xu, Z., Tu, Y., Wang, X.: Online energy estimation of relational operations in database systems. IEEE Trans. Comput. 64(11), 3223–3236 (2015). https://doi.org/10.1109/TC.2015.2394309

    Article  MathSciNet  MATH  Google Scholar 

  48. Liu, X., Wang, J., Wang, H., Gao, H.: Generating power-efficient query execution plan (2013)

  49. Lang, W., Kandhan, R., Patel, J.M.: Rethinking query processing for energy efficiency: slowing down to win the race. IEEE Data Eng. Bull. 34(1), 12–23 (2011)

    Google Scholar 

  50. Rodriguez-Martinez, M., Valdivia, H., Seguel, J., Greer, M.: Estimating power/energy consumption in database servers. Proc. Comput. Sci. 6, 112–117 (2011)

    Article  Google Scholar 

  51. Roukh, A., Bellatreche, L.: Eco-processing of OLAP complex queries, In: Proceedings of the International Conference on Big Data Analytics and Knowledge Discovery, pp. 229–242. Springer (2015)

  52. Roukh, A.: Estimating power consumption of batch query workloads, In: Proceedings of the International Conference on Model and Data Engineering, pp. 198–212. Springer (2015)

  53. Kunjir, M., Birwa, P.K., Haritsa, J.R.: Peak power plays in database engines, In: Proceedings of the EDBT, pp. 444–455. ACM (2012)

  54. Simon Pierre Dembele, L.B., Roukh, Amine: Vers des optimiseurs verts de requêtes en mode parallèle, In: Actes des 14èmes journées francophones sur les Entrepôts de Données et l’Analyse en Ligne, Business Intelligence & Big Data, EDA, RNTI, Ed., pp. 179–194 (2018)

  55. Cho, S., Melhem, R.: Corollaries to Amdahl’s law for energy. Comput. Arch. Lett. 7, 25–28 (2008). 02

    Article  Google Scholar 

  56. Elmasri, R., Navathe, S.: Fundamentals of Database Systems, 6th edn. Addison-Wesley Publishing Company, Boston (2010)

    MATH  Google Scholar 

  57. Mohan, C.: Impact of recent hardware and software trends on high performance transaction processing and analytics. In: Nambiar, R., Poess, M. (eds.) Performance Evaluation, Measurement and Characterization of Complex Systems, pp. 85–92. Springer, Berlin (2011)

    Chapter  Google Scholar 

  58. Intel: Intel xeon® phi\(^{{\rm TM}}\) processor 7285 16gb, 1.3 ghz, 68 core. https://ark.intel.com/fr/products/128691/Intel-Xeon-Phi-Processor-7285-16GB-1-3-GHz-68-Core- (2019). Accessed 18 Jan 2019

  59. Chaudhuri, S., Narasayya, V., Ramamurthy, R.: Estimating progress of execution for SQL queries, In: Proceedings of the 2004 ACM SIGMOD International Conference on Management of Data, SER, pp. 803–814. SIGMOD ’04. ACM, New York, NY, USA. https://doi.org/10.1145/1007568.1007659 (2004)

  60. Gounaris, A., Sakellariou, R., Paton, N.W., Fernandes, A.A.A.: A novel approach to resource scheduling for parallel query processing on computational grids. Distrib. Parallel Databases 19(2), 87–106 (2006). https://doi.org/10.1007/s10619-006-8490-2

    Article  Google Scholar 

  61. Özsu, M.T., Valduriez, P.: Distributed and parallel database systems. ACM Comput. Surv. 28(1), 125–128 (1996). https://doi.org/10.1145/234313.234368

    Article  Google Scholar 

  62. Bouganim, L., Florescu, D., Valduriez, P.: Dynamic load balancing in hierarchical parallel database systems, In: VLDB’96, Proceedings of 22th International Conference on Very Large Data Bases, pp. 436–447. Mumbai (Bombay), India, 3–6 Sept. http://www.vldb.org/conf/1996/P436.PDF (1996)

  63. Hong, W., Stonebraker, M.: Optimization of parallel query execution plans in XPRS,” In: Proceedings of the PDIS, IEEE Computer Society, pp. 218–225 (1991)

  64. Gawade, M.M., Kersten, M.L.: Adaptive query parallelization in multi-core column stores, In: Proceedings of the International Conference on Extending Database Technology. (2016)

  65. Freedman. C.: Parallel query execution in SQL server. Microsoft SQL Server Query Team. https://docplayer.net/36329779-Parallel-query-execution-in-sql-server-craig-freedman-software-design-engineer-sql-server-query-team.html (2019). Accessed 2 Feb 2019

  66. Djahida, B., Hidouci, K., Bellatreche, L.: OLAPS: online load-balancing in range-partitioned main memory database with approximate partition statistics. Comput. Sci. Inf. Syst. 15(2), 393–419 (2018)

    Article  Google Scholar 

  67. Mackert, L.F., Lohman, G.M.: “R* optimizer validation and performance evaluation for distributed queries, In: Proceedings of the 12th International Conference on Very Large Data Bases, ser. VLDB ’86, pp. 149–159. Morgan Kaufmann Publishers Inc., San Francisco, CA, USA. http://dl.acm.org/citation.cfm?id=645913.671480 (1986)

  68. Ouared, A., Ouhammou, Y., Bellatreche, L.: Qosmos: Qos metrics management tool suite. Comput. Lang. Syst. Struct. 54, 06 (2018)

    Google Scholar 

  69. Dayarathna, M., Wen, Y., Fan, R.: Data center energy consumption modeling: a survey. IEEE Commun. Surv. Tutor. 18(1), 732–794 (2016). Firstquarter

    Article  Google Scholar 

  70. Roukh, A.: Prise en compte de l’énergie dans la phase d’exploitation des bases de données volumineuses. Ph.D. dissertation (2017)

  71. Rawlings, J.: Applied regression analysis: a research tool, ser. Wadsworth & Brooks/Cole statistics/probability series. Wadsworth & Brooks/Cole Advanced Books & Software. (1988)

  72. Yasar, E., Erdogan, Y., Güneyli, H.: Determination of the thermal conductivity from physico-mechanical properties. Bull. Eng. Geol. Environ. 67, 219–225 (2008). 05

    Article  Google Scholar 

  73. Ahn, K.U., Park, C.S.: Artificial neural network models for building energy prediction. In: Proceedings of the 2017 Winter Simulation Conference, ser. WSC ’17, pp. 219:1–219:9. IEEE Press, Piscataway, NJ, USA. (2017)

  74. Kalogirou, S.A.: Artificial intelligence for the modeling and control of combustion processes: a review. Prog. Energy Combust. Sci. 29(6), 515–566 (2003)

    Article  Google Scholar 

  75. Günther, F., Fritsch, S.: neuralnet: training of neural networks. R J. 2, 06 (2010)

    Article  Google Scholar 

  76. Neural networks in excel. https://help.xlstat.com/customer/en/portal/articles/2910259-neural-networks-in-excel?b_id=9283. Accessed 15 Jan 2019

  77. Training of neural networks. https://cran.r-project.org/web/packages/neuralnet/neuralnet.pdf. Accessed 15 Jan 2019

Download references

Author information

Authors and Affiliations

  1. LIAS/ISAE-ENSMA - University of Poitiers, Chasseneuil, France

    Simon Pierre Dembele & Ladjel Bellatreche

  2. University of Houston, Houston, USA

    Carlos Ordonez

  3. University of Mostaganem, Mostaganem, Algeria

    Amine Roukh

Authors
  1. Simon Pierre Dembele
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Ladjel Bellatreche
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Carlos Ordonez
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Amine Roukh
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Ladjel Bellatreche.

Additional information

Publisher's Note

Springer Nature remains neutral with regard to jurisdictional claims in published maps and institutional affiliations.

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Dembele, S.P., Bellatreche, L., Ordonez, C. et al. Think big, start small: a good initiative to design green query optimizers. Cluster Comput 23, 2323–2345 (2020). https://doi.org/10.1007/s10586-019-03005-0

Download citation

  • Received:

  • Revised:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s10586-019-03005-0

Keywords

Search

Navigation