Posted on

Understanding the Database Connection Pool (DBCP) Properties

Recently, I faced an issue related to a very high load on the database layer. The database was having too many connections in parallel. I had to review my application’s database connection pool (DBCP) properties very closely. Since I was dealing with legacy code, I needed to understand the value assigned to each property and also analyze whether it is relevant for the present-day load or not. As I started looking at the properties, their values, and the consequent implications, I was able to find a decent explanation in the tomcat documentation. However, I wasn’t able to immediately map each property to the scenario where it will be used.

Since we were using Apache tomcat’s JDBC connection pool, I started reading the source code to get a better understanding. I was able to get a lot of clarity by going through the ConnectionPool class. As I didn’t find any easy resource to understand the same, I am summarizing my understanding in the form of simple flowcharts. I hope this will help others in a similar situation.

Posted on

Debugging Heavy Load on Oracle Databases

A lot of enterprises rely on the Oracle database for their data layer. Although the licenses are costly, Oracle provides a proven product in terms of performance and scalability and very good support, so many people find it to be a good trade-off. However, every product will have certain limits. If Oracle is being used to serve data by busy applications, the number of parallel database connections will often cause bottlenecks. This can lead to high CPU usage on the Oracle side. It can also starve other applications from getting connections, leading to functional issues. So it becomes critical for developers to understand not just the number of connections their applications are consuming but also how effectively they are being used.

Before we dive in, there is one prerequisite for the below analysis to work. We need to ensure that an appropriate value is set for the module attribute. One of the ways to do it is during the connection creation. The application can set the initSql attribute to call dbms_application_info.set_module('<module-name>','<action-name>') . This will help us map the database connection to a certain application while looking at the oracle database.

Posted on

Setting Advanced Options of SQL Connection Pools

In this article, we will cover the details of how Zato SQL connection pools can be configured to take advantage of features and options specific to a particular driver or to the SQLAlchemy library.

SQL Connection Pools

First, let’s review the basic Zato Dashboard form that creates a new SQL connection pool.