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

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.