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

Authors: Ming Tang; Lincheng Qi; Sibo Bi; Xinyun Cheng; Shijie Zhang

Addresses: Information Transportation Inspection Center, State Grid Jiangsu Electric Power Company Information and Telecommunication Branch, Nanjing, 21000, Jiangsu, China ' Jiangsu Electric Power Information Technology Co., Ltd., Nanjing, 210000, China ' Information Transportation Inspection Center, State Grid Jiangsu Electric Power Company Information and Telecommunication Branch, Nanjing, 21000, Jiangsu, China ' Information Transportation Inspection Center, State Grid Jiangsu Electric Power Company Information and Telecommunication Branch, Nanjing, 21000, Jiangsu, China ' Jiangsu Electric Power Information Technology Co., Ltd., Nanjing, 210000, China

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.

DOI: 10.1504/IJDS.2024.142817

International Journal of Data Science, 2024 Vol.9 No.3/4, pp.222 - 238

Received: 17 Apr 2024
Accepted: 29 Jun 2024
Published online: 23 Nov 2024 *

Full-text access for editors Full-text access for subscribers Purchase this article Comment on this article