Evaluation of connection pool PgBouncer efficiency for optimizing relational database computing resources - A. S. Boronnikov; P. S. Tsyngalev; V. G. Ilyin; T. A. Demenkova (Российский технологический журнал)
Objectives. The aim of the research is to investigate the possibilities of using the PgBouncer connection pool with various configurations in modern database installations by conducting load testing with diverse real-world like scenarios, identifying critical metrics, obtaining testing results, and interpreting them in the form of graphs.Methods. The research utilized methods of experimentation, induction, testing, and statistical analysis.Results. The main features, architecture and modes of operation of the PgBouncer service are considered. Load testing was carried out on a virtual machine deployed on the basis of an open cloud platform with different configurations of computing resources (CPU, RAM) and according to several scenarios with different configurations and different numbers of balancer connections to the database, during which the following main indicators were investigated: distribution of processor usage, utilization of RAM, disk space, and CPU. The interpretation of the data obtained and the analysis of the results obtained by highlighting critical parameters are performed. On the basis of results analysis, conclusions and recommendations are formulated on the use of a connection balancer in real high-load installations for optimizing the resources utilized by the server on which the database management system (DBMS) is located. A conclusion is presented on the usefulness of using the PgBouncer query balancer along with proposed configuration options for subsequent use in real installations.Conclusions. The degree of influence of the use of the PgBouncer connection balancer on the performance of the system as a whole deployed in a virtualized environment is investigated. The results of the work showed that the use of PgBouncer allows significantly optimization of the computing resources of a computing node for a DBMS server, namely, load on the CPU decreased by 15%, RAM—by 25–50%, disk subsystem—by 20%, depending on the test scenarios, the number of connections to the database, and the configuration of the connection balancer.