DEV Community: Kamal

Configuring Connection Pooling with Spring R2DBC

Kamal — Tue, 04 Jan 2022 15:04:07 +0000

This article is the second part of my first article related to Spring Boot R2DBC. If you haven't checked it out, please check out: Reactive Spring Boot Application with R2DBC and PostgreSQL

Connection Pooling

After successfully configuring your Spring Boot with Spring Data R2DBC, you probably want to launch it to production without any performance hiccup. Unfortunately, it's not as straightforward as you would like. You will need to use connection pooling.

Connection pooling is a way to store and reuse a connection to be reused again later, avoiding the expensive cost of creating a connection for each use.

Before we implement connection pooling, let's try to do some basic benchmarks to see whether or not it improves our application performance.

Playing Around with R2DBC Pooling Performance

There are multiple ways to load test our application, you can even use Apache Benchmark directly from your terminal with the ab command. But this time, I'd like to explore a tool called K6.

Load Testing with K6

We're testing an API that will insert a hundred record into our database. This is going to be a write-heavy operation to stress the database.

Without Connection Pooling

Without connection pooling, we only get 140 requests completed, with the 95th percentile of 2.33s.

I also noticed that the docker instance responsible for running the database CPU usage spiked dramatically.

Implementing Connection Pooling in Spring Boot R2DBC

In Spring Boot application that use blocking connection to DB such as JDBC connection pooling is usually handled by a popular library called HikariCP. Luckily, Spring Data R2DBC already includes connection pooling option that we can use just by enabling it from our properties.

To enable it, you can add below properties to your application.properties file and R2DBC will be able to pool the connections to our database! Easy right?



spring.r2dbc.pool.enabled=true
spring.r2dbc.pool.initial-size=50
spring.r2dbc.pool.max-size=100

As you can probably guess, these properties will initiate the pool with 50 connections ready to use, and when needed it can scale up to maximum of 100 connections. Now then, let's try our benchmark again.

With Connection Pooling

The result is striking. With connection pooling, we completed 480 iterations of the requests, increasing throughput by almost 3.5x and reducing our 95th percentile latency to 668ms, that's an almost 4x latency improvement! When we take a look at the docker instance, the CPU usage also stays comfy at around 76% usage. Enabling connection pooling improves the performance by almost 4x and reduced our database load significantly.

Connection Pool Sizing

After seeing an awesome performance improvement above, you might be tempted to try and increase the connection pool even further. After all, if we can get 4x improvement with 100 connection pool, then we should get even better performance with 200 connection pool, right?

Sorry to burst the bubble but the short answer is no. If you'd like to learn further about connection pool sizing, please refer to this awesome article on pool sizing by the author of HikariCP himself: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

Conclusion

We have learned how easy it is to enable connection pooling on an R2DBC project and its impact on your application performance. So don't forget to set it up whenever you're configuring your application!

As usual, you can see the source code for the project at: https://github.com/kamalhm/spring-boot-r2dbc

This article is the second part of my first article related to Spring Boot R2DBC. If you haven't checked it out, please check out: Reactive Spring Boot Application with R2DBC and PostgreSQL

Connection Pooling

Connection pooling is a way to store and reuse a connection to be reused again later, avoiding the expensive cost of creating a connection for each use.

Before we implement connection pooling, let's try to do some basic benchmarks to see whether or not it improves our application performance.

Playing Around with R2DBC Pooling Performance

There are multiple ways to load test our application, you can even use Apache Benchmark directly from your terminal with the ab command. But this time, I'd like to explore a tool called K6.

Load Testing with K6

We're testing an API that will insert a hundred record into our database. This is going to be a write-heavy operation to stress the database.

Without Connection Pooling

Without connection pooling, we only get 140 requests completed, with the 95th percentile of 2.33s.

I also noticed that the docker instance responsible for running the database CPU usage spiked dramatically.

Implementing Connection Pooling in Spring Boot R2DBC

To enable it, you can add below properties to your application.properties file and R2DBC will be able to pool the connections to our database! Easy right?



spring.r2dbc.pool.enabled=true
spring.r2dbc.pool.initial-size=50
spring.r2dbc.pool.max-size=100

With Connection Pooling

Connection Pool Sizing

Conclusion

As usual, you can see the source code for the project at: https://github.com/kamalhm/spring-boot-r2dbc

Reactive Spring Boot Application with R2DBC and PostgreSQL

Kamal — Fri, 18 Jun 2021 14:00:11 +0000

Reactive Spring Boot Application with R2DBC and PostgreSQL

Originally published on Medium

Also available on my blog

Reactive Application

Reactive application is getting more popular with the rise of microservice architecture. To utilize the full potential of a reactive system, it is recommended to make all of our systems reactive. However, making a fully reactive application is still quite a challenge in the JVM world, because JDBC (Java Database Connectivity) is a synchronous, and blocking API for connecting to relational databases, which most applications use to store their data.

To address this issue, Pivotal (the company behind the Spring framework) led a community effort to create an asynchronous way of connecting to the database. This project is now called R2DBC (Reactive Relational Database Connectivity). After the R2DBC initiatives, the Spring team decided to support R2DBC in the Spring ecosystem, and thus Spring Data R2DBC was born.

Project Setup

Let’s have a look at how we can create a fully reactive application using Spring Boot and Spring Data R2DBC. We can scaffold the project using the handy Spring Initializr, we will use Java 8, Maven, and Jar as our packaging.

Three dependencies must be included:

Spring Data R2DBC
PostgreSQL Driver
Spring Reactive Web

Lombok and Spring Boot DevTools are not mandatory, but they do help ease a lot of pain when developing Spring applications. If you don’t know what are their use case, I’d highly recommend taking a look at their documentation.

After opening the project, we will be greeted by the usual Spring Boot main class like this



@SpringBootApplication
public class ReactivePostgresApplication {

  public static void main(String[] args) {
    SpringApplication.run(ReactivePostgresApplication.class, args);
  }
}

To start, we will create our POJO class representing a very simple Member



@Value
@Builder
public class Member {
  @Id
  private Long id;
  private String name;
}

The @Value and @builder is an annotation from Lombok, they generate a setter and getter, and a bunch of other things to make our code less stuffed with boilerplate code.

R2DBC Repository

Creating a repository is also straightforward, still very familiar with what we used to do in other Spring Data frameworks, in this example we will extend our interface with R2dbcRepository.



public interface MemberRepository extends R2dbcRepository<Member, Long> {
  Mono<Member> findByName(String name);
}

On line 2, we create a custom method that will query the member data by name. If you want to see a snippet of what this repository could do, you can check this documentation for more information. In this R2DBC repository, the custom method is not returning Member directly, but it’s wrapped in Mono. Every method in this repository will use either Mono or Flux. This is in line with what the Project Reactor uses, so we have full compatibility with reactive API.

Not only R2dbcRepository, in Spring Data R2DBC we have the option to extends the interface with one of these two options:

ReactiveCrudRepository, for your generic CRUD repository and,
ReactiveSortingRepository for an additional sorting function.

You can choose any of these options, depending on your use case.

Next, let’s have a look at how we can implement a reactive controller



@RestController
@RequestMapping(value = "/api/member")
@RequiredArgsConstructor
public class MemberController {

  private final MemberRepository memberRepository;

  @GetMapping
  public Flux<Member> getAll() {
    return memberRepository.findAll();
  }

  @GetMapping(value = "/{name}")
  public Mono<Member> getOne(@PathVariable String name) {
    return memberRepository.findByName(name);
  }
}

For this example, we’ll create 2 separate endpoints that will use Mono and Flux:

On method getAll, we use the repository that we created earlier to get all the data in the database, because we will get more than 1 data, the returned data is wrapped in Flux.
On method getOne, we request for a single piece of data, for this case we wrap the returned data in Mono.

We’re almost done now. Now that we have set up the API, all we need to do is connect the application to our database. To do this, we’ll need to add these two properties in our application.properties file.



spring.r2dbc.url=r2dbc:postgresql://postgres@localhost:5432/reactive
logging.level.org.springframework.r2dbc=DEBUG

The first line will tell spring to connect to a local database with a database name of reactive with r2dbc as its connection protocol.

The second line is a debugging properties so we can see exactly the queries that are running inside our application.

Next, we need to create a schema.sql file to initialize our database table, we can do that using this query:



CREATE TABLE IF NOT EXISTS member (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

We’re saving this inside our resources folder.

One last step, because Spring Data R2DBC doesn’t support auto initializing the database with our schema, we have to manually execute this query when we’re starting our application, this is one of the ways to do it:

We can create a bean on our main application to execute schema.sql whenever we run our application.



@Bean
ConnectionFactoryInitializer initializer(@Qualifier("connectionFactory") ConnectionFactory connectionFactory) {
  ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
  initializer.setConnectionFactory(connectionFactory);
  ResourceDatabasePopulator resource =
      new ResourceDatabasePopulator(new ClassPathResource("schema.sql"));
  initializer.setDatabasePopulator(resource);
  return initializer;
}

Now we can finally run our application. When we see into the log, we can see that the application executed our schema.sql.



202-01-07 12:33:49.037 DEBUG 37404 --- [actor-tcp-nio-1] o.s.r2dbc.connection.init.ScriptUtils    : Executing SQL script from class path resource [schema.sql]
2021-01-07 12:33:49.088 DEBUG 37404 --- [actor-tcp-nio-1] o.s.r2dbc.connection.init.ScriptUtils    : 0 returned as update count for SQL: CREATE TABLE IF NOT EXISTS member ( id SERIAL PRIMARY KEY, name TEXT NOT NULL )

From our example above, we created 2 GET endpoints:

/api/member to get all of our member’s data
/api/member/{name} to get a specific member’s data

When we hit the endpoint of our API to get all member on localhost:8080/api/member we can see that the R2DBC executed this SQL



2021-01-07 12:35:21.823 DEBUG 37404 --- [ctor-http-nio-3] o.s.r2dbc.core.DefaultDatabaseClient     : Executing SQL statement [SELECT member.* FROM member]

and when we do a request on localhost:8080/api/member/{memberName}, the app shows that we’re executing a query to get a single record.



2021-01-07 12:39:24.956 DEBUG 37404 --- [ctor-http-nio-3] o.s.r2dbc.core.DefaultDatabaseClient     : Executing SQL statement [SELECT member.id, member.name FROM member WHERE member.name = $1]

At this point, we have successfully created a fully reactive application with Spring Data R2DBC, with PostgreSQL as our relational database.

Conclusion

Creating a fully reactive Spring application is easier with the help of Spring Data R2DBC. The process is not too different than what we’re used to and we can still use the same Repository that we use in a non-reactive database. It is not as smooth as it seems though, this is a very basic example application, not a production scale application with all its complex requirements. So, depending on your use case, you might not be able to use Spring Data R2DBC yet.

There is also a difference in how to fine-tune your database because we don’t use JDBC anymore, one of the examples is connection pooling. We might be familiar with HikariCP for our database connection pooling, but on the reactive side HikariCP is not available. To use connection pooling we can use R2DBC Pool instead. All in all, R2DBC is a promising technology, allowing us to have a fully reactive application.

Even though there are a lot of unknowns, it’s still worth exploring!

If you want to see the full source code, here’s the link to the repository
is a promising technology, allowing us to have a fully reactive application.

Even though there are a lot of unknowns, it’s still worth exploring!

If you want to see the full source code, here’s the link to the repository