Comparison and database performance optimisation strategies based on NSGA-II genetic algorithm: MySQL and OpenGauss

Ming Tang, Lincheng Qi, Sibo Bi, Xinyun Cheng and Shijie Zhang

International Journal of Data Science, 2024, vol. 9, issue 3/4, 222-238

Abstract: With the widespread application of databases in real-time environments, higher requirements are placed on their performance optimisation strategies. In response to the lack of dynamic adjustment and optimisation capabilities for real-time environmental changes in database performance optimisation strategies, as well as poor query throughput and response time performance, this paper adopted Non-dominated Sorting Genetic Algorithm II (NSGA-II) to study performance optimisation of My Structured Query Language (MySQL) and OpenGauss databases. Firstly, it defined three objective functions and the corresponding constraints for the response time of the database query, the performance of the query, and the utilisation of the query resource, and calculated the fitness of each individual and the distance between the layers. Then, the tournament rotation method can be used to output parents with high fitness, and the crossover and mutation probabilities can be set. Finally, the optimal parameter configuration of the database can be output. The experiment was based on the TPC-DS dataset (transaction processing performance council decision support benchmark) and compared the performance of MySQL and OpenGauss databases under different parameter configurations. The experimental results show that after optimisation by the NSGA-II genetic algorithm, MySQL and OpenGauss databases have certain improvements in query throughput, query response time, and query resource utilisation. Moreover, the optimisation effect on the MySQL database was as high as 90.30%, which is more significant than that on the OpenGauss database.

Keywords: database performance optimisation; MySQL and OpenGauss; NSGA-II; Non-dominated Sorting Genetic Algorithm II; query response time; dynamic adjustment capability; resource utilisation. (search for similar items in EconPapers)
Date: 2024
References: Add references at CitEc
Citations:

Downloads: (external link)
http://www.inderscience.com/link.php?id=142817 (text/html)
Access to full text is restricted to subscribers.

Related works:
This item may be available elsewhere in EconPapers: Search for items with the same title.

Export reference: BibTeX RIS (EndNote, ProCite, RefMan) HTML/Text

Persistent link: https://EconPapers.repec.org/RePEc:ids:ijdsci:v:9:y:2024:i:3/4:p:222-238

Access Statistics for this article

More articles in International Journal of Data Science from Inderscience Enterprises Ltd
Bibliographic data for series maintained by Sarah Parker ().