Connection pooling 101 for Rocket® UniData® and Rocket® UniVerse
Interested in the basics of connection pooling: what is it, why you need it, and some best practices, then this blog post is for you!
What is connection pooling?
Connection pooling is a technique that allows multiple clients to send requests through an application server to a shared set or a pool of persistent database connections. Connection pooling is generally used to improve performance.
Once a connection is created, it’s placed in a pool and can be reused, eliminating the overhead of establishing a brand-new connection. If all the connections in a pool are being used, a new connection can be created and added to that pool.
Connection pooling is very often used with Web applications, but it could be used with any shared environment that requires many short-term connections to a data server.
Why would you use connection pooling or what do you gain from using it?
Every time a database connection is opened, it takes time on the server. In contrast, the amount of time users spend either getting data, or calling the subroutine, for example, is a shorter amount of time in comparison to that startup time.
Connection pooling limits the number of opens and closes of database connections and reduces the use of the resources on the server itself. As an example, if 10 connection pools replace 100 database sessions, that’s a savings of 90 sessions impacting RAM usage, CPU usage, database processes and caching – using connection pools results in big resource savings. Due to these resource savings, your application will become more scalable.
Connection pools provide flexibility by ramping database connections up or down based on the flow of requests from the application server.
How many connection pools do I need?
If response time is critical, then you would probably do better with more available connection pools so they will be ready if needed. For example, if at your application’s peak hour you expect to have 20 simultaneous requests going to the database, you would want to think about having 20 connection pools so that all requests could be serviced at the same time, to avoid queueing.
If response time is less critical and you know the configuration runs well with 10 simultaneous database requests, then you might just want 10 connection pools.
The required number of connection pools is application specific. Factors such as number of incoming requests, time to perform the requests on the data server, and Service Level Agreements will help in determining a range of connection pool requirements.
What else do you need to know about connection pools?
- A user is never guaranteed to get the same thread the next time he or she comes back because the app might have a pool of five or 10 connection pools waiting. With this in mind, consider setup and shutdown tasks such as setting up a common block. In short, you want to ensure that when a new user connects, there’s no residual information from previous connections.
- From an application design perspective, you want to access a resource from the pool as late as possible and release access from the pool as soon as possible, because the sooner that connection is releases, the sooner it gets put back in the pool for whoever else might need it.
- To be cost effective, it’s important to minimize the number of database accounts accessed by connection pools.
- For the purpose of troubleshooting, tracing and performance counters are provided on all our .NET APIs and providers.