Database connection pool

Almost all business applications have a lot of database access, and typically these applications use database connection pooling. Understanding why connection pooling is needed, how it is implemented, system architecture, and performance goals can help you write correct, efficient applications. These concepts can be used to configure system operation parameters and are useful for understanding concurrent and distributed processing.

Generally speaking, an experienced engineer will try to answer three questions to any question: why, what, and how. Understanding why you can understand the real purpose of a question can help open your mind and avoid futility. What it is answers the essential concept of the question, is the guarantee of the correct answer. How to do it gives repeatable problem solving ideas, so that problems are always solved in a correct and efficient way. This article follows this idea to solve the problem of how to configure database connection pool.

1 Why is a connection pool required

Any database access requires the establishment of a database connection first. It’s a complicated, slow process. It involves a series of tasks such as communication establishment (including TCP three-way handshake), authentication, authorization, resource initialization and allocation. Also, the database server is usually separate from the application server, and all operations are distributed network requests and processing. Establishing a database connection usually takes 100ms or longer. Generally, CRUD database operations for small data are ms level or less, with network latency generally 10 to 50 ms to complete most database processing results. Some database connections are established in advance when the application is launched, and the application can greatly improve the response time by using the existing connections. In addition, Web services applications with many clients, many threads, too many connections, and frequent connection creation/deletion can also affect database performance.

To sum up, using a database connection has the following benefits:

• Saves time on creating a database connection, which is often much longer than processing data access requests.
• Unified management of database connection requests avoids performance problems caused by too many connections or frequent connection creation/deletion.
• Monitoring the health and error reporting of database connections reduces this part of the application service code.
• Can check and report errors that do not close database connections, help operations monitor database access blocking, and help programmers write correct database access code.

2 What is a database connection pool

2.1 Implementation Principles

Like most distributed infrastructure components, the principle of connection pool is relatively simple, but it involves different characteristics of database, operating system, programming language, operation and maintenance, and application scenarios, and the specific implementation is relatively complicated. There is still room for improvement after half a century of widespread demand for databases.

In principle, a set of database connections is created at the beginning of the application. You can also create connections dynamically but reuse existing ones. These connections are stored in a shared resource data structure called a connection pool. This is the classic producer-consumer concurrency model. Each thread borrows a connection when it needs to access the database, and releases the connection back to the pool for other threads to use. A good thread pool artifact has two parameters that dynamically control the size of the thread pool: minimum and maximum. Minimum number means keeping a minimum number of database connections available for emergencies, even when the load is light. When the number of threads accessing the database at the same time exceeds the minimum, more connections are dynamically created. The maximum number of connections is the maximum number of database connections allowed. When the maximum number of connections are in use and new threads need to access the database, the new threads are blocked until connections are released back into the pool. When the load becomes low and the number of connections in the pool exceeds the minimum number and only connections less than or equal to the minimum number are used, the connections above the minimum number are closed and deleted to save system resources.

In the practical application of connection pool, the most worrying problem is the character problem of borrowing and not paying back, which makes other people have no resources to use. An error in the coding logic or the release of the connection-release code that is not in the finally section can cause the connection pool to run out of resources and cause the system to slow down or even completely block. Similar to memory leaks, this condition, also known as connection leaks, is a common and difficult problem to detect. Therefore, detecting connection leaks and reporting them is a basic requirement for thread pool implementations.

The connection runs in the borrowed thread when it is used, not in the new thread. But because each connection is used with a timeout mechanism, The official Java, SQL Connection. SetNetworkTimeout API interface definition is setNetworkTimeoutExecutor executor, int milliseconds). You need to specify a thread pool to handle error reports of timeouts. That is, every time a connection runs a database access, a background thread monitors the response timeout status. Many connection pooling implementations use Cached Thread pools or Fixed Thread pools. Chached Thread Pool has no limit on the number of threads and is dynamically created and recycled, suitable for short and dynamic applications. Fixed Thread Pool is suitable for more Fixed connection requests.

In addition, network failures and specific database implementation limitations can invalidate connection pool connections. For example, MySQL allows a connection, whether in a healthy state or not, to last no more than 8 hours. Therefore, although connections run in the calling thread when they are in use, connection pool management usually requires one or more background threads to manage, maintain, and monitor the pool’s connection state and ensure that a specified number of connections are available.

As you can see, although database connections use the caller’s thread to perform database access, connection pooling implementations typically require two or more thread pools for administration and timeout handling. Of course, there are a lot of details to consider in connection pooling, but it doesn’t directly affect the application interface, which will be discussed at the end of this article.

2.2 System architecture of database connection pool

The essence of a connection pool is a counting Semaphore belonging to an operating system process that controls the number of threads that can use database connections in parallel. The Java SDK has a Semaphore Class that can be used to manage a limited number of resources. The core management function of connection pooling is to allocate a database connection from the pool to the thread that needs it, and then reclaim the connection to the pool when the thread runs out. Because the connection pool is limited, the maximum number of threads that can access the database in parallel is the maximum size of the connection pool. If you take into account the possibility that one application thread may use multiple database connections, the number of threads that can concurrently access the database is even smaller.

The consumers of connection pooling are business applications. There are usually two types: one is HTTP service threads based on user/service requests, usually using thread pools. Characteristic is the thread number dynamic change is very big, the database access mode is more diverse, processing time also has long have short, may have very big difference. The other is the background service, its thread number is relatively fixed, database access mode and processing time is relatively stable.

Connection pooling only provides established connections to business applications, and all access requests are forwarded to the background database server through connections. Database servers also typically use a pool of threads (one process per PostgreSQL connection) to handle all access requests.

Specifically, a connection pool is an intermediate channel between two thread pools. It can be viewed as the following structure:

One or more application server processes (inside thread pool < – > database connection pool) < = = = = = = = = = = = = = = = > a database server thread pool (or process)

In the figure above, the connection pool and the application service line thread pool are in the same process. Each application server process that accesses the database has its own thread pool and corresponding database connection pool. The database server may need to process database connection pool data access requests within multiple application service processes from one or more servers.

3 How do I configure a database connection pool

3.1 Configuration Objectives

When it comes to configuring database connection pools, a common and serious mistake is to confuse connection pools with thread pools. As shown in the system architecture above, the database connection pool does not control the size of the thread pool on the application side and database side. Moreover, the configuration of each database connection pool is specific to its own application server process, limiting the number of parallel threads that can access the database within the same process. The application server process manages its own thread pool separately, handling other business logic besides database access, and the number of parallel threads basically depends on the load of the service. When the application server thread needs to access the database, its concurrency and number of blocks are affected by the size of the connection pool.

As a bridge between application services and databases, the goal of connection pool parameter configuration is global optimization. There are four specific optimization objectives: to meet the concurrent database access of application services as far as possible, do not let the database server overload, can find the deadlock caused by not using, do not waste system resources.

The idea behind concurrent database access for all applications is simple: all threads that need to access the database can get the connections they need. If a thread uses more than one connection, the number of connections required also multiplies. At this point, the maximum connection pool size required should be the maximum number of concurrent database access threads multiplied by the number of connections required per thread.

Not overloading the database server is a global consideration. There may be multiple pools of connection requests from multiple application servers at the same time. According to PostgreSQL V11 documentation 18.4.3. Resource Limits, each connection is handled by a separate process. Every process, even if idle, consumes a lot of system resources such as memory, semaphore, file/network handles, queues, etc. This article Number Of Database Connections discusses the Number Of PostgreSQL V9.2 Connections. The recommended formula is ((core_count * 2) + effectiVE_SPINDLE_COUNT), which is two times the number of CPU cores plus the number of disk axes. MySQL uses a different service architecture. The default number of connections given by MySQL Too Many Connections is 151. These two systems from the concrete implementation mechanism, calculation method and suggested value are very different, as an application programmer should have a basic understanding.

This OLTP Performance — Concurrent Mid-Tier Connections video is simulated with an application service thread pool. The application server thread pool has 9600 threads constantly accessing the database. When the size of the connection pool is 2048 and 1024, the database is overloaded, there are many waiting events for the database, and the CPU utilization of the database is up to 95%. When the connection pool is reduced to 96, the database server does not wait for events, the CPU utilization is 20%, the database access request wait time is reduced from 33ms to 1ms, and the database SQL execution time is reduced from 77ms to 2ms. Overall database access response time decreased from 100ms to 3ms. In the case of one application service thread pool versus one database service thread pool, the database processing performance of 96 connection pools far exceeds the performance of 1000 connection pools. The database server needs to allocate resources for each connection.

Being able to detect the blocking caused by not using it again is also a basic requirement for choosing a connection pooling implementation. Application errors may result in borrowing but not returning the application. Repeated application failures may result in a long wait or even deadlock when the connection pool is used up. You need a timeout mechanism for connection borrowing, which depends on the application.

Not wasting system resources Means that an excessively large connection pool wastes system resources, such as memory, network ports, and synchronization signals, on application servers. At the same time, restart and operation of the thread pool will be slow. However, the overhead of application connection pooling is not very high and the waste of resources is usually not a big problem.

3.2 Configuration Methods

Once the concept is clear and the goal is clear, configuring the method is relatively easy. Connection pooling needs to consider two constraints: the size constraint of the two-end thread (process) pool and the application throughput constraint. It would be reasonable to combine the results of the two methods.

Two-end constraint: Find the maximum value of two ends, where the smaller value is the connection pool upper limit. Application service thread pool size. For example, the maximum Tomcat thread pool size is 200 by default. If only one database connection is used per thread, the maximum number of connection pools should be less than or equal to 200. If some requests use more than one connection, add it appropriately. If the maximum size of the database thread (process) pool is 151, take the smaller of the two values (200, 151), then the maximum connection pool size should be less than or equal to 151. If there are other connection pools, consider them globally. This value is the upper limit of the connection pool.

Application load constraint: Consider the load nature of application services. Application services can be divided into two categories. One is a Web application service thread pool that varies greatly in number, so the connection pool can also be configured dynamically, with corresponding minimum and maximum values. Another category is fixed-load business applications such as mail services, which can be configured with a fixed-size process pool. Both types of applications can be estimated in terms of database access complexity and response time. Little’s Law is used here: Concurrency = requests per second * database request response time. Note: The request response time here includes network time + database access time. Many times network time is greater than database access time. If an application thread has multiple database access requests, especially when something is being processed, the database request response time is actually the time to hold the connection. The formula becomes: Number of concurrent (connections) : Number of requests per second (QPS) * Database connection hold time.

If there are 100 database access requests per second and each database access request takes 20ms, then the parallelism is 100 * 0.02 = 2 and 2 concurrent database connections are sufficient. Similarly, if each request takes 100ms, then 10 concurrent connections are required.

Considering only the size of the two-end thread (process) pool would configure too large a pool of connections because this is the upper limit of the system. Because database access is only part of the work of the application thread. The reason is that in the formula for calculating the number of threads: number of threads = number of CPU cores * CPU utilization * (1 + wait time/CPU calculation time), the database wait time is only a part of the wait time of all thread operations.

Just configuring the minimum and maximum number of connections is just the beginning. Depending on the implementation, you also need to configure other parameters such as the connection life cycle, connection timeout, pending connections, and health monitoring. For details, see the connection pool usage document.

3.3 a seemingly related but actually irrelevant calculation formula

Because connection pools and thread pools are often confused, it is worth introducing another oft-mentioned but unrelated formula for counting threads. This formula comes from Java Concurrency in Practice, which every Java programmer should read. In section 8.2, page 171 of the original work, the author gives the famous formula for calculating the number of threads: number of threads = number of CPU cores * CPU utilization * (1 + wait time/CPU calculation time). This formula takes into account different processing modes that are computationally intensive (computation time) and I/O intensive (wait time). The specific technique and Script for calculating CPU usage of the process are given. However, this formula can be used to estimate the size of the application service thread pool or any thread pool, but not the size of the database connection pool. Because process pooling does not control the number of threads for application services, it controls the number of concurrent database access threads. These threads use database connections to complete asynchronous operations on network services and remote databases with little use of native CPU computing time. Applying the formula gives you a very large number, which is meaningless.

4 Connection pool configuration for Spring + MySQL applications

As mentioned above, configuring Spring connection pooling starts with the thread pool configuration for the HTTP service it uses and the connection count configuration for the back-end database server. The second is the characteristics of the application.

4.1 Number of application service threads

Spring’s server.tomcat.max-threads parameter specifies the maximum number of parallel threads. The default is 200. Because of special processing, these threads can handle a larger number of HTTP connections server.tomcat.max-connections, . The default value is 10000. Spring. Task execution. The pool. The Max – threads is controlled using the @ Async maximum number of threads, there is no limit to the default values. It is best to configure a range based on application characteristics.

4.2 Number of database connections

The MySQL database uses the max_connections environment variable to set the maximum number of connections. The default value is 151. Most recommendations are based on memory size or application load.

4.3 Setting Basic Parameters

Spring uses HikariCP by default.

Set the following basic parameters.

• MaximumPoolSize: indicates the maximum number of connections. Exceeding this number, new database access threads will be blocked. The default value is 10. Views
• MinimumIdle: The minimum number of connections. The default value is the maximum number of connections.
• LeakDetectionThreshold: The connection alarm time has not been returned. The default value is 0. If this value is greater than 0, a log alert (warn log information) will be generated if a connection is used for longer than this value. This can be set to three or five times the maximum database request length, depending on network load. Without this alarm, the correctness of the program is difficult to guarantee.
• MaxLifetime: indicates the maximum lifetime of a connection. The default value is 30 minutes. The official documentation recommends setting this value to be slightly less than the maximum connection lifetime of the database. The default value of MySQL is 8 hours. You can set it to 7 hours 59 minutes to avoid reconnecting every half hour.

4.4 Database Optimization Settings

HikariCP recommends the following MySQL configuration parameters and recommended values to help improve database access performance. The default values for these parameters are in the MySQL JDBC documentation

• PrepStmtCacheSize: 250-500. Default: 25.
• PrepStmtCacheSqlLimit: 2048. Default: 256.
• CachePrepStmts: true. Default: false.
• UseServerPrepStmts: true. Default: false

4.5 Adjustment to the forecast of future business development

So far, all our calculations and configurations are based on the existing hardware and software configurations and the scale of the business. As the business is constantly developing, we should also consider the future development of the business. For example, suppose that after a year, our business volume has doubled, then we can accordingly double our fixed calculation (provided that the machine does not reach the upper limit). This part is related to business estimates. The best advice is to configure the machine at the upper limit from the beginning and monitor all aspects of business performance in real time to scale horizontally when the existing machine fails. (If dynamic scaling is used here, you can rest assured that manual adjustment is not required).

Five other

5.1 Other implementation details of connection Pool

There are many application details to consider in connection pooling implementation.

• Transaction processing is also involved in the use of database connections, and Spring’s synchronous database access uses ThreadLocal to hold transaction processing related state. So connection pooling must perform database access on the caller’s thread and cannot run on a new thread. Spring asynchronous database access can be cross-threaded.
• Redundant connections are not closed immediately. Instead, they are closed after a period of idle time.
• Connections have a maximum lifetime limit, and the database automatically closes connections that exceed the lifetime even if the connection pool does not care. In MySql, the maximum lifetime of a connection is 8 hours. The connection pool needs to be monitored periodically to clean up invalid connections.
• Connection pooling needs to periodically check database availability and even response times to report health status in a timely manner. HikariCP Dropwizard HealthChecks is an example.
• When a connection needs to be created for new thread access, the new thread should wait for the first available connection in the pool and not for the thread created because of it. HikariCP’s document Welcome to the Jungle describes the benefits of this implementation: it avoids creating many unnecessary connections and provides better performance. Hikari handled 50 sudden short-time database access requests with five connections, both improving response time and avoiding the creation of additional connections.
• All kinds of database exception processing. Bad Behavior: Handling Database Down Different connection pool components implement different ways of Handling timeout blocking. Many connection pool artifacts are not handled correctly.
• Thread pool performance monitoring. HikariCP Dropwizard Metrics shows the performance Metrics to monitor.
• Thread blocking mechanisms and related data structures have a significant impact on connection pool performance. Down the Rabbit Hole shows optimization methods in Java. The downside is that some of the optimizations are trivial, making the code obscure and requiring extra maintenance.

5.2 Some Settings Refer to default Settings

HikariCP: DEFAULT_POOL_SIZE = 10

DBCP: Max pool size : 8

c3p0: MIN_POOL_SIZE = 3, MAX_POOL_SIZE = 15

JIRA Tuning database connections: pool-max-size = 20. Unlike the first three, this is a database application. The number of connections to the database can be hundreds of parallel connections. On the other hand, the number of connections to the application server is relatively expensive. It is recommended to set the number as small as possible.

5.3 digression

Online search a lot, did not think of such a simple database connection pool configuration problem should not be more comprehensive, clear documentation. A lot of people confuse connection pools with thread pools. Even HikariCP programmers used the wrong number of thread pools when initializing connection pools. The overhead of creating a thread pool is mainly the delay of network and remote database service requests, with little CPU cost. According to the thread calculation formula, the thread pool can be large. But the HikariCP programmers still only use the number of threads runtime.geTruntime ().availableProcessors() to create the pool. The correct number should be the minimum number of connection pools configured so that there is no waste (when the number of connections is smaller than the number of CPU cores) and best performance (when the number of connections is larger than the number of CPU cores). See this Issue: Change the thread pool size to minimumIdle on blocked initialization. This error is not surprising because HikariCP has a bad code style. A lot of widely used open source software actually has low quality code. Everyone should get to the bottom of the concepts and problems, understand others’ ideas, but be skeptical and think for themselves.