Understanding Client Connections and How Connection Pooling Works in YugabyteDB YSQL
May 18, 2023
Client applications are usually backed by a database to store and retrieve data pertaining to the use case. These applications establish connections to the database to perform CRUD (Create, Read, Update, Delete) operations. These database connections consume memory and CPU resources. For a very small set of users, the application can afford to create a separate database connection for each operation and close it afterward. If the application’s user base grows significantly, this method will hinder scaling and become a bottleneck. Therefore, to design a distributed application that can scale, understanding how database connections and connection pooling works is critical.
In this blog post we will dive deep to understand client connections in YugabyteDB, how connection pooling helps, and in the process address many questions developers may have as they start designing an application’s interaction with the database.
What happens at connection creation time?
Each connection to YugabyteDB YSQL performs a sequence of events and activities before a query is executed.
The first connection to a node loads master data to build the relation cache init files on the node where the connection is established. The relation cache for subsequent connections to the same node is then loaded from the local init files. There are two types of data that are loaded into the relation cache, both of which are in the master leader before they are loaded into the individual node:
- Global database init file
- Database-specific init file
Each connection also loads the initial catalog cache from the master leader and predominantly is user objects. Not every user object metadata is loaded at the time the connection is created; the remaining catalog cache loading occurs during the first query execution on that node.
The catalog cache is refreshed automatically whenever there are changes in the database’s catalog information, such as new objects being created or modifications to existing ones. The connection detects these changes and handles them automatically. All this is transparent to the user.
What is the node resource usage at connection creation time?
Each cache on a YugabyteDB node consumes memory per connection. The amount varies based on the database’s metadata. Memory usage can be higher during cache load or reload, which also impacts the CPU resources of the node where the connection is established.
Catalog information is retrieved from the master. So every connection on the node that requests metadata information reaches the master node—consuming its CPU and disk resources. The connection start and reset requests are particularly impactful since they read larger amounts of data.
Retrieving relation cache and catalog cache information from the master introduces extra network hops and increased latency. This affects performance in multi-region clusters, especially when the master leader is located in a different region from the nodes receiving queries. To mitigate this, you can specify leader affinity to nodes in a single region.
Leader affinity can be set in via the yb-admin set_preferred_zones command or via the Yugabyte Anywhere control plane.
What happens when you submit a query on a connection?
YSQL uses a simple query protocol. When the query is sent to the backend, the entire execution of parse, rewrite, plan, and execute is done in one step. No interaction with the client is needed for intermediate phases. After the query executes, the result is sent back to the client.
When the database client is far away from the database nodes, it may result in increased latency for sending data. However, the actual query execution occurs on the server side.
How is the first query execution on a connection different from the subsequent ones on the same connection?
During connection creation, an initial catalog cache is built. However, this cache lacks information about user/non-catalog objects. Consequently, the first execution after establishing a connection will load additional catalog data from the master for execution. Once all the metadata is loaded, subsequent SQL executions will retrieve data from the tablets on the tablet servers. As a result, subsequent query executions after the first query will be faster.
In short, the first query execution on new connections experiences higher latency due to the metadata cache load into the catalog cache using RPC calls to the master server. Only after this process, the actual request is made to the tablet servers.
There are a few options to circumvent the impact of catalog cache load on the application p99 performance. Some of these are:
- Priming queries at connection setup time
- Executing Explain on frequently accessed tables
- Having a single long SQL query on frequently used tables by combining them with UNION ALL and limiting retrievals to one row.
Why use a connection pool in the application?
By utilizing a connection pool, you can create a set of physical connections from the client application to the database during application initialization. This enables the reuse of these connections for query executions, shifting the connection establishment and metadata retrieval phases to the connection pool startup instead of it occurring with every query execution.
So it’s important that your application pools its connections and reuses these; otherwise, a lot of time and energy goes into building and initiating the connections. If the application cannot have a connection pool, having an external pooler such as pg-bouncer or pg-cat is the next best thing.
How to size a connection pool?
The best way to size a connection pool is to make it as low as possible for the application volume and, if possible, using a fixed amount (min=max) of connections.
If the min pool size is not the same as the max, and if the database has to do a lot of work due to volume increase, the application has to start building new connections to service the queries. Every time the number of connections exceeds the minimum connection pool value, the entire metadata caching will apply to every newly created connection —potentially showing up as higher query latencies. New connections might also take longer to start up because the database is busy, causing the app to wait longer and show up as increased slowness in query latencies.
If the max pool size is set too small, your app will have to share a limited number of database connections for all requests. Consequently, new requests will need to wait until a previous request finishes before they can fetch a connection from the pool and reuse it.
If max pool size is set too high, you could overwhelm the database and/or overwhelm the application server and also have high overhead for setting up/tearing down connections. Eventually, the connections will be removed and freed, but setting them up takes resources and time. The point of the pool is to reuse those resources to reduce the overall cost and resource usage.
What is the difference between active and idle connections?
Many connection pools are huge, and once set up have very small amounts of active connections. An active connection is a connection that is in use by the application. An idle connection is a connection in the connection pool that is available for use.
Once a YSQL connection gets set up but not yet used for any query execution, it has a very small footprint and uses negligible CPU. A YSQL session that needs to perform work immediately after connecting will perform catalog cache metadata activities and allocate more memory. This is why an idle, never-used connection from a pool is different from an actively-used one. An idle connection, when picked up to service application needs, will involve catalog cache metadata activities on the first query execution.
Conclusion
A lot goes on behind the scenes when creating a database connection. It is important to understand it all during the design process so that the application will perform at optimal levels.
YugabyteDB provides a robust client connection and connection pooling mechanism to efficiently handle high-concurrency workloads, delivering a scalable and dependable solution for modern applications. Additionally, YugabyteDB is actively working on optimizing connection performance. Starting with release 2.16.0, YugabyteDB offers a t-server cache implementation that reduces the need to access the master leader for every new connection on a node, resulting in improved query latencies.
YugabyteDB has also developed smart drivers in Java, Go, Python, Node.js, and C#s for YSQL API which have additional configuration options to work with existing connection pooling frameworks like HikariCP and Tomcat. Details are located on our Documentations site.
