How to make insert individual rows into MySQL table faster?

Question

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

Please provide the generated SQL. I, for one, cannot keep up with the hundred frameworks that hide MySQL from the developer. — Rick James, Dec 12 '21 at 21:04

score 0 · Answer 1 · answered Dec 12 '21 at 05:46

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:

Connecting: (3)
Sending query to server: (2)
Parsing query: (2)
Inserting row: (1 × size of row)
Inserting indexes: (1 × number of indexes)
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

I am thankful for your time. However my issue is not yet resolved, I guess. 1. Regarding JPA Repository, I am ok with a select query as it hardly takes few ms. 2. My particular implementation inserts one row by one. I am not sure how it can do bulk inserts. For eg: the API requests can come at different times or multiple requests at one time. Can you help me in showing how i can do bulk inserts in this scenario? The Mysql instance setting is onething I can explore. Let me do it — IT guy, Dec 12 '21 at 09:39
A simpler approach to thinking about batching of inserts: `INSERTing` 100 rows at a time is 10 times as fast as 100 single-row `INSERTs`. (I am dubious about the 100x claim.) — Rick James, Dec 12 '21 at 21:06
if really needed you should probably change your approch if performance is really very important for you. Instead of saving log by doing individual saveAndFlush, you can keep in memory a certain amount of log (in a java collection like List) and save this List at a time. or somethink like that — soung, Dec 12 '21 at 22:46

score 0 · Answer 2 · answered Dec 12 '21 at 21:03

0

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.

answered Dec 12 '21 at 21:03

Rick James

135,179
13
127
222

How to make insert individual rows into MySQL table faster?

2 Answers2