DB_MAX_CONNECTION_SIZE Optimization

Overview

This guide explains how to optimize the Database Connection Pool Size used in QueryPie MySQL connection settings.

QueryPie MySQL Configuration

QueryPie Server operates by configuring Meta DB, Snapshot DB, and Log DB in MySQL Database.

Meta DB: Data for features provided by QueryPie Web Console, such as user accounts, connection settings, and access control policies, are stored in Meta DB.
Snapshot DB: A temporary data storage used to record Audit Log during SQL query execution.
Log DB: A data storage that stores audit logs (Audit Log) such as user access records and SQL execution records.

QueryPie Server has components that use the Spring framework, and these components configure a Database Connection Pool to store data in QueryPie MySQL.

Setting Database Connection Pool Size

You can set the Database Connection Pool Size using environment variables when running QueryPie Server Container.

`DB_MAX_CONNECTION_SIZE`

Specifies the DB Connection Pool Size for Meta DB. The default value is 20.

`LOG_DB_MAX_CONNECTION_SIZE`

Specifies the DB Connection Pool Size for Log DB. The default value is the value set in DB_MAX_CONNECTION_SIZE. If LOG_DB_MAX_CONNECTION_SIZE is not set separately, the same value as DB_MAX_CONNECTION_SIZE will be set.

The Connection Pool Size for Snapshot DB does not require user control, so we do not provide an environment variable to control this value.

General User Usage and Load Cases

We recommend using the default value of 20 for DB_MAX_CONNECTION_SIZE. This is a Connection Pool Size that can be used in various general cases. We recommend this default value as a figure that can provide sufficient processing performance not only in PoC environments but also in general Production environments.

High Usage and High Load Cases

We recommend setting DB_MAX_CONNECTION_SIZE to a value between 20 and 40. Within an appropriate range, as DB_MAX_CONNECTION_SIZE increases, the amount of load that QueryPie can process simultaneously increases.

If the DB server has high processing performance and many CPUs, increase DB_MAX_CONNECTION_SIZE. If the Linux VM where Server Container runs has high processing performance and many CPUs, increase DB_MAX_CONNECTION_SIZE. If the number of Server Containers increases, decrease DB_MAX_CONNECTION_SIZE.

`DB_MAX_CONNECTION_SIZE` Setting Value Summary Table

DB_MAX_CONNECTION_SIZE	Description
40	Suitable when MySQL DB has high throughput and high-spec hardware processing performance. Using a setting value higher than 40 is not recommended.
30	Suitable when MySQL DB has high throughput and high-spec hardware processing performance.
20	Recommended value suitable for production environments. This is a setting value that can handle many requests universally.
10	Suitable when you want to reduce MySQL connections in production environments.
5	Suitable for PoC functional testing and production environments with few users.

Relationship Formula Between `DB_MAX_CONNECTION_SIZE` and Hardware Capacity

Through internal performance optimization tests by the development team, we derived that there is the following relationship formula between DB_MAX_CONNECTION_SIZE and the hardware processing capacity of QueryPie Server VM and DB. By referring to this relationship formula, you can set the DB_MAX_CONNECTION_SIZE value to obtain optimal processing performance while setting the smallest possible DB_MAX_CONNECTION_SIZE value.

When QueryPie MySQL is Separately Configured

This is the case where QueryPie Server Container and QueryPie MySQL are not run together in one Linux VM, but a separate MySQL Instance is configured using a separate VM or Cloud Service. This is recommended for Production environments.

DB_MAX_CONNECTION_SIZE = minimum ( Application_CPU_Count / Application_Node_Count, RDS_CPU_Count ) * 10

Application_CPU_Count - Number of vCPUs of the Linux VM where QueryPie Server Container runs
Application_Node_Count - Number of QueryPie Server Containers, or number of Linux VMs running, or number of Kubernetes Pods.
- For dualization configuration, it is 2, and for triple configuration, it is 3.
RDS_CPU_Count - Number of vCPUs of the VM Instance where separately configured QueryPie MySQL runs
minimum ( a, b, c, … ) - Selects the smallest value among multiple values separated by ,.

Example 1) The Linux VM where Server Container runs has 4 vCPUs, and 2 Linux VMs are operated. AWS Aurora MySQL has 4 vCPUs.


minimum ( Application_CPU_Count / Application_Node_Count, RDS_CPU_Count ) * 10
= minimum ( 4 / 2, 4 ) * 10
= 2 * 10
= 20

Example 2) The Linux VM where Server Container runs has 8 vCPUs, and 3 Linux VMs are operated. AWS Aurora MySQL has 8 vCPUs.


minimum ( Application_CPU_Count / Application_Node_Count, RDS_CPU_Count ) * 10
= minimum ( 8 / 3, 8 ) * 10
= 2.66 * 10
= 27

Single Linux VM Configuration

This is the case where QueryPie Server Container and QueryPie MySQL are run together in one Linux VM. This is suitable for PoC environments.

DB_MAX_CONNECTION_SIZE = Application_CPU_Count * 2.5

Application_CPU_Count - Number of vCPUs of the Linux VM where QueryPie Server Container runs

Example 1) The Linux VM where Server Container runs has 4 vCPUs, and MySQL is run together. In this case, it is appropriate to set DB_MAX_CONNECTION_SIZE to 10.


Application_CPU_Count * 2.5
= 4 * 2.5
= 10

Allowing Sufficient Connections in QueryPie MySQL Settings

QueryPie Server connects to QueryPie MySQL using a Connection Pool. If MySQL Database Server does not allow sufficient connections, QueryPie Server cannot create connections to MySQL, causing malfunction. This problem occurs when the number of DB connections required by QueryPie Server is greater than the number of connections allowed by QueryPie MySQL.

Symptoms of Not Being Able to Create DB Connections

You will encounter the error message [API] Could not open JPA EntityManager for transaction in the Web Console.

Solution 1) Increase the Number of Connections Allowed by QueryPie MySQL

You can apply settings to increase two setting values to 500 or more. This allows you to set the number of connections allowed by QueryPie MySQL to be greater than the number of DB connections required by QueryPie Server.

MAX_USER_CONNECTIONS
- https://dev.mysql.com/doc/refman/8.0/en/user-resources.html
- ALTER USER 'querypie'@'%' WITH MAX_USER_CONNECTIONS 500;
max_connections
- https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connections

Solution 2) Reduce DB_MAX_CONNECTION_SIZE

If the number of QueryPie users is small and the usage load is low, you can reduce the DB_MAX_CONNECTION_SIZE setting value. This allows you to set the number of DB connections required by QueryPie Server to be smaller than the number of connections allowed by QueryPie MySQL.

Set DB_MAX_CONNECTION_SIZE to 10 or 5 instead of the default value of 20. For PoC environments, even if you set DB_MAX_CONNECTION_SIZE to 5, you can perform general functional tests smoothly.