DEV Community: Amrut Prabhu

Spring Boot: JPA Bulk Insert Performance by 100 times.

Amrut Prabhu — Mon, 25 Jan 2021 22:46:12 +0000

So I was facing a problem where I wanted to insert millions of records into the database which needed to be imported from the file.

I did some research around this and I would like to share with you what I found which helped me improve the insert records throughput by nearly 100 times.

Initially when I was just trying to do bulk insert using spring JPA’s saveAll method, I was getting a performance of about 185 seconds per 10,000 records . After doing the following changes below, the performance to insert 10,000 records was just in 4.3 seconds .

Yes, 4.3 Seconds for 10k records.

So to achieve this, I had to change the way I was inserting data.

Change the number of records while inserting.

When I was inserting initially, I was pushing all the 10k records from the list directly by calling the saveAll method. I changed this to the batch size of 30. You could also increase the batch size to even 60, But it doesn’t half the time taken to insert records. (See the table below)

For this you need to set the hibernate property batch_size=30 .

spring.jpa.properties.hibernate.jdbc.batch_size=30

Then, I added the following connection string properties :

cachePrepStmts=true
useServerPrepStmts=true
rewriteBatchedStatements=true

e.g
jdbc:mysql://localhost:3306/BOOKS_DB?serverTimezone=UTC&cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true

2. Send Batched records

Changed the code for inserting, so that saveAll methods get batch sizes of 30 to insert as per what we also set in the properties file. A very crude implementation of something like this.

for (int i = 0; i < totalObjects; i = i + batchSize) {
    if( i+ batchSize > totalObjects){
        List<Book> books1 = books.subList(i, totalObjects - 1);
        repository.saveAll(books1);
        break;
    }
    List<Book> books1 = books.subList(i, i + batchSize);
    repository.saveAll(books1);
}

This reduced the time by a little, dropped from 185 secs to 153 Secs. Thats approximately 18% improvement.

3. Change the ID generation strategy.

This made a major impact.

Initially, I was using the @GeneratedValue annotation with strategy i.e GenerationType.IDENTITY on my entity class.

Hibernate has disabled batch update with this strategy, Because it has to make a select call to get the id from the database to insert each row. You can read more about it here

I changed the strategy to SEQUENCE and provided a sequence generator.

public class Book {
    @Id
    @GeneratedValue(strategy = SEQUENCE, generator = "seqGen")
    @SequenceGenerator(name = "seqGen", sequenceName = "seq", initialValue = 1)
    private Long id;

This drastically changed the insert performance as Hibernate was able to leverage bulk insert.

From the previous performance improvement of 153 secs, the time to insert 10k records reduced to only 9 secs. Thats an increase in performance by nearly 95%.

Note: MySQL doesn’t support creating sequences.

To get around this, I created a table with the name of the sequence having a single field called next_val. Then I added a single row with an initial value.

For the above sequence I created the following :

CREATE TABLE `seq` (
  `next_val` bigint(20) DEFAULT NULL
);
INSERT INTO `seq` (`next_val`) VALUES(1);

Hibernate then used this table as a sequence generator.

Next, I pushed it further to use higher batch sizes and I noticed that doubling the batch size does not double down on time. The time to insert only gradually reduces. You can see this bellow.

The most optimal batch size for my case was a 1000 which took around 4.39 secs for 10K records. After that, I saw the performance degrading as you can see in the graph.

Here are the stats I got.

As always you can find the code on my GitHub repo.

Spring Boot : Handle AWS RDS password change or rotation without restarting

Amrut Prabhu — Thu, 05 Nov 2020 20:48:16 +0000

This article is about how you can handle AWS RDS secrets rotation without restarting your Spring Boot application.

I had this problem wherein I had to update my database connection whenever the database password was updated for my AWS RDS instance. This can be because of a monthly password rotation policy or may be the database credentials got compromised and you want all your running applications to keep running even when the database password are changed.

To solve this kind of a problem, AWS provides a library that will handle this updating of the database connection without even restarting your Spring Boot application.

AWS has an open source library called AWS Secrets Manager JDBC , that handles database connection while your application is running and talking to the RDS instance.

Let's see how this works.

Firstly, Add the following dependency in the build file. Considering maven it would be as follows

<dependency>
    <groupId>com.amazonaws.secretsmanager</groupId>
    <artifactId>aws-secretsmanager-jdbc</artifactId>
    <version>1.0.5</version>
</dependency>

Next, specify the JDBC datasource URL with the scheme jdbc-secretsmanager instead of jdbc

spring:
  datasource:
    url: jdbc-secretsmanager:mysql://database-host:3306/rotate_db

Next, You need to specify the driver class name. For this article we will stick to MySQL RDS instance. So it’s going to be com.amazonaws.secretsmanager.sql.AWSSecretsManagerMySQLDrive.

This library also requires the database specific connection library. So you will need to add the MySQL connector library, Which is commonly the artifact mysql-connector-java. This will be used to make the actual connection with the database.

In case you are dealing with other databases you can find the corresponding drivers from the source code here

Next, Create an AWS secret for the RDS instance using the database credentials section in the AWS Secrets Manager.

Next, In the properties file application.yaml, specify the secret name you just created as the username and you don’t have to specify any password as it’s now stored in the secrets manager.

Your property file should look something like this.

spring:
  datasource:
    url: jdbc-secretsmanager:mysql://database-host:3306/rotate_db
    username: secret/rotation
    driver-class-name: com.amazonaws.secretsmanager.sql.AWSSecretsManagerMySQLDriver

Now, For the application to communicate with AWS and fetch the secret value, you would have to have AWS CLI setup and configured. Here is the link to it.

Once you have this in place, your application can connect to AWS by exporting the environment variable AWS_PROFILE with the profile you setup while configuring the AWS configuration.
With this, you are done with the changes.

Now start the application and it should be able to communicate with AWS Secrets Manager to fetch the credentials and start communicating with the AWS RDS instance.

You can test this by clicking on the rotate secret option in the secret which will generate a new password for database and check the communication with the database.

Here is a GitHub link to my implementation.

Bonus:

This also works if you have liquibase integration in place . You just have to specify the same URL in the liquibase configuration and the database secret as the username and the liquibase setup will work for you.

spring:
  datasource:
    url: jdbc-secretsmanager:mysql://database-host:3306/rotate_db
    username: secret/rotation
    driver-class-name: com.amazonaws.secretsmanager.sql.AWSSecretsManagerMySQLDriver

  liquibase:
    url: jdbc-secretsmanager:mysql://database-host:3306/rotate_db
    user: secret/rotation

Enjoy!! Have Fun!!