Summary
The provided content discusses the reasons for using JDBC connection pooling and its benefits when managing database connections from applications with variable user loads.
Reported Issue
A user is trying to connect with JDBC via Connection pooling. Connection pooling is recommended for performance enhancement and limiting the load on the database being queried.
Overview
Quoting the book JAVA Persistence with Hibernate
There are three reasons for using a pool:
Acquiring a new connection is expensive. Some database management systems even start a completely new server process for each connection.
Maintaining many idle connections is expensive for a database management system, and the pool can optimize the usage of idle connections (or disconnect if there are no requests).
Creating prepared statements is also expensive for some drivers, and the connection pool can cache statements for a connections across requests.
Although not mentioned in the quote above, there is a fourth reason, which is to control the load on the database by limiting the maximum amount of concurrent work that an application sends to it.
JDBC connection pooling allows you to control access to the database by processing all of an application's database activity over a finite number of connections (the connection pool) to the database.
Managing the number of database connections can control the load on the Dremio system from a system with a variable number of users—for example, a website with a variable load. Utilizing connection pooling, we can condense all those requests to (for example) three connections and queue the remaining requests in the application. The subsequent requests are pending waiting to get a database connection.
JDBC Connection pooling does not improve the performance of any particular statement but permits the application to manage the number of concurrent connections to the database. For example, if there is a spike in user requests, only a specific number of requests will flow to the database; the others are queued, waiting for a connection.
JDBC connection pooling allows an application that uses a JDBC driver to perform a specific number of database requests in parallel. Multiple Java packages offer JDBC Connection pooling (Hikari Pool, C3P0, etc.)
Relevant Versions Tools and Integrations
All versions
Integration of JDBC connection and a connection pool
Steps to Resolve
n/a
Common Challenges
Avoiding expensive connection acquisition, optimizing idle connections, and caching prepared statements.
Controlling the load on the database by limiting the maximum concurrent work sent from the application.
Performance improvement through cashing
Additional Resources
https://docs.dremio.com/current/sonar/client-applications/drivers/jdbc/
https://github.com/brettwooldridge/HikariCP
https://github.com/rcprcp/dremioconnectionpoolingexample