I have a server running Spring boot JPA Hibernate. I am using MySQL database (Using InnoDb engine by default). The implementation draws inspiration from many articles I had search on Internet. I have implemented REST API to facilitate building a website dynamically. I wanted to log all the API requests into a log (audit log). So when the API is called, I store the request method name and few parameters into auditlog table in MySql. Just before I return from the API, I store the response as well by updating the same record.

I was reviewing the code logs of Hibernate when I make API requests using the web application client as well as Postman. I noticed that for every API, it takes on an average 150ms - 200ms for inserts and updates. This is proving to be costly for APIs which fetch very less information.

So I want to know how I can speed up the inserts so that my inserts/updates take less than 10 -20 ms.

My Auditlog entity is

@Entity
@Table(name="auditlog") 

public class AuditLog{
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private Long id;

   @Column(nullable = false, updatable = false)
   @Temporal(TemporalType.TIMESTAMP)
   @CreatedDate
   private Date created_at;

   @Column(nullable = false)
   @Temporal(TemporalType.TIMESTAMP)
   @LastModifiedDate
   private Date updated_at;

   @NotBlank
   private String methodName;

   private String param1;

   // Text field with private information like password masked
   @Column(length = 65535, columnDefinition = "text")
   private String request;

   // Text field
   @Column(length = 65535, columnDefinition = "text")
   private String response;

   private Integer result;
   
   ... // getters and setters
}
 

My AuditLogRepository is :

public interface AuditLogRepository extends JpaRepository<AuditLog, Long>{
}

In my REST API controller I am doing the following

...
AuditLog logEntry = new AuditLog();
// set all the values except generated ones like id, created_at and updated_at
logEntry.setMethodName(...);
logEntry.setParam1(...);
logEntry.setRequest(...);

// Save into the table using autowired repoitory
auditLogRepoitory.saveAndFlush(logEntry);

// ... do the operation of the API

// Update the logEntry
logEntry.setResult(...);
logEntry.setResponse(...);
auditLogRepoitory.saveAndFlush(logEntry);
...

Please help me in improving the insert and updates to the table. Or please help in improving the code so that I can make APIs response faster.

Thanks, Sri Prad

CodePudding user response：

First tips

if you want to speed up insert/update don't user JpaRepository.save method (notice that saveAndFlush() internally calls save method).
Because JpaRepository.save internal select the entity in order to know if the entity is new or if it exists in database. Here is the default implementation of jpaRepository.save :

 @Transactional
  public <S extends T> S save(S entity) {
    Assert.notNull(entity, "Entity must not be null.");
    if (this.entityInformation.isNew(entity)) {
      this.em.persist(entity);
      return entity;
    } else {
      return this.em.merge(entity);
    }
  }

I think using jdbcTemplate is the best option.

Second tips

when thinking about optimizing the inserts, it is probably useful to think about doing bulk inserts. According to mysql documentation website , The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

1. Connecting: (3)
2. Sending query to server: (2)
3. Parsing query: (2)
4. Inserting row: (1 × size of row)
5. Inserting indexes: (1 × number of indexes)
6. Closing: (1)

So you can easily see how bulk insert can help you improve insert speed.

Third tips

You probably need to tune your mysql instance settings as explained in this stackeroverflow anwser

Others options

Make sur you have selected the right ID generation strategy as explained here https://dzone.com/articles/spring-boot-boost-jpa-bulk-insert-performance-by-100x

CodePudding user response：

If your framework allows for it, do

START TRANSACTION

at the beginning of building the page and storing the auditing. And

COMMIT

at the end.