Why Do We Need DB Connection Pools?
Typical data-driven web apps need a Database (DB) connection to render almost every page or to serve every API call. For scalability reasons, Web Server and Database servers are hosted on different Machines. In such scenario, obtaining a new DB connection is a potentially expensive task. This process of acquiring a new DB connection increases the latency of your pages and makes your web app slow.
DB Connection Pools Solve This Problem
In Java web applications DB connection pools are provided as libraries. The DB connection pool manager keeps a number of DB connections open in memory. When the data access code closes a connection, the connection pool manager doesn’t close the connection, instead, it returns it to the pool. When the next call requests a connection, it returns an unused connection from the pool, rather than creating a new one. This improves the latency of your application.
DB Connection Pool Configuration
Apart from essential connection properties such as DB username, password, host, port, etc., a Database connection pool typically has following 3 properties:
- Minimum Number of Connections: This is the number of connections the pool keeps in memory as soon as it is created. It will try to ensure that at least these many connections are available to your application.
- Maximum Number of Connections: This is the maximum number of connections the DB connection will keep in memory. Once your application reaches this numbers, further calls to obtain connections must wait till previous calls are done.
- Max Idle Number of Connections: As specified before, the connection pool keeps connections in memory. But if it finds that some connections are idle and it already has more than minimum number of connections then it tries to close these idle connections, so that some other program can use them.
Database Max Connections Setting
Most of the Database Servers have a configuration setting called as max connections. Since a DB server requires a specific amount of RAM to maintain each connection, the DB admins set the max connection setting as per the hardware available to the DB server.
Usually, on production grade Database servers, this setting should be at least 100 connections, but in the days of virtualized hardware, it might be much lower than 100. You might come across such situation on cheap PaaS DB server hosting plans.
You need to keep in mind that when you connect to the DB server from a tool like PgAdmin or MySQL WorkBench, it also takes up 1 connection.
If your DB server finds that the number of connected clients has reached to this max connections setting, it starts to reject new connections. Sometimes, if the server is low on RAM, it might not be able to provide all the connections as specified in the max connection settings configuration parameter. So this possibility also should be taken into consideration, if your DB server starts throwing errors for new connections.
Precautions to Take While Configuring Connection Pools
Avoid using the unnecessarily large number for max connections setting in the connection pool configuration. In general, the number of max connections should be equal to the number of requests you expect the web server to perform each second. This will depend on how heavy your database logic is.
For a new deployment, something around 15 as the value of max connections should be OK. After that, based on the load, you can change this setting accordingly.
When you have multiple web servers serving behind a load balancer, then you must ensure that sum of max connections configuration property of all web servers should be less than max connections configuration property of the DB server.
If you are using auto-scaling, then you should assume, the maximum number of servers are in operation and calculate your required total number of connections accordingly.
If you have multiple WAR files on the same web app server, and all of those connect to the same DB server, then you need to consider summation of max connections required by all the WAR files.
Conclusion
Connection pools, if used properly can improve speed and latency of your web application. You must ensure that connection pool configuration is done in such a way that you never exceed the number of max connections as allowed by the DB server.
You should ensure that your connection pool is destroyed properly when your web application stops or restarts. This way, it will release all the connections which can be made available by the DB server to other applications.
Armed with this knowledge, you should be able to configure connection pools with confidence for your web application.
Author's Bio:
Pritam Barhate, with an experience of 14+ years in technology, heads Technology Innovation at Mobisoft Infotech. He has a rich experience in design and development. He has been a consultant for a variety of industries and startups. At Mobisoft Infotech, he primarily focuses on technology resources and develops the most advanced solutions.
