How to handle repeated many inserts in Spring Boot with MySQL?

I am currently building an application which constantly receives XML messages via socket.io

And as soon as my application gets XML message, it parses XML into Java entity object and save it

in to MySQL Database.

Current spec of my server is as below

CPU: 24 cores RAM: 16 GB STORAGE: 128GB(SSD)

The problem is, no matter how much I refactor my codes or implement batch insert,

There occurs delay among transactions, which leads to memory leak...

Below are my part of my codes and application.yml settings.

Any suggestion would be grateful for me.

thanks

@Async
@EventListener(ApplicationReadyEvent.class)
public void listenInPlay() {
    if(allow) {
        try {
            IO.Options options = new IO.Options();
            options.transports = new String[]{"websocket"};
            options.reconnectionAttempts = 2;
            options.reconnectionDelay = 1000;
            options.timeout = 500;

            String id = getHashByIp("ibin" + myIpAddress + "ibin");

            final Socket socket = IO.socket(url + ":" + inPlayPort + "?userId=" + id, options);
            socket.on("push_data_event", objects -> runRunnable(objects[0].toString()));
            socket.connect();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

this part initiates socket client, and as the message is received, it's passed to handling function.

public void handleMessage(String rawLSportsMessage) {
    try{
        //System.out.println("InPlayMessage:----------------\n" + rawLSportsMessage);
        LSportsMessage obj = xmlMapper.readValue(rawLSportsMessage, LSportsMessage.class);
        lSportsMessages.add(obj);
        if(lSportsMessages.size() >= 100){
            insertMessage(lSportsMessages);
            lSportsMessages = new ArrayList<>();
        }
    }catch(Exception e){
        e.printStackTrace();
    }
}

this part is where I parse and stack messages in an Array in order to batch insert.

if the length of Array exceeds 100, the batch insert happens.

lSportsMessageRepository.saveAll(objs);

In this part I batch insert messages by saveAll function in JpaRepository.

Finally below is my application.yml settings

spring:
 datasource:
   url: jdbc:mysql://localhost:3306/dbname?serverTimezone=UTC&characterEncoding=UTF-8&autoReconnect=true&reWriteBatchedInserts=true
   username: user
   password: pswd
   hikari:
     maximum-pool-size: 1000
     #max-lifetime: 1000

 jpa:
   properties:
     hibernate:
       jdbc:
         batch-size: 100
         lob:
           non_contextual_creation: true

   hibernate:
     ddl-auto: update
   generate-ddl: true


Read more here: https://stackoverflow.com/questions/64397927/how-to-handle-repeated-many-inserts-in-spring-boot-with-mysql

Content Attribution

This content was originally published by Kigael Lee at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: