Database Connection Pooling in MuleSoft with HikariCP

Published in

Another Integration Blog

7 min readDec 1, 2023

Why is Database Connection Pooling Essential?

When developing a small demo app or proof of concept, implementing database connection pooling may seem unnecessary. However, for production applications, especially those handling substantial data volumes and running concurrently with other instances, Database Connection Pooling emerges as a crucial technique. It enhances application performance, efficiency, and scalability by managing a pool of reusable connections to a database, effectively addressing challenges in connection management within dynamic and resource-intensive environments.

Benefits of Database Connection Pooling:

1 — Resource Efficiency

Establishing and closing database connections can be resource-intensive. Connection pooling minimizes this overhead by maintaining a pool of pre-established connections.

2 — Improved Response Time

Opening a new database connection for each request can introduce delays. Connection pooling enables the reuse of existing connections, significantly improving the response time of database operations.

3 — Preventing Connection Leaks

Improperly closed connections can lead to leaks, tying up resources. Connection pooling frameworks often include mechanisms to detect and handle connection leaks, preventing long-term resource issues.

So, let’s see how we can set up a connection pooling configuration in MuleSoft apps for mySql and PostgreSQL Databases!

Setting Up Connection Pooling in MuleSoft for MySQL and PostgreSQL Databases

By default, the MuleSoft Database Connector does not use connection pooling. However, you can enable it with a single line of code (or visual configuration). Add a db:pooling-profile setting for maxPoolSize and minPoolSize in the database configurations:

<db:mysql-config name="dbConfig" host="localhost" port="3306" user="root"
   password="" database="esb" doc:name="MySQL Configuration">
   <db:pooling-profile maxPoolSize="17" minPoolSize="13" />
</db:mysql-config>

What does it do? The db:pooling profile uses an open-sourced JDBC Connection Pool library named c3p0 to do this job. It is a mature, highly concurrent JDBC Connection pooling library, with support for caching and reuse of PreparedStatements. So, you gain a lot for free, right on default MuleSoft installation.

Make things faster — Use HikariCP library:

There are a few JDBC Connection Pool Libraries, among them HikariCP stands out for its lightweight architecture and exceptional performance. A benchmark of several libraries set outstanding results for HikariCP, as pictured below:

Sounds like a perfect option. Let’s see how to use it in our MuleSoft apps!

How to setup HikariCP in MuleSoft

Now, it is time to get our hands a little dirty! Let’s create a simple demo app that will exemplify how to set upHikariCP to create two connection pools to perform simple queries in two databases (MySQL and PostgreSQL).

0 — Setup

Install MySQL and MySQLWorkbench and sample database: employees.
Install PostgreSQL and pgAdmin and sample database: dvdrental.
Install the latest version of Anypoint Studio.

1 — Create a new MuleSoft project

Name: connection-pooling-with-hikari-lib

2 — Add dependencies

Spring Framework: The easiest way to add Spring dependencies, is by dragging inside a flow of the Spring Authorization Filter Processor. Doing that will trigger Anypoint Studio to add the necessary Spring Framework dependencies in pom.xml, and download all files. When this is completed, you can remove the authorization filter processor.

MySQL:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

PostgreSQL:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.0</version>
</dependency>

3 — Create flows

Create a Mule configuration file named: db-pooling-mysql.xml
Create a Mule configuration file named: db-pooling-postgree.xml
In each of the XML above create a flow, add a Scheduler, a DatabaseSelect, and a logger as in the example below:

3 — Create beans.xml file

Create a beans.xml file under src/main/resources

<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"xsi:schemaLocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
      http://www.springframework.org/schema/jdbc
      http://www.springframework.org/schema/jdbc/spring-jdbc-4.2.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-4.2.xsd
      http://www.springframework.org/schema/security
      http://www.springframework.org/schema/security/spring-security-4.2.xsd">
 
<bean
  id="mysql-employees-data-source"
  class="com.zaxxer.hikari.HikariDataSource">
     <property
   name="driverClassName"
   value="com.mysql.cj.jdbc.Driver" />
  <property
   name="jdbcUrl"
   value="jdbc:mysql://localhost:3306/employees" />
  <property
   name="username"
   value="root" />
  <property
   name="password"
   value="your mysql user password here" />
  <property
   name="poolName"
   value="mainMySqlPool" />
  <property
   name="maximumPoolSize"
   value="50" />
  <property
   name="connectionTimeout"
   value="10000" />
 </bean>
 <bean
  id="postgree-dvdrental-data-source"
  class="com.zaxxer.hikari.HikariDataSource">
    <property
   name="driverClassName"
   value="org.postgresql.Driver" />
  <property
   name="jdbcUrl"
   value="jdbc:postgresql://localhost:5432/dvdrental" />
  <property
   name="username"
   value="postgres" />
  <property
   name="password"
   value="your postgre user password here" />
  <property
   name="poolName"
   value="mainPostgrePool" />
  <property
   name="maximumPoolSize"
   value="50" />
  <property
   name="connectionTimeout"
   value="10000" />
 </bean>
</beans>

HikariCP flexibility

For simplicity in keeping the objective of this post, which is to show how to set up the HikariCPconfiguration in MuleSoft, we are just setting the poolName, maximumPoolSize, and connectionTimeout properties.

The real power and flexibility of Connection Pooling resides in measuring your app requirements, queries, database capacity, and network latency and setting the connection pooling properties accordingly.

For example, after the first round of stress testing of your application. You may realize you will need to start the appwith 20 database connections pre-created in the pool and allow a maximum of 100 of maximum connections in the pool.

A list of the properties frequently used for the HikariCP includes autoCommit, idleTimeout, keepaliveTime, maxLifetime, maximumPoolSize, metricRegistry, and more.

4 — Create global.xml file

Create a mule configuration file named global.xml. Add in this file a Spring Config that refers to the beans.xml file we created in the previous step, and two database configurations as below:

<spring:config name="springConfig" files="beans.xml" />

        <db:config
		name="postgree-dvdrental-data-source-config"
		doc:name="Database Config"
		doc:id="da543a4b-9486-4ca5-a094-39520a8b0f79">
		<db:data-source-connection
			dataSourceRef="postgree-dvdrental-data-source" />
	</db:config>
	<db:config
		name="mysql-employees-data-source-config"
		doc:name="Database Config"
		doc:id="c0e88c49-b9f9-4481-b7f4-2750f466c780">
		<db:data-source-connection
			dataSourceRef="mysql-employees-data-source" />
	</db:config>

5 — Configure the Database Select

In MySQL Mule flow:

In PostgreSQL Mule flow:

6 — Test the connections

Add in the Message property of the payload of each flow:

output application/json
---
payload

Run the application.

You will see the following log that confirms that the connection pool is created and active.

Starting Bean: org.mule.runtime.module.extension.internal.runtime.config.StaticConfigurationProvider-postgree-dvdrental-data-source-config
com.zaxxer.hikari.HikariDataSource: mainPostgrePool - Starting...
com.zaxxer.hikari.HikariDataSource: mainPostgrePool - Start completed.

Starting Bean: org.mule.runtime.module.extension.internal.runtime.config.StaticConfigurationProvider-mysql-employees-data-source-config
com.zaxxer.hikari.HikariDataSource: mainMySqlPool - Starting...
com.zaxxer.hikari.HikariDataSource: mainMySqlPool - Start completed.

At that stage, it would also be a good idea to confirm that everything is working by checking the results of your database queries:

LoggerMessageProcessor: [
  {
    "dept_no": "d009",
    "dept_name": "Customer Service"
  },
  {
    "dept_no": "d005",
    "dept_name": "Development"
  },
  {
    "dept_no": "d002",
    "dept_name": "Finance"
  },
  {
    "dept_no": "d003",
    "dept_name": "Human Resources"
  },
  {
    "dept_no": "d001",
    "dept_name": "Marketing"
  }
]

LoggerMessageProcessor: [
  {
    "description": "A Fateful Reflection of a Moose And a Husband who must Overcome a Monkey in Nigeria",
    "film_id": 133,
    "title": "Chamber Italian"
  },
  {
    "description": "An Epic Drama of a Cat And an Explorer who must Redeem a Moose in Australia",
    "film_id": 384,
    "title": "Grosse Wonderful"
  },
  {
    "description": "An Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India",
    "film_id": 8,
    "title": "Airport Pollock"
  },
  {
    "description": "A Fateful Yarn of a Lumberjack And a Feminist who must Conquer a Student in A Jet Boat",
    "film_id": 98,
    "title": "Bright Encounters"
  },
  {
    "description": "A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies",
    "film_id": 1,
    "title": "Academy Dinosaur"
  }
]

Why HikariCP in MuleSoft

Performance at Scale: HikariCP’s sophisticated connection pool management algorithms ensure that connections are readily available, drastically reducing the overhead associated with connection establishment.

Resource Efficiency: Connection pooling conserves resources by reusing existing connections, preventing the resource exhaustion that accompanies a continuous influx of new connections.

Built-in Connection Leak Detection: HikariCP comes with robust mechanisms to detect and mitigate connection leaks, proactively addressing potential issues before they impact performance.

Configurability for Optimal Performance: Fine-tune connection pool settings according to the specific needs of your MuleSoft application. Adjust parameters like maximum pool size, connection timeout, and idle connection timeout to optimize performance under diverse workloads.

Conclusion

In the ever-evolving landscape of application development, where databases are the lifeblood of functionality, performance optimization is non-negotiable. The integration of MuleSoft and HikariCP, facilitated by connection pooling, not only mitigates the challenges posed by inefficient connection management but also positions your application as a scalable, responsive, and resource-efficient powerhouse.

Happy coding!

The demo source code is availablehere.